By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,294 Members | 2,588 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,294 IT Pros & Developers. It's quick & easy.

passing variable with querystring - update db - error !?

P: n/a
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>
---------------------------------------------------------------------------
Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
My first guess is that it's because of your spaces. Is the column named "
Pnr" or "Pnr?"

Also, please read the following articles when you have free time.
http://www.aspfaq.com/2191
http://www.aspfaq.com/2096

Ray at work

"Fredrik/Sweden" <fr*******@hotmail.com> wrote in message
news:a5**************************@posting.google.c om...

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

Pnr = RS(" Pnr ")

Jul 19 '05 #2

P: n/a
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...
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 19 '05 #3

P: n/a

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
"Fredrik/Sweden" <fr*******@hotmail.com> wrote in message
news:a5**************************@posting.google.c om... 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

Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.