Populating datagrid with columns with multiple tables.

Using ASP.NET (vb), I have two tables and joining on ID. I am having some issues populating my datagrid with a dataset.

Here is what I have so far:

Dim conPubs as SqlConnection
Dim dsSearch1 as DataSet
Dim dsSearch2 as DataSet
Dim adSearch1 as SqlDataAdapter
Dim adSearch2 as SqlDataAdapter
Dim iRecordsFound as Integer
Dim sSearch as String

conPubs = New SqlConnection(...)

sSearch = txtSearch.Text("'", "''")
If sSearch.Length > 0 Then
sSQL1 = "SELECT PRD_ID, BRAND, MODEL FROM t_products "
sSQL1 &= "WHERE BRAND like '%" & sSearch & "' "
sSQL1 &= "ORDER BY BRAND"

sSQL2 = "SELECT RATING, RECOMMEND FROM t_details"
Else
sSQL1 = "SELECT PRD_ID, BRAND, MODEL FROM t_products "
sSQL1 &= "ORDER BY BRAND"

sSQL2 = "SELECT RATING, RECOMMEND FROM t_details"
End If

adSearch1 = New SqlDataAdapter(sSQL1, conPubs)
adSearch2 = New SqlDataAdapter(sSQL2, conPubs)

dsSearch1 = New DataSet()
dsSearch2 = New DataSet()

adSearch1.Fill(dsSearch1, "t_products")
adSearch2.Fill(dsSearch2, "t_products")

Dim pk1(0) as DataColumn
Dim pk2(0) as DataColumn

pk1(0) = dsSearch1.Tables(0).Columns("PRD_ID")
dsSearch1.Tables(0).PrimaryKey = pk1

pk2(0) = dsSearch2.Tables(0).Columns("PRD_ID")
dsSearch2.Tables(0).PrimaryKey = pk2

dsSearch1.Merge(dsSearch2, false, MissingSchemaAction.Add)

conPubs.Open()
iRecordsFound = dsSearch1.Tables("t_products").Rows.Count.ToString()
lblRowCount.Text = iRecordsFound

datagridOutput.DataSource = dsSearch1
datagridOutput.DataBind()
conPubs.Close()


The error I get now is:
System.ArgumentNullException: 'column' argument cannot be null. Parameter name: column

Any help would be great or better way of doing this is welcome as well.
Thanks.Probably because sSQL2 isn't selecting PRD_ID.
Is there a reason your not joining the tables in your sql statment?
Yeah, your right. I needed the PRD_ID in the sSQL2. I don't get errors but my datagrid says I have over 6 pages (of 25) but I can only see 2.

You suggestion why not putting the sql all in one. I did try this way but could not get to work properly. This is how I would like to do it but couldn't get my code to work.

Any ideas?
Where you using a JOIN or just WHERE X=Y?
TPM - Using x = y.

Update: I tried again and worked so not sure what I did differently but not complaining. This is what I have:

Dim conPubs as SqlConnection
Dim cmdSearch as SqlCommand
Dim dsSearch as DataSet
Dim SadSearch as SqlDataAdapter
Dim sSQL as String
Dim iRecordsFound as Integer
Dim sSearch as String

'Open connection with connection object
conPubs = New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("MS_SQL_CONN"))

sSearch = txtSearch.Text.Replace("'", "''")
sSearch = txtSearch.Text.Replace(";", vbNullString)

'form sql
If sSearch.Length > 0 Then
sSQL = "SELECT p.PRD_ID, BRAND, MODEL, NAME, RECOMMENDED_CD FROM products p, products_details1 pd "
sSQL &= "WHERE p.PRD_ID = pd.PRD_ID "
sSQL &= "AND BRAND Like '%" & sSearch & "%' OR PMODEL Like '%" & sSearch & "%' "
sSQL &= "ORDER BY BRAND, MODEL"
Else
sSQL = "SELECT p.PRD_ID, BRAND, MODEL, NAME, RECOMMENDED_CD FROM products p, products_details1 pd WHERE p.PRD_ID = pd.PRD_ID ORDER BY BRAND"
End If

'To execute sql statement and provide active connection
SadSearch = New SqlDataAdapter(sSQL, conPubs)

'Create instance of dataset object
dsSearch = New DataSet()

'fill datagrid
SadSearch.Fill(dsSearch, "t_products")

conPubs.Open()
iRecordsFound = dsSearch.Tables("t_products").Rows.Count.ToString()
lblRowCount.Text = iRecordsFound

dgrdResults.DataSource = dsSearch
dgrdResults.DataBind()
conPubs.Close()

Do you see any advantage to using the JOIN?
Thanks