datagrid with 2 data sources

Discussion in 'ASP .Net' started by Mike P, Jun 17, 2004.

  1. Mike P

    Mike P Guest

    I am binding data to a datagrid, but there is 1 field that I need to use
    a separate query to populate.

    This is the field :

    <asp:TemplateColumn HeaderText="SIP Account/s" ItemStyle-Width="75px"
    HeaderStyle-HorizontalAlign=Center ItemStyle-HorizontalAlign=Center>
    <ItemTemplate>
    <asp:DropDownList id="ddlSIPAccounts" runat="server"
    DataValueField="SIP Account" Font-Name="arial" Font-Size="8pt"
    DataTextField="SIP Account"
    DataSource='<%# GetSIPAccounts() %>' />
    </ItemTemplate>
    </asp:TemplateColumn>

    But using GetSIPAccounts, I somehow need to only populate each row with
    the relevant SIP Accounts for that row - the row identifier is the
    Product ID and I need to only show the SIP Accounts for that Product ID
    for each row.

    This is my GetSIPAccounts procedure :

    public DataSet GetSIPAccounts()
    {
    shopping_cartDB scCart = new shopping_cartDB();
    string strCartID = scCart.GetShoppingCartId();

    SqlConnection objConnection = new
    SqlConnection(ConfigurationSettings.AppSettings["strConnectTest"]);
    string strSIPAccounts = "SELECT SIPACCOUNT AS 'SIP Account' FROM
    SHOPPING_CART_SIP WHERE CARTID = '" + strCartID + "' AND PRODUCTID = " +
    Convert.ToInt32(dgShoppingCart.Items[correct item here].Cells[0].Text);

    SqlDataAdapter objDataAdapter = new SqlDataAdapter(strSIPAccounts,
    objConnection);

    DataSet ddlDataSet = new DataSet();

    objDataAdapter.Fill(ddlDataSet, "SIPAccounts");

    return ddlDataSet;

    }

    Can anybody help me out with this? Any help would be really
    appreciated!


    Cheers,

    Mike



    *** Sent via Devdex http://www.devdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Mike P, Jun 17, 2004
    #1
    1. Advertising

  2. Mike P wrote:

    > I am binding data to a datagrid, but there is 1 field that I need to use
    > a separate query to populate.
    >
    > This is the field :
    >
    > <asp:TemplateColumn HeaderText="SIP Account/s" ItemStyle-Width="75px"
    > HeaderStyle-HorizontalAlign=Center ItemStyle-HorizontalAlign=Center>
    > <ItemTemplate>
    > <asp:DropDownList id="ddlSIPAccounts" runat="server"
    > DataValueField="SIP Account" Font-Name="arial" Font-Size="8pt"
    > DataTextField="SIP Account"
    > DataSource='<%# GetSIPAccounts() %>' />
    > </ItemTemplate>
    > </asp:TemplateColumn>
    >
    > But using GetSIPAccounts, I somehow need to only populate each row with
    > the relevant SIP Accounts for that row - the row identifier is the
    > Product ID and I need to only show the SIP Accounts for that Product ID
    > for each row.
    >
    > This is my GetSIPAccounts procedure :
    >
    > public DataSet GetSIPAccounts()
    > {
    > shopping_cartDB scCart = new shopping_cartDB();
    > string strCartID = scCart.GetShoppingCartId();
    >
    > SqlConnection objConnection = new
    > SqlConnection(ConfigurationSettings.AppSettings["strConnectTest"]);
    > string strSIPAccounts = "SELECT SIPACCOUNT AS 'SIP Account' FROM
    > SHOPPING_CART_SIP WHERE CARTID = '" + strCartID + "' AND PRODUCTID = " +
    > Convert.ToInt32(dgShoppingCart.Items[correct item here].Cells[0].Text);
    >
    > SqlDataAdapter objDataAdapter = new SqlDataAdapter(strSIPAccounts,
    > objConnection);
    >
    > DataSet ddlDataSet = new DataSet();
    >
    > objDataAdapter.Fill(ddlDataSet, "SIPAccounts");
    >
    > return ddlDataSet;
    >
    > }
    >
    > Can anybody help me out with this? Any help would be really
    > appreciated!
    >
    >
    > Cheers,
    >
    > Mike
    >
    >
    >
    > *** Sent via Devdex http://www.devdex.com ***
    > Don't just participate in USENET...get rewarded for it!


    To get the product ID into your method, you should pass in an arg to
    your method from the binding statement; pass in the current row's
    product id out of that DataSet....Container.DataItem gives you the
    current DataRow being bound to the current datagrid item, so you could
    do something like:

    DataSource='<%#
    GetSIPAccounts(((DataRow)Container.DataItem)["prod_id_col_name"]) %>'

    note you'll need to add an Import directive for System.Data to your
    ..aspx page since I used DataRow in a cast there. You could then do
    another cast if you want, because as this is, the arg will be an Object

    public DataSet GetSIPAccounts(Object productID)
    {
    //cast the prod id appropriately before using


    --
    Craig Deelsnyder
    Microsoft MVP - ASP/ASP.NET
     
    Craig Deelsnyder, Jun 17, 2004
    #2
    1. Advertising

  3. Mike P

    Raterus Guest

    I think you'll need something like this, you'll also need to modify the GetSipAccounts function to take in the ProductID as a parameter. You should be able to do it from there.

    <asp:DropDownList id="ddlSIPAccounts" runat="server"
    DataValueField="SIP Account" Font-Name="arial" Font-Size="8pt"
    DataTextField="SIP Account"
    DataSource='<%# GetSIPAccounts(Container.DataItem("Product ID")) %>' />

    "Mike P" <> wrote in message news:%...
    > I am binding data to a datagrid, but there is 1 field that I need to use
    > a separate query to populate.
    >
    > This is the field :
    >
    > <asp:TemplateColumn HeaderText="SIP Account/s" ItemStyle-Width="75px"
    > HeaderStyle-HorizontalAlign=Center ItemStyle-HorizontalAlign=Center>
    > <ItemTemplate>
    > <asp:DropDownList id="ddlSIPAccounts" runat="server"
    > DataValueField="SIP Account" Font-Name="arial" Font-Size="8pt"
    > DataTextField="SIP Account"
    > DataSource='<%# GetSIPAccounts() %>' />
    > </ItemTemplate>
    > </asp:TemplateColumn>
    >
    > But using GetSIPAccounts, I somehow need to only populate each row with
    > the relevant SIP Accounts for that row - the row identifier is the
    > Product ID and I need to only show the SIP Accounts for that Product ID
    > for each row.
    >
    > This is my GetSIPAccounts procedure :
    >
    > public DataSet GetSIPAccounts()
    > {
    > shopping_cartDB scCart = new shopping_cartDB();
    > string strCartID = scCart.GetShoppingCartId();
    >
    > SqlConnection objConnection = new
    > SqlConnection(ConfigurationSettings.AppSettings["strConnectTest"]);
    > string strSIPAccounts = "SELECT SIPACCOUNT AS 'SIP Account' FROM
    > SHOPPING_CART_SIP WHERE CARTID = '" + strCartID + "' AND PRODUCTID = " +
    > Convert.ToInt32(dgShoppingCart.Items[correct item here].Cells[0].Text);
    >
    > SqlDataAdapter objDataAdapter = new SqlDataAdapter(strSIPAccounts,
    > objConnection);
    >
    > DataSet ddlDataSet = new DataSet();
    >
    > objDataAdapter.Fill(ddlDataSet, "SIPAccounts");
    >
    > return ddlDataSet;
    >
    > }
    >
    > Can anybody help me out with this? Any help would be really
    > appreciated!
    >
    >
    > Cheers,
    >
    > Mike
    >
    >
    >
    > *** Sent via Devdex http://www.devdex.com ***
    > Don't just participate in USENET...get rewarded for it!
     
    Raterus, Jun 17, 2004
    #3
  4. Mike P

    Mike P Guest

    Craig, Raterus,

    I've tried what you have suggested, but I get the error 'denotes a
    'property' where a 'method' was expected'. Here is my complete datagrid
    :

    <asp:datagrid id="dgShoppingCart" runat="server" CellPadding="3"
    Font-Name="arial" Font-Size="8pt" ForeColor="Black"
    HeaderStyle-ForeColor="#FFFFFF"
    HeaderStyle-BackColor="#000000" HeaderStyle-Font-Bold="True"
    GridLines=Horizontal
    BorderColor="DarkBlue" BackColor="LightGoldenrodYellow"
    AutoGenerateColumns="False" DataKeyField="Quantity">
    <ItemStyle BackColor="White"></ItemStyle>
    <SelectedItemStyle ForeColor="GhostWhite"
    BackColor="DarkSlateBlue"></SelectedItemStyle>
    <AlternatingItemStyle
    BackColor="#eeeeee"></AlternatingItemStyle>
    <HeaderStyle Font-Bold=True BackColor=#000000
    ForeColor=#FFFFFF></HeaderStyle>
    <Columns>
    <asp:TemplateColumn HeaderText="Product ID"
    ItemStyle-Width="40px" HeaderStyle-HorizontalAlign=Center
    ItemStyle-HorizontalAlign=Center>
    <ItemTemplate>
    <asp:Label id="lblProductID" runat="server" Text='<%#
    DataBinder.Eval(Container.DataItem, "Product ID") %>' />
    </ItemTemplate>
    </asp:TemplateColumn>
    <asp:TemplateColumn HeaderText="Product Type"
    ItemStyle-Width="140px" HeaderStyle-HorizontalAlign=Center
    ItemStyle-HorizontalAlign=Center>
    <ItemTemplate>
    <asp:Label id="lblProductType" font-bold="True"
    Font-Name="tahoma" ForeColor="Maroon" runat="server" Text='<%#
    DataBinder.Eval(Container.DataItem, "Product Type") %>' />
    </ItemTemplate>
    </asp:TemplateColumn>
    <asp:TemplateColumn HeaderText="SIP Account/s"
    ItemStyle-Width="75px" HeaderStyle-HorizontalAlign=Center
    ItemStyle-HorizontalAlign=Center>
    <ItemTemplate>
    <asp:DropDownList id="ddlSIPAccounts" runat="server"
    DataValueField="SIP Account" Font-Name="arial" Font-Size="8pt"
    DataTextField="SIP Account"
    DataSource='<%# GetSIPAccounts(Container.DataItem("Product ID")) %>' />
    </ItemTemplate>
    </asp:TemplateColumn>
    <asp:TemplateColumn HeaderText="SIP Domain"
    ItemStyle-Width="75px" HeaderStyle-HorizontalAlign=Center
    ItemStyle-HorizontalAlign=Center>
    <ItemTemplate>
    <asp:Label id="lblSIPDomain" runat="server" Text='<%#
    DataBinder.Eval(Container.DataItem, "SIP Domain") %>' />
    </ItemTemplate>
    </asp:TemplateColumn>
    <asp:BoundColumn DataField="Unit Price (GBP)"
    HeaderText="Unit Price (GBP)" DataFormatString="{0:c}"
    ItemStyle-Width="50px" HeaderStyle-HorizontalAlign=Center
    ItemStyle-HorizontalAlign=Center />
    <asp:TemplateColumn HeaderText="Quantity"
    ItemStyle-Width="55px" HeaderStyle-HorizontalAlign=Center
    ItemStyle-HorizontalAlign=Center>
    <ItemTemplate>
    <asp:Label id="lblQuantity" runat="server" Text='<%#
    DataBinder.Eval(Container.DataItem, "Quantity") %>' />
    </ItemTemplate>
    </asp:TemplateColumn>
    <asp:TemplateColumn HeaderText="Sub Total (GBP)"
    ItemStyle-Width="60px" HeaderStyle-HorizontalAlign=Center
    ItemStyle-HorizontalAlign=Center>
    <ItemTemplate>
    <asp:Label id="lblSubTotal" runat="server"
    ForeColor="Maroon" Text='<%# DataBinder.Eval(Container.DataItem, "Sub
    Total", "{0:c}") %>' />
    </ItemTemplate>
    </asp:TemplateColumn>
    <asp:TemplateColumn HeaderText="Remove"
    ItemStyle-Width="50px" HeaderStyle-HorizontalAlign=Center
    ItemStyle-HorizontalAlign=Center>
    <ItemTemplate>
    <asp:CheckBox id="chkRemove" runat="server"
    Checked=False EnableViewState=True />
    </ItemTemplate>
    </asp:TemplateColumn>
    </Columns>
    </asp:datagrid>


    I am populating the datagrid with a stored procedure, and I am trying to
    add the additional column with this code :

    public DataSet GetSIPAccounts(int intProductID)
    {
    shopping_cartDB scCart = new shopping_cartDB();
    string strCartID = scCart.GetShoppingCartId();

    SqlConnection objConnection = new
    SqlConnection(ConfigurationSettings.AppSettings["strConnectTest"]);
    string strSIPAccounts = "SELECT SIPACCOUNT AS 'SIP Account' FROM
    SHOPPING_CART_SIP WHERE CARTID = '" + strCartID + "' AND PRODUCTID = " +
    intProductID;

    SqlDataAdapter objDataAdapter = new SqlDataAdapter(strSIPAccounts,
    objConnection);

    DataSet ddlDataSet = new DataSet();

    objDataAdapter.Fill(ddlDataSet, "SIPAccounts");

    return ddlDataSet;

    }

    Thanks,

    Mike




    *** Sent via Devdex http://www.devdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Mike P, Jun 18, 2004
    #4
  5. Mike P

    Raterus Guest

    sorry, I gave you the VB answer, you need the C# one

    DataSource='<%# GetSIPAccounts(Container.DataItem["Product ID"]) %>

    note the [ ] around "Product ID" now and not ( )

    "Mike P" <> wrote in message news:...
    > Craig, Raterus,
    >
    > I've tried what you have suggested, but I get the error 'denotes a
    > 'property' where a 'method' was expected'. Here is my complete datagrid
    > :
    >
    > <asp:datagrid id="dgShoppingCart" runat="server" CellPadding="3"
    > Font-Name="arial" Font-Size="8pt" ForeColor="Black"
    > HeaderStyle-ForeColor="#FFFFFF"
    > HeaderStyle-BackColor="#000000" HeaderStyle-Font-Bold="True"
    > GridLines=Horizontal
    > BorderColor="DarkBlue" BackColor="LightGoldenrodYellow"
    > AutoGenerateColumns="False" DataKeyField="Quantity">
    > <ItemStyle BackColor="White"></ItemStyle>
    > <SelectedItemStyle ForeColor="GhostWhite"
    > BackColor="DarkSlateBlue"></SelectedItemStyle>
    > <AlternatingItemStyle
    > BackColor="#eeeeee"></AlternatingItemStyle>
    > <HeaderStyle Font-Bold=True BackColor=#000000
    > ForeColor=#FFFFFF></HeaderStyle>
    > <Columns>
    > <asp:TemplateColumn HeaderText="Product ID"
    > ItemStyle-Width="40px" HeaderStyle-HorizontalAlign=Center
    > ItemStyle-HorizontalAlign=Center>
    > <ItemTemplate>
    > <asp:Label id="lblProductID" runat="server" Text='<%#
    > DataBinder.Eval(Container.DataItem, "Product ID") %>' />
    > </ItemTemplate>
    > </asp:TemplateColumn>
    > <asp:TemplateColumn HeaderText="Product Type"
    > ItemStyle-Width="140px" HeaderStyle-HorizontalAlign=Center
    > ItemStyle-HorizontalAlign=Center>
    > <ItemTemplate>
    > <asp:Label id="lblProductType" font-bold="True"
    > Font-Name="tahoma" ForeColor="Maroon" runat="server" Text='<%#
    > DataBinder.Eval(Container.DataItem, "Product Type") %>' />
    > </ItemTemplate>
    > </asp:TemplateColumn>
    > <asp:TemplateColumn HeaderText="SIP Account/s"
    > ItemStyle-Width="75px" HeaderStyle-HorizontalAlign=Center
    > ItemStyle-HorizontalAlign=Center>
    > <ItemTemplate>
    > <asp:DropDownList id="ddlSIPAccounts" runat="server"
    > DataValueField="SIP Account" Font-Name="arial" Font-Size="8pt"
    > DataTextField="SIP Account"
    > DataSource='<%# GetSIPAccounts(Container.DataItem("Product ID")) %>' />
    > </ItemTemplate>
    > </asp:TemplateColumn>
    > <asp:TemplateColumn HeaderText="SIP Domain"
    > ItemStyle-Width="75px" HeaderStyle-HorizontalAlign=Center
    > ItemStyle-HorizontalAlign=Center>
    > <ItemTemplate>
    > <asp:Label id="lblSIPDomain" runat="server" Text='<%#
    > DataBinder.Eval(Container.DataItem, "SIP Domain") %>' />
    > </ItemTemplate>
    > </asp:TemplateColumn>
    > <asp:BoundColumn DataField="Unit Price (GBP)"
    > HeaderText="Unit Price (GBP)" DataFormatString="{0:c}"
    > ItemStyle-Width="50px" HeaderStyle-HorizontalAlign=Center
    > ItemStyle-HorizontalAlign=Center />
    > <asp:TemplateColumn HeaderText="Quantity"
    > ItemStyle-Width="55px" HeaderStyle-HorizontalAlign=Center
    > ItemStyle-HorizontalAlign=Center>
    > <ItemTemplate>
    > <asp:Label id="lblQuantity" runat="server" Text='<%#
    > DataBinder.Eval(Container.DataItem, "Quantity") %>' />
    > </ItemTemplate>
    > </asp:TemplateColumn>
    > <asp:TemplateColumn HeaderText="Sub Total (GBP)"
    > ItemStyle-Width="60px" HeaderStyle-HorizontalAlign=Center
    > ItemStyle-HorizontalAlign=Center>
    > <ItemTemplate>
    > <asp:Label id="lblSubTotal" runat="server"
    > ForeColor="Maroon" Text='<%# DataBinder.Eval(Container.DataItem, "Sub
    > Total", "{0:c}") %>' />
    > </ItemTemplate>
    > </asp:TemplateColumn>
    > <asp:TemplateColumn HeaderText="Remove"
    > ItemStyle-Width="50px" HeaderStyle-HorizontalAlign=Center
    > ItemStyle-HorizontalAlign=Center>
    > <ItemTemplate>
    > <asp:CheckBox id="chkRemove" runat="server"
    > Checked=False EnableViewState=True />
    > </ItemTemplate>
    > </asp:TemplateColumn>
    > </Columns>
    > </asp:datagrid>
    >
    >
    > I am populating the datagrid with a stored procedure, and I am trying to
    > add the additional column with this code :
    >
    > public DataSet GetSIPAccounts(int intProductID)
    > {
    > shopping_cartDB scCart = new shopping_cartDB();
    > string strCartID = scCart.GetShoppingCartId();
    >
    > SqlConnection objConnection = new
    > SqlConnection(ConfigurationSettings.AppSettings["strConnectTest"]);
    > string strSIPAccounts = "SELECT SIPACCOUNT AS 'SIP Account' FROM
    > SHOPPING_CART_SIP WHERE CARTID = '" + strCartID + "' AND PRODUCTID = " +
    > intProductID;
    >
    > SqlDataAdapter objDataAdapter = new SqlDataAdapter(strSIPAccounts,
    > objConnection);
    >
    > DataSet ddlDataSet = new DataSet();
    >
    > objDataAdapter.Fill(ddlDataSet, "SIPAccounts");
    >
    > return ddlDataSet;
    >
    > }
    >
    > Thanks,
    >
    > Mike
    >
    >
    >
    >
    > *** Sent via Devdex http://www.devdex.com ***
    > Don't just participate in USENET...get rewarded for it!
     
    Raterus, Jun 18, 2004
    #5
  6. Mike P

    Mike P Guest

    Can you actually get this working in your applications? It seems that
    it won't accept GetSIPAccounts(Container.DataItem("Product ID")) or
    GetSIPAccounts(Container.DataItem["Product ID"]). When I try
    GetSIPAccounts(16) the value 16 gets passed, but I can't get it to pass
    anything related to Container.DataItem.


    Thanks,

    Mike



    *** Sent via Devdex http://www.devdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Mike P, Jun 18, 2004
    #6
  7. Mike P

    Jim Corey Guest

    The solutions proposed might be the quickest to implement,
    given the code you have now, but an alternative method
    would be:

    If you have two data sources, create a new datatable that
    will eventually be the datasource for the grid (you might
    clone one of the original data sources, and then add columns as needed.)
    Iterate through your two original datasources and populate the new
    datatable, then bind it.

    Or more simply, add a column to your datasource, iterate through it and
    call a function to populate the new column for each row,
    then bind it to the grid.

    HTH,
    Jim

    *** Sent via Devdex http://www.devdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Jim Corey, Jun 18, 2004
    #7
  8. Mike P

    Raterus Guest

    Yes, this works fine for me (vb.net)

    in a repeater control's <itemtemplate>

    <asp:dropdownlist id="ddlDDL" runat="server" datasource='<%# getListItems(cint(Container.DataItem("docID"))) %>'></asp:dropdownlist>

    in codebehind:

    Protected Function getListItems(ByVal docID As Integer) As String()
    Dim retVal(10) As String

    Dim i As Integer
    For i = 0 To 10
    retVal(i) = CStr(docID + i)
    Next

    Return retVal
    End Function



    "Mike P" <> wrote in message news:...
    > Can you actually get this working in your applications? It seems that
    > it won't accept GetSIPAccounts(Container.DataItem("Product ID")) or
    > GetSIPAccounts(Container.DataItem["Product ID"]). When I try
    > GetSIPAccounts(16) the value 16 gets passed, but I can't get it to pass
    > anything related to Container.DataItem.
    >
    >
    > Thanks,
    >
    > Mike
    >
    >
    >
    > *** Sent via Devdex http://www.devdex.com ***
    > Don't just participate in USENET...get rewarded for it!
     
    Raterus, Jun 18, 2004
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. John
    Replies:
    1
    Views:
    386
  2. AD
    Replies:
    1
    Views:
    1,050
    Francis Shanahan
    Aug 23, 2003
  3. Winterminute

    Combining Two Data Sources for one Data List

    Winterminute, Dec 3, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    837
    Winterminute
    Dec 3, 2003
  4. Bogdan
    Replies:
    1
    Views:
    690
    Mr. Arnold
    Sep 29, 2009
  5. Motley Drew

    Delete Datagrid from Multiple Sources (VB)

    Motley Drew, Feb 18, 2004, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    128
    Motley Drew
    Feb 18, 2004
Loading...

Share This Page