Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
307 views
in Technique[技术] by (71.8m points)

mysql - How to search data in all tables in database using select query in vb.net?

How to search data in all tables in database using select query in vb.net? Here is my code:

Try
    mysqlconn.Open()

    Dim query As String
    query = "select * from  where (Item_Description LIKE '%" & TextBox11.Text & "%' or Vendor LIKE '%" & TextBox11.Text & "%' OR S_N LIKE '%" & TextBox11.Text & "%' or Tag_num LIKE '%" & TextBox11.Text & "%')"
    command = New MySqlCommand(query, mysqlconn)
    sda.SelectCommand = command
    sda.Fill(dbdataset)
    bsource.DataSource = dbdataset
    DataGridView1.DataSource = bsource
    sda.Update(dbdataset)
    mysqlconn.Close()

Catch ex As Exception

Finally
    mysqlconn.Dispose()
End Try
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Sorry Ana I don't think you can leave out the table name in the from clause. If you really want to search 30 different tables (that all have the same columns...?) then you'd likely have to iterate over each one of them separately and join the information yourself

You could do a for each (Table Name) loop to get the data from each table. The adapter adds all the new information into the datatable so that you have just 1 datatable in the end with results from all 30 tables.

Dim query As String
Dim dt as new Datatable
For each tablename in (tablenamelist) 
    query = "select * from " & tablename & " where (Item_Description LIKE '%" & TextBox11.Text & "%' or Vendor LIKE '%" & TextBox11.Text & "%' OR S_N LIKE '%" & TextBox11.Text & "%' or Tag_num LIKE '%" & TextBox11.Text & "%')"
    command = New MySqlCommand(query, mysqlconn)
    sda.SelectCommand = command
    sda.Fill(dt)
next

where tablenamelist is a list of all your datatable names. This will get the job done, but there are many improvements to be made here.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

2.1m questions

2.1m answers

60 comments

57.0k users

...