ASP.NET

Updating SQl Bit from checkbox using sqldatasource RSS

4 replies

Last post Nov 12, 2012 02:10 AM by TgaLamx

‹ Previous Thread|Next Thread ›

TgaLamx

Member

25 Points

61 Posts

Updating SQl Bit from checkbox using sqldatasource

Nov 07, 2012 11:36 PM|LINK

I am trying to use the edit in a grid view to update a sql table but the checkboxes are throwing an exception ,

Please if anyone can point me right,

Thanks

Glen

   Sub bindedge()
        edthds.SelectCommand = "SELECT * FROM [Edgedetail] WHERE (Proid = " & DropDownList1.SelectedValue & ")"
        edgethi.DataBind()
    End Sub


<asp:Panel runat="server">
              <asp:Label runat="server">Edging Available</asp:Label><br />
                <asp:Label ID="Label7" runat="server" Text="Product: "></asp:Label>
        <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" 
            DataSourceID="prods"  DataTextField="Product" DataValueField="ID" OnSelectedIndexChanged="bindedge" >
           
        </asp:DropDownList>
        <asp:SqlDataSource ID="prods" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
            SelectCommand="SELECT * FROM [products] WHERE ([Supplier] = @Supplier)">
            <SelectParameters>
                <asp:SessionParameter Name="Supplier" SessionField="custid" Type="Int32" />
            </SelectParameters>
        </asp:SqlDataSource><br />
                  <asp:GridView ID="edgethi" runat="server" AutoGenerateColumns="False" 
                      DataSourceID="edthds" >
                      <Columns>
                          <asp:CommandField ShowEditButton="True" />
                          <asp:BoundField DataField="Edge" HeaderText="Edge" SortExpression="Edge" />
                          <asp:CheckBoxField DataField="30 mm" HeaderText="30 mm" 
                              SortExpression="[30 mm]" />
                          <asp:CheckBoxField DataField="37 mm" HeaderText="37 mm" 
                              SortExpression="37 mm" />
                          <asp:CheckBoxField DataField="50 mm" HeaderText="50 mm" 
                              SortExpression="[50 mm]" />
                          <asp:CheckBoxField DataField="60 mm" HeaderText="60 mm" 
                              SortExpression="[60 mm]" />
                      </Columns>
                  </asp:GridView>
              
                  <asp:SqlDataSource ID="edthds" runat="server" 
                      ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
                      DeleteCommand="DELETE FROM [Edgedetail] WHERE [ID] = @ID" 
                      InsertCommand="INSERT INTO [Edgedetail] ([Edge],  [30 mm], [37 mm], [50 mm], [60 mm], [ProID]) VALUES (@Edge, @Price, @column1, @column2, @column3, @column4, @ProID)" 
                      SelectCommand="SELECT * FROM [Edgedetail]" 
                      UpdateCommand="UPDATE [Edgedetail] SET [Edge] = @Edge,  [30 mm] = @column1, [37 mm] = @column2, [50 mm] = @column3, [60 mm] = @column4 WHERE [ID] = @ID">
                      <DeleteParameters>
                          <asp:Parameter Name="ID" Type="Int32" />
                      </DeleteParameters>
                      <UpdateParameters>
                          <asp:Parameter Name="Edge" Type="String" />
                          <asp:Parameter Name="Price" Type="Decimal" />
                          <asp:Parameter Name="column1" Type="Boolean" />
                          <asp:Parameter Name="column2" Type="Boolean" />
                          <asp:Parameter Name="column3" Type="Boolean" />
                          <asp:Parameter Name="column4" Type="Boolean" />
                          <asp:Parameter Name="ProID" Type="Int32" />
                          <asp:Parameter Name="ID" Type="Int32" />
                      </UpdateParameters>
                      <InsertParameters>
                          <asp:Parameter Name="Edge" Type="String" />
                          <asp:Parameter Name="Price" Type="Decimal" />
                          <asp:Parameter Name="column1" Type="Boolean" />
                          <asp:Parameter Name="column2" Type="Boolean" />
                          <asp:Parameter Name="column3" Type="Boolean" />
                          <asp:Parameter Name="column4" Type="Boolean" />
                          <asp:Parameter Name="ProID" Type="Int32" />
                      </InsertParameters>
                  </asp:SqlDataSource>
              
              </asp:Panel>

- Reply
Decker Dong ...

All-Star

118619 Points

18779 Posts

Re: Updating SQl Bit from checkbox using sqldatasource

Nov 09, 2012 12:23 AM|LINK

Hello,

