Filling DataTables to create Relationship (Access 2K3, using .aspx)

Topic

New Reply

Greetings!
I’m working with data contained in two tables – a parent table and a child table (1 to many relationship). I’m having trouble with code that will define the relationship on the page so I can implement nested repeaters. I’ve tried several different ways and syntax, all with no success. I’m hoping someone can look at this chunk of code and tell me what I’ve done wrong. Any help is greatly appreciated.

Thanks much!

cmdMbrs = New OleDbCommand( _
“SELECT * ” & _
“FROM tblSubType ” & _
“WHERE (((tblSubType.SubType)=” & strWhere & “))” & _
“ORDER BY tblSubType.SubType;”, conClsf)

cmdMbrs2 = New OleDbCommand( _
“SELECT * ” & _
“FROM tblSubContractors ” & _
“WHERE (((tblSubContractors.SubType)=” & strWhere & “))” & _
“ORDER BY tblSubContractors.SubType;”, conClsf)

Dim DA as OleDbDataAdapter = New OleDbDataAdapter
Dim DA2 as OleDbDataAdapter = New OleDbDataAdapter

DA.SelectCommand = cmdMbrs

Dim ds As DataSet = New DataSet
Dim tblType as DataTable
Dim tblContractors as DataTable

ds.Tables.Add(“tblSubType”)
ds.Tables.Add(“tblSubContractors”)

rdrMbrs = cmdMbrs.ExecuteReader

DA.Fill(“tblSubType”, rdrMbrs)

DA2.SelectCommand = cmdMbrs2

conClsf.close

conClsf.Open

rdrMbrs2 = cmdMbrs2.ExecuteReader

DA2.Fill(“tblSubContractors”, rdrMbrs2)

Dim parentCol As DataColumn
Dim childCol As DataColumn

parentCol = ds.Tables(“tblSubType”).Columns(“SubType”)
childCol = ds.Tables(“tblSubContractors”).Columns(“SubType”)

myRel = New System.Data.DataRelation(“Subs”, parentCol, childCol)
ds.Relations.Add(myRel)

Repeater1.DataSource = ds.Tables(“tblSubType”)
Repeater1.DataBind()

cmdMbrs.Dispose
cmdMbrs2.Dispose
conClsf.Close

Reply | Quote

Replies

Where is the problem occurring? There doesn’t appear to be anything wrong with the code creating the relationship, so I think you may have to look in the bits of code you’ve left out of your post.

Reply | Quote

Thanks for the quick reply!
The error I’m currently getting is:

Overload resolution failed because no accessible ‘Fill’ can be called with these arguments:

(on this line –>) DA.Fill(“tblSubType”, rdrMbrs)

In researching solutions, it seems like there are several different ways to use .Fill, but I haven’t had success with any of them.

I’ve attached the code as a text file, if you happen to notice anything about it, I’d love to get your opinion.

Thanks so much!

Reply | Quote

The first argument in Fill is a dataset, but you’re passing a string, which may be why you’re having problems. Try this:

DA.Fill(ds.Tables(“tblSubType”), rdrMbrs)

Reply | Quote

Check the type of the arguments of the Fill method. You need to pass the actual DataSet, not a string of the table name.

Look in the object browser to find the valid argument options for the Fill method.

Reply | Quote

Thank you all for the help! Upon further review, I’ve altered the code slightly:

cmdMbrs = New OleDbCommand( _
“SELECT * ” & _
“FROM tblSubType ” & _
“WHERE (((tblSubType.SubType)=” & strWhere & “))” & _
“ORDER BY tblSubType.SubType;”, conClsf)

cmdMbrs2 = New OleDbCommand( _
“SELECT * ” & _
“FROM tblSubContractors ” & _
“WHERE (((tblSubContractors.SubType)=” & strWhere & “))” & _
“ORDER BY tblSubContractors.SubType;”, conClsf)

Dim DA as OleDbDataAdapter = New OleDbDataAdapter
Dim DA2 as OleDbDataAdapter = New OleDbDataAdapter

DA.SelectCommand = cmdMbrs

Dim ds As DataSet = New DataSet
Dim tblSubType as DataTable
Dim tblSubContractors as DataTable

tblSubType = ds.Tables.Add(“tblSubType”)
tblSubContractors = ds.Tables.Add(“tblSubContractors”)

‘rdrMbrs = cmdMbrs.ExecuteReader

DA.Fill(tblSubType)

DA2.SelectCommand = cmdMbrs2

‘rdrMbrs.close
conClsf.close

conClsf.Open

‘rdrMbrs2 = cmdMbrs2.ExecuteReader

DA2.Fill(tblSubContractors)

Dim parentCol As DataColumn
Dim childCol As DataColumn

parentCol = ds.Tables(“tblSubType”).Columns(“SubType”)
childCol = ds.Tables(“tblSubContractors”).Columns(“SubType”)

myRel = New System.Data.DataRelation(“Subs”, parentCol, childCol)
ds.Relations.Add(myRel)

Repeater1.DataSource = tblSubType
Repeater1.DataBind()

cmdMbrs.Dispose
cmdMbrs2.Dispose
‘rdrMbrs2.close
conClsf.Close

First issue now: I get an invalid # of arguments error if I select more than one checkbox. I had the select statement working fine before, and haven’t changed any of that. This error happens on the same line: DA.Fill(tblSubType).

