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 Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Data type mismatch in criteria expresion.... 1

Status
Not open for further replies.
cdogstu99

cdogstu99

MIS
Jan 17, 2005
68
US
I have a page i'm creating in which i'm trying to be able to update, add, and delete values from a database. When I try to update, i get the error
"Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/modcontact.asp, line 56

here's my code:

<HTML>
<BODY>
<B>Price List DB Sample</B>
<%
Actionvar=Request.QueryString("actionvar")

Set conn = server.createobject("adodb.connection")
DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtemp=dsntemp & "DBQ=" & server.mappath("database/pricelist2.mdb")
conn.Open DSNtemp

'If the QueryString has the Actionvar = add then generate
'the page for adding Coin_Names
IF Actionvar="add" THEN
IF Len(TRIM(request.form("flag"))) = 0 THEN
%>
Add<HR>
<FORM METHOD="post" ACTION="modgold.asp?Actionvar=add">
Coin_Name: <INPUT TYPE="text" NAME="Coin_Name"><BR>
Price1: $<INPUT TYPE="text" NAME="Price1"><BR>
Price2: $<INPUT TYPE="text" NAME="Price2"><BR>
Price3: $<INPUT TYPE="text" NAME="Price3"><BR>
Price4: $<INPUT TYPE="text" NAME="Price4"><BR>
Price5: $<INPUT TYPE="text" NAME="Price5"><BR>



<BR>
<INPUT TYPE="hidden" NAME="flag" VALUE="2">
<INPUT TYPE="submit" VALUE="ADD">
</FORM>
<%
ELSEIF Request.Form("flag")="2" THEN
SQLstmt = "INSERT INTO gold_prices (Coin_Name,Price1,Price2,Price3,Price4,Price5)"
SQLstmt = SQLstmt & " VALUES ('" & request.form("Coin_Name") & "','" & request.form("Price1") & "','" & request.form("Price2") & "','" & request.form("Price3") & "','" & request.form("Price4") & "','" & request.form("Price5") & "')"
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
conn.execute(SQLstmt)
Response.Write "Operation Complete<BR><A HREF=""modgold.asp"">Home</A>"
END IF

'If the QueryString has the Actionvar = update then generate
'the page for updating Coin_Names
ELSEIF Actionvar="update" THEN
IF Len(TRIM(Request.Form("flag"))) = 0 THEN
SQLstmt = "SELECT * FROM gold_prices WHERE ID=" & Request.QueryString("Recid")
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Set rs = conn.Execute(SQLstmt)
IF NOT RS.EOF THEN
%>
Update<HR>
<FORM METHOD="post" ACTION="modgold.asp?Actionvar=update">
<B>Coin_Name:</B> <INPUT TYPE="text" NAME="Coin_Name" VALUE="<%=rs("Coin_Name")%>"><BR>
<B>Price1:</B> $<INPUT TYPE="text" NAME="Price1" VALUE="<%=rs("Price1")%>"><BR>
<B>Price2:</B> <INPUT TYPE="text" NAME="Price2" VALUE="<%=rs("Price2")%>"><BR>
<B>Price3:</B> <INPUT TYPE="text" NAME="Price3" VALUE="<%=rs("Price3")%>"><BR>
<B>Price4:</B> <INPUT TYPE="text" NAME="Price4" VALUE="<%=rs("Price4")%>"><BR>
<B>Price5:</B> <INPUT TYPE="text" NAME="Price5" VALUE="<%=rs("Price5")%>"><BR>

<INPUT TYPE="hidden" NAME="flag" VALUE="2">
<INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("ID")%>">
<INPUT TYPE="submit" VALUE="Update">
</FORM>
<%
rs.MoveNext
rs.Close
END IF
ELSEIF Request.Form("flag")="2" THEN
SQLstmt = "UPDATE gold_prices SET "
SQLstmt = SQLstmt & "Coin_Name= '" & Request.Form("Coin_Name") & "',"
SQLstmt = SQLstmt & "Price1= " & Request.Form("Price1") & ","
SQLstmt = SQLstmt & "Price2= " & Request.Form("Price2") & ","
SQLstmt = SQLstmt & "Price3= " & Request.Form("Price3") & ","
SQLstmt = SQLstmt & "Price4= " & Request.Form("Price4") & ","
SQLstmt = SQLstmt & "Price5= " & Request.Form("Price5") & ""

SQLstmt = SQLstmt & " WHERE ID= " & Request.Form("Recordid")


%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Set Rs=Conn.Execute(SQLstmt)
Response.Write "Operation Complete<br><A HREF=""modgold.asp"">Home</A>"
END IF

