Searching multiple MS SQL Tables

[TaoistTotty]

I would be grateful if anyone could let me know of an easy way of searching multiple tables for information.

I would like to display any found results in a grid alowing the user to choose the correct one and have this disply in the main form.

If this easy to do? I know it will need joins etc., but these are new to me.

Thanking you in advance.

[iamthwee]

If you are looking to pull out just one value (field) then this is simple enough. But databases are not like that, they're linked to different tables. How they are linked will depend on how easy it is to get the correct data out.

[TaoistTotty]

The tables are one table linked to three or four others (each of these are not link to each other)

Does this help?

[iamthwee]

Is it possible to show us a schematic of your tables, how they are linked and what are the primary keys etc.

[TaoistTotty]

Please find the information below, thanks for you help.

[iamthwee]

I can barely read that text can you post a picture where all the text is big and clear to read. Thanks.

[TaoistTotty]

Is this better?

[QVeen72]

Hi,

what u actually want..? Search all the fields of all the tables for any one Specific condition...?

-Vee

[TaoistTotty]

Vee, well put that is what I want.

I know this seems strange, but the old DB (propitiatory) can do this and they want to keep that functionality.

[QVeen72]

Hi,

What is the Database u r using..? Access..?

-Vee

[TaoistTotty]

QVeen72, I am using SQL Server Express edition at the moment, but do have full standard version available if needed.

[QVeen72]

hi,

In Total how many tables do u need to do search operation..?
If Many Tables, and many recs in each table, then this will be a huge Overhead on the database.
But if u need to do it compulsorily, Write a Stroredprocedure which accepts Search String. And Checks each table and returns Table Name/id/fieldname if found..

Regards
Veena

[QVeen72]

Hi,

u can check the Storedprocedure here
It Checks and Replaces, u can modify it to Check and Return back...

Regards
Veena

[TaoistTotty]

There are five tables, and I imaging in total about 5,000 records (once completed).

Thank you for the link, that looks great.

[TaoistTotty]

I have had a chance to test the code and it works well, not all I have to do is to work out how to get the result into a format that will be useful to me.

Time to try and put my thinking cap on.

[QVeen72]

Hi,

Just Get the Stored procedure to output these parameters, If the Search is Matched..:

TableName, FieldName, FieldValue, ID of the Matched record.

If user selects some thing from the List, the Next drill down would be easy since u know Table name, field name and ID of the Table...

REgards
Veena

[TaoistTotty]

Thanks Veena.

This will enable everything to work.

Thanks once again.

Michael

[TaoistTotty]

I have been working on this for a while.

Using the stored Procedure from the link QVeen72 provided I have been able to get the search results I want with in SQL.

My problem is getting this to display in vb.net in a data grid.

Can anyone offer some help as to where to look for a solution.

The code I am using at the moment is:

Dim cs As String = "Data Source=.\SQLEXPRESS;Initial Catalog=Database;Integrated Security=True"
Using con As New System.Data.SqlClient.SqlConnection(cs)
con.Open()
Dim cmd As New System.Data.SqlClient.SqlCommand()
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "SearchAllTables"
cmd.Parameters.Add("@SearchStr", SqlDbType.NVarChar, 12)
cmd.Parameters("@SearchStr").Value = TextBox1.Text
Using reader As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
DataGridView1.DataSource = reader
End Using

The information I have see says that the last line should add: DataGridView1.DataBind, but I do no seem to have this as an option and it does not work if there is.

[TaoistTotty]

I have managed to get everything solved.

Thank you for your help.