Now: one checkbox checked does run, however it only gives me the SubType result (nothing in the child table is displayed). Also, I referenced MSDN, and it shows valid syntax for DA.Fill(DataTable, IDataReader), although even after defining the tables and writing the code that way, I got an ‘Invalid Type Cast error’.

You guys are great! I’m really having a hard time with this when I thought it would be relatively simple.

Thanks!

Reply | Quote

You don’t need the DataTable objects. Instead, you can use the Fill method with the Dataset and DataTable name (string) arguments. See this MSDN topic.

So the process should be:
– Create the two data adapters with the desired SQL statement (looks good – although you could use a single data adapter and simply replace the select command with the second sql statement, but that’s just an option – your way works fine too)
– Instantiate the dataset object
– Use the first data adapter to fill the dataset, passing the desired table name of the first table
– Use the second data adapter to fill the dataset, passing the desired table name of the second table, the end result should give you the dataset with both tables populated. To verify this, I would output the ds.GetXml() method to the debug window. This will show you all of the XML behind the dataset and you can verify that both tables are created.
– Create relationship (your code looks fine there)

Hope this helps

Reply | Quote

Thanks!

Seems that both tables are being populated correctly! At least with only one Sub Type selected, that is. When I select more than one Sub Type, I get the following error:

No value given for one or more required parameters.

It is happening on this line: DA2.Fill(ds, “tblSubContractors”)

Selecting multiple Sub Types should only change the SQL statement, so I’m not sure what parameter I’m missing. Any ideas? Thanks again, you’ve been a tremendous help thusfar.

Reply | Quote

You are probably trying to fill a table that is already filled. In which case, you’ll want to remove the table before attempting to refill it.

ds.Tables(“tblSubContractors”).Remove()
DA2.Fill(ds,”tblSubContractors”)

Reply | Quote

Thanks again to all!
OK….I put the space after the )) in the SQL. I also added this line above DA2.Fill(ds, “tblSubContractors”) —> ds.Tables.Remove(“tblSubContractors”)

To test the SQL, I created a label and checked that indeed the SELECT statement looks good, properly spaced and punctuated and all.

I’m still getting the same error though, No Value given…….

I’m really stumped, but here’s the most recent copy of the code, maybe I’m missing something obvious.

Thanks again, I owe all of you big!

Note: The third repeater in this code is just for testing, as I can’t seem to get the nested repeaters working yet with the following line:

<asp:Repeater ID="Repeater2 runat="server" DataSource='’>

Reply | Quote

I’m just about to read through the code to see if I can see anything but have you tried copying both SQL statements and pasting them into a query window in the Access db to check they run OK there?

Reply | Quote

I’ve just realised that your strWhere string references tblSubType specifically – hence, it will not work for tblSubContractors if you have more than one criterion because it ends up looking like:
“Walls” Or (tblSubType.SubType)=”Utiltities”
which clearly won’t work for tblSubContractors! Either build two separate criteria strings or use the IN method I suggested.

Reply | Quote

Duh!!!
Of course. Changed my SQL and now it runs fine.
Last problem:
I’m getting no rows displayed for “Repeater2”, here is the code:

Company Name

Contact

Phone

Extension

Fax

Mobil

Extra

<asp:Repeater ID="Repeater2"
runat="server" DataSource='’>

You guys are great! Thanks so much!

Reply | Quote

Can you post your current code, please – your last version mentioned repeater3 not repeater2 so I assume much of it has changed.

Reply | Quote

Of course!
Thanks much. You guys are great.

Reply | Quote

You appear to be missing the lines:

Repeater2.DataSource = ds.Tables("tblSubContractors")
Repeater2.DataBind()

unless I overlooked them.

Reply | Quote

You didn’t overlook them. In fact, I had them there at first, but removed them. For some reason, since Repeater2 is nested within Repeater1, Repeater2 is not recognized during compilation, so I get a compile error: “Repeater2 is not defined”
In the examples I looked at, it defines the datasource in the repeater tag:

Company Name

Contact

Phone

Extension

Fax

Mobil

Extra

<asp:Repeater ID="Repeater2"
runat="server" DataSource='’>

(The output of this shows only the SubTypes (from Repeater1). Nothing from Repeater2 is displayed.)

I’m not quite sure what I’ve done wrong with it.

Thanks again, as always!

Reply | Quote

Does it help if you change these lines in repeater2:

to this:

Reply | Quote

Hmm…It still runs, but produces the same results. I also tried changinging the DataSource part of Repeater2’s tag to:

DataBinder.Eval(Container.DataItem.Row.GetChildRows(“myRel”))

This produces an error stating “No Eval accepts this number of arguments” (or something to that effect)

Thanks again for the help – I sincerely appreciate it!

Reply | Quote

Change this line:

runat="server" DataSource=''>

to this:

runat="server" DataSource=''>

and that should work!

Reply | Quote

Nice!!!
I tried that before, but I had left the DataBinder.Eval…… in front of all the items, so it didn’t initally do anything.

Changed it back to your previous recommendation, and VIOLA!!!! It’s beautimus!

You’re a genius, and for that I am sincerely appreciative!

Thanks much

Reply | Quote

Glad it’s working!

Reply | Quote

Hi,
A couple of comments about your SQL:
1. You should really have a space after the two parentheses in the WHERE statement – “WHERE (((tblSubContractors.SubType)=” & strWhere & “)) ”
2. You might find it easier to build an IN statement rather than having 30-odd OR conditions – i.e. you end up with a string something like:
“WHERE (((tblSubContractors.SubType) IN (“Walls”, “Utilities”, “Structures”))) “

Reply | Quote