• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 519
  • Last Modified:

How to make CheckBoxes in ASP.NET GridView update database when checked. *When checked*

Dear Experts,

When I click on a checkbox to either check or uncheck it I get the Server Error shown below the .aspx and .vb markup below.
I got the original C# code from here:http://www.ezzylearning.com/tutorial.aspx?tid=5187857 and tried to convert it to VB.NET.
I can't figure out what's causing the ExecuteNonQuery() to give me a hard time.  Thank you for any help.
(Please note, I'm working in VB.NET.)

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" 
             BorderWidth="1px" CellPadding="3" DataKeyNames="CustomerID" DataSourceID="SqlDataSource1" AllowSorting="True">
   <Columns>
       <asp:BoundField DataField="CustomerID" HeaderText="ID"  InsertVisible="False" ReadOnly="True" SortExpression="CustomerID" />
       <asp:TemplateField HeaderText="CA" SortExpression="Computer">
           <ItemTemplate>
               <asp:CheckBox ID="cbComputerItem" runat="server" AutoPostBack="True" 
                             OnCheckedChanged="cbComputerItem_CheckedChanged"
                             Checked='<%# Convert.ToBoolean(Eval("Computer")) %>' 
                             Text='<%# Eval("Computer").ToString() %>' />
           </ItemTemplate>
       </asp:TemplateField>

Open in new window


Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.UI.WebControls

Partial Class Admin
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(sender As Object, e As EventArgs)
        If Not Page.IsPostBack Then
            LoadData()
        End If
    End Sub


    Private Sub LoadData()
        'Dim constr As String = "Server=.\SQLEXPRESS;Database=TestDB;uid=waqas;pwd=sql;"
        Dim constr As New SqlConnection(ConfigurationManager.ConnectionStrings("OneStopSignInConnectionString").ConnectionString)
        Dim query As String = "SELECT CustomerID, Computer FROM Customers"

        Dim da As New SqlDataAdapter(query, constr)
        Dim table As New DataTable()
        da.Fill(table)

        GridView1.DataSource = table
        GridView1.DataBind()
    End Sub

    Protected Sub cbComputerItem_CheckedChanged(sender As Object, e As System.EventArgs)
        Dim cbComputerItem As CheckBox = DirectCast(sender, CheckBox)
        Dim row As GridViewRow = DirectCast(cbComputerItem.NamingContainer, GridViewRow)

        Dim cid As String = row.Cells(1).Text
        Dim status As Boolean = cbComputerItem.Checked

        Dim query As String = "UPDATE Customers SET Computer = @Computer WHERE CustomerID = @CustomerID"

        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("OneStopSignInConnectionString").ConnectionString)
        Dim com As New SqlCommand(query, con)

        com.Parameters.Add("@Computer", SqlDbType.Bit).Value = status
        com.Parameters.Add("@CustomerID", SqlDbType.Int).Value = cid

        con.Open()
        com.ExecuteNonQuery()
        con.Close()

        LoadData()
    End Sub
End Class

Open in new window


Input string was not in a correct format. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.FormatException: Input string was not in a correct format.

Source Error: 


Line 42: 
Line 43:         con.Open()
Line 44:         com.ExecuteNonQuery()
Line 45:         con.Close()
Line 46: 
 

Source File: E:\inetpub\www2\OneStopSignIn\Admin.aspx.vb    Line: 44 

Stack Trace: 


[FormatException: Input string was not in a correct format.]
   System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +9586043
   System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +119
   System.String.System.IConvertible.ToInt32(IFormatProvider provider) +46
   System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) +385
   System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) +5075522

[FormatException: Failed to convert parameter value from a String to a Int32.]
   System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) +5074733
   System.Data.SqlClient.SqlParameter.GetCoercedValue() +32
   System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc) +103
   System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters) +209
   System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc) +237
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +178
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
   Admin.cbComputerItem_CheckedChanged(Object sender, EventArgs e) in E:\inetpub\www2\OneStopSignIn\Admin.aspx.vb:44
   System.Web.UI.WebControls.CheckBox.OnCheckedChanged(EventArgs e) +118
   System.Web.UI.WebControls.CheckBox.RaisePostDataChangedEvent() +142
   System.Web.UI.WebControls.CheckBox.System.Web.UI.IPostBackDataHandler.RaisePostDataChangedEvent() +10
   System.Web.UI.Page.RaiseChangedEvents() +134
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5201
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1 

Open in new window

0
megnin
Asked:
megnin
  • 6
  • 3
1 Solution
 
megninAuthor Commented:
I changed the data type on line 41 from .Int to .VarChar and the "Input sting was not in the correct format" error stoped:
        com.Parameters.Add("@Computer", SqlDbType.Bit).Value = status        com.Parameters.Add("@CustomerID", SqlDbType.Int).Value = cid

Open in new window

to:
        com.Parameters.Add("@Computer", SqlDbType.Bit).Value = status
        com.Parameters.Add("@CustomerID", SqlDbType.VarChar).Value = cid

