datagrid with 2 data sources

[Mike P]

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!

[Craig Deelsnyder]

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

[Raterus]

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" <mr*@telcoelectronics.co.uk> wrote in message news:%2****************@TK2MSFTNGP11.phx.gbl...

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]

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]

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" <mr*@telcoelectronics.co.uk> wrote in message news:ug**************@tk2msftngp13.phx.gbl...

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]

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!

[Jim Corey]

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!

[Raterus]

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" <mr*@telcoelectronics.co.uk> wrote in message news:Oo**************@tk2msftngp13.phx.gbl...

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!