Dreamweaver SQL...

[Plastic Box]

OK Dreamweaver isn't that helpful when it comes to complex SQL statements.

The following doesn't produce any errors, but it doesn't provide the output I'm expecting!

I'm confident that MMColParam and MMColParam2 are correct - it's the date thing I'm having problem with. Data is stored in a MySQL database and the event_date is stored as a date within the database. I'm trying to pull all events out of the database that happen after today.

If you could point out what is probably a small error I'd be very grateful!

Cheers


<%
Dim thedate
thedate = date()
%>
<%
Dim rs_whatson__MMColParam
rs_whatson__MMColParam = "Local Event"
If (Request("MM_EmptyValue") <> "") Then
rs_whatson__MMColParam = Request("MM_EmptyValue")
End If
%>
<%
Dim rs_whatson__MMColParam2
rs_whatson__MMColParam2 = "1"
If (Request("MM_EmptyValue") <> "") Then
rs_whatson__MMColParam2 = Request("MM_EmptyValue")
End If
%>
<%
Dim rs_whatson__MMColParam3
rs_whatson__MMColParam3 = thedate
If (("MM_EmptyValue") <> "") Then
rs_whatson__MMColParam3 = ("MM_EmptyValue")
End If
%>
<%
Dim rs_whatson
Dim rs_whatson_cmd
Dim rs_whatson_numRows
Set rs_whatson_cmd = Server.CreateObject ("ADODB.Command")
rs_whatson_cmd.ActiveConnection = MM_website_data_STRING
rs_whatson_cmd.CommandText = "SELECT * FROM 964_websitedata.whatson WHERE event_type = ? AND event_online = ? AND event_date > ? ORDER BY event_date ASC"
rs_whatson_cmd.Prepared = true
rs_whatson_cmd.Parameters.Append rs_whatson_cmd.CreateParameter("param1", 200, 1, 255, rs_whatson__MMColParam) ' adVarChar
rs_whatson_cmd.Parameters.Append rs_whatson_cmd.CreateParameter("param2", 5, 1, -1, rs_whatson__MMColParam2) ' adDouble
rs_whatson_cmd.Parameters.Append rs_whatson_cmd.CreateParameter("param3", 200, 1, 255, rs_whatson__MMColParam3) ' adVarChar
Set rs_whatson = rs_whatson_cmd.Execute
rs_whatson_numRows = 0
%>

[pureinfinity]

From what I can see, you are passing the date in as a string and so when you are doing a greater than (>) you are doing it against a string and not a date. You need to change the parameter type to a date so you can do the > date comparison.
eg.
rs_whatson_cmd.Parameters.Append rs_whatson_cmd.CreateParameter("param3", 133, 1, 255, rs_whatson__MMColParam3) ' adDBDate

[Plastic Box]

Thanks... I think I must have tried that in the past... it does make sense and *should* be the answer.

This is the error generated:



ADODB.Commanderror '800a0d5d'
Application uses a value of the wrong type for the current operation. /Local/WhatsOn.asp, line 60

Line 60 is the "select * from" line.

[pureinfinity]

just before the 3rd if statement place a response.write/end to check the date your passing in. This will check to see if the date is changing at all

eg:

Dim rs_whatson__MMColParam3
rs_whatson__MMColParam3 = thedate
response.write "test1:" & rs_whatson__MMColParam3 & "<BR>"
If (("MM_EmptyValue") <> "") Then
rs_whatson__MMColParam3 = ("MM_EmptyValue")
End If
response.write "test2:" & rs_whatson__MMColParam3
response.end

[Plastic Box]

Top man - thanks :)

For some reason the MM_EmptyValue was putting itself there! I've removed that from it and it all works just fine now.

I'm not a great one for fault finding within ASP!

Many thanks for your help - it all works great now, thanks :)

[pureinfinity]

Thats the only thing with Dreamweaver you can not debug/step through the ASP code.

Just a bit of advice aswell you dont need so many ASP delimeters (<%%>). You have them around every seperate ASP code you can just do the following:

<%
Dim thedate
thedate = date()

Dim rs_whatson__MMColParam
rs_whatson__MMColParam = "Local Event"
If (Request("MM_EmptyValue") <> "") Then
rs_whatson__MMColParam = Request("MM_EmptyValue")
End If

Dim rs_whatson__MMColParam2
rs_whatson__MMColParam2 = "1"
If (Request("MM_EmptyValue") <> "") Then
rs_whatson__MMColParam2 = Request("MM_EmptyValue")
End If

Dim rs_whatson__MMColParam3
rs_whatson__MMColParam3 = thedate
If (("MM_EmptyValue") <> "") Then
rs_whatson__MMColParam3 = ("MM_EmptyValue")
End If

Dim rs_whatson
Dim rs_whatson_cmd
Dim rs_whatson_numRows
Set rs_whatson_cmd = Server.CreateObject ("ADODB.Command")
rs_whatson_cmd.ActiveConnection = MM_website_data_STRING
rs_whatson_cmd.CommandText = "SELECT * FROM 964_websitedata.whatson WHERE event_type = ? AND event_online = ? AND event_date > ? ORDER BY event_date ASC"
rs_whatson_cmd.Prepared = true
rs_whatson_cmd.Parameters.Append rs_whatson_cmd.CreateParameter("param1", 200, 1, 255, rs_whatson__MMColParam) ' adVarChar
rs_whatson_cmd.Parameters.Append rs_whatson_cmd.CreateParameter("param2", 5, 1, -1, rs_whatson__MMColParam2) ' adDouble
rs_whatson_cmd.Parameters.Append rs_whatson_cmd.CreateParameter("param3", 200, 1, 255, rs_whatson__MMColParam3) ' adVarChar
Set rs_whatson = rs_whatson_cmd.Execute
rs_whatson_numRows = 0
%>

Because everytime you start and close one it makes a seperate call to the server.