DataReader - NextResults question

[Rob Meade]

Hi all,

Feel like I'm trying to run before I can walk a little here - very new to
ASP.Net so please bare with me..

I am trying to return a count based on criteria (username) entered by the
user, and then the corresponding userid, forename, surname etc etc...

Below is the code (or some there of) that I am using, but I keep getting an
error...

strSQL = "SELECT CAST(Count(UserID) AS int) AS RecordCounter FROM
view_UserLogon WHERE UPPER(Username) = '" & UCase(strUsername) & "';"
strSQL = strSQL & "SELECT UserID, Forename, Surname FROM view_UserLogon
WHERE UPPER(Username) = '" & UCase(strUsername) & "'"

objCommand = New SqlCommand(strSQL, objConnection)

objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConn ection)

If objDataReader.HasRows Then

While objDataReader.Read()

intRecordCounter = objDataReader("RecordCounter")

If intRecordCounter = 0 Then

Label4.Text = "Sorry, the username supplied does not exist
within the Clinical Portal"

ElseIf intRecordCounter = 1 Then

objDataReader.NextResult()

Label4.Text = "Everythings ok so far."
Label5.Text = objDataReader("UserID")

ElseIf intRecordCounter > 1 Then

Label4.Text = "Error: More than one user exists within the
Clinical Portal with the same username."

End If

End While

Else

' Here, if no rows we return then the record counter failed.
Label4.Text = "Error : Unable to return record count."
End If
I read that you can put 2 or more SQL statements in the one string,
splitting them with a semicolon, and then use both from the datareader in
the code, I've tried following an example but it's not going quite to
plan...

The error generated from entering a username that is in the database (and
only one instance of) - is this :

System.IndexOutOfRangeException: RecordCounter

The line it doesnt like is :

intRecordCounter = objDataReader("RecordCounter")

The example online had the first instance of the results referred to as (0),
ie strString = objDataReader(0) - I've tried this and whilst it then steps
over this error, it generates another for the line of code which should put
text in Label5.

If anyone could shed some light on this one for me I would be most
grateful - please remember I am NEW to this, so answers written/explained in
simple english would be appreciated ;o)

Regards

Rob

[Marina]

After you go to the next result, you have to call Read to move the cursor to
the first row of that result set.

"Rob Meade" <ro**********@NOSPAMubht.swest.nhs.uk> wrote in message
news:u4**************@TK2MSFTNGP12.phx.gbl...

Hi all,

Feel like I'm trying to run before I can walk a little here - very new to
ASP.Net so please bare with me..

I am trying to return a count based on criteria (username) entered by the
user, and then the corresponding userid, forename, surname etc etc...

Below is the code (or some there of) that I am using, but I keep getting an error...

strSQL = "SELECT CAST(Count(UserID) AS int) AS RecordCounter FROM
view_UserLogon WHERE UPPER(Username) = '" & UCase(strUsername) & "';"
strSQL = strSQL & "SELECT UserID, Forename, Surname FROM view_UserLogon
WHERE UPPER(Username) = '" & UCase(strUsername) & "'"

objCommand = New SqlCommand(strSQL, objConnection)

objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConn ection)

If objDataReader.HasRows Then

While objDataReader.Read()

intRecordCounter = objDataReader("RecordCounter")

If intRecordCounter = 0 Then

Label4.Text = "Sorry, the username supplied does not exist
within the Clinical Portal"

ElseIf intRecordCounter = 1 Then

objDataReader.NextResult()

Label4.Text = "Everythings ok so far."
Label5.Text = objDataReader("UserID")

ElseIf intRecordCounter > 1 Then

Label4.Text = "Error: More than one user exists within the
Clinical Portal with the same username."

End If

End While

Else

' Here, if no rows we return then the record counter failed.
Label4.Text = "Error : Unable to return record count."
End If
I read that you can put 2 or more SQL statements in the one string,
splitting them with a semicolon, and then use both from the datareader in
the code, I've tried following an example but it's not going quite to
plan...

The error generated from entering a username that is in the database (and
only one instance of) - is this :

System.IndexOutOfRangeException: RecordCounter

The line it doesnt like is :

intRecordCounter = objDataReader("RecordCounter")

The example online had the first instance of the results referred to as (0), ie strString = objDataReader(0) - I've tried this and whilst it then steps
over this error, it generates another for the line of code which should put text in Label5.

If anyone could shed some light on this one for me I would be most
grateful - please remember I am NEW to this, so answers written/explained in simple english would be appreciated ;o)

Regards

Rob

[Rob Meade]

"Marina" wrote...

After you go to the next result, you have to call Read to move the cursor to the first row of that result set.

Hi Marina,

Many thanks for your reply - all working now :)

Rob