Calling a variable in a SQL statement using ASP...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Calling a variable in a SQL statement using ASP...

Author

Topic

nathan Pitman
Starting Member

3 Posts

Posted - 2002-01-14 : 11:37:44

Hi, Been trying for a whole day to get this to work. I have an ASP page in which I have a variable called 'dDate' declared. This adopts a different date value depending on the link clicked on a calendar which features on the page. So what I want is to retreive all records from the db which are equal to this variable. But I cannot suss it out...

"SELECT * FROM studio_timesheets WHERE (entryLogDate) = '" & dDate & "'"

Is what I currently have..., but I get a 'Data type mismatch in criteria expression.' error from IIS5.

What am I doing wrong???

If I get the variable to render in the page it shows up in the following format dd/mm/yyyy, and this is the same format as that stored in the database...???

Help!?

mfemenel
Professor Frink

1421 Posts

Posted - 2002-01-14 : 12:17:02

dDate might be being interpreted as a text value though. Try using
cDate(dDate)to convert it to a date before passing it to the database.

Mike
"A program is a device used to convert data into error messages."

Edited by - mfemenel on 01/14/2002 12:18:00

nathan Pitman
Starting Member

3 Posts

Posted - 2002-01-14 : 12:25:50

Thanks Mike, will try that, it has just occured to me that after reading up on data mismatch type that maybe I need to pass the date in mm/dd/yyyy format, in my database they're stored in the UK format which is dd/mm/yyyy...? Apparently you should also surround a date value with #'s in a SQL statement??? Is this true?

quote:

dDate might be being interpreted as a text value though. Try using
cDate(dDate)to convert it to a date before passing it to the database.

Mike
"A program is a device used to convert data into error messages."

Edited by - mfemenel on 01/14/2002 12:18:00

GreatInca
Posting Yak Master

102 Posts

Posted - 2002-01-14 : 12:35:46

I think when converting from a char datatype to a date datatype it uses the operating system date settings to determin how to interpate the text date. I think cdate() does too. It would be on the server unless our converting in app code and its vb or something (not asp or other server side language). I think the UK format is better anyway but in data, the opposite is better in my opinion (yy/mm/dd) for string sorting purposes.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-01-14 : 17:32:32

nathan,

