retrieving data from database to a textbox

[kn2790]

Hi,

Doing a project in vb.net whereby i have different levels of users accessing the system, i have established a connection with the database where all the info will be stored but got stuck when trying to retrieve the data.. i am using and oledb connection

At the log-in form when user types there details in (staffid) & (password), and click the button, these details are verified to allow access or not...

Here is what i have coded so far

Imports MySql.Data.MySqlClient
Imports System.Data.OleDb
--------------------
Public Class StaffM

    Dim cN As OleDbConnection
    Dim myDataReader As OleDbDataReader
    Dim myOleDbCommand As OleDbCommand
    Dim username As String, password As String

------------------
 Private Sub loginS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles loginS.Click

        cN = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\LVuserdb.accdb;Jet OLEDB:Database Password=1")
        myOleDbCommand = New OleDbCommand("SELECT staffid,password FROM Staff", cN)
        username = Me.staffid.Text
        password = Me.staffpass.Text

        ' Open a connection.
        cN.Open()

        ' Look up the user name/password.
        myDataReader = myOleDbCommand.ExecuteReader()


        ' See if there is a match.
        Do While (myDataReader.Read())
            If (myDataReader.IsDBNull(4)) Then

                ' If there is no match.
                ' Do not allow the login.
                Me.Hide()
                MsgBox("Invalid user name/password")
            Else
                ' There is a match.
                ' Display the program's main form.
                Staffmenu.Show()
                Me.Hide()
            End If
        Loop


        ' Close when finished reading.
        myDataReader.Close()

        ' Closes the connection when done
        cN.Close()



    End Sub
---------------------------

My problem is
myDataReader = myOleDbCommand.ExecuteReader()

and im not too sure if the Do while and If statement bit is correct

Any help would be much appreciated.

Thanks

[Phasma]

kn2790
You're on the right track. Inside the Loop you just need to compare dReader.GetValue(0) and dReader.GetValue(1) with your variables username and password to see if there is a match. This is assuming index 0 is your username and index 1 is the password from your data source. The variable types must match the fields returned from the Data Source

While myDataReader.Read
     
    If username = myDataReader.GetValue(0) AND  _
    password = myDataReader.GetValue(1) Then
	Staffmenu.Show()
                Me.Hide()
                Exit While
    End If
     
             
 End While

Hope this helps

[kn2790]

kn2790
You're on the right track. Inside the Loop you just need to compare dReader.GetValue(0) and dReader.GetValue(1) with your variables username and password to see if there is a match. This is assuming index 0 is your username and index 1 is the password from your data source. The variable types must match the fields returned from the Data Source

While myDataReader.Read
     
    If username = myDataReader.GetValue(0) AND  _
    password = myDataReader.GetValue(1) Then
	Staffmenu.Show()
                Me.Hide()
                Exit While
    End If
     
             
 End While

Hope this helps

firstly, thanks for replyin much appreciate it...

ok i understand i need to compare the the values with the same variable type, i took out the

If myDataReader.HasRows = 0 Then

bit and subbed it with the code you suggested

but im still having a problem with

' Look up the user name/password.
        myDataReader = myOleDbCommand.ExecuteReader()

'No value given for one or more required parameters.'
im guessin that means its not reading anything in the first place?

also for a comparison could i jus use the or comparison of T or an If statement for the recordset like

If LVuserdbDataSet.StaffDataTable =

thanks

[c0deFr3aK]

firstly, thanks for replyin much appreciate it...

ok i understand i need to compare the the values with the same variable type, i took out the

If myDataReader.HasRows = 0 Then

bit and subbed it with the code you suggested

but im still having a problem with

' Look up the user name/password.
        myDataReader = myOleDbCommand.ExecuteReader()

'No value given for one or more required parameters.'
im guessin that means its not reading anything in the first place?

also for a comparison could i jus use the or comparison of T or an If statement for the recordset like

If LVuserdbDataSet.StaffDataTable =

thanks

I suspect you are missing something with your query statement.

try this:

myOleDbCommand = New OleDbCommand("SELECT staffid,password FROM Staff WHERE staffid = '" & username &"' AND password = '" & password &"'", cN)
        username = Me.staffid.Text
        password = Me.staffpass.Text

Therefore if your string "username" and "password" did not match to your specific query it will return invalid login.

[kn2790]

Thanks for your post .

nope still saying

'No value given for one or more required parameters.'

Googled the error to see if it was common and found this:

http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/6e06fe86-b946-4803-973f-8604d335c776/

this seems to suggest that the sql bit is the problem as well, i checkd the database and all the relevant columns etc are there

anyone know why?

[kn2790]

ok well i went through this

http://www.vbforums.com/showthread.php?t=451558#syntax

to see how i could solve the error knowing that its a problem with my sql statement...

changed the column names in my DB table, checked that it wasnt a reserved word, checked if it work and still no luck...

At the moment my code looks like this

cN = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\LVuserdb.accdb;Jet OLEDB:Database Password=1")
myOleDbCommand = New OleDbCommand("SELECT StaffID,Password FROM Staff WHERE Staffid = '" & username & "' AND Password1 = '" & password & "'", cN)
username = Me.staffid.Text
password = Me.staffpass.Text

can anyone see the problem? i bet its blindingly obvious but i jus cant seem to pinpoint it

[c0deFr3aK]

cN = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\LVuserdb.accdb;Jet OLEDBatabase Password=1")
myOleDbCommand = New OleDbCommand("SELECT StaffID,Password FROM Staff WHERE Staffid = '" & username & "' AND Password1 = '" & password & "'", cN)
username = Me.staffid.Text
password = Me.staffpass.Text

