Problem with datagrid update

R

riaz.hasani

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
 
E

Elton Wang

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
(e-mail address removed)



-----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

.
 
R

riaz.hasani

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
 
E

Elton Wang

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

Thanks,

Elton Wang
 
R

riaz.hasani

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>
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top