Problem with datagrid update

Discussion in 'ASP .Net Datagrid Control' started by riaz.hasani@gmail.com, Feb 8, 2005.

  1. Guest

    Hi every1, I'm having problem with datagrid updates.

    When I click on update I receive an error. Which I'm unable to trace.

    I'm posting the code & error.
    Any help would be appreciated.

    Code
    *******************************************************************

    Sub dgdSearch_Update(ByVal sender As Object, ByVal e As
    DataGridCommandEventArgs)
    'Read in the values of the updated row
    Dim DemoNumber As Integer =
    dgdSearch.DataKeys(e.Item.ItemIndex)
    'Dim strCustomerName As String =
    CType(e.Item.Cells(1).Controls(0), TextBox).Text
    'Dim strCustomerNumber As String =
    CType(e.Item.Cells(2).Controls(0), TextBox).Text
    Dim strSerialNumber As String =
    CType(e.Item.Cells(3).Controls(0), TextBox).Text
    'Dim strModelNumber As String =
    CType(e.Item.Cells(4).Controls(0), TextBox).Text
    'Dim strRMANumber As String =
    CType(e.Item.Cells(5).Controls(0), TextBox).Text
    'Dim strConditionNumber As String =
    CType(e.Item.Cells(6).Controls(0), TextBox).Text
    'Dim strLocationNumber As String =
    CType(e.Item.Cells(7).Controls(0), TextBox).Text
    Dim strStatus As String = CType(e.Item.Cells(8).Controls(0),
    TextBox).Text
    'Dim strRevisedDate As Date =
    CType(e.Item.Cells(9).Controls(0), TextBox).Text
    Dim strNote As String
    If TypeOf (e.Item.Cells(10).Controls(0)) Is TextBox Then
    strNote = CType(e.Item.Cells(10).Controls(0), TextBox).Text
    End If


    'Construct the SQL statement using Parameters
    Dim strSQL As String = "UPDATE [Equip] SET [SerialNumber] =
    @SerialNumber, " & _
    "[Status] = @Status, [Note] = @Note " &
    _
    "WHERE [DemoNumber] = @DemoNumber"

    ' Create Instance of Connection and Command Object
    '1. Create a connection
    Const strConnString As String = ("server=(local);Integrated
    Security=SSPI;database=Equipment Log")

    Dim objConn As New SqlConnection(strConnString)
    objConn.Open()

    Dim myCommand As SqlCommand = New SqlCommand(strSQL, objConn)
    myCommand.CommandType = CommandType.Text

    ' Add Parameters to the SQL query
    Dim parameterDemoNumber As SqlParameter = New
    SqlParameter("@DemoNumber", SqlDbType.Int, 4)
    parameterDemoNumber.Value = DemoNumber
    myCommand.Parameters.Add(parameterDemoNumber)

    Dim parameterSerialNumber As SqlParameter = New
    SqlParameter("@SerialNumber", SqlDbType.VarChar, 10)
    parameterSerialNumber.Value = strSerialNumber
    myCommand.Parameters.Add(parameterSerialNumber)

    Dim parameterStatus As SqlParameter = New
    SqlParameter("@Status", SqlDbType.VarChar, 10)
    parameterStatus.Value = strStatus
    myCommand.Parameters.Add(parameterStatus)

    Dim parameterNote As SqlParameter = New SqlParameter("@Note",
    SqlDbType.VarChar, 500)
    parameterNote.IsNullable = True
    parameterNote.Value = strNote
    myCommand.Parameters.Add(parameterNote)

    'Execute the UPDATE query
    myCommand.ExecuteNonQuery()

    objConn.Close()

    'Finally, set the EditItemIndex to -1 and rebind the DataGrid
    dgdSearch.EditItemIndex = -1
    BindData()

    'refresh the datagrid
    dgdSearch.DataBind()
    End Sub

    *******************************************************************

    Error

    Prepared statement '(@SerialNumber varchar(10),@Status
    varchar(10),@Note varchar(500' expects parameter @Note, which was not
    supplied.
    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: Prepared
    statement '(@SerialNumber varchar(10),@Status varchar(10),@Note
    varchar(500' expects parameter @Note, which was not supplied.

    Source Error:


    Line 400: myCommand.Parameters.Add(parameterDemoNumber)
    Line 401:
    Line 402: myCommand.ExecuteNonQuery() 'Execute the UPDATE
    query
    Line 403:
    Line 404: objConn.Close()


    Source File: c:\inetpub\wwwroot\Demo Equip Log\Search Equipment.aspx.vb
    Line: 402

    Stack Trace:


    [SqlException: Prepared statement '(@SerialNumber varchar(10),@Status
    varchar(10),@Note varchar(500' expects parameter @Note, which was not
    supplied.]
    System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
    cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +742
    System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    Demo_Equip_Log.Search_Equipment.dgdSearch_Update(Object sender,
    DataGridCommandEventArgs e) in c:\inetpub\wwwroot\Demo Equip Log\Search
    Equipment.aspx.vb:402

    System.Web.UI.WebControls.DataGrid.OnUpdateCommand(DataGridCommandEventArgs
    e) +109
    System.Web.UI.WebControls.DataGrid.OnBubbleEvent(Object source,
    EventArgs e) +507
    System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs
    args) +26
    System.Web.UI.WebControls.DataGridItem.OnBubbleEvent(Object source,
    EventArgs e) +106
    System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs
    args) +26
    System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e)
    +120

    System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
    eventArgument) +115
    System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
    sourceControl, String eventArgument) +18
    System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
    +138
    System.Web.UI.Page.ProcessRequestMain() +1292




    --------------------------------------------------------------------------------
    Version Information: Microsoft .NET Framework Version:1.1.4322.2032;
    ASP.NET Version:1.1.4322.2032
    , Feb 8, 2005
    #1
    1. Advertising

  2. Elton Wang Guest

    Hi riaz,

    It seems something wrong with the strNote.

    In your code:

    Dim strNote As String
    If TypeOf (e.Item.Cells(10).Controls(0)) Is TextBox Then
    strNote = CType(e.Item.Cells(10).Controls(0),
    TextBox).Text
    End If
    ' Else strNote = Nothing

    So it may cause the trouble.

    HTH

    Elton Wang





    >-----Original Message-----
    >Hi every1, I'm having problem with datagrid updates.
    >
    >When I click on update I receive an error. Which I'm

    unable to trace.
    >
    >I'm posting the code & error.
    >Any help would be appreciated.
    >
    >Code
    >**********************************************************

    *********
    >
    > Sub dgdSearch_Update(ByVal sender As Object, ByVal e

    As
    >DataGridCommandEventArgs)
    > 'Read in the values of the updated row
    > Dim DemoNumber As Integer =
    >dgdSearch.DataKeys(e.Item.ItemIndex)
    > 'Dim strCustomerName As String =
    >CType(e.Item.Cells(1).Controls(0), TextBox).Text
    > 'Dim strCustomerNumber As String =
    >CType(e.Item.Cells(2).Controls(0), TextBox).Text
    > Dim strSerialNumber As String =
    >CType(e.Item.Cells(3).Controls(0), TextBox).Text
    > 'Dim strModelNumber As String =
    >CType(e.Item.Cells(4).Controls(0), TextBox).Text
    > 'Dim strRMANumber As String =
    >CType(e.Item.Cells(5).Controls(0), TextBox).Text
    > 'Dim strConditionNumber As String =
    >CType(e.Item.Cells(6).Controls(0), TextBox).Text
    > 'Dim strLocationNumber As String =
    >CType(e.Item.Cells(7).Controls(0), TextBox).Text
    > Dim strStatus As String = CType(e.Item.Cells

    (8).Controls(0),
    >TextBox).Text
    > 'Dim strRevisedDate As Date =
    >CType(e.Item.Cells(9).Controls(0), TextBox).Text
    > Dim strNote As String
    > If TypeOf (e.Item.Cells(10).Controls(0)) Is

    TextBox Then
    > strNote = CType(e.Item.Cells(10).Controls(0),

    TextBox).Text
    > End If
    >
    >
    > 'Construct the SQL statement using Parameters
    > Dim strSQL As String = "UPDATE [Equip] SET

    [SerialNumber] =
    >@SerialNumber, " & _
    > "[Status] = @Status,

    [Note] = @Note " &
    >_
    > "WHERE [DemoNumber] =

    @DemoNumber"
    >
    > ' Create Instance of Connection and Command Object
    > '1. Create a connection
    > Const strConnString As String = ("server=

    (local);Integrated
    >Security=SSPI;database=Equipment Log")
    >
    > Dim objConn As New SqlConnection(strConnString)
    > objConn.Open()
    >
    > Dim myCommand As SqlCommand = New SqlCommand

    (strSQL, objConn)
    > myCommand.CommandType = CommandType.Text
    >
    > ' Add Parameters to the SQL query
    > Dim parameterDemoNumber As SqlParameter = New
    >SqlParameter("@DemoNumber", SqlDbType.Int, 4)
    > parameterDemoNumber.Value = DemoNumber
    > myCommand.Parameters.Add(parameterDemoNumber)
    >
    > Dim parameterSerialNumber As SqlParameter = New
    >SqlParameter("@SerialNumber", SqlDbType.VarChar, 10)
    > parameterSerialNumber.Value = strSerialNumber
    > myCommand.Parameters.Add(parameterSerialNumber)
    >
    > Dim parameterStatus As SqlParameter = New
    >SqlParameter("@Status", SqlDbType.VarChar, 10)
    > parameterStatus.Value = strStatus
    > myCommand.Parameters.Add(parameterStatus)
    >
    > Dim parameterNote As SqlParameter = New

    SqlParameter("@Note",
    >SqlDbType.VarChar, 500)
    > parameterNote.IsNullable = True
    > parameterNote.Value = strNote
    > myCommand.Parameters.Add(parameterNote)
    >
    > 'Execute the UPDATE query
    > myCommand.ExecuteNonQuery()
    >
    > objConn.Close()
    >
    > 'Finally, set the EditItemIndex to -1 and rebind

    the DataGrid
    > dgdSearch.EditItemIndex = -1
    > BindData()
    >
    > 'refresh the datagrid
    > dgdSearch.DataBind()
    > End Sub
    >
    >**********************************************************

    *********
    >
    >Error
    >
    >Prepared statement '(@SerialNumber varchar(10),@Status
    >varchar(10),@Note varchar(500' expects parameter @Note,

    which was not
    >supplied.
    >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:

    Prepared
    >statement '(@SerialNumber varchar(10),@Status varchar

    (10),@Note
    >varchar(500' expects parameter @Note, which was not

    supplied.
    >
    >Source Error:
    >
    >
    >Line 400: myCommand.Parameters.Add

    (parameterDemoNumber)
    >Line 401:
    >Line 402: myCommand.ExecuteNonQuery() 'Execute

    the UPDATE
    >query
    >Line 403:
    >Line 404: objConn.Close()
    >
    >
    >Source File: c:\inetpub\wwwroot\Demo Equip Log\Search

    Equipment.aspx.vb
    > Line: 402
    >
    >Stack Trace:
    >
    >
    >[SqlException: Prepared statement '(@SerialNumber varchar

    (10),@Status
    >varchar(10),@Note varchar(500' expects parameter @Note,

    which was not
    >supplied.]
    > System.Data.SqlClient.SqlCommand.ExecuteReader

    (CommandBehavior
    >cmdBehavior, RunBehavior runBehavior, Boolean

    returnStream) +742
    > System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    > Demo_Equip_Log.Search_Equipment.dgdSearch_Update

    (Object sender,
    >DataGridCommandEventArgs e) in c:\inetpub\wwwroot\Demo

    Equip Log\Search
    >Equipment.aspx.vb:402
    >
    >System.Web.UI.WebControls.DataGrid.OnUpdateCommand

    (DataGridCommandEventArgs
    >e) +109
    > System.Web.UI.WebControls.DataGrid.OnBubbleEvent

    (Object source,
    >EventArgs e) +507
    > System.Web.UI.Control.RaiseBubbleEvent(Object source,

    EventArgs
    >args) +26
    > System.Web.UI.WebControls.DataGridItem.OnBubbleEvent

    (Object source,
    >EventArgs e) +106
    > System.Web.UI.Control.RaiseBubbleEvent(Object source,

    EventArgs
    >args) +26
    > System.Web.UI.WebControls.LinkButton.OnCommand

    (CommandEventArgs e)
    >+120
    >
    >System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBa

    ckEventHandler.RaisePostBackEvent(String
    >eventArgument) +115
    > System.Web.UI.Page.RaisePostBackEvent

    (IPostBackEventHandler
    >sourceControl, String eventArgument) +18
    > System.Web.UI.Page.RaisePostBackEvent

    (NameValueCollection postData)
    >+138
    > System.Web.UI.Page.ProcessRequestMain() +1292
    >
    >
    >
    >
    >----------------------------------------------------------

    ----------------------
    >Version Information: Microsoft .NET Framework

    Version:1.1.4322.2032;
    >ASP.NET Version:1.1.4322.2032
    >
    >.
    >
    Elton Wang, Feb 8, 2005
    #2
    1. Advertising

  3. Guest

    Hi Elton, Thanks for your response.

    I don't know if there is any other way to do this.

    When changed the declaration I'm getting a cast error.
    Any idea about that.

    Thanks

    Specified cast is not valid.
    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.InvalidCastException: Specified cast is not
    valid.

    Source Error:


    Line 354: Dim strStatus As String =
    CType(e.Item.Cells(8).Controls(0), TextBox).Text
    Line 355: 'Dim strRevisedDate As Date =
    CType(e.Item.Cells(9).Controls(0), TextBox).Text
    Line 356: Dim strNote As String =
    CType(e.Item.Cells(10).Controls(0), TextBox).Text
    Line 357:
    Line 358: 'Dim strNote, DemoNumber As String


    Source File: c:\inetpub\wwwroot\Demo Equip Log\Search Equipment.aspx.vb
    Line: 356

    Stack Trace:


    [InvalidCastException: Specified cast is not valid.]
    Demo_Equip_Log.Search_Equipment.dgdSearch_Update(Object sender,
    DataGridCommandEventArgs e) in c:\inetpub\wwwroot\Demo Equip Log\Search
    Equipment.aspx.vb:356

    System.Web.UI.WebControls.DataGrid.OnUpdateCommand(DataGridCommandEventArgs
    e) +109
    System.Web.UI.WebControls.DataGrid.OnBubbleEvent(Object source,
    EventArgs e) +507
    System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs
    args) +26
    System.Web.UI.WebControls.DataGridItem.OnBubbleEvent(Object source,
    EventArgs e) +106
    System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs
    args) +26
    System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e)
    +120

    System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
    eventArgument) +115
    System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
    sourceControl, String eventArgument) +18
    System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
    +138
    System.Web.UI.Page.ProcessRequestMain() +1292




    --------------------------------------------------------------------------------
    Version Information: Microsoft .NET Framework Version:1.1.4322.2032;
    ASP.NET Version:1.1.4322.2032
    , Feb 8, 2005
    #3
  4. Elton Wang Guest

    Could you give the datagrid code, I mean from HTML view in
    the .aspx.

    Thanks,

    Elton Wang

    >-----Original Message-----
    >Hi Elton, Thanks for your response.
    >
    >I don't know if there is any other way to do this.
    >
    >When changed the declaration I'm getting a cast error.
    >Any idea about that.
    >
    >Thanks
    >
    >Specified cast is not valid.
    >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.InvalidCastException: Specified

    cast is not
    >valid.
    >
    >Source Error:
    >
    >
    >Line 354: Dim strStatus As String =
    >CType(e.Item.Cells(8).Controls(0), TextBox).Text
    >Line 355: 'Dim strRevisedDate As Date =
    >CType(e.Item.Cells(9).Controls(0), TextBox).Text
    >Line 356: Dim strNote As String =
    >CType(e.Item.Cells(10).Controls(0), TextBox).Text
    >Line 357:
    >Line 358: 'Dim strNote, DemoNumber As String
    >
    >
    >Source File: c:\inetpub\wwwroot\Demo Equip Log\Search

    Equipment.aspx.vb
    > Line: 356
    >
    >Stack Trace:
    >
    >
    >[InvalidCastException: Specified cast is not valid.]
    > Demo_Equip_Log.Search_Equipment.dgdSearch_Update

    (Object sender,
    >DataGridCommandEventArgs e) in c:\inetpub\wwwroot\Demo

    Equip Log\Search
    >Equipment.aspx.vb:356
    >
    >System.Web.UI.WebControls.DataGrid.OnUpdateCommand

    (DataGridCommandEventArgs
    >e) +109
    > System.Web.UI.WebControls.DataGrid.OnBubbleEvent

    (Object source,
    >EventArgs e) +507
    > System.Web.UI.Control.RaiseBubbleEvent(Object source,

    EventArgs
    >args) +26
    > System.Web.UI.WebControls.DataGridItem.OnBubbleEvent

    (Object source,
    >EventArgs e) +106
    > System.Web.UI.Control.RaiseBubbleEvent(Object source,

    EventArgs
    >args) +26
    > System.Web.UI.WebControls.LinkButton.OnCommand

    (CommandEventArgs e)
    >+120
    >
    >System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBa

    ckEventHandler.RaisePostBackEvent(String
    >eventArgument) +115
    > System.Web.UI.Page.RaisePostBackEvent

    (IPostBackEventHandler
    >sourceControl, String eventArgument) +18
    > System.Web.UI.Page.RaisePostBackEvent

    (NameValueCollection postData)
    >+138
    > System.Web.UI.Page.ProcessRequestMain() +1292
    >
    >
    >
    >
    >----------------------------------------------------------

    ----------------------
    >Version Information: Microsoft .NET Framework

    Version:1.1.4322.2032;
    >ASP.NET Version:1.1.4322.2032
    >
    >.
    >
    Elton Wang, Feb 9, 2005
    #4
  5. Guest

    Sure Elton here it is hope you will be able to figure it out.
    Thanks for your help.


    <asp:datagrid id="dgdSearch" style="Z-INDEX: 103; LEFT: 48px; POSITION:
    absolute; TOP: 504px" runat="server" Width="272px" BackColor="White"
    Height="192px" CellSpacing="1" AllowSorting="True"
    DataMember="Equip" DataKeyField="DemoNumber"
    AutoGenerateColumns="False" AllowCustomPaging="True" AllowPaging="True"
    GridLines="Horizontal" CellPadding="3" BorderWidth="1px"
    BorderStyle="None" BorderColor="#E7E7FF" ShowFooter="True"
    OnCancelCommand="dgdSearch_Cancel" OnEditCommand="dgdSearch_Edit"
    OnUpdateCommand="dgdSearch_Update">
    <FooterStyle ForeColor="#4A3C8C" BackColor="#B5C7DE"></FooterStyle>
    <SelectedItemStyle Font-Bold="True" ForeColor="#F7F7F7"
    BackColor="#738A9C"></SelectedItemStyle>
    <AlternatingItemStyle BackColor="#F7F7F7"></AlternatingItemStyle>
    <ItemStyle ForeColor="#4A3C8C" BackColor="#E7E7FF"></ItemStyle>
    <HeaderStyle Font-Bold="True" ForeColor="#F7F7F7"
    BackColor="#4A3C8C"></HeaderStyle>
    <Columns>
    <asp:BoundColumn DataField="DemoNumber" ReadOnly="True"
    HeaderText="Demo Number"></asp:BoundColumn>
    <asp:BoundColumn DataField="CustomerName" ReadOnly="True"
    HeaderText="Customer Name"></asp:BoundColumn>
    <asp:BoundColumn DataField="CustomerNumber" ReadOnly="True"
    HeaderText="Customer Number"></asp:BoundColumn>
    <asp:BoundColumn DataField="SerialNumber" HeaderText="Serial
    Number"></asp:BoundColumn>
    <asp:BoundColumn DataField="ModelNumber" ReadOnly="True"
    HeaderText="Model Number"></asp:BoundColumn>
    <asp:BoundColumn DataField="RMANumber" ReadOnly="True" HeaderText="RMA
    Number"></asp:BoundColumn>
    <asp:BoundColumn DataField="ConditionNumber" ReadOnly="True"
    HeaderText="Condition Number"></asp:BoundColumn>
    <asp:BoundColumn DataField="LocationNumber" ReadOnly="True"
    HeaderText="Location Number"></asp:BoundColumn>
    <asp:BoundColumn DataField="Status"
    HeaderText="Status"></asp:BoundColumn>
    <asp:BoundColumn DataField="RevisedDate" ReadOnly="True"
    HeaderText="Revised Date"></asp:BoundColumn>
    <asp:TemplateColumn HeaderText="Notes">
    <EditItemTemplate>
    <asp:TextBox id="txtNote" TextMode="MultiLine" runat="server" Text='<%#
    DataBinder.Eval(Container, "DataItem.Note") %>'> </asp:TextBox>
    </EditItemTemplate>
    </asp:TemplateColumn>
    <asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Update"
    CancelText="Cancel" EditText="Edit"></asp:EditCommandColumn>
    </Columns>
    <PagerStyle HorizontalAlign="Right" ForeColor="#4A3C8C"
    Position="TopAndBottom" BackColor="#E7E7FF"
    Mode="NumericPages"></PagerStyle>
    </asp:datagrid>
    , Feb 9, 2005
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. matthew schouppe
    Replies:
    3
    Views:
    2,343
    Scott Allen
    Apr 30, 2004
  2. =?Utf-8?B?TWljaGFlbA==?=

    How to update datagrid after update

    =?Utf-8?B?TWljaGFlbA==?=, Mar 17, 2006, in forum: ASP .Net
    Replies:
    1
    Views:
    495
    Alex D.
    Mar 17, 2006
  3. Andreas Klemt
    Replies:
    0
    Views:
    266
    Andreas Klemt
    Oct 8, 2003
  4. Kostia
    Replies:
    1
    Views:
    254
    Scott Mitchell [MVP]
    Feb 26, 2004
  5. Stephan Kämper
    Replies:
    1
    Views:
    132
    Assaph Mehr
    Jul 15, 2006
Loading...

Share This Page