ASP + Access Database [Archive]

TMdev

05-01-2006, 10:26 PM

Trying for the first time to mod some code:

Database: Access
Table name: tbl_lists
Fields in table: name, description, po, id
adovbs.inc located in root of web site

Problem:
Add items to database works fine, data shows up within database.
However, only "id" field appears in html table when displayed
Delete does not work
Update does not work

Simply looking to read fields, add, edit and delete fields within database via asp page. Can someone review my code and let me know where I can first try to rectify these problems?

Thanks for helping out a newbie.

======================
ASP page below
======================



<HTML>
<BODY>
<B>Administration of Lists</B>
<%
Actionvar=Request.QueryString("actionvar")

Set Conn = server.createobject("adodb.connection")
Conn.Mode = 3 '3 = adModeReadWrite
DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtemp=dsntemp & "DBQ=" & server.mappath("..\fpdb\login.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>
Description: <INPUT TYPE="text" NAME="description"><BR>
Purchase Order #: <INPUT TYPE="text" NAME="po"><BR>
<INPUT TYPE="hidden" NAME="flag" VALUE="2">
<INPUT TYPE="submit" VALUE="ADD">
</FORM>
<%
ELSEIF Request.Form("flag")="2" THEN
SQLstmt = "INSERT INTO tbl_lists (name,description,po)"
SQLstmt = SQLstmt & " VALUES ('" & request.form("name") & "','" & request.form("description") & "','" & request.form("po") & "')"
%>
<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 tbl_lists WHERE ID=" & Request.QueryString("id")
%>
<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>Description:</B> <INPUT TYPE="text" NAME="description" VALUE="<%=rs("description")%>"><BR>
<B>Purchase Order Number:</B> <INPUT TYPE="text" NAME="po" VALUE="<%=rs("po")%>"><BR>
<INPUT TYPE="hidden" NAME="flag" VALUE="2">
<INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("id")%>">
<input type="hidden" name="name" value="<%=rs("name")%>">
<INPUT TYPE="submit" VALUE="Update">
</FORM>
<%
rs.MoveNext
rs.Close
END IF
ELSEIF Request.Form("flag")="2" THEN
SQLstmt = "UPDATE tbl_lists SET "
SQLstmt = SQLstmt & "name='" & TRIM(Request.Form("name")) & "', "
SQLstmt = SQLstmt & "description='" & TRIM(Request.Form("description")) & "', "
SQLstmt = SQLstmt & "po=" & TRIM(Request.Form("po"))
SQLstmt = SQLstmt & "WHERE ID=" & TRIM(Request.Form("id"))
%>
<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 tbl_lists WHERE ID=" & TRIM(Request.QueryString("id"))
%>
<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 tbl_lists"
%>
<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>Description</B></CENTER></TD>
<TD> </TD>
<TD><CENTER><B>Purchase Order #</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")
Description = rs("description")
Ordernumber = rs("po")
%>
<TR>
<TD><%=x%></TD>
<TD> </TD>
<TD ALIGN=CENTER> </TD>
<TD> </TD>
<TD><%=po%></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>[/SIZE]

GDawgGWC

05-02-2006, 11:20 AM

DSNtemp=dsntemp & "DBQ=" & server.mappath("..\fpdb\login.mdb")

is your database above the website root? That's what this indicates, just making sure.

SQLstmt = SQLstmt & "po=" & TRIM(Request.Form("po"))
SQLstmt = SQLstmt & "WHERE ID=" & TRIM(Request.Form("id"))

You need ' ' around the values here, so it should read:
SQLstmt = SQLstmt & "po='" & TRIM(Request.Form("po")) & "' "
SQLstmt = SQLstmt & "WHERE ID='" & TRIM(Request.Form("id")) & "'"

I know that's not all the problems you're having, but those are a couple things to look at. Hope it helps.

TMdev

05-03-2006, 12:17 AM

Perfect, that helped and I am on my way.

Thanks.

GDawgGWC

05-03-2006, 10:19 AM

Glad I could help.

-Greg