PDA

View Full Version : Need help comparing values in a query !!!

ramkiran77
10-07-2009, 07:20 PM
I am trying to get the count of records by comparing the values one taken from a input box in the form and another from table using a query

Please see the code below

set conn = server.createobject("ADODB.Connection")
conn.open "work-data"

sql = "select * from Ddata"
set rs = server.createobject("ADODB.Recordset")
rs.open sql, conn

If Not rs.eof Then
sql1 = "Select count(*) from Ddata where stime >=" & Document.form2.reqtime.value
Set rs1 = server.CreateObject("ADODB.Recordset")
rs1.open sql1, conn
End If

Even though i have defined the connection it is giving me an error
" Variable is undefined: 'conn' "

Please suggest what could be the mistake i am doing here....??
Old Pedant
10-08-2009, 07:52 PM
If you coded OPTION EXPLICIT at the top of this page, then that just means you forgot to do
Dim conn


If you are not using OPTION EXPLICIT, then I dunno. Would need to see more of page.
ramkiran77
10-09-2009, 03:24 AM
I am not using OPTION EXPLICIT.....though i tried using it when i got this error but it gave me the same error without giving the variable "conn".....

Now based on my requirement i have made a slight change to the code....see the page below....


<html>
<head>
</head>
<body>

<script language="javascript" type="text/javascript" src="datetimepicker.js">
</script>

<form id="form2" name="form2">

<INPUT TYPE="text" NAME="reqtime" value="<%=request.Form("reqtime")%>" title="Select the Date and Time from the datepicker next to this text box in IST " >&nbsp;
<a href="javascript:NewCal('reqtime','mmddyyyy',true,24)"><img src="cal.gif" width="16" height="16" border="0" alt="Pick a date"></a>(IST)

<INPUT TYPE="button" NAME="metrics" VALUE="SUBMIT">

<%
reqDate=request.Form("reqtime")

Set conn = server.CreateObject("ADODB.Connection")
conn.open "tracker-new"

sql = "Select * from Case"
Set rs = server.CreateObject("ADODB.Recordset")
rs.open sql, conn

If Not rs.eof Then

sql1 = "Select count(*) from Case where Case.stime >=" & Date(reqDate)+06:00:00 & " and Case.stime <=" & Date(reqDate)+14:00:00 &"
Set rs1 = server.CreateObject("ADODB.Recordset")
rs1.open sql1, conn
End if

%>
</form>
</body>
</html>

Please suggest as now i am getting a syntax error in the query, i do not understand what i am doing wrong....
Old Pedant
10-09-2009, 06:51 PM
I'm afraid you aren't very close to success.

But for starters, we need to know the type of DB you are using: MySQL, Access, SQL Server??? Or what? Yes, it matters. Each of them handles DATETIME values differently.

But no matter what DB, your VBScript code is totally bogus here:

Date(reqDate)+06:00:00

(a) The DATE() function of VBScript DOES NOT accept *ANY* argument. Possibly you intended to use CDATE()?? Or possibly DATEVALUE()??
(b) VBScript treats a COLON as an "end of statement, begin next statement". So in your code, the line

sql1 = "Select count(*) from Case where Case.stime >=" & Date(reqDate)+06:00:00 & " and Case.stime <=" & Date(reqDate)+14:00:00 & "

is actually SEEN as several separate statements:

sql1 = "Select count(*) from Case where Case.stime >=" & Date(reqDate)+06
00
00 & " and Case.stime <=" & Date(reqDate)+14
00
00 & "

Hopefully you can see that is just nonsense.

If you want to specify a literal date or time in VBS, you use #...# around the value:

sql1 = "Select count(*) from Case where Case.stime >=" & (CDate(reqDate)+#06:00:00#) _
& " and Case.stime <=" & (CDate(reqDate)+#14:00:00#)

And that should get you past the VBScript syntax errors, but it still won't be valid SQL.