David Megnin
asked on
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.)
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>
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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.)
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?
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?
ASKER
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.
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.
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.
ASKER
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.
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.
ASKER
Much thanks for your help with this. That did solve the problem.
ASKER
Open in new window
to: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.
Open in new window