Help, I cannot update my Database!!!!

Dev Shed Forums Sponsor:

Be the architects of evolution and help create the mobile internet future. It’s your move---enter to win here!

March 10th, 2004, 02:25 PM

cstuart

Registered User

Join Date: Mar 2004

Posts: 1

Time spent in forums: < 1 sec
Reputation Power: 0

Help, I cannot update my Database!!!!

i have a page set up to modify an access database. i can add, and delete records. I cannot update records...here's the code. please advise!! thanks in advance

<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 items
IF Actionvar="add" THEN
IF Len(TRIM(request.form("flag"))) = 0 THEN
%>
Add<HR>

<FORM METHOD="post" ACTION="moddata.asp?Actionvar=add">
Name: <INPUT TYPE="text" NAME="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>

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

'If the QueryString has the Actionvar = update then generate
'the page for updating items
ELSEIF Actionvar="update" THEN
IF Len(TRIM(Request.Form("flag"))) = 0 THEN
SQLstmt = "SELECT * FROM silver_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="moddata.asp?Actionvar=update"><B>Name:</B>
<INPUT TYPE="text" NAME="Name" VALUE="<%=rs("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>
<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 silver_prices SET"
SQLstmt = SQLstmt & " Name='" & TRIM(Request.Form("Name")) & "', "
SQLstmt = SQLstmt & " Price1='" & TRIM(Request.Form("Price1")) & ", "
SQLstmt = SQLstmt & " Price2='" & TRIM(Request.Form("Price2")) & ", "
SQLstmt = SQLstmt & " Price3='" & TRIM(Request.Form("Price3")) & ", "
SQLstmt = SQLstmt & " Price4='" & TRIM(Request.Form("Price4")) & ", "
SQLstmt = SQLstmt & " WHERE ID=" & Request.Form("Recordid") & ""
%>
<HR>SQL statement:
<%=SQLstmt%>
<HR>
<%
conn.Execute (SQLstmt)
Response.Write "Operation Complete<br><A HREF=""moddata.asp"">Home</A>"
END IF

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

'If the QueryString Actionvar isn't set to anything, generate the list of items
ELSE
SQLstmt = "SELECT * FROM silver_prices"
%>
<HR>SQL statement:
<%=SQLstmt%>
<HR>
<%
Set rs = conn.Execute(SQLstmt)
%>
<TABLE BORDER="0">
<TR>
<TD>
<CENTER><B>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 COLSPAN="2">
<CENTER><B>Modify</B></CENTER>
</TD>
<%
x=0
DO WHILE NOT rs.EOF
x=x+1
Recid = rs("ID")
Name = rs("Name")
Price1 = rs("Price1")
Price2 = rs("Price2")
Price3 = rs("Price3")
Price4 = rs("Price4")
%>
<TR>
<TD>
<%=x%>) <%=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>
<CENTER><A HREF="moddata.asp?Actionvar=delete&Recid=<%=Recid %>">Delete</A></CENTER>
</TD>
<TD><A HREF="moddata.asp?Actionvar=update&Recid=<%=Recid %>">Update</A>
</TD>
<%
rs.MoveNext
LOOP
RS.Close
Response.Write "</TABLE>"
Response.Write "<HR><A HREF='moddata.asp?actionvar=add'>Add a Record</A>"
END IF

conn.Close
Set conn = nothing
Set SQLstmt = nothing
%>

</BODY>
</HTML>

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: