locked
How do I bind an accessdatasource to a repeater? should be simple but not working! RRS feed

  • Question

  • User1595815895 posted

    Hi Im new to asp.net 

     I cant seem to get this simple thing to work

     I have my repeater setup and used the smart task to link it to my access database but when I run it i get this error

     

     Error    4    'System.Data.DataRowView' does not contain a definition for 'toString' and no extension method 'toString' accepting a first argument of type 'System.Data.DataRowView' could be found (are you missing a using directive or an assembly reference?)    C:\Users\Andy\Documents\Visual Studio 2008\WebSites\messageboard\Default2.aspx.cs    58  

     

    Here is my page code what am I doing wrong:

     

     <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title>Untitled Page</title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:Repeater ID="Repeater1" runat="server" DataSourceID="AccessDataSource1">
            <HeaderTemplate>
                <table border="0" cellpadding="3" >
                    <tr>
                        <th>category</th>
                        <th>Threads</th>
                        <th>Posts</th>
                        <th>LastName</th>  
                    </tr>               
            </HeaderTemplate>
            
            <ItemTemplate>
                  <tr>
                      <td></td>
                      <td></td>  
                      <td></td>
                      <td></td>
                  </tr>  
            </ItemTemplate>   
            
            <FooterTemplate>
                </table>
            </FooterTemplate>   
            </asp:Repeater>
            <asp:AccessDataSource ID="AccessDataSource1" runat="server"
                DataFile="~/App_Data/mbbase.mdb" SelectCommand="SELECT * FROM [mb_category]">
            </asp:AccessDataSource>
        </div>
        </form>
    </body>
    </html>

    Wednesday, November 19, 2008 7:24 PM

Answers

  • User-821857111 posted

    Forget the AccessDataSource, and arrays.  You want to display a One-to-many relationship. You will need to use OleDb, and two separate commands to fill two datatables in a dataset, then use the Relations property to do what you want.  A SQL Server version is here: http://www.mikesdotnetting.com/Article.aspx?ArticleID=57. You'll need to change it to use OleDb.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 20, 2008 5:17 PM

