Data type mismatch using date() function

[rdwagco2]

I know where the problem is but i just cannot get past it, please help me and tell me why I get the error message:

Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression. (line 71)

<%
dim vendmsg
dim messagedate
dim sql
dim idnumber
rs.source = "SELECT * FROM Messages WHERE VendNum = '" & VendNumb & "';"
rs.open()
do while not rs.eof
if rs.recordcount > 0 then
idnumber = rs("IDNum")
vendmsg = rs("Message")
messagedate = rs("DateCreated")
todaysdate = date()
rs.close
sql = "UPDATE Messages SET DateDisplayed = '" & todaysdate & "' WHERE VendNum = '" & VendNumb & "' AND IDNum = '" & idnumber & "';"
rs.open(sql)
else
vendmsg = "You have no new messages."
messagedate = date
end if
response.Write("<font color=#cc0000 size=4 face=Georgia>" & messagedate & "<BR>" & vendmsg & "<BR><br>")
rs.movenext()
loop
%>

[pclement]

Are VendNum and ISNum Text or Number database types? If they are Number then remove the single quotes. Is DateDisplayed a Text or Date/Time database type?

Which line of code is 71?

[rdwagco2]

Paul,
It was the IDNum with the literal quotes around it. Now I am getting an error with the "rs.movenext()" function. It says that this action cannot be performed when the database is open.....so what is my problem now....where am I missing something or have something out of place?

[pclement]

Where is your Connection object? I don't see a connection for your Recordset object.

[rdwagco2]

I am using a seperate file for the connection....

// FileName="Connection_ado_conn_string.htm"
// Type="ADO"
// DesigntimeType="ADO"
// HTTP="false"
// Catalog=""
// Schema=""
'MM_SOASCon_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\inetpub\wwwroot\SupplierPortal\SupplierLogin.mdb"
MM_SOASCon_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("./SupplierLogin.mdb")& ";"
set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = MM_SOASCon_STRING
rs.CursorType = 0
rs.CursorLocation = 3
rs.LockType = 3

[pclement]

Since you appear to be re-using your Recordset object I would recommend creating a separate Connection object and placing that in your include file (I'm assuming that you posted the code from an include file).

Whenever you create and open a new Recordset specify the Connection object as a parameter in the Open statement, or set the Recordset's ActiveConnection property to the Connection object.