Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 756
  • Last Modified:

Bulk update on a gridview with a checkbox gives error

I'm using the code from http://msdn2.microsoft.com/en-us/library/ms404814.aspx to do a bulk update on a datagrid. Basicly you put text boxes in the item view, then click an update button which saves the data to a table scan trhough it to see if a row has changed then does a SQL update. As I'm trying to do this I have one setting that is a check box. When I try to do a row update that gives me an error that it can't save null into a boolean value. I've got it set correctly in the upade parameters and everything as a boolean. What am I messing up?

Protected Sub UpdateButton_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles UpdateButton.Click
        originalDataTable = CType(ViewState("originalValuesDataTable"), System.Data.DataTable)

        For Each r As GridViewRow In GridView1.Rows
            If IsRowModified(r) Then GridView1.UpdateRow(r.RowIndex, False) <-- can't save a null to a bit in SQL????
        Next

        ' Rebind the Grid to repopulate the original values table.
        tableCopied = False
        GridView1.DataBind()
End Sub

Protected Function IsRowModified(ByVal r As GridViewRow) As Boolean
        Dim currentID As Integer
        Dim currentQuantity As String
        Dim currentsellprice As String
        Dim currentextprice As String
        Dim currentUnitCost As String
        Dim currentSystemSale As Boolean

        currentID = Convert.ToInt32(GridView1.DataKeys(0).Value)

        currentQuantity = CType(r.FindControl("QuantityTextBox"), TextBox).Text
        currentsellprice = CType(r.FindControl("sellpriceTextBox"), TextBox).Text
        currentextprice = CType(r.FindControl("extpriceTextBox"), TextBox).Text
        currentUnitCost = CType(r.FindControl("UnitCostTextBox"), TextBox).Text
        currentSystemSale = CType(r.FindControl("chkSystemSale"), CheckBox).Checked

        Dim row As System.Data.DataRow = originalDataTable.Select(String.Format("OrderDetailID = {0}", currentID))(0)

        If Not currentQuantity.Equals(row("Quantity").ToString()) Then Return True
        If Not currentsellprice.Equals(row("sellprice").ToString()) Then Return True
        If Not currentextprice.Equals(row("extprice").ToString()) Then Return True
        If Not currentUnitCost.Equals(row("UnitCost").ToString()) Then Return True
        If Not currentSystemSale.Equals(row("SystemSale").ToString()) Then Return True

        Return False
End Function

0
kevlause
Asked:
kevlause
1 Solution
 
RamuncikasCommented:
Did you try
If IsRowModified(r) Then GridView1.UpdateRow(r.RowIndex, 0)

?
0
 
kevlauseAuthor Commented:
I still get...
Cannot insert the value NULL into column 'SystemSale', table 'dbPoints.dbo.OrderDetails'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

The false is if you want to cause validation or not I thought it was an issue with myupdate statement, but I think I have that right. I set the select command in the page load section.

                            <asp:SqlDataSource ID="SqlDataSource7" runat="server" ConnectionString="<%$ ConnectionStrings:dbPointsConnectionString %>" DeleteCommand="DELETE FROM [OrderDetails] WHERE [OrderDetailID] = @OrderDetailID" UpdateCommand="UPDATE [OrderDetails] SET [Quantity] = @Quantity, [SellPrice] = @SellPrice, [UnitCost] = @UnitCost, [SystemSale] = @SystemSale WHERE [OrderDetailID] = @OrderDetailID" >
                                <DeleteParameters>
                                    <asp:Parameter Name="OrderDetailID" Type="Int32" />
                                </DeleteParameters>
                                <UpdateParameters>
                                    <asp:Parameter Name="Quantity" Type="Double" />
                                    <asp:Parameter Name="SellPrice" Type="Decimal" />
                                    <asp:Parameter Name="UnitCost" Type="Decimal" />
                                    <asp:Parameter Name="SystemSale" Type="Boolean" />
                                    <asp:Parameter Name="OrderDetailID" Type="Int32" />
                                </UpdateParameters>
                             </asp:SqlDataSource>
