What is the problem with the following login code? (asp.net vb)

[rambok4]

What is the problem with the following login code?

Dim conn As SqlCommand = New SqlCommand("Data Source=RAHUL-034890AF0\SERVER2005;Initial Catalog=user_accounts;Integrated Security=True")
        conn.Prepare()
        Dim command As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("select", conn.Connection)
        command.Parameters(1).Value = txtusername.Text
        command.Parameters(2).Value = txtpass.Text
        Dim i As Integer = command.ExecuteNonQuery
        conn.BeginExecuteNonQuery()
        If Page.IsValid Then
            Response.Redirect("~/salesbill.aspx")
        Else
            Lblerror.Text = "Username and password do not match"
        End If
        conn.Dispose()

I have tested the query and it works fine. The following is the error, which seems to be related to prepare statement. Im using SQL2005 and Vwd2008. The following is the error page:

Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 17:         lbldate.Text = DateTime.Now
Line 18:         Dim conn As SqlCommand = New SqlCommand("Data Source=RAHUL-034890AF0\SERVER2005;Initial Catalog=user_accounts;Integrated Security=True")
Line 19:         conn.Prepare()
Line 20:         Dim command As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("select", conn.Connection)
Line 21:         command.Parameters(1).Value = txtusername.Text

Source File: D:\sms_new\loginpage.aspx.vb    Line: 19 

Stack Trace: 

[NullReferenceException: Object reference not set to an instance of an object.]
   System.Data.SqlClient.SqlCommand.Prepare() +85
   _Default.btnlogin_Click(Object sender, EventArgs e) in D:\sms_new\loginpage.aspx.vb:19
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746

[Ramesh S]

In line 18, you are using SqlCommand object to open a connection which is wrong. You need to use SqlConnection object to open a databas connection.

SqlCommand represents a Transact-SQL statement or stored procedure to execute against a SQL Server database.

Try the following code.

Protected Sub btnLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim connStr As String = "Data Source=RAHUL-034890AF0\SERVER2005;Initial Catalog=user_accounts;Integrated Security=True"
        Dim conn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(connStr)
        Dim command As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand()
        command.Connection = conn
        conn.Open()
        command.CommandText = "YourProcName"
        command.CommandType = CommandType.StoredProcedure
        command.Parameters.Add(New SqlParameter("@Use_Name", System.Data.SqlDbType.VarChar, 20)).Value = txtUserName.Text
        command.Parameters.Add(New SqlParameter("@Password", System.Data.SqlDbType.VarChar, 6)).Value = txtPasswod.Text

        Dim reader As Data.SqlClient.SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
        Dim result As Boolean = reader.HasRows
        reader.Close()
        conn.Close()
        If result Then
            Response.Redirect("~/salesbill.aspx")
        Else
            lblError.Text = "Username and password do not match"
        End If

[rambok4]

No offense but the above is simply another way of writing what i wrote... It did not help me changing that line either...

This thing seemed to work for me.

Protected Sub btnlogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnlogin.Click
        lbldate.Text = DateTime.Now

        Dim conn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection("Data Source=RAHUL-034890AF0\SERVER2005;Initial Catalog=user_accounts;Integrated Security=True")
        conn.Open()

        Dim command As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("SELECT count (*) FROM [employees] WHERE (password = '" + txtpass.Text + "') AND (employee_id = '" + txtusername.Text + "')", conn)
        Dim L As Integer = Command.ExecuteScalar

        If L > 0 Then
            Response.Redirect("~/salesbill.aspx")
            Session("Username") = txtusername
        Else
            Lblerror.Text = "Username and password do not match"
            Lblerror.Visible = True
        End If
        conn.Close()
    End Sub

People use it as a reference, dont simply copy paste...

[kvprajapati]

Run your program with following values

txtPass.Text="1' or '1'='1"
 txtusername.Text="1' or '1'='1"

Dim command As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("SELECT count (*) FROM [employees] WHERE (password = '" + txtpass.Text + "') AND (employee_id = '" + txtusername.Text + "')", conn)