CODEWITHSUNDEEP
databasevb.netms-accessvba
Tyler_Coder
Tyler_Coder

Reputation: 25

How to search multiple fields of an access database in VB using code

I have created a program which displays data from different tables in an access database(without the use of wizards to connect to the database. i.e. all connection have been hard coded), I have created a simple search which searches through one field in one table. However I would like to make a function which allows me to search through all of the fields in a table. Below is the simple search function I have created. 

     Public Sub Search()         
    con.Open()
    Dim dt As New DataTable("Table1")
    Dim rs As New OleDb.OleDbDataAdapter("Select * from Table1 where FirstName = '" & txtTabel1.Text & "'", con)

    rs.Fill(dt)
    dgvTabel2.DataSource = dt
    dgvTabel2.Refresh()

    rs.Dispose()
    con.Close()  
     end sub

The new function should look something like this

   Public Sub SearchHard(TableName As String)
    con.Open()
    Dim dt As New DataTable("TableName")
    Dim rs As New OleDb.OleDbDataAdapter(("Select * from " & TableName & " where FirstName = '" & txtTabel1.Text & "'") Or ("Select * from " & TableName & " where LastName = '" & txtTabel1.Text & "')"), con)
    'SELECT * FROM MyTable WHERE FirstName LIKE '% txtTable1.text %' OR LastName LIKE '%txtTable1.tetx%'

    rs.Fill(dt)
    dgvTabel1.DataSource = dt
    dgvTabel1.Refresh()

    rs.Dispose()
    con.Close()
End Sub

It would also be a big plus if the function could accept a parameter to select a different table to reduce overusing the code. Any and all help would be much appreciated

Working code to with searches.

    con.Open()
    Dim dt As New DataTable("Table1")
    Dim rs As New OleDb.OleDbDataAdapter("SELECT * FROM  Table1  WHERE (FirstName = '" & txtTabel1.Text & "') or (LastName = '" & txtTabel1.Text & "')", con)
    rs.Fill(dt)
    dgvTabel1.DataSource = dt
    dgvTabel1.Refresh()

    rs.Dispose()
    con.Close()

Upvotes: 2

Views: 8826

Answers (2)

T.S.
T.S.

Reputation: 19330

On top of what was already mentioned here by @andre451, you could use union if you want to search multiple tables

SELECT field_1 , field_2
FROM table_1
WHERE field_3 = 'value' or field_4 = 'value'
UNION [ALL]
SELECT field_100 , field_200
FROM table_2
WHERE field_300 = 'value' or field_400 = 'value'

As long as your field_1 and field_100 match data types and count of fields matches

Upvotes: 0

Andre
Andre

Reputation: 27634

You have some wrong quotes in there:

"Select * from Table where Field1 = '" & txtTabel1.Text & "'" or Field2 = '" & txtTabel1.Text"'"

should be

"Select * from Table where Field1 = '" & txtTabel1.Text & "' or Field2 = '" & txtTabel1.Text & "'"

Now of course you could use your parameter TableName like this

"Select * from " & TableName & " where Field1 = '" & txtTabel1.Text & "'"

But different tables will have different field names, so that wouldn't really work. In the end you'd have to add lots of parameters and sort of re-invent SQL.

A better solution would be to pass the full SQL SELECT string to your function.

Upvotes: 1

Related Questions