Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ASP Newbie - SQL Query

Status
Not open for further replies.
bryandj23

bryandj23

IS-IT--Management
Joined
Aug 18, 2003
Messages
105
Location
US
Hi All.

I'm an ASP newb. I'm using Frontpage 2002 at the moment to connect to a SQL 2000 database. I'm able to execute the following command in SQL:

Select *
From calldata
Where (Extension= '129' AND Date >= DateAdd(Day, DateDiff(Day, 0, '6/18/2007'), -0)
and Date < DateAdd(Day,DateDiff(Day,0,'6/18/2007'),1))

The "Date" field is a DateTime datatype.
This command in SQL runs fine.

However, when I bring this over to FrontPage, and put in form field values, it's pukes:

Database Results Error
Unable to find operator in query string. Query string currently is SELECT * From merlin Where (Extension= ? AND Date >= DateAdd(Day, DateDiff(Day, 0, '::CallDate::'), -0) and Date < DateAdd(Day,DateDiff(Day,0,'::CallDate::'),1))

Notice it looks as if it's trying to pass the extension field (because it shows a ?), but it doesn't want to pass along the CallDate field.

I'm running out of places to bang my head around here. Can someone shed a little bit of light this way?

Thanks very much in advance.

 
Sort by date Sort by votes
Update: I typed the table names wrong in my post. The table names are correct in my queries.
 
Upvote 0 Downvote
Are you setting a connection string so that your file can communicate with SQL??

Please post all ASP code you have to run that query.

[monkey][snake] <.
 
Upvote 0 Downvote
Here's what FP has generated for me:
Code: 
<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Extension</title>
</head>

<body>

<form BOTID="0" METHOD="POST" ACTION="calldetail.asp">
  <table BORDER="0">
    <tr>
      <td><b>Extension</b></td>
      <td>
      <input TYPE="TEXT" NAME="Extension" VALUE="<%=Server.HtmlEncode(Request("Extension"))%>" size="20"></td>
    </tr>
    <tr>
      <td><b>CallDate</b></td>
      <td>
      <input TYPE="TEXT" NAME="CallDate" VALUE="<%=Server.HtmlEncode(Request("CallDate"))%>" size="20"></td>
    </tr>
  </table>
  <p><br>
  <input TYPE="Submit"><input TYPE="Reset"><!--webbot bot="SaveAsASP" clientside suggestedext="asp" preview=" " --></p>
  <p>&nbsp;</p>
</form>
<table width="100%" border="1">
  <thead>
    <tr>
      <td><b>id</b></td>
      <td><b>CallType</b></td>
      <td><b>Date</b></td>
      <td><b>CalledNumber</b></td>
      <td><b>CallTag</b></td>
      <td><b>Duration</b></td>
      <td><b>LineNum</b></td>
      <td><b>Extension</b></td>
      <td><b>Acct</b></td>
      <td><b>TalkTime</b></td>
    </tr>
  </thead>
  <tbody>
    <!--webbot bot="DatabaseRegionStart" s-columnnames="id,CallType,Date,CalledNumber,CallTag,Duration,LineNum,Extension,Acct,TalkTime" s-columntypes="3,200,135,200,200,200,3,3,200,200" s-dataconnection="Merlin" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="TRUE" s-recordsource s-displaycolumns="id,CallType,Date,CalledNumber,CallTag,Duration,LineNum,Extension,Acct,TalkTime" s-criteria s-order s-sql="SELECT *&lt;br&gt;From merlin&lt;br&gt;Where  (Extension= '::Extension::' AND Date &gt;= DateAdd(Day, DateDiff(Day, 0, '::CallDate::'), -0) &lt;br&gt;   and Date &lt; DateAdd(Day,DateDiff(Day,0,'::CallDate::'),1))" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="Extension=&amp;CallDate=&amp;CallDate=" s-norecordsfound="No records returned." i-maxrecords="0" i-groupsize="0" botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY" preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the start of a Database Results region. The page must be fetched from a web server with a web browser to display correctly; the current web is stored on your local disk or network.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;" startspan --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * From merlin Where  (Extension= '::Extension::' AND Date >= DateAdd(Day, DateDiff(Day, 0, '::CallDate::'), -0)     and Date < DateAdd(Day,DateDiff(Day,0,'::CallDate::'),1))"
fp_sDefault="Extension=&CallDate=&CallDate="
fp_sNoRecords="<tr><td colspan=10 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Merlin"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&id=3&CallType=200&Date=135&CalledNumber=200&CallTag=200&Duration=200&LineNum=3&Extension=3&Acct=200&TalkTime=200&"
fp_iDisplayCols=10
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="35683" --><tr>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="id,CallType,Date,CalledNumber,CallTag,Duration,LineNum,Extension,Acct,TalkTime" s-column="id" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;id&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"id")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="3421" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="id,CallType,Date,CalledNumber,CallTag,Duration,LineNum,Extension,Acct,TalkTime" s-column="CallType" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;CallType&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"CallType")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="14082" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="id,CallType,Date,CalledNumber,CallTag,Duration,LineNum,Extension,Acct,TalkTime" s-column="Date" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;Date&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"Date")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="3267" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="id,CallType,Date,CalledNumber,CallTag,Duration,LineNum,Extension,Acct,TalkTime" s-column="CalledNumber" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;CalledNumber&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"CalledNumber")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="29254" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="id,CallType,Date,CalledNumber,CallTag,Duration,LineNum,Extension,Acct,TalkTime" s-column="CallTag" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;CallTag&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"CallTag")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="11439" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="id,CallType,Date,CalledNumber,CallTag,Duration,LineNum,Extension,Acct,TalkTime" s-column="Duration" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;Duration&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"Duration")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="16962" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="id,CallType,Date,CalledNumber,CallTag,Duration,LineNum,Extension,Acct,TalkTime" s-column="LineNum" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;LineNum&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"LineNum")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="13259" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="id,CallType,Date,CalledNumber,CallTag,Duration,LineNum,Extension,Acct,TalkTime" s-column="Extension" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;Extension&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"Extension")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="21582" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="id,CallType,Date,CalledNumber,CallTag,Duration,LineNum,Extension,Acct,TalkTime" s-column="Acct" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;Acct&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"Acct")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="3344" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="id,CallType,Date,CalledNumber,CallTag,Duration,LineNum,Extension,Acct,TalkTime" s-column="TalkTime" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;TalkTime&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"TalkTime")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="14992" --></td>
    </tr>
    <!--webbot bot="DatabaseRegionEnd" b-tableformat="TRUE" b-menuformat="FALSE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside tag="TBODY" preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the end of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;" startspan --><!--#include file="_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="62730" --></tbody>
