Update function created using Web Matrix Code Wizard fails

microsoft.public.dotnet.framework

24 Mar 2006 7:45 PM

glenn

Hello,

I am using the Web Matrix Code Wizard's Update feature. I have a very
simple user interface that has two textboxes. One is for a Name and the
other is for a Description. I also have a dropdown listbox. I am working
with the Northwind database attempting to update the Category table.

The dropdown listbox is populated with the categoryID and the two textboxes
are for the CategoryName and CategoryDescription fields. I have data
successfully populating the textboxes and when I select a new categoryID in
the dropdown listbox, the data in the textboxes changes accordingly.

My problem is updating the database. Below I have a function that was
created using the Update Code Wizard. I also have an event subroutine that
fires when I click the Save button which calls the MyUpdateMethod function.

Not sure why, but when I make changes to the textboxes and click save, the
changes do not stick. I select a different ID and then select the ID that I
made changes to but the changes are not there.

I am running on my localhost Web Matrix Server.

To view a MSDN walkthrough of a similar exercise go to
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbwlkwalkthroughupdatingdatausingdatabaseupdatequeryinwebforms.asp

Any ideas would be greatly appreciated.

Function MyUpdateMethod(ByVal categoryID As Integer, ByVal categoryName As
String, ByVal description As String) As Integer
        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;
Ole DB Services=-4; Data Source=C:\Northwind.mdb"
        Dim dbConnection As System.Data.IDbConnection = New
System.Data.OleDb.OleDbConnection(connectionString)

        Dim queryString As String = "UPDATE [Categories] SET
[CategoryID]=@CategoryID, [CategoryName]=@CategoryName,
[Description]=@Description WHERE ([Categories].[CategoryID] = @CategoryID)"
        Dim dbCommand As System.Data.IDbCommand = New
System.Data.OleDb.OleDbCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection

        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)
        Dim dbParam_description As System.Data.IDataParameter = New
System.Data.OleDb.OleDbParameter
        dbParam_description.ParameterName = "@Description"
        dbParam_description.Value = description
        dbParam_description.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_description)

        Dim rowsAffected As Integer = 0
        dbConnection.Open
        Try
            rowsAffected = dbCommand.ExecuteNonQuery
        Finally
            dbConnection.Close
        End Try

        Return rowsAffected
    End Function

' ddlCategoryID is the dropdown listbox ID, txtCategoryName and
txtCategoryDescription are textbox ID's

Sub btnSave_Click(sender As Object, e As EventArgs)
            MyUpdateMethod(ddlCategoryID.SelectedIndex,
txtCategoryName.Text, txtCategoryDescription.Text)
End Sub