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> </TD>
<TD><CENTER><B>Price1</B></CENTER></TD>
<TD> </TD>
<TD><CENTER><B>Price2</B></CENTER></TD>
<TD> </TD>
<TD><CENTER><B>Price3</B></CENTER></TD>
<TD> </TD>
<TD><CENTER><B>Price4</B></CENTER></TD>
<TD> </TD>
<TD><CENTER><B>Price5</B></CENTER></TD>
<TD> </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> </TD>
<TD ALIGN=RIGHT>$<%=Price1%></TD>
<TD> </TD>
<TD><%=Price2%></TD>
<TD> </TD>
<TD><%=Price3%></TD>
<TD> </TD>
<TD><%=Price4%></TD>
<TD> </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>
"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> </TD>
<TD><CENTER><B>Price1</B></CENTER></TD>
<TD> </TD>
<TD><CENTER><B>Price2</B></CENTER></TD>
<TD> </TD>
<TD><CENTER><B>Price3</B></CENTER></TD>
<TD> </TD>
<TD><CENTER><B>Price4</B></CENTER></TD>
<TD> </TD>
<TD><CENTER><B>Price5</B></CENTER></TD>
<TD> </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> </TD>
<TD ALIGN=RIGHT>$<%=Price1%></TD>
<TD> </TD>
<TD><%=Price2%></TD>
<TD> </TD>
<TD><%=Price3%></TD>
<TD> </TD>
<TD><%=Price4%></TD>
<TD> </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>