Comparing datareader result to a string

[Guest]

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 The
Dim conPubs As SqlConnectio
Dim cmdSelectRecord As SqlComman
Dim dtrUser As SqlDataReade

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 The
If dtrUser("accountstat") = "inactive" The
txtInvalid.text = "Your account has not been activated. Follow the instructions sent to you via email to activate your account.
Els
' Session("uid") = dtrUser("uid"
' Response.Redirect(Session("Referer")
End I
Els
txtInvalid.text = "Invalid email address and/or password
session("forgotEmail")=txtUsername.tex
End I

dtrUser.Close(
conPubs.Close(
End I
End Sub

 

[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
(e-mail address removed) 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

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."