Drax Drax - 2 months ago 17
Vb.net Question

Visual Studio Local Database, check if boolean is true

first time poster here.. I've been struggeling with this problem for a while.
This piece of code checks if the combination of username and password exist, and if it does it redirects to a new form.
The problem is that i also wanna check if a bit value is true or false, and if it then redirect to another page aswell. I just dont know how to.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles
Button1.Click
Dim connection As New SqlClient.SqlConnection
Dim command As New SqlClient.SqlCommand
Dim myData As SqlClient.SqlDataReader
Dim Dataset As New DataSet
Dim adaptor As New SqlClient.SqlDataAdapter
connection.ConnectionString = ("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\NewFolder1\Members.mdf;Integrated Security=True")
command.CommandText = "SELECT * FROM [User] WHERE username = '" & TextBox1.Text & "' AND password= '" & TextBox2.Text & "';"
connection.Open()
command.Connection = connection
adaptor.SelectCommand = command
adaptor.Fill(Dataset, 0)
myData = command.ExecuteReader

If Not myData.HasRows Then
TextBox1.Clear()
TextBox2.Clear()
MsgBox("Forkert login, prøv igen")
ElseIf myData.HasRows Then
Me.Hide()
LoggetInd.Show()
End If

Answer

Here is what you can do:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim connection As New SqlClient.SqlConnection
    Dim command As New SqlClient.SqlCommand
    Dim myData As SqlClient.SqlDataReader
    connection.ConnectionString = ("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\NewFolder1\Members.mdf;Integrated Security=True")
    'Don't use SELECT *, call out the columns you want by name, in the order you want them
    command.CommandText = "SELECT Username, Password, Bit1 FROM  [User] WHERE username = '" & TextBox1.Text & "' AND password= '" & TextBox2.Text & "';"
    connection.Open()
    command.Connection = connection
    myData = command.ExecuteReader(CommandBehavior.CloseConnection)
    Dim dbUsername As String, dbPassword As String, dbBit1 As Boolean
    If myData.Read Then
        'Access the data in the datareader using a 0-based index
        'Be careful as this requires you to know the datatype in the database
        'If you have a 64bit integer stored in the database,
        'you can't call GetInt32, you have to call GetInt64.
        dbUsername = myData.GetString(0)
        dbPassword = myData.GetString(1)
        dbBit1 = myData.GetBoolean(2)
    End If
    'Don't forget to Close all your DataReaders
    myData.Close()
    If dbUsername = "" Then
        TextBox1.Clear()
        TextBox2.Clear()
        MsgBox("Forkert login, prøv igen")
    Else
        If dbBit1 Then
            'Redirect as needed
        Else
            Me.Hide()
            LoggetInd.Show()
        End If
    End If
End Sub

Plutonix is right, you need to use a hash to encrypt/store your passwords. You also need to use SQL parameters. Your current method is an SQL injection playground, among other things.

Call Close on all your datareaders when you are done with them, if not you will have open SQL connections all over the place. When you call ExecuteReader, be sure to use CommandBehavior.CloseConnection. This closes the Connection automatically after you Close the datareader.

This will hopefully get your code working, but you do need to make additional changes for security and stability.

-E