I struggled for some time, trying to understand the procedure to update via an OleDbDataAdapter. This is what I learned and I hope it helps you.
First we make our declarations of what we will need. We also declare our SELECT query that will start us off. Dim strSql As String = "SELECT * FROM tblNewOrderNum" Dim objData AS New DataSet() Dim objConn As New OledbConnection(Application("dbAccess")) Dim objAdapt As New OleDbDataAdapter(strSql, objConn) Dim objTable As DataTable Dim objRow As DataRow Dim strNewNumI, strNewNum As String objAdapt.Fill(objData, "NewOrders") ' This is where we fill our DataSet so that we can use it.
... Work with your dataset here. ...
Try 'First we want to create our UPDATE command. 'The question mark specifies that we will later put a parameter (a variable of sorts) here. This will not work unless we add the parameter! objAdapt.UpdateCommand = new oledbcommand("UPDATE tblNewOrderNum SET wOrders = ? ", objConn)
'Here we are going to do a number of things. 'First we tell .Net that we want to Add a Parameter to the Updatecommand for objAdapt. 'But of course we specify what that paramerter is with "@myParam". The @ is imperative as it specifies that this is our parameter name. 'Then we specify the type. You can find a listing of types here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconusingparameterswithdataadapters.asp 'The size is also specified. For DataTypes like Int we do not need this. 'We also specify the Value here. We could do this later, but I do it now. Note: The dataType for this Value must corespond with the dataType of the parameter. objAdapt.Updatecommand.Parameters.Add("@myParam", system.data.oledb.OleDbType.VarWChar, 15).Value = someValue
'This specifies the column we are updating. We may be able to drop this since we have specified it in the Query string, but I don't know for sure yet, so I leave it in. objAdapt.Updatecommand.parameters("@myParam").sourcecolumn="originalColumn"
'Then of couse, run our update, specifying which dataset and dataTable in that DataSet we will be updating.. objAdapt.Update(objData, "NewOrders") Catch ex As Exception Msg.Text = ex.ToString ' We won't need this since we are such excellent programmers, but just in case. End Try
So that's it. I hope I have helped some of you up there understand this better. Check that link out. It has more information on this but is a little complex, at least it was for me. K, there you go. ;)