'If the QueryString has the Actionvar = delete then delete the Coin_Name
'and generate an "Operation Complete" page
ELSEIF Actionvar="delete" THEN
SQLstmt = "DELETE * FROM gold_prices WHERE ID=" & TRIM(Request.QueryString("Recid"))
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
conn.execute(SQLstmt)
Response.Write "Operation Complete<BR><A HREF=""modgold.asp"">Home</A>"

'If the QueryString Actionvar isn't set to anything, generate the list of Coin_Names
ELSE
SQLstmt = "SELECT * FROM gold_prices"
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Set rs = conn.Execute(SQLstmt)
%>
<TABLE BORDER=0>
<TR>
<TD><CENTER><B>Coin_Name</B></CENTER></TD>
<TD>&nbsp;</TD>
<TD><CENTER><B>Price1</B></CENTER></TD>
<TD>&nbsp;</TD>
<TD><CENTER><B>Price2</B></CENTER></TD>
<TD>&nbsp;</TD>
<TD><CENTER><B>Price3</B></CENTER></TD>
<TD>&nbsp;</TD>
<TD><CENTER><B>Price4</B></CENTER></TD>
<TD>&nbsp;</TD>
<TD><CENTER><B>Price5</B></CENTER></TD>
<TD>&nbsp;</TD>




<TD COLSPAN=2><CENTER><B>Modify</B></CENTER></TD>
<%
x=0
DO WHILE NOT rs.EOF
x=x+1
Recid = rs("ID")
Coin_Name = rs("Coin_Name")
Price1 = rs("Price1")
Price2 = rs("Price2")
Price3 = rs("Price3")
Price4 = rs("Price4")
Price5 = rs("Price5")
%>
<TR>
<TD><%=x%>) <%=Coin_Name%></TD>
<TD>&nbsp;</TD>
<TD ALIGN=RIGHT>$<%=Price1%></TD>
<TD>&nbsp;</TD>
<TD><%=Price2%></TD>
<TD>&nbsp;</TD>
<TD><%=Price3%></TD>
<TD>&nbsp;</TD>
<TD><%=Price4%></TD>
<TD>&nbsp;</TD>
<TD><%=Price5%></TD>




<TD><CENTER><A HREF="modgold.asp?Actionvar=delete&Recid=<%=Recid %>">Delete</A></CENTER></TD>
<TD><A HREF="modgold.asp?Actionvar=update&Recid=<%=Recid %>">Update</A></TD>
<%
rs.MoveNext
LOOP
RS.Close
Response.Write "</TABLE>"
Response.Write "<HR><A HREF='modgold.asp?actionvar=add'>Add a Record</A>"
END IF

conn.Close
Set conn = nothing
Set SQLstmt = nothing
%>
</BODY>
</HTML>
 
Sort by date Sort by votes
which is line 56?

Also why are you not using single quotes for your request.form variables in your update sql query...

-DNG
 
Upvote 0 Downvote
it's this

<%
Set rs = conn.Execute(SQLstmt)
IF NOT RS.EOF THEN
%>


about the single quotes; i had a similar page which is working fine, so i just copied that...would it make a difference?
 
Upvote 0 Downvote
ok..the first line has rs has recordset object and the next line has RS

make sure that you have consistency in the variables you use...

and about the single quotes... i was just wondering because you used then in your insert sql query but not in your update query...

-DNG

 
Upvote 0 Downvote
also, when i choose to delete, i get the same error but for line 112, which is this...

<%
conn.execute(SQLstmt)
Response.Write "Operation Complete<BR><A HREF=""modcontact.asp"">Home</A>"
 
Upvote 0 Downvote
SQLstmt = "DELETE * FROM gold_prices WHERE ID=" & TRIM(Request.QueryString("Recid"))

when you output the above sql statement..did you make sure that Request.QueryString("Recid") is not empty

-DNG
 
Upvote 0 Downvote
DNG...i just realized the code i copied above is the wrong one...that is the one that is working...here's the correct code...i'm sorry about that...thanks for helping me! this is driving me crazy

<HTML>
<BODY>
<B>Price List DB Sample</B>
<%
Actionvar=Request.QueryString("actionvar")

Set conn = server.createobject("adodb.connection")
DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtemp=dsntemp & "DBQ=" & server.mappath("database/pricelist2.mdb")
conn.Open DSNtemp

