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

Question

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()

Andre · Accepted Answer · 2015-10-23 15:33:10Z

1

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.

answered Oct 23, 2015 at 15:33

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tyler_Coder Over a year ago

@ Andre451 Thank you very much, the standard search with a parameter is working, however when I add an or it gives an error "Conversion from string "Select * from Table1 where First" to type 'Long' is not valid." pointing at "rs.fill(dt). New code =(("Select * from " & TableName & " where FirstName = '" & txtTabel1.Text & "'") Or ("Select * from " & TableName & " where LastName = '" & txtTabel1.Text & "')"), con)

Andre Over a year ago

@Tyler_Coder: It's not (SELECT * FROM table WHERE field1 = 'x') OR (SELECT * FROM table WHERE field2 = 'x'), but SELECT * FROM table WHERE (field1 = 'x') OR (field2 = 'x')

Tyler_Coder Over a year ago

Thank you very much for all the help and bearing with me through all of my mistakes, I have finally fixed my problems and everything is working well.

T.S. · Accepted Answer · 2015-10-23 18:47:35Z

0

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

answered Oct 23, 2015 at 18:47

T.S.

19.6k11 gold badges70 silver badges96 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related