VB.NET - Searching for data in an MS Access database

Scratching my head over this one. I've never attempted to use VB.NET with a database before but I need to now.
I have a database called "Asset Management" and I am currently able to create new records in it but I need to search for records now and I just cant figure out how to do the following.

After creating or changing an asset the analyst will swipe their card which has a specific code embedded. I need to search the table "Badge_Numbers" for this code. If found it will create or update the records in another table.
It also needs to return the analyst name as found in the "Badge_Numbers" table.

So far example the Badge_Numbers table will look like this:
BadgeNo                Analyst
C%7644764           Joe Bloggs
G%5454564          Cindy Crawford
N£2132143           Joan Collins

If Joan swipes her card it needs to look for "N£2132143" and store the name associated with it "Joan Collins" in a string so it can be entered into another table later

Here's the awful code that I have so far:
Dim con As New OleDb.OleDbConnection
Dim dbProvider As String
Dim dbSource As String
Dim sql As String
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
dbProvider = "Provider=Microsoft.ACE.OLEDB.12.0;"
dbSource = "Data Source = " & "\\server\Asset Management.accdb"
con.ConnectionString = dbProvider & dbSource
con.Open()

sql = "SELECT * FROM Badge_Numbers"
da = New OleDbDataAdapter(sql, con)
da.Fill(ds, "analyst")

Dim badge As String = txtAnalyst.Text
Dim foundrow As DataRow = ds.Tables("Badge_Numbers").Rows.Find(badge)

 If foundrow IsNot Nothing Then
     MsgBox(foundrow(1).ToString())
End If

Open in new window


When I run this I receive the error 'Object reference not set to an instance of an object' on this line:
Dim foundrow As DataRow = ds_a.Tables("Badge_Numbers").Rows.Find(badge)

Any help appreciated
LVL 2
fruitloopyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
Hi fruitloopy;

Try using this setup to return the data from the Badge_Numbers table. Seeming there is only one record that should be returned no need to use a DataAdapter or DataTable.
Dim scanedBadgeNum As String = "Scaned in badge number from reader"
Dim dbProvider As String = "Provider=Microsoft.ACE.OLEDB.12.0;"
Dim dbSource As String = "Data Source = " & "\\server\Asset Management.accdb"
'' Select only the record with the badge number you are looking for.
Dim sql As String = "SELECT * FROM Badge_Numbers WHERE BadgeNo = ?"

'' Use Using statements to make sure all resources are releaced when not needed
Using con As New OleDb.OleDbConnection(dbProvider & dbSource)
    Using cmd As New OleDb.OleDbCommand(sql, con)
        '' Set the type of command and give a parameter to match what you are looking for.
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("BadgeNo", scanedBadgeNum)
        con.Open()
        Using reader As OleDb.OleDbDataReader = cmd.ExecuteReader()
            While (reader.Read())
                '' Modify this section of the code to meet your needs.
                Console.WriteLine(reader("BadgeNo").ToString() & " : " + reader("Analyst").ToString())
            End While
        End Using
    End Using
End Using

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fruitloopyAuthor Commented:
Fantastic!
Works perfectly, thank you
0
Fernando SotoRetiredCommented:
Not a problem fruitloopy, glad to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.