retrieving data from database to a textbox

Question

kn2790 0 Light Poster

15 Years Ago

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

vb.net

6 Contributors
19 Replies
276 Views
1 Week Discussion Span
Latest Post 15 Years Ago Latest Post by Teme64

Phasma 11 Junior Poster in Training

15 Years Ago

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

c0deFr3aK 1 Junior Poster in Training

15 Years Ago

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.

c0deFr3aK 1 Junior Poster in Training

15 Years Ago

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.

abrars 1 Newbie Poster

15 Years Ago

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.

kn2790 commented: your the man thankss a bunchh!" +1

Teme64 215 Veteran Poster

15 Years Ago

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 commented: thanks very much +1

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

kn2790 0 Light Poster · Answer 1 · 2009-04-13T18:14:01+00:00

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

kn2790 0 Light Poster · Answer 2 · 2009-04-14T17:30:51+00:00

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 0 Light Poster · Answer 3 · 2009-04-14T17:54:27+00:00

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

kn2790 0 Light Poster · Answer 4 · 2009-04-15T17:52:28+00:00

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 0 Newbie Poster · Answer 5 · 2009-04-15T21:12:38+00:00

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 0 Light Poster · Answer 6 · 2009-04-16T04:24:04+00:00

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 0 Newbie Poster · Answer 7 · 2009-04-16T07:56:58+00:00

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 0 Light Poster · Answer 8 · 2009-04-17T04:10:52+00:00

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 215 Veteran Poster · Answer 9 · 2009-04-17T09:48:22+00:00

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 0 Light Poster · Answer 10 · 2009-04-17T17:57:09+00:00

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 215 Veteran Poster · Answer 11 · 2009-04-18T12:33:22+00:00

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.

kn2790 0 Light Poster · Answer 12 · 2009-04-19T04:07:48+00:00

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 0 Light Poster · Answer 13 · 2009-04-19T04:15:12+00:00

Nooooooooooooooooo
i SORTED IT

many thanks

Teme64 215 Veteran Poster · Answer 14 · 2009-04-19T10:43:53+00:00

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