Updating Data in a SQL Database using Datagrid
hi there... hope all is well
i am going through the tutorial of asp.net quickstart tutorial and i was working on one of the examples of Server-Side Data Access - Updating data in sql Database using sql database with datagrid editCommandColumn.. i am using vb.net ... i have done everything as the examplie specifies but i am getting this error and i cant seem to understand how to solve it can someone please help .. i would really appreciate it...
This is the error i get in my aspx.vb file
Specified argument was out of the range of valid values. Parameter name: index
this is the html code which i have in my aspx file
Code:
<form id="Form1" method="post" runat="server">
<asp:DataGrid id="DataGrid1" runat="server" AutoGenerateColumns="False" BorderColor="#003366"
BorderWidth="6px" CellPadding="5" OnEditCommand="DataGrid1_EditCommand" OnCancelCommand="DataGrid1_CancelCommand"
OnUpdateCommand="DataGrid1_Updatecommand" DataKeyField="au_id" OnItemDataBound="DataGrid1_ItemDataBound"
EnableViewState="False">
<AlternatingItemStyle Font-Size="Smaller" Font-Names="Century" ForeColor="Black" BackColor="Cornsilk"></AlternatingItemStyle>
<ItemStyle Font-Size="Smaller" Font-Names="Century" ForeColor="Black" BackColor="Snow"></ItemStyle>
<HeaderStyle Font-Size="Smaller" Font-Names="Century" ForeColor="Black" BackColor="Snow"></HeaderStyle>
<FooterStyle ForeColor="Black" BackColor="Snow"></FooterStyle>
<Columns>
<asp:BoundColumn HeaderText="au_id" SortExpression="au_id" ReadOnly="True" DataField="au_id" ItemStyle-Wrap="false" />
<asp:BoundColumn HeaderText="au_lname" SortExpression="au_lname" DataField="au_lname" />
<asp:BoundColumn HeaderText="au_fname" SortExpression="au_fname" DataField="au_fname" />
<asp:BoundColumn HeaderText="phone" SortExpression="phone" DataField="phone" />
<asp:BoundColumn HeaderText="address" SortExpression="address" DataField="address" />
<asp:BoundColumn HeaderText="city" SortExpression="city" DataField="city" />
<asp:BoundColumn HeaderText="state" SortExpression="state" DataField="state" />
<asp:BoundColumn HeaderText="zip" SortExpression="zip" DataField="zip" />
<asp:BoundColumn HeaderText="contract" SortExpression="contract" DataField="contract" />
<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Update" CancelText="Cancel" EditText="Edit"></asp:EditCommandColumn>
</Columns>
</asp:DataGrid>
<br>
<asp:Label id="lblmessage" runat="server"></asp:Label>
</form>
this is the code which i have in the aspx.vb file
Code:
imports system.data
imports system.data.sqlClient
Dim myconn As SqlConnection
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
myconn = New SqlConnection(ConfigurationSettings.AppSettings("SONIA"))
datagridDB()
End Sub
Public Sub DataGrid1_CancelCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.CancelCommand
DataGrid1.EditItemIndex = -1
datagridDB()
End Sub
Sub datagridDB()
Dim adaptAuthors As New SqlDataAdapter("SELECT * FROM Authors", myconn)
Dim dsAuthors As New DataSet
adaptAuthors.Fill(dsAuthors, "Authors")
DataGrid1.DataSource = dsAuthors.Tables("Authors").DefaultView
DataGrid1.DataBind()
End Sub
Public Sub DataGrid1_EditCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.ItemCommand
DataGrid1.EditItemIndex = CInt(e.Item.ItemIndex)
datagridDB()
End Sub
Public Sub DataGrid1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGrid1.ItemDataBound
If (e.Item.ItemType = ListItemType.EditItem) Then
Dim i As Integer
For i = 0 To e.Item.Controls.Count - 1
Try
If (e.Item.Controls(i).Controls(0).GetType().ToString() = "System.Web.UI.WebControls.TextBox") Then
Dim tb As TextBox
tb = e.Item.Controls(i).Controls(0)
tb.Text = Server.HtmlDecode(tb.Text)
End If
Catch
End Try
Next
End If
End Sub
Public Sub DataGrid1_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.ItemCommand
Dim ds As New DataSet
Dim mycommand As SqlCommand
Dim UpdateCommand As String = "UPDATE Authors SET au_id = @id, au_lname = @lname,au_fname=@fname,phone=" _
& " @phone,address = @address, city = @city, state = @state, zip= @zip, contract = @contract WHERE au_id= @id "
mycommand = New SqlCommand(UpdateCommand, myconn)
mycommand.Parameters.Add(New SqlParameter("@id", SqlDbType.VarChar, 11))
mycommand.Parameters.Add(New SqlParameter("@lname", SqlDbType.NVarChar, 40))
mycommand.Parameters.Add(New SqlParameter("@fname", SqlDbType.NVarChar, 20))
mycommand.Parameters.Add(New SqlParameter("@phone", SqlDbType.NVarChar, 12))
mycommand.Parameters.Add(New SqlParameter("@address", SqlDbType.NVarChar, 40))
mycommand.Parameters.Add(New SqlParameter("@city", SqlDbType.NVarChar, 20))
mycommand.Parameters.Add(New SqlParameter("@state", SqlDbType.NVarChar, 2))
mycommand.Parameters.Add(New SqlParameter("@zip", SqlDbType.NVarChar, 5))
mycommand.Parameters.Add(New SqlParameter("@contract", SqlDbType.NVarChar, 1))
mycommand.Parameters("@id").Value = DataGrid1.DataKeys(CInt(e.Item.ItemIndex))
Dim cols As String() = {"@id", "@lname", "@fname", "@phone", "@address", "@city", "@state", "@zip", "@contract"}
Dim numcols As Integer = e.Item.Cells.Count
Dim I As Integer
For I = 2 To numcols - 2 'skip the first and the second last columns
Dim currenttextbox As TextBox
currenttextbox = e.Item.Cells(I).Controls(0) 'i get this error here: Specified argument was out of the range of valid values. Parameter name: index Dim colValue As String = currenttextbox.Text
'check for null values in the required fields
If I < 6 And colValue = "" Then
lblmessage.Text = "ERROR:Null values are not allowed for AuthorId,Phone,Name"
lblmessage.Style("color") = "red"
Return
End If
mycommand.Parameters(cols(I - 1)).Value = Server.HtmlEncode(colValue)
Next
'append the last row by changing to true/false values to 1 or 0
Dim ContractTextbox As TextBox
'i get this error here also: Specified argument was out of the range of valid values. Parameter name: index
ContractTextbox = e.Item.Cells(numcols - 1).Controls(0)
If ContractTextbox.Text = "true" Then
mycommand.Parameters("@contract").Value = "1"
Else
mycommand.Parameters("@contract").Value = "0"
End If
myconn.Open()
Try
mycommand.ExecuteNonQuery()
lblmessage.Text = "<b>Record Updated</b><br>" + UpdateCommand.ToString()
lblmessage.Style("color") = "red"
Catch ex As SqlException
If ex.Number = 2627 Then
lblmessage.Text = "ERROR: A record already exists with this primary key"
Else
lblmessage.Text = "ERROR: Could not update the field make sure that the fields are all filled"
End If
lblmessage.Style("color") = "red"
End Try
myconn.Close()
datagridDB()
End Sub
would appreciate if someone could help thank in advance