passing variable with querystring - update db - error !?

F

Fredrik/Sweden

Hi folks !
got this problem...
i have a table 'Accounts' in my database, which contains a bunch of
users. From the main menu i choose "edit user" and all users in the db
are presented in a table. The first column 'Pnr' is a unique ID for
each user that i made appear as a link. clicking on one userID should
present a form where the picked users userdata is already filled in so
i can easily edit it and then move on to submit the form to update the
db. i get to the part where all users are listed nicely in a table,
but clicking on one, i get this error:

Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested
name or ordinal.
/VS_Files/updateUserForm.asp, line 19

I think i'm doing something wrong with passing a value with the
querystring...?
include the code below...

could really use some help...
appreciate it a lot...

Fredrik Holm


-------------------------------------------------------------------------
chooseUserToEdit.asp

<!--#Include File="Connect.asp" -->

<%
Set RS = Server.CreateObject("ADODB.Recordset")
RS.ActiveConnection = Con

SQLstring = "SELECT Pnr, FirstName, LastName FROM Accounts"
RS.Open SQLstring
%>

<html>
<body bgcolor="">

<center>
<table width="500" border=1 bgcolor=""
cellpadding="4" cellspacing="0">
<tr>
<td align="center" colspan="3" bgcolor="">
<font face="Arial" size="3"><b>
Choose user to edit
</b></font>
</td>
</tr>

<%
'loop through all available users & display them

WHILE NOT RS.EOF
%>
<tr>
<!--passes a querystring to updateUserForm.asp
matches href with right record -->

<td width="100"><a href="updateUserForm.asp?pid=<%=RS("Pnr")%>">
<%=RS("Pnr")%></a>
</td>
<td width="200"><%=RS("FirstName")%> </td>
<td><%=RS("LastName")%> </td>
</tr>
<%
RS.MoveNext
WEND
%>

<%
RS.Close
Con.Close
%>

</table>
</center>
</body>
</html>
------------------------------------------------------------------------
updateUserForm.asp

<!--#Include File="Connect.asp" -->

<%
'Get the user Pnr from chooseUserToEdit.asp (through the
querystring)

Pnumber = Request.Querystring("pid")

'Open the Recordset

Set RS = Server.CreateObject("ADODB.Recordset")
RS.ActiveConnection = Con

SQLstring = "SELECT * FROM Accounts WHERE Pnr = " & "'" & Pnumber &
"'"
RS.Open SQLstring

'store the record in local variables

IF NOT RS.EOF THEN
Pnr = RS(" Pnr ")
FirstName = RS(" FirstName ")
LastName = RS(" LastName ")
UserName = RS(" UserName ")
PassWord = RS(" PassWord ")
UserType = RS(" UserType ")
Company = RS(" Company ")
END IF
RS.Close

%>

<html>
<head><title>Update User</title></head>
<body bgcolor="">

<form method="post" action="updateUser.asp">

<center>
<table width="600" border=1 bgcolor=""
cellpadding="4" cellspacing="0">
<tr>
<td colspan="2" bgcolor="">
<font face="Arial" size="3"><b>
Edit User
</b></font>
</td>
</tr>
<tr>
<td>
<b> Pnr:</b>
</td>
<td>
<input name="Pnr"
size="20" maxlength="20"
value = "<%=Server.HTMLEncode(Pnr)%>">
</td>
</tr>
<tr>
<td>
<b>First name:</b>
</td>
<td>
<input name="FirstName" size="20"
value = "<%=Server.HTMLEncode(FirstName)%>">
</td>
</tr>
<tr>
<td>
<b>Last name:</b>
</td>
<td>
<input name="LastName"
size="20"
value = "<%=Server.HTMLEncode(LastName)%>">
</td>
</tr>
<tr>
<td>
<b>User name:</b>
</td>
<td>
<input name="UserName"
size="20"
value = "<%=Server.HTMLEncode(UserName)%>">
</td>
</tr>
<tr>
<td>
<b>Password:</b>
</td>
<td>
<input name="PassWord"
size="20" maxlength="20"
value = "<%=Server.HTMLEncode(PassWord)%>">
</td>
</tr>
<tr>
<td>
<b>User type:</b>
</td>
<td>
<input name="UserType"
size="20" maxlength="20"
value = "<%=Server.HTMLEncode(UserType)%>">
</td>
</tr>
<tr>
<td>
<b>Company:</b>
</td>
<td>
<input name="Company"
size="20" maxlength="20"
value = "<%=Server.HTMLEncode(Company)%>">
</td>
</tr>
<tr>
<td colspan=2 align="right">
<input type="submit" value="Add User">
</td>
</tr>
</table>
</center>

<input name="updateUser" type="hidden" value="1">
</form>

</body>
</html>
-------------------------------------------------------------------
updateUser.asp

<!--#Include File="Connect.asp" -->

<%

' Get the Form Variables
updateUser = TRIM( Request( "updateUser" ) )

Pnr = TRIM( Request( "Pnr" ) )
FirstName = TRIM( Request( "FirstName" ) )
LastName = TRIM( Request( "LastName" ) )
UserName = TRIM( Request( "UserName" ) )
PassWord = TRIM( Request( "PassWord" ) )
UserType = TRIM( Request( "UserType" ) )
Company = TRIM( Request( "Company" ) )


' Assign Default Values
IF Pnr = "" THEN
Pnr = "???"
END IF
IF FirstName = "" THEN
FirstName = "???"
END IF
IF LastName = "" THEN
LastName = "???"
END IF
IF UserName = "" THEN
UserName = "???"
END IF
IF PassWord = "" THEN
PassWord = "???"
END IF
IF UserType = "" THEN
UserType = "???"
END IF
IF Company = "" THEN
Company = "???"
END IF

%>
<html>
<head><title>Update Users</title></head>
<body bgcolor="gray">
<%
' Update User
IF updateUser <> "" THEN

sqlString = "UPDATE Accounts SET Pnr = ' " & Pnr & "'," & "'" &
FirstName & "'," &_
"'" & LastName & "'," & "'" & UserName & "'," & "'" &
PassWord & "'," &_
"'" & UserType & "'," & "'" & Company & "'" &_
"WHERE Pnr = " & "'" & Pnr & "'"


Con.Execute sqlString
%>
<center>
<table width="600" cellpadding="4"
cellspacing="0" bgcolor="lightyellow">
<tr>
<td>
<%=FirstName%> was updated in the database
</td>
</tr>
</table>
</center>
<p>
<%
END IF
%>

<a href="updateUserForm.asp">Update User</a>

</body>
</html>
---------------------------------------------------------------------------
 
B

Bullschmidt

Perhaps change this:
Pnr = RS(" Pnr ")
FirstName = RS(" FirstName ")
LastName = RS(" LastName ")
UserName = RS(" UserName ")
PassWord = RS(" PassWord ")
UserType = RS(" UserType ")
Company = RS(" Company ")

To be this instead:
Pnr = RS("Pnr")
FirstName = RS("FirstName")
LastName = RS("LastName")
UserName = RS("UserName")
PassWord = RS("PassWord")
UserType = RS("UserType")
Company = RS("Company")

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
http://www.Bullschmidt.com
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...
 
T

Tim Williams

All of these field names have spaces around them. Fix that and it should be
fine (assuming that's the only error)
IF NOT RS.EOF THEN
Pnr = RS(" Pnr ")
FirstName = RS(" FirstName ")
LastName = RS(" LastName ")
UserName = RS(" UserName ")
PassWord = RS(" PassWord ")
UserType = RS(" UserType ")
Company = RS(" Company ")
END IF


Tim
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top