Increment value in datagrid column programatically

[JulesBos]

Hi I have a datagrid that is populated with a dataset. There is an extra template column that the datagrid has that I would like to populate programatically, but I'm not sure how to go about doing this. Here's my current code:

'this sub populates the data grid
        Connection.Open()
        'set the data adapter if it's not already been done
        PrintItemsDataSet1.Clear()
        PrintDataAdapter.SelectCommand.CommandText = _
            "SELECT QuoteItems.SubHeading, QuoteItems.Qty, QuoteItems.PN, QuoteItems.Desc, " + _
            "QuoteItems.LeadTime, QuoteItems.Optional, QuoteItems.Price, Quotes.ROE, " + _
            "[Price]*[Roe] AS USDPrice, [Qty]*[USDPrice] AS TotalValue " + _
            "FROM Quotes INNER JOIN QuoteItems ON Quotes.RecordID = QuoteItems.QuoteID " + _
            "GROUP BY QuoteItems.SubHeading, QuoteItems.Qty, QuoteItems.PN, QuoteItems.Desc, " + _
            "QuoteItems.LeadTime, QuoteItems.Optional, QuoteItems.Price, Quotes.ROE, " + _
            "[Price]*[Roe], [Qty]*[USDPrice] " + _
            "ORDER BY QuoteItems.SubHeading, QuoteItems.PN WHERE QuoteItems.QuoteID=" + quoteID
        PrintDataAdapter.Fill(PrintItemsDataSet1, 0)
        DataGrid.DataBind()
        Connection.Close()

My new column is ItemNo and all I want to do is cycle through the records and give each record an item number starting at 1 and incrementing 1 for each record.

If anyone has any ideas, I'd be grateful.

Thanks

 

[ca8msm]

You could do this in your SQL Statement.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[JulesBos]

Thanks, will investigate, but I also have another column where I want to replace the textual value with "N/A" if it contains a certain value. Could I do that in the datagrid/dataset?

Julia

 

[ca8msm]

Yes, you can use the data bound event of the datagrid or again you could do this by using a case statement in your sql statement.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[JulesBos]

Thanks, have you any examples? I'm struggling to work out how to access a column value.

Julia

 

[JulesBos]

Will this do it?

Private Sub DataGrid_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGrid.ItemDataBound
    
'for each item that's databound in the datagrid, give it an item number
        itemNumber += itemNumber
        Select Case e.Item.ItemType
            Case ListItemType.AlternatingItem, ListItemType.Item
                Dim ItemNo As Label = e.Item.FindControl("Item No")
                ItemNo.Text = CStr(itemNumber)
        End Select
        'then check to see if the part number is a service and change the part number to n/a
        Connection.Open()
        Dim SQLSelectString As String = _
            "SELECT PartNumbers.PartNumber FROM PartNumbers " + _
            "WHERE PartNumbers.Service=True AND PartNumbers.RecordCurrent=True"
        DBCommand.commandtext = SQLSelectString
        Dim datareader As OleDb.OleDbDataReader = DBCommand.ExecuteReader(CommandBehavior.CloseConnection)
        While datareader.Read
            Select Case e.Item.ItemType
            Case ListItemType.AlternatingItem, ListItemType.Item
                Dim PN As Label = e.Item.FindControl("Part Number")
                If PN.Text = datareader.GetValue(0) Then
                    PN.Text = "N/A"
                End If
            End Select
        End While
End Sub

 

[ca8msm]

I'm not sure, just give it a go and see what happens.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[JulesBos]