you missed the "SELECT StaffID,Password1"
in some database "Password" is a reserve word.

[kn2790]

alrite i changed that after i realisedd as soon as , still got the problem though

'No value given for one or more required parameters.'

[Dalvinder singh]

This error will exist when there is error in myOledbCommand.
Please Check it carefully only then error will removed. Otherwise there is no problem with rest of your code.

[kn2790]

i have checked the myOledbCommand bit a few times and i think i have everything included... i really cnt seem to find the problem

[Dalvinder singh]

alrite i changed that after i realisedd as soon as , still got the problem though

'No value given for one or more required parameters.'

Value that has to be given by you: integer or string in staffId.
check your data type in database.
staffid is integer or string
staffPassword is string.

According to me, problem arises only with myoledbcommand: "select * from staffId="& username &""

[kn2790]

checked the values and both are string, checked database agian too and it seems fine..
i went through the Sql statement and there are no quotes or anything missing

myOleDbCommand = New OleDbCommand("SELECT StaffID,Password1 FROM Staff WHERE Staffid = '" & username & "' AND Password1 = '" & password & "'", cN)

[Teme64]

A few obvious points are wrong with the code

cN = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\LVuserdb.accdb;Jet OLEDBatabase Password=1")
myOleDbCommand = New OleDbCommand("SELECT StaffID,Password FROM Staff WHERE Staffid = '" & username & "' AND Password1 = '" & password & "'", cN)
username = Me.staffid.Text
password = Me.staffpass.Text

First, get the user given values before you build the SQL clause

cN = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\LVuserdb.accdb;Jet OLEDBatabase Password=1")
username = Me.staffid.Text
password = Me.staffpass.Text
myOleDbCommand = New OleDbCommand("SELECT StaffID,Password1 FROM Staff WHERE Staffid = '" & username & "' AND Password1 = '" & password & "'", cN)

Second, do you open the connection?

cN = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\LVuserdb.accdb;Jet OLEDBatabase Password=1")
username = Me.staffid.Text
password = Me.staffpass.Text
cN.Open() ' Open the DB connection
myOleDbCommand = New OleDbCommand("SELECT StaffID,Password1 FROM Staff WHERE Staffid = '" & username & "' AND Password1 = '" & password & "'", cN)

[kn2790]

ok changed the ordering of the first bit like suggested... made no difference?

when i put the cn.open to open the connection there i got another error! 'Could not find installable ISAM'

it was originally after that SQL statement

[Teme64]

ok changed the ordering of the first bit like suggested... made no difference?

Because the error happens when you try to open the connection.

Seems that there's something wrong in your connection string. See The connection string reference for MS Access connection strings. Second, try googling "Could+not+find+installable+ISAM" to see if you find a solution.

I'm sorry but I can only give those links for help. But I hope you get some 'ideas' how to solve the connection problem. I also suggest putting cN.Open() in a Try...Catch...End Try block to catch the error. If you can't get the problem solved, please post as detailed error message or messages as possible.

[abrars]

Public Class login
Public userName As String
Public password As String
Public Sub loginCheck(ByVal username As String, ByVal password As String)
Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\abrar\My Documents\Database2.mdb")
Dim sql = "SELECT Username,Password FROM Customer WHERE Username = '" & Trim(username) & "' AND Password = '" & password & "'"
Dim cmd As New OleDbCommand(sql, connection)
Try
connection.Open()
Catch ex As InvalidOperationException
MsgBox(ex.Message)
End Try
Dim custReader As OleDbDataReader = cmd.ExecuteReader()
Try
If custReader.Read = False Then
MsgBox("CHECK USERNAME AND PASSWORD", MsgBoxStyle.OkOnly)
Else
BrowseDVD.Show()
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class

Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click
Dim login As New login
login.loginCheck(UsernameTextBox.Text, PasswordTextBox.Text)
End Sub

hope this code will help you ,if you use little bit of logic you can get your result.

[Teme64]

The OP has a problem with connection string. The code above (with path to Access file fixed) is a good sample of Access connection string for the OP.

This goes a bit OT, but the sample code has a tiny problem i.e. calling login.loginCheck("John", "Doe';delete customer -- ") would have some unwanted 'side-effects' at least with SQL Server. Access may require a slightly different syntax ;)

[kn2790]

umm the problem may be with the connection string and i checked the link you supplied, very useful thanks....

However i did not change that and used the code that abrars posted, it seems to work...well a bit anyway.... now its jus coming up with the MsgBox("CHECK USERNAME AND PASSWORD", MsgBoxStyle.OkOnly) even when i put the correct username and password in.... does it mean theres a problem with my sql statement? or isit a problem with the coding

Private Sub loginS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles loginS.Click

       
        Dim connection As New OleDbConnection(ConnectString)
        Dim sql = ("SELECT StaffID,Password1 FROM Staff WHERE Staffid = '" & username & "' AND Password1 = '" & password & "'")
        Dim cmd As New OleDbCommand(sql, connection)
        Try
            connection.Open()
        Catch ex As InvalidOperationException
            MsgBox(ex.Message)
        End Try
        Dim custReader As OleDbDataReader = cmd.ExecuteReader()
        Try
            If custReader.Read = False Then
                MsgBox("CHECK USERNAME AND PASSWORD", MsgBoxStyle.OkOnly)
            Else
                Staffmenu.Show()
                Me.Hide()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        connection.Close()
    End Sub

[kn2790]

Nooooooooooooooooo
i SORTED IT

many thanks

[Teme64]

Hi! Nice to hear that you got answer to your problem, could you please mark the thread as solved. Thank you!