All replies

  • User2138254679 posted

     Hi,

    You have to change your <itemtemplate> as 

    <ItemTemplate>
                  <tr>
                      <td><% #Eval("FieldName1)%></td>
                      <td><% #Eval("FieldName2)%></td>  
                      <td><% #Eval("FieldName3)%></td>
                      <td><% #Eval("FieldName4)%></td>
                  </tr>  
            </ItemTemplate>   

    Thursday, November 20, 2008 12:50 AM
  • User2138254679 posted

     Hi,

    You can find more information at http://www.sitepoint.com/article/asp-net-repeater-control/

    and

    http://www.aspcode.net/ASPNET-Repeater-control-instead-of-grid.aspx (Using code)

    Thursday, November 20, 2008 12:54 AM
  • User1595815895 posted

     Thats not what Im asking! Sorry should have phrased it better. How do I bind my access database programmatically and then load the results into some kind of array?

     My access data base is stored in the app data folder of my soultion.

    Thursday, November 20, 2008 5:32 AM
  • User-821857111 posted

    Sorry should have phrased it better. How do I bind my access database programmatically and then load the results into some kind of array?
     

    LOL!  there was no hint of a need to use arrays in your first post... but nevermind [:D]

    What "kind of array" are you anticipating using, and more importantly, what for?

     

    Thursday, November 20, 2008 10:13 AM
  • User1595815895 posted

     I was wanting to do something like this but bind it to a repeater inorder to create a layout similar to the front page of this forum. With top level categorys comeing from one database table then sub-categorys from the second database table based on the id of the top level category.

    Here is a c# version:

     

    protected void Page_Load(object sender, EventArgs e)

        {

            String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + Server.MapPath("~/App_Data/musiccollection.mdb");

            OleDbConnection connection = new OleDbConnection(connectionString);

            String sql="SELECT * FROM album order by albumid";

            OleDbCommand command = new OleDbCommand(sql);

     

            // Set the Connection to the new OleDbConnection.

            command.Connection = connection;

     

            // Open the connection and execute the select command.

            try

            {

                Response.Write("<table class='forumMain' id='category' cellspacing='0' cellpadding='1' border='1'>");

                connection.Open();

                command.ExecuteNonQuery();

                OleDbDataReader dbread = command.ExecuteReader();

                while (dbread.Read())

                {

                    Response.Write("<tr>");

                    Response.Write("<th class='forumHeading1'>" + dbread[0].ToString() + "</th>");

                    Response.Write("<th class='forumHeading2'>" + dbread[1].ToString() + "</th>");

                    Response.Write("<th class='forumHeading3'>" + dbread[2].ToString() + "</th>");

                    Response.Write("<th class='forumHeading4'>" + dbread[3].ToString() + "</th>");

                    String sql2 = "Select * From track where albumid=" + dbread[0];

                    OleDbCommand command2 = new OleDbCommand(sql2);

                    command2.Connection = connection;

                    command2.ExecuteNonQuery();

                    OleDbDataReader dbread2 = command2.ExecuteReader();

                    while (dbread2.Read())

                    {

                        Response.Write("<tr>");

                        Response.Write("<td class='forumRow'>" + dbread2[0].ToString() + "</td>");

                        Response.Write("<td class='forumRow'>" + dbread2[1].ToString() + "</td>");

                        Response.Write("<td class='forumRow'>" + dbread2[2].ToString() + "</td>");

                        Response.Write("</tr>");

                    }

                    Response.Write("</tr>");

                    dbread2.Close();

                }

                Response.Write("</table>");

                dbread.Close();

                connection.Close();

     

            }

            catch (Exception ex)

            {

     

            }

     

        }

    Thursday, November 20, 2008 4:14 PM
  • User-821857111 posted

    Forget the AccessDataSource, and arrays.  You want to display a One-to-many relationship. You will need to use OleDb, and two separate commands to fill two datatables in a dataset, then use the Relations property to do what you want.  A SQL Server version is here: http://www.mikesdotnetting.com/Article.aspx?ArticleID=57. You'll need to change it to use OleDb.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 20, 2008 5:17 PM
  • User1595815895 posted
    Yes exactly what  I want, but finding articles like that aint easy! All the Books Iv looked at and tutorials I came across just seem to deal with basic things like grid views. Coming from a php background the way I done it seemed logical. Thanks for the links
    Thursday, November 20, 2008 7:05 PM
  • User-821857111 posted

    It's always easy when you know how.  But I have sympathy with you.  I had exactly the same kind of problems you are experiencing when I migrated from classic ASP.

     

    Friday, November 21, 2008 2:30 PM
  • User1595815895 posted

    Is there no simple way to just pass the parameters between the two AccessDataSources in the SelectParameters ?

    For example day I have the following:

    <asp:AccessDataSource ID="AccessDataSource1" runat="server"
                DataFile="~/App_Data/mbbase.mdb" SelectCommand="SELECT * FROM [mb_category]">
            </asp:AccessDataSource>
            
            <asp:AccessDataSource ID="AccessDataSource2" runat="server"
                DataFile="~/App_Data/mbbase.mdb"
                SelectCommand="SELECT * FROM [mb_list] WHERE ([category_id] = ?)">
                <SelectParameters>
                   
                   
                </SelectParameters>

     

    I want to pass the ID field value returned from Accessdatasource1 to AccessDataSource2 and use it in the WHERE([category_id]= ? )

    as in ID becomes ?

     Is this possible if so how?


    Friday, November 21, 2008 11:03 PM
  • User-821857111 posted

    This isn't the way to do it.  AccessDataSource controls are fine for simple databinding - configure using the wizard and forget. As soon as you start finding that you are adding loads of code-behind for DataSource controls, I'd advise going the pure ADO.NET route, such as in the example I linked to.

    You are still trying to fire a loop of queries to the database as each of the category rows are bound, and that's inefficient.

     

    Saturday, November 22, 2008 8:24 AM
  • User1595815895 posted

     Yes but a repeater is the only way that I know of that will do it the way I want! I was trying to do it without any code-behind. Just a nested repeater and two accessdatasource controlds one linked to the other like in my example.

    I find the example you gave me quite hard to understand.

    Saturday, November 22, 2008 11:43 AM
  • User-821857111 posted

    "No code-behind" seems attractive, but is not possible for most situations except the most basic.  You will need to get to grips with ADO.NET code if you want to build sites that actually do very much at all.  The code I linked to may seem a bit overwhelming, but that's because at the moment you don't understand it.  At its simplest, it uses a DataSet, which is like an in-memory excel workbook.  It can hild any number of datatables (like Excel Worksheets) and they can be related to eachother (using the Relations collection).  Each DataTable can be populated from a separate query, so the first one would contain the Categories, and the second the Products, with their categoryID.  The CategoryID is what's used to create the relationship.

    Then, as each item is bound in the outer repeater, its associated records in the second datatable are retrieved and bound too.

     

    Saturday, November 22, 2008 12:24 PM
  • User1595815895 posted

     Well I had thought that it would be possible to pass data between two accessdatacontrols with the minimum of code behind. Surly what I asked must be possible buy loading the selectcommand into vaibles and passing it to the other? You do seem to avoid giving me direct answers to my questions [*-)]. 

    I have got to grips with the basics of ADO.net what I dont understand very well are Datasets, datareaders and interfacing with objects like the Relations collection ( what is this?) and all that sort of stuff.

    At the end of the day all I need to do is get this working it does'nt have to be super efficient or particularly well coded. But your right I do need to learn how to do it properly.

    Sunday, November 23, 2008 10:14 AM
  • User-821857111 posted

    I'm avoiding giving the answer you want because it is a poor way to programme.  I always use the test "How would I feel sharing the same web server as you" when people request poor code, and if the answer is "no thanks", I try to give what I feel is good advice.  I'm also mindful of the fact that other learners will read this thread and think what you are trying to do is ok.  It's not.

    Needless to say, I haven't got any ready-made code for what you want, because I would sooner use ColdFusion or Lotus Notes than do it your way.  However, I suspect that you can nest the datasource with the nested repeater, then obtain the Primary Key of the Category from the DataKeys property within the ItemDataBound event of the Outer Repeater.  Then you can pass it to the inner repeater so that it can fire a Select Command for every Category you have.  It requires a bit of code-behind. And learning about DataKeys and ItemDataBound events, and how to reference values.

    One way or another, you will have to learn something in order to get this to work, so why not learn to do it properly?


    Sunday, November 23, 2008 5:49 PM
  • User1595815895 posted

     Ok Iv been studying the article you pointed me to and I understand it more or less. But its not 100% clear. First off how can I do the same thing without creating a stored procedure? My database is simply and access data file stored in the app data folder of my solution. Im not using sql server express or anything like that. This will never be a live site its all just a exercise. Im working towards a degree in web development and currently Im going far beyond the requrements of the project going to this depth of asp.net. They would be happy with a couple of grid views in a nice html page!

    Sunday, November 23, 2008 9:36 PM
  • User1595815895 posted

     Ok forget my last post I just managed to get it working using your method. I dont fully understand it yet but I understand enought to use it.

    Any tips on where to start looking for advice on how best to turn the sub-categorys returned from the repeaters into hyperlinks that will link to another page dynamicaly generated from their category_id?  [:D]

    Sunday, November 23, 2008 10:41 PM
  • User-821857111 posted

    Im working towards a degree in web development and currently Im going far beyond the requrements of the project going to this depth of asp.net. They would be happy with a couple of grid views in a nice html page!
     

    And that is why I never look at the educational achievements of any candidate I interview.   My experience is that they never teach you how to become a proper web developer - just how to throw a couple of GridViews on a page....

    [;)] 

     

    Monday, November 24, 2008 2:49 AM
  • User-821857111 posted

    Ok forget my last post I just managed to get it working using your method. I dont fully understand it yet but I understand enought to use it.
     

    You are allowed to ask me questions on any aspect that isn't clear to you yet, if you are interested in the answer.

    Any tips on where to start looking for advice on how best to turn the sub-categorys returned from the repeaters into hyperlinks that will link to another page dynamicaly generated from their category_id?
     

    Normally, a different question should require a new post in a suitable forum, such as Data Presentation, but the answer is to look at the HyperLink control [:D]

    <asp:Hyperlink ID="Category" NavigateURL='<%#Eval("CategoryID", "Category.aspx?CategoryID={0}") %>' runat="server"><%# Eval("Category") %></asp:Hyperlink>

     

    Monday, November 24, 2008 2:54 AM
  • User1595815895 posted

     No probs. Thanks for your help mate

    Monday, November 24, 2008 6:42 AM
  • User1595815895 posted

     Iv created a new thread with a bunch of questions here :

     http://forums.asp.net/t/1352525.aspx

    Monday, November 24, 2008 11:23 PM