Solved

Problem with Defining Relationsships Between Datatables

Posted on 2006-07-16
5
168 Views
Last Modified: 2010-04-23
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
0
Comment
Question by:dplsr
  • 2
  • 2
5 Comments
 
LVL 14

Expert Comment

by:ptakja
ID: 17119028
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".
0
 
LVL 41

Expert Comment

by:graye
ID: 17123062
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"))
0
 
LVL 41

Expert Comment

by:graye
ID: 17123071
...er.... that was suppose to read "Typically you would NOT do a join..."
0
 
LVL 14

Accepted Solution

by:
ptakja earned 500 total points
ID: 17123102
Good catch Graye...

Here's the problem:

da.selectCommand = new SqlCommand("select distinct category  from products where cdescription like '%kitchen_Sink%' ", objConn)
da.fill(ds, "Products")
objConn.close

As Graye said, the Products table only has one column in it called "category".

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

Your relation has 2 columns in it, however the "mfg" column doesn't exist in the Products table becasue of the Select statement above. At a minimum, you would need to add that column to the SELECT query and you should be OK.




0
 

Author Comment

by:dplsr
ID: 17124896
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)
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now