Hey guys I need a little assistance in doing a basic search query from my form program to connect to an access database. basically I want to learn how to with the click of my query button, search text entered in a textbox and show the corresponding data in another text box, based on a match found. Any help would be greatly appreciated.
here is the code of my query button so far..
Private Sub btnQuery_Click(sender As Object, e As EventArgs) Handles btnQuery.Click
con = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Eric\Documents\Fadv.accdb")
Dim id As Integer
Dim SQL As String = "SELECT FirstName, NAME FROM info WHERE FirstName=" & txtFirstName.Text & ""
Using cmd As New OleDbCommand(SQL, con)
id = Val(InputBox("Enter Query Criteria"))
Using con As New OleDbCommand(connectionstring)
Dim cmd As New OleDbCommand(SQL, con)
Dim reader As OleDbDataReader = cmd.ExecuteReader()
txtFirstName.Text = reader(0).ToString())
ExecuteNonQuery() is supposed to be used to write to the database, not read from it. "Although the ExecuteNonQuery returns no rows..." says the MSDN Documentation.
There's a couple ways to perform what you're trying to do. I suggest looking at the example here because it will show you how to perform a query using
To get the data from your query to a textbox, you can do something like:
Using con As New OleDbConnection(connectionstring) //Your connection string Dim command As New OleDbCommand(SQL, con) connection.Open() Dim reader As OleDbDataReader = command.ExecuteReader() While reader.Read() TextBox1.Text = reader(0).ToString()) End While reader.Close() End Using
This link provides even more clarification, and will greatly help you if you read what it has to say. Specifically:
"You use the Read method of the DataReader object to obtain a row from the results of the query. You can access each column of the returned row by passing the name or ordinal reference of the column to the DataReader. However, for best performance, the DataReader provides a series of methods that allow you to access column values in their native data types (GetDateTime, GetDouble, GetGuid, GetInt32, and so on)."
Also note: you want to paramaterize your queries.