Open in new window


...but the [computer] field in the database is not updated when I click a checkbox.  It changes briefly when I click a checkbox but when the page posts back it returns to the original state.

I also removed the DataSourceID from the GridView since the DataSource is defined in the .vb

I'm not getting any errors now, but the checkbox state does not change when I click it.

Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.UI.WebControls

Partial Class Admin
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(sender As Object, e As EventArgs)
        If Not Page.IsPostBack Then
            LoadData()
        End If
    End Sub


    Private Sub LoadData()
        'Dim constr As String = "Server=.\SQLEXPRESS;Database=TestDB;uid=waqas;pwd=sql;"
        Dim constr As New SqlConnection(ConfigurationManager.ConnectionStrings("OneStopSignInConnectionString").ConnectionString)
        'Dim query As String = "SELECT CustomerID, Computer FROM Customers"
        Dim query As String = "SELECT [CustomerID], [Computer], [FirstName], [LastName], [Email], [SSN], [FirstVisit], [Employed], [Office], [VeteranServ], [DisabilityServ], [ResourceRoom], [Unemployment], [Workshop], [GeneralInfo], [Interview], [DateTime] FROM [Customers]"

        Dim da As New SqlDataAdapter(query, constr)
        Dim table As New DataTable()
        da.Fill(table)

        GridView1.DataSource = table
        GridView1.DataBind()
    End Sub

    Protected Sub cbComputerItem_CheckedChanged(sender As Object, e As System.EventArgs)
        Dim cbComputerItem As CheckBox = DirectCast(sender, CheckBox)
        Dim row As GridViewRow = DirectCast(cbComputerItem.NamingContainer, GridViewRow)

        Dim cid As String = row.Cells(1).Text
        Dim status As Boolean = cbComputerItem.Checked

        Dim query As String = "UPDATE Customers SET Computer = @Computer WHERE CustomerID = @CustomerID"

        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("OneStopSignInConnectionString").ConnectionString)
        Dim com As New SqlCommand(query, con)

        com.Parameters.Add("@Computer", SqlDbType.Bit).Value = status
        com.Parameters.Add("@CustomerID", SqlDbType.VarChar).Value = cid

        con.Open()
        com.ExecuteNonQuery()
        con.Close()

        LoadData()
    End Sub
End Class

Open in new window

0
 
CmdoProg2Commented:
Your cid is empty since your reading the text of the second column.  The Cells collection is zero based.
Try...
       Dim cid As String = row.Cells(0).Text

0
 
megninAuthor Commented:
Ahh, that explains why when I added a "select" column it started updating!  

An odd thing though... I have 15 test records in the table right now and they all update the database when I click the checkbox except for record number 11.  When I click number 11's checkbox I get an error saying that field can't accept a NULL value.  That field in the DB has a default value set, and it's just that one record.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
megninAuthor Commented:
Oh, and also, it didn't work until I removed all the references to the DataLoad() subroutine and put the DataSourceID back in the GridView.  I'm not sure what that DataLoad routine was supposed to accomplish.  (I got it all from the web link in my question post.)
0
 
CmdoProg2Commented:
The dataload routine is a generic load of the data to the gridview, which a lot of example code use.  I pefer using a datasourceID for these types of controls.

On the "odd thing",  I need a little clarification.  When you toggle the checkbox for record 2, does change for only record 2 or does it changes all records except for number 11?

When you are updating record 11, you may want to check the values before it is sent to the database.  On the database, does the table have a trigger?  What type of SQL are you using?
0
 
megninAuthor Commented:
I'm thinking record #11 was corrupt or something.  I can't think of any reason one record would behave differently than any other.  Each record would update correctly, i.e. if I clear the checkbox in #2 the database record for #2 would update accordingling and not affect any record but #2.  Same for all the other individual records, except 11.  If I clicked the checkbox on 11 I got the NULL exception.  I added an Edit button to the controls column of the GridView and tried to edit #11 that way and got the same results.  All others good.  #11 complained about not accepting a NULL value.

So, I deleted record 11 from the database and everthing else seems to be fine.

I left the controls column and adjusted Dim cid As String = row.Cells(1).Text accordingly, back to "1".

I'm going to test adding some new records and see if everything keeps working.
0
 
CmdoProg2Commented:
okay,  my only thought about #11 is the null exception could be from another field on the record that is not apart of update, but I doubt it.
0
 
megninAuthor Commented:
Yeah, I looked for another field that may cause the problem but #11 had the same data in the same fields as the other records.  So far it seems to be working okay.  

I'm adding new records and testing the update and adding a meta refresh to the Admin.aspx page to keep it up to date with the new record entry page.  

It's a sign in form for our resource centers and the grid is an "admin" page for staff to manage sign-ins and assign computer workstation to them as they come in.
0
 
megninAuthor Commented:
Much thanks for your help with this.  That did solve the problem.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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