0
 
Bob LearnedCommented:
How is the GridView bound?  Have you defined columns?  Or are they auto-generated?  Can you show the columns for the GridView?

Bob
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
kevlauseAuthor Commented:
I just figured this out another way. I think the problem was I was setting the select command on page load and it was messing with the way the datasource was working. I ended up just writing a sql commmand o write to the database to get around it. Thanks for the post!
0
 
kevlauseAuthor Commented:
   Protected Function IsRowModified(ByVal r As GridViewRow) As Boolean

        Dim currentID As Integer
        Dim currentQuantity As String
        Dim currentsellprice As String
        Dim currentUnitCost As String
        Dim currentSystemSale As Boolean
        currentID = Convert.ToInt32(GridView1.DataKeys(Session("scan")).Value)
        currentQuantity = CType(r.FindControl("QuantityTextBox"), TextBox).Text
        currentsellprice = CType(r.FindControl("sellpriceTextBox"), TextBox).Text
        currentUnitCost = CType(r.FindControl("UnitCostTextBox"), TextBox).Text
        currentSystemSale = CType(r.FindControl("chkSystemSale"), CheckBox).Checked

        Dim row As System.Data.DataRow = originalDataTable.Select(String.Format("OrderDetailID = {0}", currentID))(0)

        If Not currentQuantity.Equals(row("Quantity").ToString()) Then Return True
        If Not currentsellprice.Equals(row("sellprice").ToString())  Then Return True
        If Not currentUnitCost.Equals(String.Format("{0:c}", row("UnitCost").ToString())) Then Return True
        If Not currentSystemSale.Equals(row("SystemSale").ToString()) Then Return True
        Return False
    End Function

    Protected Sub UpdateButton_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles UpdateButton.Click
        originalDataTable = CType(ViewState("originalValuesDataTable"), System.Data.DataTable)
        Session("scan") = 0
        For Each r As GridViewRow In GridView1.Rows
            If IsRowModified(r) = True Then
                Dim currentID As Integer
                Dim currentQuantity As String
                Dim currentsellprice As String
                Dim currentUnitCost As String
                Dim currentSystemSale As Boolean
                currentID = Convert.ToInt32(GridView1.DataKeys(Session("scan")).Value)
                Dim row As System.Data.DataRow = originalDataTable.Select(String.Format("OrderDetailID = {0}", currentID))(0)
                currentQuantity = CType(r.FindControl("QuantityTextBox"), TextBox).Text
                currentsellprice = CType(r.FindControl("sellpriceTextBox"), TextBox).Text
                currentUnitCost = CType(r.FindControl("UnitCostTextBox"), TextBox).Text
                currentSystemSale = CType(r.FindControl("chkSystemSale"), CheckBox).Checked
                Dim sqlcmd As String = "UPDATE OrderDetails SET Quantity = '" & currentQuantity & "', SellPrice = convert(money,'" & currentsellprice & "'), UnitCost = convert(money,'" & currentUnitCost & "'), SystemSale = '" & System.Convert.ToInt32(currentSystemSale) & "' WHERE OrderDetailID = '" & currentID & "'"
                Dim connectionString As String = WebConfigurationManager.ConnectionStrings("dbPointsConnectionString").ConnectionString
                Dim conn As New SqlConnection(connectionString)
                Dim cmd As New SqlCommand(sqlcmd, conn)
                Using conn
                    conn.Open()
                    cmd.ExecuteScalar()
                End Using
            End If
            Session("scan") = Session("scan") + 1
        Next
        Session("scan") = 0
        ' Rebind the Grid to repopulate the original values table.
        tableCopied = False
        GridView1.DataBind()
    End Sub
0
 
RomModCommented:
Asker resolved question - the 500 points have been refunded and the question
PAQ'd

Regards,
RomMod
Community Support Moderator
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now