Good Afternoon Everyone! :)

I am working on the update command for my DataGrid and i am following a examples from two sources one is the Beginning Dynamic Websites book by Wrox and one is a tutorial from 4guysfromrolla.

Something is not write in code as it is causing an error could you please give me some advice.. they both different methods it appears. This is my code:

Sub dgSubCategories_UpdateCommand(sender As Object, e As DataGridCommandEventArgs)
        Dim connectionString As String = ConfigurationSettings.AppSettings("ConnectionStrin g")
        Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString )
        Dim SubCategoryName as String = CType(e.Item.Cells(2).Controls(0), TextBox).Text
        Dim CategoryName as String = CType(e.Item.Cells(3).Controls(0), TextBox).Text
        Dim CategoryID as Integer = CType(e.Item.Cells(4).Controls(0), TextBox).Text


        Dim queryString As String = "UPDATE [tblSubCategory] SET [CategoryID]=@CategoryID, [SubCategoryName]=@SubCateg"& _
"oryName, [CategoryName]=@CategoryName"
        Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection

        Dim dbParam_subCategoryName As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_subCategoryName.ParameterName = "@SubCategoryName"
        dbParam_subCategoryName.Value = subCategoryName
        dbParam_subCategoryName.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_subCategoryName)
        Dim dbParam_categoryID As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_categoryID.ParameterName = "@CategoryID"
        dbParam_categoryID.Value = categoryID
        dbParam_categoryID.DbType = System.Data.DbType.Int32
        dbCommand.Parameters.Add(dbParam_categoryID)
        Dim dbParam_categoryName As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_categoryName.ParameterName = "@CategoryName"
        dbParam_categoryName.Value = categoryName
        dbParam_categoryName.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_categoryName)


        dbCommand.ExecuteNonQuery

        dbConnection.Close

        dgSubCategories.EditItemIndex = -1
End Sub

Can anyone tell me if there is a simpler way of doing this... and can i use ddl in there so when they select a category it not only updates the categoryName but the categoryID at the same time?

Are there any real good tutorials out there for this that anyone knows of? Thanks for any help you can give. :D

David Jenkins
-------------------------------------------------------------
Do you want to make extra money around your commitments?
Credit cards, bills, loans and a mortgage - all getting you down?
Is your pension going to be enough when you retire?
There is a solution visit http://www.1stmillion.co.uk
or call 01772 489521

__________________
David Jenkins

What error are you getting?

Here is the way I do it...
Sub dgrdProducts_UpdateCommand( s As Object, e As DataGridCommandEventArgs )
  Dim intProductID As Integer
  Dim txtProductName As TextBox
  Dim txtUnitPrice As TextBox
  Dim strProductName As String
  Dim decUnitPrice As Decimal

  intProductID = dgrdProducts.DataKeys( e.Item.ItemIndex )
  txtProductName = e.Item.Cells( 1 ).Controls( 0 )
  txtUnitPrice = e.Item.Cells( 2 ).Controls( 0 )
  strProductName = txtProductName.Text
  decUnitPrice = txtUnitPrice.Text
  strSql = "Update Products Set ProductName=@ProductName, " _
   & "UnitPrice=@UnitPrice Where ProductID=@ProductID"
  cmdSql = New SqlCommand( strSql, conNorthwind )
  cmdSql.Parameters.Add( "@ProductName", strProductName )
  cmdSql.Parameters.Add( "@UnitPrice", decUnitPrice )
  cmdSql.Parameters.Add( "@ProductID", intProductID )
  conNorthwind.Open()
  cmdSql.ExecuteNonQuery()
  conNorthwind.Close()
  dgrdProducts.EditItemIndex = -1
  BindDataGrid
End Sub

Sorry for this such late response, i have been away. The error code that I am getting is:

Specified argument was out of the range of valid values. Parameter name: index
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.ArgumentOutOfRangeException: Specified argument was out of the range of valid values. Parameter name: index

Source Error:


