none
Creating a login element of a Windows Form using VB.NET and an SQL database RRS feed

  • Question

  • Private Sub Login1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Login1.Click

    Dim conn As SqlClient.SqlConnection

    conn = New SqlClient.SqlConnection()

    conn.ConnectionString = "server=localhost; user id = ''; password = ''; database = 'Web Tracking'"

    Dim myAdapter = New SqlClient.SqlDataAdapter

    Dim Sqlquery = "SELECT * FROM user list Where Username = '" & ID1.Text & "' and Password = '" & Pass1.Text & "'"

    Dim myCommand As New SqlClient.SqlCommand()

    myCommand.Connection = conn

    myCommand.CommandText = Sqlquery

    myAdapter.selectcommand = myCommand

    Dim mydata As SqlClient.SqlDataReader

    mydata = myCommand.ExecuteReader() *

    If mydata.hasrows = 0 Then

    Message.Visible = True

    Else

    Log1.Visible = True

    Login1.Visible = False

    End If

    End Sub

     

     

    The error in this code is on the line with the asterix, the code for a login element of the program, it is supposed to match an entry of a database against what is entered in the textboxes and perform functions depending on if the login is valid or not.

     

    another post suggests that conn.open() is needed but once added the error shows there instead. Can anyone help?

     

    Web Tracking is the database title, ID1 and Pass1 are where the ID and password is entered.

    Sunday, February 10, 2008 2:43 PM

Answers

  • With help from the posts above and some other sources of information, here is the solution, thank you for your assistance:

     

     

    Dim conn As SqlClient.SqlConnection

            conn = New SqlClient.SqlConnection()
            conn.ConnectionString = "Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|\WebTracking.mdf; Integrated Security=True; User Instance=True"

            Try
                conn.Open()
            Catch ex As SqlException
                Dim str As String
                str = "Connection String: " & conn.ConnectionString
                str = str & "Error : " & ex.Message & Environment.NewLine
                If (ex.InnerException IsNot Nothing) Then
                    str = str & " InnerException : " & ex.InnerException.Message
                End If
                MessageBox.Show(str, "Error while connecting")
                Return
            End Try
            MessageBox.Show(conn.State.ToString())


            Dim sqlStr as String
            sqlStr = "SELECT * FROM [User List] WHERE [Username] = '" & ID1.Text & "' and [Password] = '" & Pass1.Text & "'"

            Dim myCommand As New SqlClient.SqlCommand()
            myCommand.CommandText = sqlstr.ToString()
            MessageBox.Show(myCommand.CommandText, "Command Text")
            Dim mydata As SqlClient.SqlDataReader
            myCommand.Connection = conn
            mydata = myCommand.ExecuteReader()
            If mydata.HasRows Then
                Log1.Visible = True
                Login1.Visible = False
                Button1.Visible = True
                Button2.Visible = True
                Button3.Visible = True
                Button4.Visible = True
                Grant1.Visible = True
                Message1.Visible = False
            Else
                Message1.Visible = True
            End If
        End Sub

    Friday, February 29, 2008 4:15 PM

