Update Not Working

V

VinceC

Member
Joined
May 30, 2003
Messages
15
Location
Ontario, Canada
Can anyone tell me why this code is not updating my table?

Code: 
        Dim MyConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test.mdb"
        Dim MyConn As OleDb.OleDbConnection = New OleDb.OleDbConnection(MyConnStr)

        Dim MySelectStr As String = "SELECT * FROM MyTable"
        Dim MySelectCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(MySelectStr, MyConn)

        Dim MyUpdateCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand()
        Dim MyUpdateStr As String = "UPDATE MyTable SET Name=""Maria"" WHERE ID=2"
        MyUpdateCmd.CommandText = MyUpdateStr
        MyUpdateCmd.Connection = MyConn
        MyUpdateCmd.Parameters.Add(New OleDb.OleDbParameter("Name", OleDb.OleDbType.VarWChar, 50, "Name"))
        MyUpdateCmd.Parameters.Add(New OleDb.OleDbParameter("Original_ID", OleDb.OleDbType.Integer, 0, ParameterDirection.Input, False, CType(10, Byte), CType(0, Byte), "ID", DataRowVersion.Original, Nothing))

        Dim MyDBAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()
        MyDBAdapter.SelectCommand = MySelectCmd
        MyDBAdapter.TableMappings.AddRange(New Common.DataTableMapping() {New Common.DataTableMapping("Table", "MyTable", New Common.DataColumnMapping() {New Common.DataColumnMapping("ID", "ID"), New Common.DataColumnMapping("Name", "Name")})})
        MyDBAdapter.UpdateCommand = MyUpdateCmd

        Dim MyDS As DataSet = New DataSet()

        MyDBAdapter.Update(MyDS, "Table")

Thanks.
 
> MyDBAdapter.Update(MyDS, "Table")

Where did "Table" come from? From your snippet, I dont see any MyDBAdapter.Fill(MyDS, "Table") command.
 
Vince, do you only need to update the records you specified
(id = 2), if so why do you need a data adapter in the first place just use

Dim MyUpdateCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand()
Dim MyUpdateStr As String = "UPDATE MyTable SET Name=""Maria"" WHERE ID=2"
MyUpdateCmd.CommandText = MyUpdateStr
MyUpdateCmd.Connection = MyConn
MyUpdateCmd.executeNonQuery()
 
My apologies, I had removed this line prior to posting

Code: 
MyDBAdapter.Update(MyDS, "Table")

However, the code still does not update my Access DB table "MyTable".

Im just trying to figure-out what code I need in order to use the .update command. The specific record that I selected (ID=2) is just a test. Basically, I want to read one record at a time from the DB table, do some calculations with it in VB.NET and then write back the results to other fields in the same DB table.

Thanks for the .executeNonQuery() method/command, I tried that yesterday and it works. However, Im still curious about how to use the .update command properly in this example.
 
Originally posted by VinceC
Basically, I want to read one record at a time from the DB table, do some calculations with it in VB.NET and then write back the results to other fields in the same DB table.

Thanks for the .executeNonQuery() method/command, I tried that yesterday and it works. However, Im still curious about how to use the .update command properly in this example.
Click to expand...

I dont think the problem is the proper use of the .update method in your example.
Im curious, why the mappings manipulation?
There should be no need for that.

A DataSet has no knowledge of where the data it contains comes from, and a Connection has no knowledge of what happens to the data it retrieves. The DataAdapter maintains the connection between the two. It does this by using the TableMappings collection. The TableMappings collection contains one or more DataTableMapping objects. If a DataAdapter manages multiple record sets, as might be the case with a stored procedure that returns multiple result sets, there will be a DataTableMapping object for each record set. The DataTableMapping object, in turn, is another collection of one or more DataColumnMappping objects. There are two properties for each DataColumnMapping object: the SourceColumn and the DataSetColumn. There is a DataColumnMapping object for each column managed by the DataAdapter. By default, the .NET Framework will create a TableMappings collection (and all of the objects it contains) with the DataSetColumn name set to the SourceColumn name when a DataAdapter object is instantiated.