Your codes look nice……So what's the matter with you? And what's in fact about your problem? What excpetion have you got now? Can you elebrate it more?

Reguards!
- Reply
TgaLamx

Member

25 Points

61 Posts

Re: Updating SQl Bit from checkbox using sqldatasource

Nov 09, 2012 12:40 AM|LINK

This is the exception I get when it trys to convert the checkboxs to bit fields

in a sql datadase

Thanks

Server Error in '/9-11-2012 edging' Application.

--------------------------------------------------------------------------------

Incorrect syntax near 'bit'. 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.Data.SqlClient.SqlException: Incorrect syntax near 'bit'. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [SqlException (0x80131904): Incorrect syntax near 'bit'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +204 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954 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) +175 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +386 System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +325 System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +92 System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +907 System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +704 System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +95 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37 System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +123 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37 System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +118 System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +135 System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +175 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565 -------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:2.0.50727.3634; ASP.NET Version:2.0.50727.3634
- Reply
Decker Dong ...

All-Star

118619 Points

18779 Posts

Re: Updating SQl Bit from checkbox using sqldatasource

Nov 09, 2012 01:29 AM|LINK

TgaLamx
Incorrect syntax near 'bit'. Description:

Hi,

As far as I see, you can just check your update statement in the SQL Management Studio to see whether there's anything mistake by syntax.

Reguards!

TgaLamx

Member

25 Points

61 Posts

Re: Updating SQl Bit from checkbox using sqldatasource

Nov 12, 2012 02:10 AM|LINK

Problem fixed here is the answer:

