Passing Database Information into Vb.net on a row that has been selected

Question

I have a vb.net forms program linked to by an oledb connection to a database. The database contains a login table called tbl_user. In the program the user enters their username and password which is compared to the table values

Dim sql As String = "SELECT * FROM tbl_user WHERE username='" & txtUsername.Text & "' AND password = '" & txtPassword.Text & "' "
            Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)
            sqlCom.Connection = conn
            conn.Open()
            Dim sqlRead As System.Data.OleDb.OleDbDataReader = sqlCom.ExecuteReader()

However i also wish to pass through another piece of data on the row it detected, this column is called accesslvl. So i am trying to pass that through then compare it to statements. I cant figure out how to also select and return the accesslvl value.

Things Attempted:

Dim sql As String = "SELECT * FROM tbl_user WHERE username='" & txtUsername.Text & "' AND password = '" & txtPassword.Text & "' "
        Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)
        Dim accesslvl As String = "SELECT accesslvl FROM tbl_user WHERE username='" & txtUsername.Text & "' AND password = '" & txtPassword.Text & "' "
        Dim accesslvlCom As New System.Data.OleDb.OleDbCommand(accesslvl)
        Dim accesslvlInt As Integer
        Open Database Connection
        sqlCom.Connection = conn
        conn.Open()
        accesslvlCom = accesslvlInt

variations on the sql string such as

-"SELECT username,password,accesslvl FROM tbl_user WHERE username='" & txtUsername.Text & "' AND password = '" & txtPassword.Text & "' "

The values of accesslvl are all integers ranging from 0-3.The point of this is so that when accesslvl is returned it will load the correct ui.

I was starting at the bottom up, so i was going to sanitize my inputs once i was sure it was all working properly. Do you think it'd be better to do all that before sorting this issue out? — Zak Stevenson, Nov 18 '15 at 09:53
If `accesslvl` is in `tbl_user` then it will be returned in `SELECT * ..." provided there is a row matching the WHERE clause. There are a number of things wrong in the code though. Dont store passwords as plaintext and [just about everything here also[(http://stackoverflow.com/a/29187199/1070452) — Ňɏssa Pøngjǣrdenlarp, Nov 18 '15 at 14:00

Mihail Panagiotopoulos · Answer 1 · 2015-11-18T23:24:20.920

This is how i do that(hope it helps!):

Imports System.Data.OleDb
Public Class Login
        Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\database.mdb;")
        Dim cm As New OleDbCommand
        Dim da As New OleDbDataAdapter
        Dim ds As New DataSet
        Dim username, password As String
        Dim accesslvl As Integer
    Private Sub button1_click(sender as object, e as EventArgs) Handles Button1.Click
        'Opening the connection'
        cn.Open()
        cm.Connection = cn
        cm.CommandType = CommandType.Text
        cm.CommandText = "Select * from tbl_user Where username='" & txtUsername.text & "' and Password='" & txtPassword.text & "'"
        'Execute command'
        cm.ExecuteNonQuery()
        'Close the connection'
        cn.Close()
        'data adapter is an oledb object which collects data from selected command'
        da.SelectCommand = cm
        'Then it fills the data set with that data'
        da.Fill(ds)
        'If there are no data(or no rows) Then'
        If ds.Tables(0).Rows.Count = 0 Then
            MessageBox.Show("Wrong username/password error goes here", "Error", MessageBoxButtons.OK)
            Exit Sub
        End If 
        'However if there is the string username will get the value of the Username column in the row 0 (the only row if there are no duplicates)'
        username = ds.Tables(0).Rows(0).Item("Username")
        'same with this one'
        password = ds.Tables(0).Rows(0).Item("Password")
        'so now beacuse the access is no case sensitive we must check the password. If its ok we are giving the accesslvl value to the accesslvl integer'
        If txtUsername.Text = username And txtPassword.Text = password Then
        accesslvl = CInt(ds.Tables(0).Rows(0).Item("accesslvl"))
        MessageBox.Show("successful login message goes here", "Login", MessageBoxButtons.OK)
        End If
    End Sub
End Class

I added the explanations. – Mihail Panagiotopoulos Nov 18 '15 at 23:27 — Mihail Panagiotopoulos, Nov 18 '15 at 23:27

Passing Database Information into Vb.net on a row that has been selected

1 Answers1