Question Searching in 3 tables ?

[mcavanilla]

Hello,

I'm trying to develop an application in VB.net, for search informations in 3 tables (TBClient with 3 columns "Company", "Address" and "Fone", TBContact with 2 columns "Name" and "Email" and TBContract with 2 columns "ContractNumber" and "Date").

I'm using a combo box (for select the column of the TBClient), and a Text box to write what i'm looking for. The result is showed in a datagrid (i'm using dataset)
My question is, how can I make this search to show the result of the 3 tables in the same datagrid? Example: If I search for the company name, I would like to see the informations of the other two tables related to the company name.

I'm using the Visual Studio 2010 and SQL server 2008.

sorry for my poor english

thanks

Marco

 

[Herman]

For that, you need to add a foreign key column to your TBContact and TBContract tables, for example "ExClientID", and I also assume that the TBClient table has a primary key, let's say ClientId. The you query your three tables:

SELECT *
FROM TBClient c
INNER JOIN TBContact ct ON c.ClientId = ct.ExClientId
INNER JOIN TBContract ctr ON c.ClientId = ctr.ExClientId
WHERE c.Company = 'Some Company Name'

 

[mcavanilla]

Hello Herman,

Thanks for your information! Now I understand the logic

I add a foreign key in other two tables ("FK_IDClient"). My primary key from TBClient is "PK_IDClient".

I developed the following code to search just on TBClient:

Dim strSearch As String = txtPesquisa.Text 'My text box

If cmbNames.SelectedIndex = "0" Then
Dim strSqlPesquisa0 As String = "SELECT from TBClient WHERE Company LIKE '%" + strPesquisa + "%'"
DataGrid2.DataSource = _
Me.Docs_MgrDataSet.TBClient.Select("Company like '%" + strSearch + "%'")
DataGrid2.Visible = True

Could you help me change this code to search in the 3 tables?

Thank you so much

Marco

 

[Herman]

Sorry for the late response. First the strSqlPesquisa0 variable is unused, so you might as well remove the line. Second, the .Select() method is extremely limited, you can only implement WHERE clauses with it. Instead, you should use a SqlDataAdapter to pass your query, get the results, and show them.

        Dim dtResults As New DataTable

        Using myConnection As New SqlClient.SqlConnection("Insert your connection string here")
            Using myCommand As New SqlClient.SqlCommand(<string>SELECT *
                                                                FROM TBClient c
                                                                INNER JOIN TBContact ct ON c.PK_IDClient = ct.FK_IDClient
                                                                INNER JOIN TBContract ctr ON c.PK_IDClient = ctr.FK_IDClient
                                                                WHERE c.Company LIKE '<%= txtPesquisa.Text %>'
                                                        </string>, myConnection)
                Using myTableAdapter As New SqlClient.SqlDataAdapter(myCommand)
                    myTableAdapter.Fill(dtResults)
                End Using
            End Using
        End Using

        DataGrid2.DataSource = dtResults.DefaultView