Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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!

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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)
    Charlotte

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  6. #6
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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!

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  8. #8
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

    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"))) "
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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='<%# Container.DataItem.Row.GetChildRows("myRel") %>'>

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

    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?
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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")

  14. #14
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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:

    <asp:Repeater ID="Repeater1" runat="server">
    <HeaderTemplate>
    <table>
    <tr>
    <td width="20">*</td>
    <td width="120">Company Name</td>
    <td width="110">Contact</td>
    <td width="110">Phone</td>
    <td width="30">Extension</td>
    <td width="110">Fax</td>
    <td width="110">Mobil</td>
    <td width="120">Extra</td>
    <td width="120">*</td>


    </tr>
    </table>
    </HeaderTemplate>
    <ItemTemplate>
    <%#DataBinder.Eval(Container.DataItem, "SubType")%>


    <asp:Repeater ID="Repeater2"
    runat="server" DataSource='<%# Container.DataItem.Row.GetChildRows("myRel") %>'>
    <ItemTemplate>
    <table>
    <tr>
    <td width="20">*</td>
    <td width="120"><%#DataBinder.Eval(Container.DataItem, "Company Name")%></td>
    <td width="110"><%#DataBinder.Eval(Container.DataItem, "Contact")%></td>
    <td width="110"><%#DataBinder.Eval(Container.DataItem, "Phone")%></td>
    <td width="30"><%#DataBinder.Eval(Container.DataItem, "Extension")%></td>
    <td width="110"><%#DataBinder.Eval(Container.DataItem, "Fax")%></td>
    <td width="110"><%#DataBinder.Eval(Container.DataItem, "Mobil")%></td>
    <td width="120"><%#DataBinder.Eval(Container.DataItem, "Extra")%></td>
    <td width="120">*</td>


    </tr>
    </table>
    </ItemTemplate>
    </asp:Repeater>
    </ItemTemplate>
    <FooterTemplate>

    </FooterTemplate>
    </asp:Repeater>

    You guys are great! Thanks so much!

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

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

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •