Comparing datareader result to a string
(Tuesday, September 14, 2004)
Found the following interesting discussion in the Newsgroups:
Comparing datareader result to a string
by:Anonymous
| I'm a newbie to VB.NET and ASP.NET (as you'll see by my code). However, I'm not able to get a simple process to work. I'm trying to retrieve a record and then check to see if the 'accountstat' column of the DB is set to 'inactive'. I can't, however, get it to compare correctly. Here's the code:
Sub Sign_In(s As Object, e As EventArgs)
If IsValid Then
Dim conPubs As SqlConnection
Dim cmdSelectRecord As SqlCommand
Dim dtrUser As SqlDataReader
conPubs = New SqlConnection("Server=moses;UID=sa;PWD=webdev;Database=testDB")
conPubs.Open()
cmdSelectRecord = new SqlCommand("Select * from siteusers where email='" & txtUsername.text & "' and password='"& txtPassword.Text &"'", conPubs)
dtrUser = cmdSelectRecord.ExecuteReader()
If dtrUser.Read Then
If dtrUser("accountstat") = "inactive" Then
txtInvalid.text = "Your account has not been activated. Follow the instructions sent to you via email to activate your account."
Else
' Session("uid") = dtrUser("uid")
' Response.Redirect(Session("Referer"))
End If
Else
txtInvalid.text = "Invalid email address and/or password"
session("forgotEmail")=txtUsername.text
End If
dtrUser.Close()
conPubs.Close()
End If
End Sub
| | | Reply:
by:William Ryan eMVP
| | | Read is pushing the pointer up one. If you are just checking for one value,
use an Output parameter or executescalar. you can use it like SELECT Email
from whatever
...then just use Dim s as String = cmd.ExecuteScalar . If the query returned
bill@somewhere.net then that's what the string would be. Assuming that you
really want a datareader though, get rid of that .Read statement, b/c at
most, you'll only be dealing with one row. If you are using the 1.1
framework, replace it with.
If dtrUser.HasRows Then
While dr.Read
If CType(dtrUser("accountstat"), String) = "inactive" Then
txtInvalid.text = "Your account has not been activated. Follow the
instructions sent to you via email to activate your account."
End While
End If
Now,first off, get rid of the name based lookup . It's slow and wasteful.
Either use the getordinal method
Dim i as Integer = dtrUser.GetOrdinal("accountstat") and then reference this
with
dtrUser(i)
or just use the index. The index will be the position the field is in the
query so the first field will always be 0.
Now, TURN ON OPTION STRICT! This code shouldn't even compile and C#
wouldn't let this run. Option Strict Off = Option Slow ON and Option Bugs
On
this should actually read dtrUser(0).GetString("accountstat") ' or the index
you want to reference instead. Now the logic is still in question but this
is the crux of the problem. I'd also use some breakpoints and assertions
just to see what's happening... but before you do anything else, Turn On
Option Strict... trust me on this.
If you have any specific problems, let me know, we'll get you through it but
this should fix it.
Cheers,
Bill
|
Posted by Xander Zelders
 
|
0 Comments:
Post a Comment
<< Home