Line 130: Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString )
Line 131: Dim SubCategoryName as String = CType(e.Item.Cells(2).Controls(0), TextBox).Text
Line 132: Dim CategoryName as String = CType(e.Item.Cells(3).Controls(0), TextBox).Text
Line 133: Dim CategoryID as Integer = CType(e.Item.Cells(4).Controls(0), TextBox).Text
Line 134:


Source File: C:\Inetpub\wwwroot\application\admin\categories.as px Line: 132

Stack Trace:


[ArgumentOutOfRangeException: Specified argument was out of the range of valid values.
Parameter name: index]
   System.Web.UI.ControlCollection.get_Item(Int32 index) +58
   ASP.categories_aspx.dgSubCategories_UpdateCommand( Object sender, DataGridCommandEventArgs e) in C:\Inetpub\wwwroot\application\admin\categories.as px:132
   System.Web.UI.WebControls.DataGrid.OnUpdateCommand (DataGridCommandEventArgs e) +83
   System.Web.UI.WebControls.DataGrid.OnBubbleEvent(O bject source, EventArgs e) +499
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26
   System.Web.UI.WebControls.DataGridItem.OnBubbleEve nt(Object source, EventArgs e) +106
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26
   System.Web.UI.WebControls.LinkButton.OnCommand(Com mandEventArgs e) +95
   System.Web.UI.WebControls.LinkButton.System.Web.UI .IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +115
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler sourceControl, String eventArgument) +18
   System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +138
   System.Web.UI.Page.ProcessRequestMain() +1277

What would i do to change the category name to a drop down list in the editdatacolumn so that it replaces the name and replaces the categoryname id aswell.

Is it normal that you have to click the Edit button twice? I keep having to.

Thanks

David Jenkins
-------------------------------------------------------------
Do you want to make extra money around your commitments?
Credit cards, bills, loans and a mortgage - all getting you down?
Is your pension going to be enough when you retire?
There is a solution visit http://www.1stmillion.co.uk
or call 01772 489521

You are probably referring to the wrong cell no. The datagrid cells start with an index of 0. Check whether the cell you are referring is the 4th column in the datagrid.

HI there,

Yeah i was making that mistake. I resolved it and another error but it generates this one... but why coz i am passing the parameters already!

Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters.

Source Error:


Line 155:
Line 156:
Line 157: dbCommand.ExecuteNonQuery
Line 158:
Line 159: dbConnection.Close


Source File: C:\Inetpub\wwwroot\application\admin\categories.as px Line: 157

Stack Trace:


[OleDbException (0x80040e10): No value given for one or more required parameters.]
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextE rrorHandling(Int32 hr) +41
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS dbParams, Object& executeResult) +122
   System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object& executeResult) +92
   System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior behavior, Object& executeResult) +65
   System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior behavior, String method) +112
   System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +66
   ASP.categories_aspx.dgSubCategories_UpdateCommand( Object sender, DataGridCommandEventArgs e) in C:\Inetpub\wwwroot\application\admin\categories.as px:157
   System.Web.UI.WebControls.DataGrid.OnUpdateCommand (DataGridCommandEventArgs e) +83
   System.Web.UI.WebControls.DataGrid.OnBubbleEvent(O bject source, EventArgs e) +499
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26
   System.Web.UI.WebControls.DataGridItem.OnBubbleEve nt(Object source, EventArgs e) +106
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26
   System.Web.UI.WebControls.LinkButton.OnCommand(Com mandEventArgs e) +95
   System.Web.UI.WebControls.LinkButton.System.Web.UI .IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +115
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler sourceControl, String eventArgument) +18
   System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +138
   System.Web.UI.Page.ProcessRequestMain() +1277




David Jenkins
-------------------------------------------------------------
Do you want to make extra money around your commitments?
Credit cards, bills, loans and a mortgage - all getting you down?
Is your pension going to be enough when you retire?
There is a solution visit http://www.1stmillion.co.uk
or call 01772 489521

Response.Write all of these right after you assign them.
SubCategoryName
CategoryName
CategoryID
Then Response.End() after the 3 Response.Write(s).

