Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Update a Non Indexed table using OleDbDataAdapter
Best Practices
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.
[color green]' This is where we fill our DataSet so that we can use it.[/color]
...
Work with your dataset here.
...
[color red]
'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![/color]
[color green]
'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.[/color]
[color green]
'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.[/color]
[color green]
'Then of couse, run our update, specifying which dataset and dataTable in that DataSet we will be updating..[/color]
[color green]' We won't need this since we are such excellent programmers, but just in case.[/color]
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.
First we make our declarations of what we will need. We also declare our SELECT query that will start us off.
Code:
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")...
Work with your dataset here.
...
Code:
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![/color]
Code:
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.[/color]
Code:
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.[/color]
Code:
objAdapt.Updatecommand.parameters("@myParam").sourcecolumn="originalColumn"'Then of couse, run our update, specifying which dataset and dataTable in that DataSet we will be updating..[/color]
Code:
objAdapt.Update(objData, "NewOrders")
Catch ex As Exception
Msg.Text = ex.ToString
Code:
End TrySo 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.