Your problem is that SQL Server is seeing your date as a string. You need to do the following and it will work (or I'll give you your money back)

If dDate is dd/mm/yyyy then
"SELECT * FROM studio_timesheets WHERE (entryLogDate) = CONVERT(datetime,'" & dDate & "',103)"

Or if dDate is mm/dd/yyyy then
"SELECT * FROM studio_timesheets WHERE (entryLogDate) = CONVERT(datetime,'" & dDate & "',101)"

(In case you're not aware, the CONVERT function allows conversion based on the format you specify (either 103 or 101). For a full listing look in the Books Online - type in "CONVERT".)

FOR THE PEDANTS....
In fact, to be really correct, you shouldn't really be mixing datatypes - concatenating a date and a string. You should really be converting dDate into a string first (then you'll be in control of the formatting) ie

"SELECT * FROM studio_timesheets WHERE (entryLogDate) = CONVERT(datetime,'" & Format(dDate,"dd mmm yyyy") & "',101)"

Yep, I know, the Format() function is not available in VBScript. So I wrote my own.

NB. #s are used in Access to demarcate date values in a string. However, there is a better way even in Access. Ask me if you need to know.

I hope that when I die someone will say of me "That guy sure owed me a lot of money"

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-01-14 : 17:37:40

Nathan,

Regardless of the user requirements for displaying dates as dd/mm/yyyy or any other format

Use the ISO Date format of yyyymmdd hh:nn:ss to pass dates to SQL Server..

The DB will know exactly what date it is....

DavidM

Tomorrow is the same day as Today was the day before.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-01-14 : 18:05:31

Nathan

David is correct that using the ISO format would be the "best most logical" way to go. However, whichever format you use to send to SQL, you need to be in control of it, and not just concatenate it into your string, hoping for the best.

Cheers

I hope that when I die someone will say of me "That guy sure owed me a lot of money"

nathan Pitman
Starting Member

3 Posts

Posted - 2002-01-15 : 05:03:14

I've tried what you guys have suggested but I just get expected end of statement errors... :/

Here's the ASP:

<%@LANGUAGE="VBSCRIPT"%>

<%
set rs_getTimesheets = Server.CreateObject("ADODB.Recordset")
rs_getTimesheets.ActiveConnection = MM_studioIntranet_STRING
rs_getTimesheets.Source = "SELECT * FROM studio_timesheets WHERE entryLogDate = #01/07/2002#"
'If dDate is dd/mm/yyyy then
'rs_getTimesheets.Source = "SELECT * FROM studio_timesheets WHERE (entryLogDate) = CONVERT(datetime,'" & dDate & "',103)"

'Or if dDate is mm/dd/yyyy then
'rs_getTimesheets.Source = "SELECT * FROM studio_timesheets WHERE (entryLogDate) = CONVERT(datetime,'" & dDate & "',101)"

rs_getTimesheets.CursorType = 0
rs_getTimesheets.CursorLocation = 2
rs_getTimesheets.LockType = 3
rs_getTimesheets.Open()
rs_getTimesheets_numRows = 0
%>
<%
' ***Begin Function Declaration***
' New and improved GetDaysInMonth implementation.
' Thanks to Florent Renucci for pointing out that I
' could easily use the same method I used for the
' revised GetWeekdayMonthStartsOn function.
Function GetDaysInMonth(iMonth, iYear)
Dim dTemp
dTemp = DateAdd("d", -1, DateSerial(iYear, iMonth + 1, 1))
GetDaysInMonth = Day(dTemp)
End Function

Function GetWeekdayMonthStartsOn(dAnyDayInTheMonth)
Dim dTemp
dTemp = DateAdd("d", -(Day(dAnyDayInTheMonth) - 1), dAnyDayInTheMonth)
GetWeekdayMonthStartsOn = WeekDay(dTemp)
End Function

Function SubtractOneMonth(dDate)
SubtractOneMonth = DateAdd("m", -1, dDate)
End Function

Function AddOneMonth(dDate)
AddOneMonth = DateAdd("m", 1, dDate)
End Function
' ***End Function Declaration***

Dim dDate ' Date we're displaying calendar for
Dim iDIM ' Days In Month
Dim iDOW ' Day Of Week that month starts on
Dim iCurrent ' Variable we use to hold current day of month as we write table
Dim iPosition ' Variable we use to hold current position in table

' Get selected date. There are two ways to do this.
' First check if we were passed a full date in RQS("date").
' If so use it, if not look for seperate variables, putting them togeter into a date.
' Lastly check if the date is valid...if not use today
If IsDate(Request.QueryString("date")) Then
dDate = CDate(Request.QueryString("date"))
Else
If IsDate(Request.QueryString("day") & "/" & Request.QueryString("month") & "/" & Request.QueryString("year")) Then
dDate = CDate(Request.QueryString("day") & "/" & Request.QueryString("month") & "/" & Request.QueryString("year"))
Else
dDate = Date()
' The elegant solution for those of you running IIS4
If Request.QueryString.Count <> 0 Then Response.Write "The date you picked was not a valid date. The calendar was set to today's date.<BR><BR>"
End If
End If

'Now we've got the date. Now get Days in the choosen month and the day of the week it starts on.
iDIM = GetDaysInMonth(Month(dDate), Year(dDate))
iDOW = GetWeekdayMonthStartsOn(dDate)
%>
<%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
rs_getTimesheets_numRows = rs_getTimesheets_numRows + Repeat1__numRows
%>
<html>
<head>
<title>Test4</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<link rel="stylesheet" href="../assets/intranet.css" type="text/css">
<body bgcolor="#FFFFFF" text="#000000">
<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0>
<TR>
<TD bgcolor="#999999">
<TABLE BORDER=0 CELLSPACING=1 CELLPADDING=1 BGCOLOR=#FFFFFF>
<TR bgcolor="#666666">
<TD ALIGN="center" COLSPAN=7>
<TABLE WIDTH=100% BORDER=0 CELLSPACING=0 CELLPADDING=0>
<TR>
<TD width="50" ALIGN="right"><A HREF="calendar4.asp?date=<%= SubtractOneMonth(dDate) %>"><FONT COLOR=#FFFF00 SIZE="-1"><<</FONT></A></TD>
<TD ALIGN="center"><FONT COLOR=#FFFF00><B><%= MonthName(Month(dDate)) & " " & Year(dDate) %></B></FONT></TD>
<TD width="50" ALIGN="left"><A HREF="calendar4.asp?date=<%= AddOneMonth(dDate) %>"><FONT COLOR=#FFFF00 SIZE="-1">>></FONT></A></TD>
</TR>
</TABLE>
</TD>
</TR>
<TR bgcolor="#999999">
<TD ALIGN="center">Sun<BR>
<IMG SRC="assets/shim.gif" WIDTH=60 HEIGHT=1 BORDER=0></TD>
<TD ALIGN="center">Mon<BR>
<IMG SRC="assets/shim.gif" WIDTH=60 HEIGHT=1 BORDER=0></TD>
<TD ALIGN="center">Tue<BR>
<IMG SRC="assets/shim.gif" WIDTH=60 HEIGHT=1 BORDER=0></TD>
<TD ALIGN="center">Wed<BR>
<IMG SRC="assets/shim.gif" WIDTH=60 HEIGHT=1 BORDER=0></TD>
<TD ALIGN="center">Thu<BR>
<IMG SRC="assets/shim.gif" WIDTH=60 HEIGHT=1 BORDER=0></TD>
<TD ALIGN="center">Fri<BR>
<IMG SRC="assets/shim.gif" WIDTH=60 HEIGHT=1 BORDER=0></TD>
<TD ALIGN="center">Sat<BR>
<IMG SRC="assets/shim.gif" WIDTH=60 HEIGHT=1 BORDER=0></TD>
</TR>
<%
' Write spacer cells at beginning of first row if month doesn't start on a Sunday.
If iDOW <> 1 Then
Response.Write vbTab & "<TR>" & vbCrLf
iPosition = 1
Do While iPosition < iDOW
Response.Write vbTab & vbTab & "<TD> </TD>" & vbCrLf
iPosition = iPosition + 1
Loop
End If

' Write days of month in proper day slots
iCurrent = 1
iPosition = iDOW
Do While iCurrent <= iDIM
' If we're at the begginning of a row then write TR
If iPosition = 1 Then
Response.Write vbTab & "<TR>" & vbCrLf
End If

' If the day we're writing is the selected day then highlight it somehow.
If iCurrent = Day(dDate) Then
Response.Write vbTab & vbTab & "<TD BGCOLOR=#00FFFF><FONT SIZE=""-1""><B>" & iCurrent & "</B></FONT><BR><BR></TD>" & vbCrLf
Else
Response.Write vbTab & vbTab & "<TD BGCOLOR=#CCCCCC><A HREF=""calendar4.asp?date=" & iCurrent & "/" & Month(dDate) & "/" & Year(dDate) & """><FONT SIZE=""-1"">" & iCurrent & "</FONT></A><BR><BR></TD>" & vbCrLf
End If

' If we're at the endof a row then write /TR
If iPosition = 7 Then
Response.Write vbTab & "</TR>" & vbCrLf
iPosition = 0
End If

' Increment variables
iCurrent = iCurrent + 1
iPosition = iPosition + 1
Loop

' Write spacer cells at end of last row if month doesn't end on a Saturday.
If iPosition <> 1 Then
Do While iPosition <= 7
Response.Write vbTab & vbTab & "<TD> </TD>" & vbCrLf
iPosition = iPosition + 1
Loop
Response.Write vbTab & "</TR>" & vbCrLf
End If
%>
</TABLE>
</TD>
</TR>
</TABLE>
<BR>
<FORM ACTION="calendar4.asp" METHOD=GET>
<select name="day">
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
<option value="13">13</option>
<option value="14">14</option>
<option value="15">15</option>
<option value="16">16</option>
<option value="17">17</option>
<option value="18">18</option>
<option value="19">19</option>
<option value="20">20</option>
<option value="21">21</option>
<option value="22">22</option>
<option value="23">23</option>
<option value="24">24</option>
<option value="25">25</option>
<option value="26">26</option>
<option value="27">27</option>
<option value="28">28</option>
<option value="29">29</option>
<option value="30">30</option>
<option value="31">31</option>
</select>
<SELECT NAME="month">
<OPTION VALUE=1>January</OPTION>
<OPTION VALUE=2>February</OPTION>
<OPTION VALUE=3>March</OPTION>
<OPTION VALUE=4>April</OPTION>
<OPTION VALUE=5>May</OPTION>
<OPTION VALUE=6>June</OPTION>
<OPTION VALUE=7>July</OPTION>
<OPTION VALUE=8>August</OPTION>
<OPTION VALUE=9>September</OPTION>
<OPTION VALUE=10>October</OPTION>
<OPTION VALUE=11>November</OPTION>
<OPTION VALUE=12>December</OPTION>
</SELECT>
<SELECT NAME="year">
<option value="2000">2000</option>
<option value="2001">2001</option>
<option value="2002">2002</option>
<option value="2003">2003</option>
<option value="2004">2004</option>
<option value="2005">2005</option>
<option value="2006">2006</option>
<option value="2007">2007</option>
<option value="2008">2008</option>
<option value="2009">2009</option>
<option value="2010">2010</option>
</SELECT>
<INPUT TYPE="submit" VALUE="Show date">
</FORM>
<p> </p>
<p>Todays date Day: <%= (Day(dDate))%> Month: <%= MonthName(Month(dDate))%> Year: <%= (Year(dDate))%> </p>
<p><b>Timesheet entries for this day:<%=(dDate)%> </b></p>
<table width="100%" border="0" cellspacing="0" cellpadding="5">
<%
While ((Repeat1__numRows <> 0) AND (NOT rs_getTimesheets.EOF))
%>
<tr>
<td><%=(rs_getTimesheets.Fields.Item("entryLogDate").Value)%></td>
<td><%=(rs_getTimesheets.Fields.Item("entryJob").Value)%></td>
<td><%=(rs_getTimesheets.Fields.Item("entryDesc").Value)%></td>
<td><%=(rs_getTimesheets.Fields.Item("entryTime").Value)%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rs_getTimesheets.MoveNext()
Wend
%>
</table>
</body>
</html>
<%
rs_getTimesheets.Close()
%>

Perhaps the code I've cobbled together is just too darn complicated... :)

Nathan

Scott
Posting Yak Master

145 Posts

Posted - 2002-01-15 : 05:39:04

Go back to basics and keep it simple, change your statement as follows:
"SET DATEFORMAT dmy SELECT * FROM studio_timesheets WHERE (entryLogDate) = '" & dDate & "'"

If your select statement is bringing back dates then don't select *, list each item and the dates do the following: ..., convert(varchar, Cap_Date, 103) as Cap_Date, ...
The 103 converts it to the British dd/mm/yyyy.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-01-15 : 18:44:11

By doing
SELECT *

you are still hoping for the best wrt entryLogDate - and you really don't know what format it will return. You can either do it as Scott says or you could do the verbose form....

rs_getTimesheets.Source = "SELECT Convert(datetime, entryLogDate,103) as entryLogDate, entryJob, entryDesc, entryTime FROM studio_timesheets WHERE (entryLogDate) = CONVERT(datetime,'" & dDate & "',103)"

I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources