[RESOLVED] Populating datagrid with columns with multiple tables.

[lleemon]

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.

[-TPM-]

Probably because sSQL2 isn't selecting PRD_ID.
Is there a reason your not joining the tables in your sql statment?

[lleemon]

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?

[-TPM-]

Where you using a JOIN or just WHERE X=Y?

[lleemon]

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