none
Login functionality using VB and MS SQL RRS feed

  • Question

  • Hello All!

    I have a bit of a problem...

    Having been banging my head off my keyboard for the past two days hopefully someone on here might be able to help me! Currently i have a database on sequel server which is connected to a VB web application.

    Ideally I would like the user to enter his/her details and if the username/password matches with the data on the server, they are brought to a new page. However, things aren't quite going to plan and at runtime i'm receiving im getting a few errors.

    Thanks for taking the time to look at the code. Massive virtual high five for anyone who can point me in the right direction!!

    Here we go...

     

    Protected

     

    Sub Button2_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button2.Click

     

     

    Dim MyConnection As SqlConnection = New SqlConnection()

    MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings(

    "peidbdub1.Health_Care_Compliance.dbo").ConnectionString             

    Dim theSqlCommand As New SqlCommand("SELECT [Uid] FROM [tblUserPasswords] WHERE [username] = @username AND [password] =@password")

     

    Dim theUsername As New SqlParameter("@username", SqlDbType.NVarChar, 50)

    theUsername.Value =

    "Username"

     

    Dim thePassword As New SqlParameter("@password", SqlDbType.NVarChar, 50)

    thePassword.Value =

    "Password"

    theSqlCommand.Parameters.Add(theUsername)

    theSqlCommand.Parameters.Add(thePassword)

     

     

    'open the connection

    theSqlCommand.Connection.Open()

     

    Dim theDataReader As SqlDataReader = theSqlCommand.ExecuteReader(CommandBehavior.CloseConnection)

     

     

    Dim theLoginType As Object = Nothing

     

    If theDataReader.HasRows Then

    Response.Redirect(

    "WebForm1.aspx")

     

    End If

    theSqlCommand.Connection.Close()

     

     

     

    End Sub

     

    Friday, October 15, 2010 10:48 AM

Answers

  • You need to set the Connection property of the Command (cmd) object to MyConnection (from your code) before calling the Open method.

    cmd.Connection = MyConnection
    


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by liurong luo Tuesday, October 19, 2010 2:31 AM
    Friday, October 15, 2010 4:36 PM
  • Could you post the line of code on which the error occurs?

    The Connection property of the Command is an object and it sounds like you're setting a string to it rather than a Connection object.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by liurong luo Tuesday, October 19, 2010 2:31 AM
    Monday, October 18, 2010 11:49 AM

All replies

  • So what type of problem are you having? Maybe you could indicate any errors, unexpected behavior, etc.

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, October 15, 2010 12:50 PM
  • So what type of problem are you having? Maybe you could indicate any errors, unexpected behavior, etc.

    Paul ~~~~ Microsoft MVP (Visual Basic)


    Thanks for taking the time to reply

    Since my last post I've made a redraft of my code:

    Again, its a basic login form. The Error I'm receiving is "SQLException was unhandled - Login failed for user sa"

    Heres the code:

     

    Imports

     

    System.Data.SqlClient

     

     

    Public

     

    Class LoginForm

     

    Dim inc As Integer

     

    Dim MaxRows As Integer

     

    Dim con As New SqlConnection

     

    Dim ds As New DataSet

     

    Dim da As New SqlDataAdapter

     

    Dim sql As String

     

    Dim i As Boolean

     

    Dim theSqlCommand As New SqlCommand

     

     

     

     

    Private Sub btnButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnButton.Click

    con.ConnectionString =

    "Data Source=IDB;Initial Catalog=Health_Care_Compliance;Persist Security Info=True;User ID=sa;Password=***********"

    con.Open()

    sql =

    "SELECT [Uid] FROM [tblUserPasswords] WHERE [username] = txtUsername AND [password] =txtPassword"

    i = theSqlCommand.ExecuteNonQuery

     

    If (i = True) Then

    MsgBox(

    "You are now logged on")

     

    Else

    MsgBox(

    "Incorrect Login Details")

     

    End If

     

     

     

    End Sub

    End

     

    Class

    Friday, October 15, 2010 3:12 PM
  • If you are using SQL Server security then that would indicate that the password in your connection string for the sa account is invalid. You will either need to change that password or configure your connection string for Windows Integrated (Trusted) Security.

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, October 15, 2010 3:39 PM
  • The connection string seems ok, i've been changing around the code for the last day or two trying to find a solution. My main problem, or error, has been with opening the connection string so maybe changing that password is the way to go.

    A pretty common error has been "Object reference not set to an instance of an object." Null Reference Exception was Unhandled. And this error occurs at the line of code:

    cmd.Connection.Open()

     

    I have this cmd declared as a New SqlCommand

     

    This occurs at when i run the project. Any ideas?

    Friday, October 15, 2010 3:48 PM
  • Also when I test the connection with the SA account username it seems to work ok...

    Friday, October 15, 2010 3:52 PM
  • You need to set the Connection property of the Command (cmd) object to MyConnection (from your code) before calling the Open method.

    cmd.Connection = MyConnection
    


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by liurong luo Tuesday, October 19, 2010 2:31 AM
    Friday, October 15, 2010 4:36 PM
  • Hi Paul, thanks for the advice!

    I tried to set the connection property of the command to my connection string but I'm getting this error.

    "Value of type 'String' cannot be converted to 'System.Data.SqlClient.SqlCommand"

    Any ideas?

    Thanks

    Monday, October 18, 2010 9:29 AM
  • Could you post the line of code on which the error occurs?

    The Connection property of the Command is an object and it sounds like you're setting a string to it rather than a Connection object.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by liurong luo Tuesday, October 19, 2010 2:31 AM
    Monday, October 18, 2010 11:49 AM
  • ya thats exactly what happend, i fixed it and it works now

     

    Thanks for your help, very much appreciated!

    Monday, October 18, 2010 2:06 PM