asked on

Problem with Defining Relationsships Between Datatables

Hi experts!
i get error Exception Details: System.ArgumentNullException: 'column' argument cannot be null. Parameter name: column

when i run this code. I have checked both datasets with a repeater and they work fine. What am I doing wrong?

Sub Page_Load(Source as Object, E as EventArgs)

dim ds as dataset
dim da as sqldataadapter
dim prow as datarow
dim crow as datarow
ds = new dataset
Dim objConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
da = new Sqldataadapter ("select * from mfg", objConn)
objConn.open()
da.fill(ds, "mfg")
da.selectCommand = new SqlCommand("select distinct category from products where cdescription like '%kitchen_Sink%' ", objConn)
da.fill(ds, "Products")
objConn.close

ds.relations.add("mfg category", _
ds.tables( "Mfg").columns("mfg"),ds.tables("Products").columns("mfg"))

for each prow in ds.tables("mfg").rows
lbl.text &="<h3>" & prow ("mfg") & "<h3>"
for each crow in prow.getchildrows("mfg category")
lbl.text &="<LI>" & crow("products")

next
next

End Sub

ptakja

In order to add a relation to a dataset, you have to define primary keys for each table within the dataset.

You can do that like this:

Dim key(1) As DataColumn 'Create array of DataColumn objects
key(0) = Me.MyDataSet.Tables(0).Columns(0) ' Put the primary key columns into the array
Me.MyDataSet.Tables(0).PrimaryKey = key ' Pass the array into the PrimaryKey property of the table

Also, this may not matter, but in your code above you fill the dataset with a table called "mfg" but then reference it using "Mfg".

graye

Well the 2nd DataTable has only one column, called "Category"... I can't tell the column names of the 1st DataTable. Typically you would do a "join" operation on a table with just one column.... so something isn't quiet right there.

So, that DataSet.Relations.Add() method is expecting two *COLUMNS*, so I'd expect it to look like this:

ds.relations.add("mfg category", ds.tables("Mfg").columns("SOME_VALID_COLUMN"), ds.tables("Products").columns("Category"))

graye

...er.... that was suppose to read "Typically you would NOT do a join..."

ASKER CERTIFIED SOLUTION

ptakja

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dplsr

ASKER

Hi all! sorry I was out for the norning! Thanks ptakja! You fixed it, thanks!

da.selectCommand = new SqlCommand("select distinct category,mfg from CMRC_products where cdescription like '%kitchen_Sink%' ", objConn)