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