'If the QueryString has the Actionvar = add then generate
'the page for adding Coin_Names
IF Actionvar="add" THEN
IF Len(TRIM(request.form("flag"))) = 0 THEN
%>
Add<HR>
<FORM METHOD="post" ACTION="modcontact.asp?Actionvar=add">
Coin1: <INPUT TYPE="text" NAME="Coin1"><BR>
Coin2: $<INPUT TYPE="text" NAME="Coin2"><BR>
Coin3: $<INPUT TYPE="text" NAME="Coin3"><BR>
Coin4: $<INPUT TYPE="text" NAME="Coin4"><BR>
Coin5: $<INPUT TYPE="text" NAME="Coin5"><BR>
Coin6: $<INPUT TYPE="text" NAME="Coin6"><BR>
Coin7: $<INPUT TYPE="text" NAME="Coin7"><BR>
Coin8: $<INPUT TYPE="text" NAME="Coin8"><BR>
Coin9: $<INPUT TYPE="text" NAME="Coin9"><BR>

<BR>
<INPUT TYPE="hidden" NAME="flag" VALUE="2">
<INPUT TYPE="submit" VALUE="ADD">
</FORM>
<%
ELSEIF Request.Form("flag")="2" THEN
SQLstmt = "INSERT INTO contact (Coin1,Coin2,Coin3,Coin4,Coin5,Coin6,Coin7,Coin8,Coin9)"
SQLstmt = SQLstmt & " VALUES ('" & request.form("Coin1") & "','" & request.form("Coin2") & "','" & request.form("Coin3") & "','" & request.form("Coin4") & "','" & request.form("Coin5") & "','" & request.form("Coin6") & "','" & request.form("Coin7") & "','" & request.form("Coin8") & "','" & request.form("Coin9") &"')"
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
conn.execute(SQLstmt)
Response.Write "Operation Complete<BR><A HREF=""modcontact.asp"">Home</A>"
END IF

'If the QueryString has the Actionvar = update then generate
'the page for updating Coin_Names
ELSEIF Actionvar="update" THEN
IF Len(TRIM(Request.Form("flag"))) = 0 THEN
SQLstmt = "SELECT * FROM contact WHERE ID=" & Request.QueryString("Recid")
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Set rs = conn.Execute(SQLstmt)
IF NOT rs.eof THEN
%>
Update<HR>
<FORM METHOD="post" ACTION="modcontact.asp?Actionvar=update">
<B>Coin1:</B> <INPUT TYPE="text" NAME="Coin1" VALUE="<%=rs("Coin1")%>"><BR>
<B>Coin2:</B> $<INPUT TYPE="text" NAME="Coin2" VALUE="<%=rs("Coin2")%>"><BR>
<B>Coin3:</B> <INPUT TYPE="text" NAME="Coin3 VALUE="<%=rs("Coin3")%>"><BR>
<B>Coin4:</B> <INPUT TYPE="text" NAME="Coin4" VALUE="<%=rs("Coin4")%>"><BR>
<B>Coin5:</B> <INPUT TYPE="text" NAME="Coin5" VALUE="<%=rs("Coin5")%>"><BR>
<B>Coin6:</B> <INPUT TYPE="text" NAME="Coin6" VALUE="<%=rs("Coin6")%>"><BR>
<B>Coin7:</B> <INPUT TYPE="text" NAME="Coin7" VALUE="<%=rs("Coin7")%>"><BR>
<B>Coin8:</B> <INPUT TYPE="text" NAME="Coin8" VALUE="<%=rs("Coin8")%>"><BR>
<B>Coin9:</B> <INPUT TYPE="text" NAME="Coin9" VALUE="<%=rs("Coin9")%>"><BR>

<INPUT TYPE="hidden" NAME="flag" VALUE="2">
<INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("ID")%>">
<INPUT TYPE="submit" VALUE="Update">
</FORM>
<%
rs.MoveNext
rs.Close
END IF
ELSEIF Request.Form("flag")="2" THEN
SQLstmt = "UPDATE contact SET "
SQLstmt = SQLstmt & "Coin1= '" & Request.Form("Coin1") & "',"
SQLstmt = SQLstmt & "Coin2= " & Request.Form("Coin2") & ","
SQLstmt = SQLstmt & "Coin3= " & Request.Form("Coin3") & ","
SQLstmt = SQLstmt & "Coin4= " & Request.Form("Coin4") & ","
SQLstmt = SQLstmt & "Coin5= " & Request.Form("Coin5") & ","
SQLstmt = SQLstmt & "Coin6= " & Request.Form("Coin6") & ","
SQLstmt = SQLstmt & "Coin7= " & Request.Form("Coin7") & ","
SQLstmt = SQLstmt & "Coin8= " & Request.Form("Coin8") & ","
SQLstmt = SQLstmt & "Coin9= " & Request.Form("Coin9") & ""