Ok i think that i did what you meant. I have removed the CategoryID until i get this working properly to keep it simple for me. What it is doing at the moment is writing it to the page. Clicking on view --> source doesnt show anything but what i have put in, not even html. Thanks for this help you are giving... i think once its cracked it will be most of my problem sorted.

Sub dgSubCategories_UpdateCommand(sender As Object, e As DataGridCommandEventArgs)
        Dim connectionString As String = ConfigurationSettings.AppSettings("ConnectionStrin g")
        Dim dbConnection As New OleDbConnection(ConnectionString)
        dbConnection.Open()
        Dim SubCategoryName as String = CType(e.Item.Cells(1).Controls(0), TextBox).Text
        Response.Write (SubCategoryName)
        Dim CategoryName as String = CType(e.Item.Cells(2).Controls(0), TextBox).Text
        Response.Write (CategoryName)

        Response.End()


        Dim queryString As String = "UPDATE [tblSubCategory] SET [SubCategoryName]=@SubCategoryName, [CategoryName]=@CategoryName"
        Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection

        Dim dbParam_subCategoryName As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_subCategoryName.ParameterName = "@SubCategoryName"
        dbParam_subCategoryName.Value = subCategoryName
        dbParam_subCategoryName.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_subCategoryName)
        Dim dbParam_categoryID As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter

        Dim dbParam_categoryName As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_categoryName.ParameterName = "@CategoryName"
        dbParam_categoryName.Value = categoryName
        dbParam_categoryName.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_categoryName)


        dbCommand.ExecuteNonQuery

        dbConnection.Close

        dgSubCategories.EditItemIndex = -1
End Sub


David Jenkins
-------------------------------------------------------------
Do you want to make extra money around your commitments?
Credit cards, bills, loans and a mortgage - all getting you down?
Is your pension going to be enough when you retire?
There is a solution visit http://www.1stmillion.co.uk
or call 01772 489521

Hi there,

I have been playing around with the code on this and i took out the response.write lines.... I got i clicked the update and rather than causing an error this time, it changed ALL the records in the table to same thing! is there something wrong in my update string?

the code now is...

Sub dgSubCategories_UpdateCommand(sender As Object, e As DataGridCommandEventArgs)
        Dim connectionString As String = ConfigurationSettings.AppSettings("ConnectionStrin g")
        Dim dbConnection As New OleDbConnection(ConnectionString)
        dbConnection.Open()
        Dim SubCategoryName as String = CType(e.Item.Cells(1).Controls(0), TextBox).Text
        Dim CategoryName as String = CType(e.Item.Cells(2).Controls(0), TextBox).Text

        Dim queryString As String = "UPDATE [tblSubCategory] SET [SubCategoryName]=@SubCategoryName, [CategoryName]=@CategoryName"
        Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection

        Dim dbParam_subCategoryName As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_subCategoryName.ParameterName = "@SubCategoryName"
        dbParam_subCategoryName.Value = subCategoryName
        dbParam_subCategoryName.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_subCategoryName)
        Dim dbParam_categoryID As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter

        Dim dbParam_categoryName As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_categoryName.ParameterName = "@CategoryName"
        dbParam_categoryName.Value = categoryName
        dbParam_categoryName.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_categoryName)


        dbCommand.ExecuteNonQuery

        dbConnection.Close

        dgSubCategories.EditItemIndex = -1
        dgSubCategories.DataSource() = getSubCategories()
        dgSubCategories.DataBind()
End Sub

My theory is that it is not telling the table which row to update but in the examples the e.Items.Cell is meant to tell the table which one to update isnt it.

David Jenkins
-------------------------------------------------------------
Do you want to make extra money around your commitments?
Credit cards, bills, loans and a mortgage - all getting you down?
Is your pension going to be enough when you retire?
There is a solution visit http://www.1stmillion.co.uk
or call 01772 489521

Dim queryString As String = "UPDATE [tblSubCategory] SET [SubCategoryName]=@SubCategoryName, [CategoryName]=@CategoryName"

You need to be more specific. Add a WHERE categoryID = @catID and the code to pull this from your datagrid.