</table>

</body>

</html>
 
Upvote 0 Downvote
I believe your problem is that you are not running that page on a web server. (This is a guess)


Try viewing the page in a web browser and see what happens.

Frontpage scares me.

[monkey][snake] <.
 
Upvote 0 Downvote
I use FP just to sorta "get the hang of it". Since I virtually know nothing about ASP coding, I'm learning on the fly.

This page is on an ASP enabled web server. If I take the "DateofCall" text box out of the form, and out of the query, the page works correctly. Once I put in the Date variable, it all goes to hell.

The "Database results error" that was posted in my original post was taken from the page viewed over the web server in IE.

Basically, I need a form that asks for an extension number, and a date. Look for records in the database that match the date and extension number, and give 'em to me.

Maybe PHP is the way to go..
 
Upvote 0 Downvote
Your problem lies in the following bit of code (from what you posted above):
Code: 
<!--webbot bot="DatabaseRegionStart" s-columnnames="id,CallType,Date,CalledNumber,CallTag,Duration,LineNum,Extension,Acct,TalkTime" s-columntypes="3,200,135,200,200,200,3,3,200,200" s-dataconnection="Merlin" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="TRUE" s-recordsource s-displaycolumns="id,CallType,Date,CalledNumber,CallTag,Duration,LineNum,Extension,Acct,TalkTime" s-criteria s-order s-sql="SELECT *&lt;br&gt;From merlin&lt;br&gt;Where  (Extension= '[B][I]::Extension::[/I][/B]' AND Date &gt;= DateAdd(Day, DateDiff(Day, 0, '[B][I]::CallDate::[/I][/B]'), -0) &lt;br&gt;   and Date &lt; DateAdd(Day,DateDiff(Day,0,'[B][I]::CallDate::[/I][/B]'),1))" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="Extension=&amp;CallDate=&amp;CallDate=" s-norecordsfound="No records returned." i-maxrecords="0" i-groupsize="0" botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY" preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the start of a Database Results region. The page must be fetched from a web server with a web browser to display correctly; the current web is stored on your local disk or network.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;" startspan -->
What is happening is that you are passing it literals called "Extension" (you're not passing that variable, either) and "Calldate" instead of the variables themselves. Fix it like so (for the sake of simplicity, I'm assuming that the names are the same and that they are request variables from the form - you'll have to modify as needed):
Code: 
<!--webbot bot="DatabaseRegionStart" s-columnnames="id,CallType,Date,CalledNumber,CallTag,Duration,LineNum,Extension,Acct,TalkTime" s-columntypes="3,200,135,200,200,200,3,3,200,200" s-dataconnection="Merlin" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="TRUE" s-recordsource s-displaycolumns="id,CallType,Date,CalledNumber,CallTag,Duration,LineNum,Extension,Acct,TalkTime" s-criteria s-order s-sql="SELECT *&lt;br&gt;From merlin&lt;br&gt;Where  (Extension= '[COLOR=red]" & request.form("Extension") & "[/color]' AND Date &gt;= DateAdd(Day, DateDiff(Day, 0, '[COLOR=red]" & request.form("CallDate") & "[/color]'), -0) &lt;br&gt;   and Date &lt; DateAdd(Day,DateDiff(Day,0,'[COLOR=red]" & request.form("CallDate") & "[/color]'),1))" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="Extension=&amp;CallDate=&amp;CallDate=" s-norecordsfound="No records returned." i-maxrecords="0" i-groupsize="0" botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY" preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the start of a Database Results region. The page must be fetched from a web server with a web browser to display correctly; the current web is stored on your local disk or network.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;" startspan -->
As a final thought, I would warn you against using FP when writing anything as it throws a lot of junk into your code that is unnecessary and difficult to read.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill
 
Upvote 0 Downvote
Chopstik..

I can't agree with you more as far as FrontPage is concerned. Looking through the jumble of "webbot" code, I'm almost thinking it would be easier to just to some VBScript for this project rather than go through FP.

I'll try the changes that you've outlined above, and then I think I'll redo my code from scratch.

Thanks VERY MUCH for the reply.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question Question
write HTML code within ASP line
Replies
1
Views
1K
xwb
xwb
leifoet
  • Locked
  • Question Question
How to send HTML emails with ASP classic?
Replies
0
Views
1K
leifoet
leifoet
penguinspeaks
  • Locked
  • Question Question
Return to current location on page after asp processes.
Replies
2
Views
1K
penguinspeaks
penguinspeaks
how2trainyourdragon
  • Locked
  • Question Question
Request.Server variables blank for classic ASP
Replies
12
Views
2K
GriffMG
GriffMG
Swi
  • Locked
  • Question Question
JQUERY AJAX POST and how to receive data via Classic ASP
Replies
0
Views
1K
Swi
Swi

Part and Inventory Search

Sponsor

Back
Top