Newbie fails to understand code....

[Rob Meade]

Hi all,

We've just started using ASP.Net at work, and whilst I have several years of
regular ASP experience, I am struggling to get a good foot hold with .net...

My current query is with the following code :

If objDataReader.Read() Then

' strUserID = objDataReader("UserID")
' strForename = objDataReader("Forename")
' strSurname = objDataReader("Surname")
' Label4.Text = "STEP 1 " & strUserID & " " & strForename & " " &
strSurname

While objDataReader.Read()
strUserID = objDataReader("UserID")
strForename = objDataReader("Forename")
strSurname = objDataReader("Surname")
Label4.Text = "STEP 2 " & strUserID & " " & strForename & " " &
strSurname
End While

Else

Label4.Text = "No user found"

End If

I have a small test page where I am trying to get a user to enter a
username/password, click login and then the code runs off to the database,
this is so far working - however, to ensure that I returned a recordset (ie,
a user was found) then I added the If objDataReader.Read() Then code - what
I have found though is that if there is only ONE user in the database with
the username then it will use the code above but doesn't do anything - if I
uncomment the commented code - it will work using the top bit, however, if I
place a second user in the database with the same username, then it ignores
the top bit and uses the While objDataReader.Read() loop....and obviously
overwrites the label with the second users details...

I'd assumed that the code was doing this :

go get me a recordset
is there anyone in it?
If there is, then iterate through and plonk their details in the label

But that doesnt seem to be the case - I dont understand why I've had to
duplicate code to get this to work - I'm clearly doing something
incorrectly, but I have no idea why..

If I enter a username that isnt in the database, it correctly displays the
"No user found" message, its only when it finds one or more users with the
same username that I seem to have problems....

Any help would be gratefully received, and please be gently - this is all
VERY taxing at the moment!

Regards

Rob

 

[Guest]

Hi Rob

With objDataReader.Read() you load data from the que into
your DataObject. In your Code your doing this twice, so
the first record will be disregarded.

S

 

[Rob Meade]

With objDataReader.Read() you load data from the que into
your DataObject. In your Code your doing this twice, so
the first record will be disregarded.

Hi thanks for your reply.

I seem to have resolved the problem now by changing

If objDataReader.Read() Then

to

If objDataReader.HasRows Then

which solved the first problem...

Now I'm stuck with trying to establish the number of rows returned...

Having read around on the net a bit the concensus seems to be to use a
SELECT COUNT(field) in a seperate SQL Statement first, set a variable, then
do the other SQL Statement afterwards...still having problems with this
though - any examples of this? My code is below....

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

objCommand = New SqlCommand(strSQL, objConnection)
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)

If objDataReader.HasRows Then
intRecordCounter = objDataReader("RecordCounter")
Else
intRecordCounter = 0
End If


I keep getting the following error when it runs though :

Invalid attempt to read when no data is present.

I added the CAST function to the SQL Statement having read a small snippet
talking about the same problem - they suggested "you might need to CAST the
result though" - doesn't work with or without this - clearly something else
going wrong...

Any further help would be appreciated...

Rob

 

[Alex Papadimoulis]

Hi Rob,

If all you need back from a database is a single value, such as a count, use
the command's ExecuteScalar method. If no results are returned it will
return Nothing (C# null), so, you could check for a count like:

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

objCommand = New SqlCommand(strSQL, objConnection)
objTemp = objCommand.ExecuteScalar
if objTemp is nothing then
intRecordCounter = 0
else
intRecordCounter = CInt( objTemp )
end if

Also, just as a FYI to a newbie ... Hungarian Notation is out, stick with
Pascal/Camel. Lookup naming conventions in your MSDN Library.

Good luck
Alex Papadimoulis

 

[Rob Meade]

...

Also, just as a FYI to a newbie ... Hungarian Notation is out, stick with
Pascal/Camel. Lookup naming conventions in your MSDN Library.

Hi Alex,

Thanks for the reply - Hungarian Notation??

Regards

Rob