SQLstmt = SQLstmt & " WHERE ID= " & Request.Form("Recordid")


%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Set Rs=Conn.Execute(SQLstmt)
Response.Write "Operation Complete<br><A HREF=""modcontact.asp"">Home</A>"
END IF

'If the QueryString has the Actionvar = delete then delete the Coin_Name
'and generate an "Operation Complete" page
ELSEIF Actionvar="delete" THEN
SQLstmt = "DELETE * FROM contact WHERE ID=" & TRIM(Request.QueryString("Recid"))
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
conn.execute(SQLstmt)
Response.Write "Operation Complete<BR><A HREF=""modcontact.asp"">Home</A>"

'If the QueryString Actionvar isn't set to anything, generate the list of Coin_Names
ELSE
SQLstmt = "SELECT * FROM contact"
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Set rs = conn.Execute(SQLstmt)
%>
<TABLE BORDER=0>
<TR>
<TD><CENTER><B>Coin1</B></CENTER></TD>
<TD>&nbsp;</TD>
<TD><CENTER><B>Coin2</B></CENTER></TD>
<TD>&nbsp;</TD>
<TD><CENTER><B>Coin3</B></CENTER></TD>
<TD>&nbsp;</TD>
<TD><CENTER><B>Coin4</B></CENTER></TD>
<TD>&nbsp;</TD>
<TD><CENTER><B>Coin5</B></CENTER></TD>
<TD>&nbsp;</TD>
<TD><CENTER><B>Coin6</B></CENTER></TD>
<TD>&nbsp;</TD>
<TD><CENTER><B>Coib7</B></CENTER></TD>
<TD>&nbsp;</TD>
<TD><CENTER><B>Coin8</B></CENTER></TD>
<TD>&nbsp;</TD>
<TD><CENTER><B>Coin9</B></CENTER></TD>
<TD>&nbsp;</TD>




<TD COLSPAN=2><CENTER><B>Modify</B></CENTER></TD>
<%
x=0
DO WHILE NOT rs.EOF
x=x+1
Recid = rs("ID")
Coin1 = rs("Coin1")
Coin2 = rs("Coin2")
Coin3 = rs("Coin3")
Coin4 = rs("Coin4")
Coin5 = rs("Coin5")
Coin6 = rs("Coin6")
Coin7 = rs("Coin7")
Coin8 = rs("Coin8")
Coin9 = rs("Coin9")



%>
<TR>
<TD><%=x%>) <%=Coin1%></TD>
<TD>&nbsp;</TD>
<TD ALIGN=RIGHT>$<%=Coin2%></TD>
<TD>&nbsp;</TD>
<TD><%=Coin3%></TD>
<TD>&nbsp;</TD>
<TD><%=Coin4%></TD>
<TD>&nbsp;</TD>
<TD><%=Coin5%></TD>
<TD>&nbsp;</TD>
<TD><%=Coin6%></TD>
<TD>&nbsp;</TD>
<TD><%=Coin7%></TD>
<TD>&nbsp;</TD>
<TD><%=Coin8%></TD>
<TD>&nbsp;</TD>
<TD><%=Coin9%></TD>




<TD><CENTER><A HREF="modcontact.asp?Actionvar=delete&Recid=<%=Recid %>">Delete</A></CENTER></TD>
<TD><A HREF="modcontact.asp?Actionvar=update&Recid=<%=Recid %>">Update</A></TD>
<%
rs.MoveNext
LOOP
RS.Close
Response.Write "</TABLE>"
Response.Write "<HR><A HREF='modcontact.asp?actionvar=add'>Add a Record</A>"
END IF

conn.Close
Set conn = nothing
Set SQLstmt = nothing
%>
</BODY>
</HTML>
 
Upvote 0 Downvote
DNG, I figured it out...you were right. My database "ID" field wasn't on autonumber, so the ID field was blank in the table.
Also, I had a couple of quotes missing. Anway I thank you for your help, and i'll definitely give you a star

Thanks!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

DiDYRO_
  • Locked
  • Question
How do I make a VBS file that can collect dialog-type answers in a local .log file?
Replies
0
Views
188
DiDYRO_
DiDYRO_
penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
196
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
Wrong code in a href ?
Replies
1
Views
209
leifoet
leifoet
leifoet
  • Locked
  • Question
How to display an existing date in Input type=&quot;date&quot;?
Replies
4
Views
179
leifoet
leifoet
Swi
  • Locked
  • Question
PDF Interrogation
Replies
0
Views
116
Swi
Swi

Part and Inventory Search

Sponsor

Back
Top