come on SQLdatareader....read....read....read...ok help.

OK, I'm using VS2003 and I'm having trouble. The page works perfectly when I created it with WebMatrix but I want to learn more about creating code behind pages and this page doesn't work. I think it has some things to do with Query builder but I can't seem to get change outside "please register". I have the table populated and it is not coming back with "login successful" or "password wrong" when I've entered correct information. Enclosed is what I've done in VS2003. Can you see where my error is? Any help would be greatly appreciated.
Thanks again.

Imports System.data.sqlclient
Imports System.Data
Public Class login2
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.SqlCommand1 = New System.Data.SqlClient.SqlCommand
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "server=LAWORKSTATION;user id=sa;database=test;password=t3st"
'
'SqlCommand1
'
Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = 'txtusername.text')"
Me.SqlCommand1.Connection = Me.SqlConnection1

End Sub
Protected WithEvents lblUsername As System.Web.UI.WebControls.Label
Protected WithEvents lblPassword As System.Web.UI.WebControls.Label
Protected WithEvents txtUsername As System.Web.UI.WebControls.TextBox
Protected WithEvents txtPassword As System.Web.UI.WebControls.TextBox
Protected WithEvents btnSubmit As System.Web.UI.WebControls.Button
Protected WithEvents lblMessage As System.Web.UI.WebControls.Label
Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents SqlCommand1 As System.Data.SqlClient.SqlCommand

'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
End Sub

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
SqlConnection1.Open()
Dim dr As SqlDataReader = SqlCommand1.ExecuteReader
If dr.Read() Then
If dr("password").ToString = txtPassword.Text Then
lblMessage.Text = "login successful"
Else
lblMessage.Text = "Wrong password"
End If
Else
lblMessage.Text = "Please register"
End If
dr.Close()
SqlConnection1.Close()
End Sub
End ClassTake a look at this line here:

Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = 'txtusername.text')"

What you are asking here is to retrieve all [pass] fields from the rows in [Customer] table where the value of field [email] is "txtusername.text".

What you probably meant to ask is retrieve all [pass] fields from the rows in [Customer] table where the value of field [email] is the same as the value of txtUsername.Text.

Which should translate into command text like so:

Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = '" & txtUsername.Text & "')"

That should work. However, now you need to think about the possibility of a SQL injection attacks. Suppose somebody enters this is string as an email (single qoutes are significant):

'); DELETE Customer; SELECT ('

As you might've guessed, that would neatly zap your Customer table.

There are many ways to protect yourself from such an attack. Here's one possibility:

Create a function like this (pardon my VB.NET, it's rusty):

Function Tick(s As String)
Tick = s.Replace("'", "''")
End Function

And modify the command text assignment line like so:

Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = '" & Tick(txtUsername.Text) & "')"

That would make sure that any attempts to close the tick marks in the statement would fail.

Anyways, happy coding!|||Better than trying to outsmart folks trying SQL Injection attacks,use parameters.|||OK, for testing purposes I did change it with querybuilder and this is what VS2003 enters in for the command:

Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = ' "" & txtUsername.text & "" ')"

It enters double "" by default...and it doesn't work.

I then manually entered you line precisely as typed above:

Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = '" & txtUsername.Text & "')"

I've also tried:
Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE email = '" & txtUsername.Text & "'"

It still doesn't work. The response is always "Please register".

I've tested the connection and it works. The table data is correct when I enter in the right login and password but the prompt is nothing but "Please register". This page without the code behind works flawlessly. I'm stumped.|||Erm unless I'm reading this incorrectly isn't the result of txtUserName.text ALWAYS empty? You're populating the command string before anyone's typed in their name.

Try moving the ...CommandText = ... line just before the ExecuteReader|||Good catch, pkr!

He is right. Although we've fixed the one problem, there's still the other one present: InitializeComponent is called (and the CommandText property initialized) at the time the page is loading, which prevents the actual submitted value of txtUsername from ever being submitted.

As "douglas.reily" noted, time to call in parameters for help.

First, let's modify our query to this:

SELECT pass FROM Customer WHERE (email = @.email)

Then, just before you call ExecuteReader, add parameter to the command:

Me.SqlCommand1.Parameters.Add(New SqlParameters("@.email", textUsername.Text)

That should do it.|||I think you are reading it incorrectly. SQLdatareader runs SQLcommand1 which, in turn, runs the query for getting the login and password. SQLdatareader is run only when the user has clicked on the "submit" button and txtUsername.text is populated then. At least, I think this is how it happens.
I'm going to move to stored procedures and parameters instead but this is just bugging me that this page works perfectly fine without a code behind page but it has problems when I redid it with VS2003.|||OK, I've made some changes and added a parameter. In the "click" sub I've populated the parameter like this:

SqlCommand1.Parameters.Item("@.email").Value = txtUsername.Text

This seems to work. Thanks for all your help.|||Glad it's working. But out of interest does it still work in you put the ....value = txtUserName.Text... line back to it's original position. i.e. not in the same function as the button event?