I can rewrite your code to make the single record selected be modified after it is selected and then update the data source based on those modifications. There are multiple means of modifying the record, including but certainly not limited to using parameters. Writing data back to a different source column, via the DataAdapters update method requires a valid TableMappings object. I would discourage you from manipulating the TableMappingsObject to do the simple task you have described. That being said, ss I stated above, and as you are aware as evidenced by your code, the TableMappings is a collection. Adding to that collection does not clear the previously held objects in the collection.

Code: 
MayDBAdapter.TableMappings.Clear()

Removes all TableMappingObjects from the collection and should then allow for the addition of a new TableMappings object.

Jon
 
Jon, thanks for the info. about TableMappings and everything else in your reply. I guess I went off on a tangent when trying to use the .update method to make changes to my Access DB table. I would like to take you up on your offer to write my code. Again, I would like to read data from MyTable one record-at-a-time and then write results back to the same table into different empty fields. Im new to .NET but understood how to do this in VB6. You can make-up the data that gets read and then what gets written back. Please keep it simple. Your help is appreciated, Ive been doing a lot of reading but have been unsuccessful with this particular task. Thx.
 
Code: 
        Dim MyConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test.mdb"
        Dim MyConn As OleDb.OleDbConnection = New OleDb.OleDbConnection(MyConnStr)

        Dim MySelectStr As String = "SELECT * FROM MyTable WHERE Name = Maria" what follows assumes the selection only returns a single row (in zero indexed world, row 0) as 
referenced in the "Fred Flinstone" line below...if your select returns greater than one row, there are several 
ways to then search for a specific row in the dataset...gets into bindingcontext objects, dont think we want to go there right now.
        Dim MySelectCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(MySelectStr, MyConn)  the next 
line uses the select statement and the connection object in the DataAdapter objects constructor thus making this unneccesary
    Dim MyDBAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(MySelectStr, MyConn)
Dim MyDS As DataSet = New DataSet()

MyDBAdapter.Fill(MyDS, "MyTable") Now your dataset is filled with data
next, make the changes to the data you want to change

MyDS.Tables("MyTable").Rows(0).Item("Name") = "Fred Flinstone"

Dim updateSQL As String = "UPDATE MyTable SET Name = ? WHERE Name = ?"
Since were using the oledb namespace, we use the ? placeholder for our parameters and then add them below.
         The dataadapter class contains references to four
        Command objects each of which has a commandtext property that contains the actual
        SQL command to be executed.  The next line creates a new oledbcommand object
         passing the update string into the constructor and then assigns the update command
        that new command object.

        MyDBAdapter.UpdateCommand() = New OleDbCommand(updateSQL, conn)
then we add (and name) the parameters to the DataAdapters parameters collection in the same order they are referenced above
        MyDBAdapter.UpdateCommand.Parameters.Add("Name", OleDbType.VarWChar, 50, "Name")
     MyDBAdapter.UpdateCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Name", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Name", System.Data.DataRowVersion.Original, Nothing))

    MyDBAdapter.Update(MyDS, "MyTable")

I think that will do it.
The parameters reference a source column in the dataset. Setting that to something other than the original source column will write the data back to whatever column you specify...the data types have to match though or your db will complain.

Give it a whirl, let me know how you do.

Jon
 
Thats great! It worked. I also learned some new commands. By the way, I changed the line:

Code: 
MyDBAdapter.UpdateCommand() = New OleDbCommand(updateSQL, conn)

to

Code: 
MyDBAdapter.UpdateCommand() = New OleDbCommand(updateSQL, MyConn)

Seems like I was spinning my wheels for a little while and not getting anywhere. Thanks for clearing things up for me.
 
Back
Top