R
RN1
A Form has 2 textboxes - one for entering username & the other one for
entering password - both of which are populated in a SQL Server DB
table. The DB table has 3 columns - UID (Identity), Username &
Password.
Using the NextRecordset method of the Recordset object, I want to
retrieve the UID for the just inserted record. This is how I did it:
============================================
<%
Dim objConn
Set objConn=Server.CreateObject("ADODB.CONNECTION")
objConn.Open 'blah...blah...blah....
Dim strSQL
strSQL="INSERT INTO Table1 (UserName,Password) VALUES('" &
Request.Form("txtUserName") & "','" & Request.Form("txtPassword") &
"');SELECT UID From Table1 WHERE UserName='" &
Request.Form("txtUserName") & "' AND Password='" &
Request.Form("txtPassword") & "';SELECT @@IDENTITY"
Dim objRS
Set objRS=objConn.Execute(strSQL).NextRecordset
Dim iUID
iUID=objRS(0).Value
Response.Write("Your ID is " & iUID)
%>
============================================
Please note that the SQL query shown above is just for the sake of
brevity otherwise it doesn't make much sense especially the 2 SELECT
queries since both of them effectively retrieve the same record!
As such the above code retrieves the correct UID from the DB table.but
if I replace the line
============================================
iUID=objRS(0).Value
============================================
with
============================================
iUID=objRS(1).Value
============================================
the following error gets generated:
============================================
Item cannot be found in the collection corresponding to the requested
name or ordinal.
============================================
Why? objRS(0) is the recordset from the first SELECT query; so isn't
objRS(1) the recordset from the second SELECT query?
Thanks,
Ron
entering password - both of which are populated in a SQL Server DB
table. The DB table has 3 columns - UID (Identity), Username &
Password.
Using the NextRecordset method of the Recordset object, I want to
retrieve the UID for the just inserted record. This is how I did it:
============================================
<%
Dim objConn
Set objConn=Server.CreateObject("ADODB.CONNECTION")
objConn.Open 'blah...blah...blah....
Dim strSQL
strSQL="INSERT INTO Table1 (UserName,Password) VALUES('" &
Request.Form("txtUserName") & "','" & Request.Form("txtPassword") &
"');SELECT UID From Table1 WHERE UserName='" &
Request.Form("txtUserName") & "' AND Password='" &
Request.Form("txtPassword") & "';SELECT @@IDENTITY"
Dim objRS
Set objRS=objConn.Execute(strSQL).NextRecordset
Dim iUID
iUID=objRS(0).Value
Response.Write("Your ID is " & iUID)
%>
============================================
Please note that the SQL query shown above is just for the sake of
brevity otherwise it doesn't make much sense especially the 2 SELECT
queries since both of them effectively retrieve the same record!
As such the above code retrieves the correct UID from the DB table.but
if I replace the line
============================================
iUID=objRS(0).Value
============================================
with
============================================
iUID=objRS(1).Value
============================================
the following error gets generated:
============================================
Item cannot be found in the collection corresponding to the requested
name or ordinal.
============================================
Why? objRS(0) is the recordset from the first SELECT query; so isn't
objRS(1) the recordset from the second SELECT query?
Thanks,
Ron