Hi, well I've given this a go and it doesn't work, but I'm sure it's because I've got something wrong, not because it can't work.

 <asp:DataGrid id=DataGrid runat="server" Width="25.5cm" Font-Size="XX-Small" Font-Names="Verdana" AutoGenerateColumns="False" DataMember="Table" DataSource="<%# PrintItemsDataSet1 %>" BorderColor="Black" BorderWidth="1px" ShowFooter="True">
                        <FooterStyle Font-Bold="True"></FooterStyle>
                        <HeaderStyle Font-Bold="True"></HeaderStyle>
                        <Columns>
                            <asp:BoundColumn DataField="SubHeading" SortExpression="SubHeading" HeaderText="SubHeading"></asp:BoundColumn>
                            <asp:TemplateColumn HeaderText="Item No">
                                <ItemTemplate>
                                    <asp:Label ID="ItemNoLabel"></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateColumn>
                            <asp:BoundColumn DataField="Qty" SortExpression="Qty" HeaderText="Qty"></asp:BoundColumn>
                            <asp:BoundColumn DataField="Optional" SortExpression="Optional" HeaderText="Optional"></asp:BoundColumn>
                            <asp:TemplateColumn HeaderText="Part Number">
                                <ItemTemplate>
                                    <asp:Label runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.PN") %>' ID="PartNoLabel">
                                    </asp:Label>
                                </ItemTemplate>
                                <EditItemTemplate>
                                    <asp:TextBox runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.PN") %>'>
                                    </asp:TextBox>
                                </EditItemTemplate>
                            </asp:TemplateColumn>
                            <asp:BoundColumn DataField="Desc" SortExpression="Desc" HeaderText="Description"></asp:BoundColumn>
                            <asp:BoundColumn DataField="LeadTime" SortExpression="LeadTime" HeaderText="Delivery Months (ARO)"></asp:BoundColumn>
                            <asp:BoundColumn DataField="USDPrice" SortExpression="USDPrice" HeaderText="Unit Price $" DataFormatString="{0:F2}"></asp:BoundColumn>
                            <asp:BoundColumn DataField="TotalValue" SortExpression="TotalValue" HeaderText="Total Price $" DataFormatString="{0:F2}"
                                FooterText="Total value $"></asp:BoundColumn>
                        </Columns>
                    </asp:DataGrid>

Private Sub DataGrid_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGrid.ItemDataBound
        'for each item that's databound in the datagrid, give it an item number
        itemNumber += itemNumber
        Select Case e.Item.ItemType
            Case ListItemType.AlternatingItem, ListItemType.Item
                Dim ItemNo As Label = e.Item.FindControl("ItemNoLabel")
                ItemNo.Text = CStr(itemNumber)
        End Select
        'then check to see if the part number is a service and change the part number to n/a
        Dim servicePN As ListItem
        For Each servicePN In Me.ServicePNList.Items
            Select Case e.Item.ItemType
            Case ListItemType.AlternatingItem, ListItemType.Item
                Dim PN As Label = e.Item.FindControl("PartNoLabel")
                If PN.Text = servicePN.Value Then
                    PN.Text = "N/A"
                End If
            End Select
        Next
    End Sub

the code runs to line ItemNo.Text = CStr(itemNumber) where it stops because ItemNo is Nothing.

What have I done wrong?

Julia

 

[JulesBos]

Anyone any ideas on this one?

Julia

 

[Kalisto]

Private Sub DataGrid_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGrid.ItemDataBound
    
'for each item that's databound in the datagrid, give it an item number
        itemNumber += itemNumber
        Select Case e.Item.ItemType
            Case ListItemType.AlternatingItem, ListItemType.Item

This will increment item number for each item, even headers, footers etc. I'd suggest purring the increment inside the loop so it only increments for data rows.

Where is it declared by the way? (itemNumber)

Dim ItemNo As Label = e.Item.FindControl("Item No")

try using the numeric index to access the cell, not find control.

Dim cell as TableCell = e.Row.Cells(0)
cell.Text = CStr(itemNumber)

I think you need to put a breakpoint on this method and debug it, so you can see what is going on...


K

 

[JulesBos]

Thanks, you're a genius!

Here's the updated code that works perfectly.

Select Case e.Item.ItemType
            Case ListItemType.AlternatingItem, ListItemType.Item
                itemNumber = itemNumber + 1
                Dim ItemNo As Label = e.Item.FindControl("ItemNoLabel")
                Dim cell As TableCell = e.Item.Cells(1)
                cell.Text = CStr(itemNumber)
        End Select

FYI, the itemNumber variable is declared at form level.

Julia