All replies

  • Hello,

     

    Could you post the text of the error you have received ?

    It' easier to understand

    i'm surprised with your connectionstring

    i think that's better to use a SqlConnectionStringBuilder

    Dim bu As New SqlConnectionStringBuilder = new SqlConnectionStringBuilder

    bu.DataSource = "instancename"       instancenome = localhost\instance

    bu.InitialCatalog = "Web Tracking"

    bu.UserId = ID1.Text

    bu.Password = Pass1.Text

    conn.ConnectionString = bu.ConnectionString

    conn.Open

     

    If the table name is really User List in 2 words, you must enclose the table name between brackets ( well in any case it's a good habit to do it )

    Dim Sqlquery = "SELECT * FROM [user list] Where Username = '" & ID1.Text & "' and Password = '" &

     

    I prefer with StringBuilder

    Dim bu1 As New StringBuilder

    bu1.Append('SELECT * FROM [user list] WHERE [UserName] = '")   ' 1 simple quote followed by a double quote

    bu1.Append(ID1.Text)

    bu1.Append("' and [Password] = '")       ' 1 simple quote followed by a double quote at the end 1 double quote followed by 1 simple quote at the beginning

    bu1.Append(Pass1.Text)

    bu1.Append("'")    ' double quote simple quote double quote

     

    To obtain the message of error enclose your Sql Code ( open , executereader and so on  between try catch .

    I did not remember how to use it in VB but i think it will be easy for you to find it )

     

    mycommand.CommandText = bu1.ToString

     

    Another surprising line

    If mydata.hasrows = 0 Then

     

    I think that HasRow is a Boolean not an integer ( indicate whether your SqlDataReader has at least 1 row )

     

    I hope that will help you

    I'm searching for a code example of try/catch in VB

     

    Remark : the use of SqlConnectionStringBuilder and StringBuilder avoids many problems with injection attacks and errors in key words. You have only to use Intellisense

     

    Have a nice day 

     

     

    Sunday, February 10, 2008 6:18 PM
  •  

    Error shown (debug):

     

     A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll

     

     

    Line effected:

     

    mydata = myCommand.ExecuteReader()

     

     

    The corrections you suggested were picked up as errors before I had the debug on, about as incompatible as contestants of reality programmes, i've marked where the problems are in grey, either i've alter it incorrectly or what you have sent me is incompatible with VB.NET Windows Form Projects:

     

    Private Sub Login1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Login1.Click

    Dim bu As New SqlConnectionStringBuilder = SqlConnectionStringBuilder()

    bu.DataSource = "instancename"

    instancename = localhost \ instance

    bu.InitialCatalog = "Web Tracking"

    bu.UserId = ID1.Text

    bu.Password = Pass1.Text

    conn.ConnectionString = bu.ConnectionString

    conn.Open()

     

    Dim bu1 As New StringBuilder

    bu1.Append("'SELECT * FROM [user list] WHERE [UserName] = '") ' 1 simple quote followed by a double quote

    bu1.Append(ID1.Text)

    bu1.Append("' and [Password] = '") ' 1 simple quote followed by a double quote at the end 1 double quote followed by 1 simple quote at the beginning

    bu1.Append(Pass1.Text)

    bu1.Append("'") ' double quote simple quote double quote

    Dim myCommand As New SqlClient.SqlCommand()

    myCommand.Connection = conn.ToString

    myCommand.CommandText = bu1.ToString

    bu1.selectcommand = myCommand

    Dim mydata As SqlClient.SqlDataReader

    mydata = myCommand.ExecuteReader()

    If mydata.hasrows = 0 Then

    Message.Visible = True

    Else

    Log1.Visible = True

    Login1.Visible = False

    End If

    End Sub

    Monday, February 11, 2008 1:35 PM
  • Hello,

     

    I have created a small prog in VB ( not my prefered language )

     

    When i answered you, i had not my VB available , so everything was written with my memory and i may have done some errors

     

    Imports System.Data.SqlClient

    Imports System.Text

    Public Class Form1

    Dim conn As New SqlConnection

    Private Sub LoginBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoginBtn.Click

    Dim problem As Boolean = False

    Dim user As String

    Dim pass As String

    user = UserTxt.Text.Trim()

    If (user.Length = 0) Then

    MessageBox.Show("User may not be empty")

    problem = True

    End If

    pass = PassTxt.Text.Trim()

    If (pass.Length = 0) Then

    MessageBox.Show("Password may not be empty")

    problem = True

    End If

    If (problem) Then

    Return

    End If

    Dim bu As New SqlConnectionStringBuilder

    bu.DataSource = "CHAMBRE\SQLEXPRESS"

    bu.InitialCatalog = "master"

    bu.IntegratedSecurity = False

    bu.UserID = user

    bu.Password = pass

    conn.ConnectionString = bu.ConnectionString

    Try

    conn.Open()

    Catch ex As SqlException

    Dim str As String

    str = "Error : " & ex.Message & Environment.NewLine

    If (ex.InnerException IsNot Nothing) Then

    str = str & " InnerException : " & ex.InnerException.Message

    End If

    MessageBox.Show(str, "Error while connecting")

    Return

    End Try

    MessageBox.Show(conn.State.ToString())

    End Sub

    Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing

    conn.Close()

    End Sub

    End Class

     

     

    My form uses 2 textboxes and a button

    UserTxt for getting the user name ( ID1 ?)

    PassTxt for getting the password

    LoginBtn for trying to connect to the instance CHAMBRE\SQLEXPRESS

    for this test i choose the database master because it was easier for me

     

    I try to suppress the two lines of Imports ==> SqlConnection,SqlConnectionStringBuilder are unknown

    StringBuilder is defined in the namespace System.Text

     

    I have copied the habit ( when i writing in VC# ) to always add these lines to avoid any problem ( i do not want to search if these namespaces are already defined in VB )

     

    I will  continue with a database ( in construction ) which uses tables of which the names are with more than one word

    When it will be ready, i will post it

     

    I'm using C from 16 years and VC++ from 10 years , VC# from 5 years. I have discovered never to suppose that the environment where i'm working is perfect and so i have some habits which helped me everywhere i worked.

     

    I'm not criticizing you , that's only my experience which is talking

     

    Sorry for my poor english which prevents me to be clear ( French people are not good in english generally )

     

    Have a nice day

     

    Have a nice day

     

    Monday, February 11, 2008 7:02 PM
  • i am not sure if you got everything sorted but i thought i would throw this into the mix here

    Maybe it will help

     

     

    Public Class Form1

     

        Public Users_string As String = String.Empty

        Public Users_command As SqlClient.SqlCommand

        Public Users_data_reader As SqlClient.SqlDataReader

     

     

        Private Sub check_user_login()

            Users_string = "SELECT * FROM Users WHERE Name = @name AND password = @password"

            Users_command = New SqlClient.SqlCommand(Users_string, Bill_connection)

           

       With Users_command

                Users_command.Parameters.Add(New SqlClient.SqlParameter("@name", SqlDbType.int, 0)).Value = Me.nameTextBox.Text

                Users_command.Parameters.Add(New SqlClient.SqlParameter("@password", SqlDbType.nvarchar, 50)).Value = Me.passwordTextBox.Text

            End With

           

       If Users_command.Connection.State = ConnectionState.Closed Then

                Users_command.Connection.Open()

            End If

     

            Users_data_reader = Users_command.ExecuteReader()

     

            If Users_data_reader.Read Then

                If Not TypeOf Users_data_reader("name") Is DBNull Then

                    UserID_variable = CStr(Users_data_reader("name"))

                End If

                If Not TypeOf Users_data_reader("password") Is DBNull Then

                    UserName_variable = CStr(Users_data_reader("password"))

                End If

                'MsgBox("Login Successful")

            Else

                MsgBox("Incorrect User Name or Password")

            End If

            Users_data_reader.Close()

            Users_command.Connection.Close()

        End Sub

     

        'Generic variables

        Dim UserID_variable As String = String.Empty

        Dim UserName_variable As String = String.Empty

       

    End Class

     

     

    Monday, February 11, 2008 10:08 PM
  • This is how the code is looking so far, it's showing a messagebox instead of going to debug and messing the program up, but it's still not connecting to the database, I suspect that the database connection is unstable and not from my end, that or there is something missing.

     

     

    Dim bu As New SqlConnectionStringBuilder

    Dim conn As SqlClient.SqlConnection

    bu.DataSource = ".\SQLEXPRESS"

    bu.InitialCatalog = "WebTracking"

    bu.IntegratedSecurity = True

    bu.UserID = "root"

    bu.Password = ""

     

    conn = New SqlClient.SqlConnection()

    conn.ConnectionString = bu.ConnectionString

    Try

    conn.Open()

    Catch ex As SqlException

    Dim str As String

    str = "Connection String: " = conn.ConnectionString

    str = str & "Error : " & ex.Message & Environment.NewLine

    If (ex.InnerException IsNot Nothing) Then

    str = str & " InnerException : " & ex.InnerException.Message

    End If

    MessageBox.Show(str, "Error while connecting")

    Return

    End Try

    MessageBox.Show(conn.State.ToString())

     

    'Title - SqlException was unhandled

    'Description - Cannot open database "WebTracking" requested by the login.

    'the login failed. login failed for user 'STUDENT\ls244575'.

    Dim bu1 As New StringBuilder

    bu1.Append("'SELECT * FROM [User List] WHERE [Username] = '") ' 1 simple quote followed by a double quote

    bu1.Append(ID1.Text)

    bu1.Append("' and [Password] = '") ' 1 simple quote followed by a double quote at the end 1 double quote followed by 1 simple quote at the beginning

    bu1.Append(Pass1.Text)

    bu1.Append("'") ' double quote simple quote double quote

    Dim myCommand As New SqlClient.SqlCommand()

    myCommand.CommandText = bu1.ToString

    Dim mydata As SqlClient.SqlDataReader

    mydata = myCommand.ExecuteReader()

    If mydata.HasRows Then

    Message.Visible = True

    Else

    Log1.Visible = True

    Login1.Visible = False

    End If

    End Sub

    Monday, February 18, 2008 2:18 PM
  • Hello,

     

    In your code , i have seen ;

    bu.IntegratedSecurity = True

    bu.UserID = "root"

    bu.Password = ""

     

    bu.IntegratedSecurity = True ==> you are using Windows authentification so no need of the two next lines ( which are necessary only if you use Sql Server authentification corresponding to bu.IntegratedSecurity = False ). I suppose you have masked the password. A very good use

     

    I'm searching why your connection is unstable

     

    Have a nice day

    Monday, February 18, 2008 5:41 PM
  • The messagebox shows the message "open", that might assume that the database connection is stable but the debug seems to have a problem with the red highlighted line.

     

     

    Private Sub Login1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Login1.Click

            Dim bu As New SqlConnectionStringBuilder
            Dim conn As SqlClient.SqlConnection

            conn = New SqlClient.SqlConnection()
            conn.ConnectionString = "Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|\WebTracking.mdf; Integrated Security=True; User Instance=True"

            Try
                conn.Open()
            Catch ex As SqlException
                Dim str As String
                str = "Connection String: " & conn.ConnectionString
                str = str & "Error : " & ex.Message & Environment.NewLine
                If (ex.InnerException IsNot Nothing) Then
                    str = str & " InnerException : " & ex.InnerException.Message
                End If
                MessageBox.Show(str, "Error while connecting")
                Return
            End Try
            MessageBox.Show(conn.State.ToString())

            'Description - Connection String: Data Source = .\SQLEXPRESS;
            'InitialCatalogue = WebTracking; Integrated Security = True;
            'UserID = Root; Password =

            ' Error: Cannot open database "WebTracking" requested by the login.
            'the login failed. login failed for user 'STUDENT\ls244575'.

            Dim bu1 As New StringBuilder
            bu1.Append("'SELECT * FROM [User List] WHERE [Username] = '") ' 1 simple quote followed by a double quote
            bu1.Append(ID1.Text)
            bu1.Append("' and [Password] = '") ' 1 simple quote followed by a double quote at the end 1 double quote followed by 1 simple quote at the beginning
            bu1.Append(Pass1.Text)
            bu1.Append("'") ' double quote simple quote double quote
            Dim myCommand As New SqlClient.SqlCommand()
            myCommand.CommandText = bu1.ToString
            Dim mydata As SqlClient.SqlDataReader
            mydata = myCommand.ExecuteReader()
            If mydata.HasRows Then
                Message.Visible = True
            Else
                Log1.Visible = True
                Login1.Visible = False
            End If
        End Sub

    Tuesday, February 19, 2008 2:55 PM
  • Hello,

     

    I have seen that :

            ' Error: Cannot open database "WebTracking" requested by the login.
            'the login failed. login failed for user 'STUDENT\ls244575'.

     

    This message is not clear but very usual : it means that either the login name STUDENT\ls244575 or the password you have given are false

    - either this login STUDENT\ls244575 does not exist for SQL Server

    - either the user

     

    For me, the windows user ( as you have IntegratedSecurity = true ) has no access to the database and i'm not surprised

    STUDENT : name of domain or computer

    ls244575 : name of a windows user

     

    You have to open SSMSE ( Sql Server Management Studio Express )

    Expand the node of your instance

     

    expand the node corresponding to Security

    if STUDENT\ls244575 does not exist , it has no right for this instance and you can"t connect to it

    right click on STUDENT\ls244575 

    in the context menu click on properties

    click on user mapping ( 3rd line )

    in the upper right panel you will see a list of databases

    click on the line corresponding to your database to select it ( no check in the checkbox at the beginning of the line )

    in the lower right panel, you will see the permissions that user has on this database

    it must have at minimum the role public  checked ( if not connect forbidden )

    it you have the role db_owner checked and no connection possible : enormous problem with that database. A db_owner has all rights of a database...

    if the user must have read and write access , check db_datareader and db_datawriter

    if the user has db_denydatareader : no SELECT on this database ( interesting...)

    if the user has db_denydatawriter : no INSERT,UPDATE,DELETE on this database

     

    If no role is checked no access to the database ( and no connection )

     

    Could you check that, please ?

     

    Have a nice day

     

     

    Tuesday, February 19, 2008 4:40 PM
  • Sorry, i've been providing incorrect data, which means the post before this one is irrelevant. The errors sent with my previous post were invalid as they were corrected and i had forgotten to remove them from the area of the program which they were in. The light blue highlighted areas are the modifications i've made, according to the message boxes, the database connection is open, the SQL statement is being processed and yet the red highlighted area is showing in the debug stage.

     

     

    Private Sub Login1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Login1.Click

            Dim bu As New SqlConnectionStringBuilder
            Dim conn As SqlClient.SqlConnection

            conn = New SqlClient.SqlConnection()
            conn.ConnectionString = "Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|\WebTracking.mdf; Integrated Security=True; User Instance=True"

            Try
                conn.Open()
            Catch ex As SqlException
                Dim str As String
                str = "Connection String: " & conn.ConnectionString
                str = str & "Error : " & ex.Message & Environment.NewLine
                If (ex.InnerException IsNot Nothing) Then
                    str = str & " InnerException : " & ex.InnerException.Message
                End If
                MessageBox.Show(str, "Error while connecting")
                Return
            End Try
            MessageBox.Show(conn.State.ToString())


            Dim bu1 As New StringBuilder
            bu1.Append("'SELECT * FROM [User List] WHERE [Username] = '") ' 1 simple quote followed by a double quote
            bu1.Append(ID1.Text)
            bu1.Append("' and [Password] = '") ' 1 simple quote followed by a double quote at the end 1 double quote followed by 1 simple quote at the beginning
            bu1.Append(Pass1.Text)
            bu1.Append("'") ' double quote simple quote double quote
            Dim myCommand As New SqlClient.SqlCommand()
            myCommand.CommandText = bu1.ToString()
            MessageBox.Show(myCommand.CommandText, "Command Text")
            Dim mydata As SqlClient.SqlDataReader
            mydata = myCommand.ExecuteReader()
            If mydata.HasRows Then
                Message.Visible = True
            Else
                Log1.Visible = True
                Login1.Visible = False
            End If
        End Sub

     

    Wednesday, February 20, 2008 1:37 PM
  • With help from the posts above and some other sources of information, here is the solution, thank you for your assistance:

     

     

    Dim conn As SqlClient.SqlConnection

            conn = New SqlClient.SqlConnection()
            conn.ConnectionString = "Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|\WebTracking.mdf; Integrated Security=True; User Instance=True"

            Try
                conn.Open()
            Catch ex As SqlException
                Dim str As String
                str = "Connection String: " & conn.ConnectionString
                str = str & "Error : " & ex.Message & Environment.NewLine
                If (ex.InnerException IsNot Nothing) Then
                    str = str & " InnerException : " & ex.InnerException.Message
                End If
                MessageBox.Show(str, "Error while connecting")
                Return
            End Try
            MessageBox.Show(conn.State.ToString())


            Dim sqlStr as String
            sqlStr = "SELECT * FROM [User List] WHERE [Username] = '" & ID1.Text & "' and [Password] = '" & Pass1.Text & "'"

            Dim myCommand As New SqlClient.SqlCommand()
            myCommand.CommandText = sqlstr.ToString()
            MessageBox.Show(myCommand.CommandText, "Command Text")
            Dim mydata As SqlClient.SqlDataReader
            myCommand.Connection = conn
            mydata = myCommand.ExecuteReader()
            If mydata.HasRows Then
                Log1.Visible = True
                Login1.Visible = False
                Button1.Visible = True
                Button2.Visible = True
                Button3.Visible = True
                Button4.Visible = True
                Grant1.Visible = True
                Message1.Visible = False
            Else
                Message1.Visible = True
            End If
        End Sub

    Friday, February 29, 2008 4:15 PM