Edit and Delete dont work? [Archive]

Crash1hd

05-10-2003, 09:26 PM

Ok useing the following code below the add works but the edit and delete dont work?

admin.asp
<%@Language=VBScript%>
<%Response.Buffer = True%>



<html><head><title>AlwaysRemember.ca</title>
<LINK href="/Scripts/style.css" rel="stylesheet" type="text/css">
<div style='position: absolute; top=0; left=90%; z-index: 1'><a href=/include/utility.asp?method=abandon>log out</a></div>
<script language="JavaScript" src="/Scripts/Header.js"></script></head>
<body>

<center>
<table border="1" bgcolor="#c0c0c0">
<form action="/Include/update.asp?method=Add" method="Post">
<tr><td><b>Username</b></td><td><input type="text" name="username" size="10"></td></tr>
<tr><td><b>Password</b></td><td><input type="password" name="pass" size="10"></td></tr>
<tr><td><b>Clearance Level (1 - 9)</b></td>
<td>
<select name="level">
<option value="1">1
<option value="2">2
<option value="9">Admin
</select>
</td></tr>

<tr><td><b>Confirmed</b></td>
<td>
<select name="confirmed">
<option value="-1">Yes
<option value="0">No
</select>
</td></tr>

<%'<tr><td><b>Expiration Date</b></td><td><input type="text" name="expdate" size="10" value="<%=DateAdd("yyyy", 1, Date)"></td></tr>' Not used but wanted to keep for oter stuff%>
<tr><td><input type="submit" value="Add New Account"></td><td> </tr>
</form>
</table>
</center>

<%
SQL = "Select userid, username, pass, clearance, confirmed From members Order By userid"
Set RS = Conn.Execute(SQL)

Response.Write "<center>"

While Not RS.EOF
Response.Write "<form name=""Update"" method=""Post"">"
Response.Write "<table border=""1"" bgcolor=""#c0c0c0"">"

%>
<tr><td><b>Username</b></td><td><b>Password</b></td><td><b>Level</b></td><td><b>Confirmed</b></td></tr>
<tr><td><input type="hidden" name="id" value="<%=RS("userid")%>"></td></tr>
<tr>
<td><input type="text" name="username" size="10" value="<%=RS("username")%>"></td>
<td><input type="text" name="pass" size="10" value="<%=RS("Pass")%>"></td>
<td><input type="text" name="level" size="1" value="<%=RS("Clearance")%>"></td>
<td><input type="text" name="confirmed" size="10" value="<%=RS("confirmed")%>"></td>
<td bgcolor="#c0c0c0"><input type="submit" value="Update" onClick="this.form.action='/Include/update.asp?method=Edit';"></td>
<td bgcolor="#c0c0c0"><input type="submit" value="Delete" onClick="this.form.action='/Include/update.asp?method=Delete';"></td>
</tr>
<%
Response.Write "</table>"
Response.Write "</form>"
RS.MoveNext
Wend

Response.Write "</center>"

CleanUp(RS)

Response.Write "<p><center><a href=""/Include/utility.asp?method=abandon""><b>Log Off</b></a></center>"
%>

</body>
</html>

update.asp
<%@Language=VBScript%>
<%Response.Buffer = True%>



<%
Dim Method

Method = Request.QueryString("method")

Select Case Method
Case "Add"
Add(Conn)
Case "Edit"
Edit(Conn)
Case "Delete"
Delete(Conn)
End Select

'/////////////////////////////////////////////////////////////////////////////////

Sub Add(Conn)

Dim username, pass, Level, confirmed, SQL

username = Replace(Trim(Request.Form("username")), "'", "''")
pass = Replace(Trim(Request.Form("pass")), "'", "''")
Level = Trim(Request.Form("level"))
confirmed = Trim(Request.Form("confirmed"))

If username = "" Or pass = "" Or Level = "" Or confirmed = "" Then Response.Redirect "/Login/admin.asp"

SQL = "Insert Into members (username, pass, Clearance, confirmed) Values('"&username&"', '"&pass&"', '"&Level&"', '"&confirmed&"')"

Conn.Execute(SQL)

CleanUp2()

Response.Redirect "/Login/admin.asp"

End Sub

'////////////////////////////////////////////////////////////////////////////////////

Sub Edit(Conn)

Dim userid, username, pass, level, confirmed

userid = CInt(Request.Form("userid"))
username = Replace(Request.Form("username"), "'", "''")
pass = Replace(Request.Form("pass"), "'", "''")
level = CInt(Request.Form("level"))
confirmed = Request.Form("confirmed")

SQL = "Update Members Set username = '"&username&"', pass = '"&pass&"'"
SQL = SQL & ", Clearance = "&level&", confirmed = '"&confirmed&"' Where userid = "&userid&""

Set RS = Conn.Execute(SQL)

CleanUp2()

Response.Redirect "/Login/admin.asp"

End Sub

'////////////////////////////////////////////////////////////////////////////////////

Sub Delete(Conn)

Dim userid, SQL

userid = CInt(Request.Form("userid"))

SQL = "Delete * From Members Where userid = "&userid&""
Conn.Execute(SQL)

CleanUp2()

Response.Redirect "/Login/admin.asp"

End Sub
%>

and the db file is run like so

config.asp
<%
'///////////////////////////////////////////////////////////////////////////////////
'connection string

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Mid(Server.MapPath("\"), 1, InStrRev(Server.MapPath("\"),"\")-1) & "\AR DbFiles\AR.mdb;" & _
"Persist Security Info=False;"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open sConnString

'///////////////////////////////////////////////////////////////////////////////////

'///////////////////////////////////////////////////////////////////////////////////
'cleanup routines

Sub CleanUp(RS)
RS.Close
Conn.Close
Set RS = Nothing
Set Conn = Nothing
End Sub

Sub CleanUp2()
Conn.Close
Set Conn = Nothing
End Sub

'////////////////////////////////////////////////////////////////////////////////////
%>

raf

05-10-2003, 09:39 PM

Did you look at the executed sql statements? By adding
response.write sql
response.end

right before the conn.execute

The browse to the page and look at the printed statement. Check if all values or correct. Also, replace the single quotes by double quotes (see the sticky !)) with
replace(string,"'","''")

Crash1hd

05-10-2003, 10:26 PM

Ok it has something to do with the confirmed field being a yes/no box?

Cause if I change it to a text field it works fine! but when its a yes no field I get an error

Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

/login/update.asp, line 58

so it showing true and false when it should be showing yes/no or -1/0 If i manually change the field to a -1 then it works fine!

raf

05-10-2003, 11:35 PM

Not sure i understodd all that, but if its a yes/no field, then you shouldn't need quotes in the sql statement for that field (there are some now). Otherwise you get a typemismatch error.

so it showing true and false when it should be showing yes/no or -1/0 If i manually change the field to a -1 then it works fine!

Didn't quite understand that. Showing where? In the db?