<asp:Panel runat="server">
              <asp:Label runat="server">Edging Available</asp:Label><br />
                <asp:Label ID="Label7" runat="server" Text="Product: "></asp:Label>
        <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True"
            DataSourceID="prods"  DataTextField="Product" DataValueField="ID" OnSelectedIndexChanged="bindedge" >
           
        </asp:DropDownList>
        <asp:SqlDataSource ID="prods" runat="server"
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
            SelectCommand="SELECT * FROM [products] WHERE ([Supplier] = @Supplier)">
            <SelectParameters>
                <asp:SessionParameter Name="Supplier" SessionField="custid" Type="Int32" />
            </SelectParameters>
        </asp:SqlDataSource><br />
                  <asp:GridView ID="edgethi" runat="server" OnRowUpdating="GridView1_RowUpdating" AutoGenerateColumns="False"      DataSourceID="edthds"             >
                      <Columns>
                          <asp:CommandField ShowEditButton="True" />
                          <asp:TemplateField HeaderText="Edge" SortExpression="Edge">
                              <EditItemTemplate>
                                  <asp:TextBox ID="txtedge" ReadOnly="true" runat="server" Text='<%# Bind("Edge") %>'></asp:TextBox>
                              </EditItemTemplate>
                              <ItemTemplate>
                                  <asp:Label ID="Label1" runat="server" Text='<%# Bind("Edge") %>'></asp:Label>
                              </ItemTemplate>
                          </asp:TemplateField>
                          <asp:TemplateField HeaderText="30 mm" SortExpression="[30 mm]">
                              <EditItemTemplate>
                                  <asp:CheckBox ID="chk30" runat="server" Checked='<%# Bind("[30 mm]") %>' />
                              </EditItemTemplate>
                              <ItemTemplate>
                                  <asp:CheckBox ID="chk30" runat="server" Checked='<%# Bind("[30 mm]") %>'
                                      Enabled="False" />
                              </ItemTemplate>
                          </asp:TemplateField>
                          <asp:TemplateField HeaderText="37 mm" SortExpression="37 mm">
                              <EditItemTemplate>
                                  <asp:CheckBox ID="chk37" runat="server" Checked='<%# Bind("[37 mm]") %>' />
                              </EditItemTemplate>
                              <ItemTemplate>
                                  <asp:CheckBox ID="chk37" runat="server" Checked='<%# Bind("[37 mm]") %>'
                                      Enabled="False" />
                              </ItemTemplate>
                          </asp:TemplateField>
                          <asp:TemplateField HeaderText="50 mm" SortExpression="[50 mm]">
                              <EditItemTemplate>
                                  <asp:CheckBox ID="chk50" runat="server" Checked='<%# Bind("[50 mm]") %>' />
                              </EditItemTemplate>
                              <ItemTemplate>
                                  <asp:CheckBox ID="chk50" runat="server" Checked='<%# Bind("[50 mm]") %>'
                                      Enabled="False" />
                              </ItemTemplate>
                          </asp:TemplateField>
                          <asp:TemplateField HeaderText="60 mm" SortExpression="[60 mm]">
                              <EditItemTemplate>
                                  <asp:CheckBox ID="chk60" runat="server" Checked='<%# Bind("[60 mm]") %>' />
                              </EditItemTemplate>
                              <ItemTemplate>
                                  <asp:CheckBox ID="chk60" runat="server" Checked='<%# Bind("[60 mm]") %>'
                                      Enabled="False" />
                              </ItemTemplate>
                          </asp:TemplateField>
                      </Columns>
                  </asp:GridView>
                  <asp:SqlDataSource ID="edthds" runat="server"
                      ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
                      DeleteCommand="DELETE FROM [Edgedetail] WHERE [ID] = @ID"
                      InsertCommand="INSERT INTO [Edgedetail] ([Edge],  [30 mm], [37 mm], [50 mm], [60 mm], [ProID]) VALUES (@Edge, @Price, @column1, @column2, @column3, @column4, @ProID)"
                      SelectCommand="SELECT * FROM [Edgedetail]"
                      UpdateCommand=";">
                      <DeleteParameters>
                          <asp:Parameter Name="ID" Type="Int32" />
                      </DeleteParameters>
                      <UpdateParameters>
                          <asp:Parameter Name="Edge" Type="String" />
                          <asp:Parameter Name="Price" Type="Decimal" />
                          <asp:Parameter Name="column1" Type="Boolean" />
                          <asp:Parameter Name="column2" Type="Boolean" />
                          <asp:Parameter Name="column3" Type="Boolean" />
                          <asp:Parameter Name="column4" Type="Boolean" />
                          <asp:Parameter Name="ProID" Type="Int32" />
                          <asp:Parameter Name="ID" Type="Int32" />
                      </UpdateParameters>
                      <InsertParameters>
                          <asp:Parameter Name="Edge" Type="String" />
                          <asp:Parameter Name="Price" Type="Decimal" />
                          <asp:Parameter Name="column1" Type="Boolean" />
                          <asp:Parameter Name="column2" Type="Boolean" />
                          <asp:Parameter Name="column3" Type="Boolean" />
                          <asp:Parameter Name="column4" Type="Boolean" />
                          <asp:Parameter Name="ProID" Type="Int32" />
                      </InsertParameters>
                  </asp:SqlDataSource>
              <br />
             
              </asp:Panel>


    Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
    
        
    

        Dim cb30 As CheckBox = edgethi.Rows(e.RowIndex).FindControl("chk30")
        Dim cb37 As CheckBox = edgethi.Rows(e.RowIndex).FindControl("chk37")
        Dim cb50 As CheckBox = edgethi.Rows(e.RowIndex).FindControl("chk50")
        Dim cb60 As CheckBox = edgethi.Rows(e.RowIndex).FindControl("chk60")
        Dim txtedge As TextBox = edgethi.Rows(e.RowIndex).FindControl("txtedge")
        Dim i30, i37, i50, i60 As Integer
        If (cb30.Checked) Then
            i30 = 1
        Else
            i30 = 0
            
        End If
        If (cb37.Checked) Then
            i37 = 1
        Else
            i37 = 0
            
        End If
        If (cb50.Checked) Then
            i50 = 1
        Else
            i50 = 0
            
        End If
        If (cb60.Checked) Then
            i60 = 1
        Else
            i60 = 0
            
        End If
        edthds.UpdateCommand = "UPDATE [Edgedetail] SET   [30 mm] = " & i30 & " , [37 mm] = " & i37 & ", [50 mm] =" & i50 & ", [60 mm] = " & i60 & " WHERE [edge] = '" & txtedge.Text & "' AND Proid = " & DropDownList1.SelectedValue
        edthds.Update()
        edgethi.EditIndex = -1
        e.Cancel = True
        edthds.UpdateCommand = ";"
        
    End Sub

Edited by TgaLamx on Nov 12, 2012 02:10 AM
Edited by decker dong - msft on Nov 14, 2012 12:45 AM
Marked as answer by decker dong - msft on Nov 14, 2012 12:45 AM

‹ Previous Thread|Next Thread ›

Updating SQl Bit from checkbox using sqldatasource RSS

4 replies

TgaLamx

Updating SQl Bit from checkbox using sqldatasource

Decker Dong ...

Re: Updating SQl Bit from checkbox using sqldatasource

TgaLamx

Re: Updating SQl Bit from checkbox using sqldatasource

Decker Dong ...

Re: Updating SQl Bit from checkbox using sqldatasource

TgaLamx

TgaLamx

Re: Updating SQl Bit from checkbox using sqldatasource

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft