Data Adapter resources?

jcoyan

Member
Joined
Jul 21, 2006
Messages
24
Programming Experience
Beginner
Is anyone familar of a resource I could use, to get familar with Data Adapters? I've read all material I can find, but can't find a solid way to do what I need to do.

I have a datatable that is filled from a data adapter. The datatable is then set as the source of a datagrid. I need users to enter data into this grid, then either when a button is clicked or when the grid is exited, the data will update on the actual database.

I'm not looking for someone to tell me every step I need to take, just perhaps point me in the direction of a nice tutorial, or example code.

Thanks in advance!
 
Updating the datasource

Hi,

Updating the datasource is done using the update method of the DataAdapter class. This method is responsible for examining the RowState property of each of the DataRow objects in the Rows collection. The Rows Collection is a member of each of the DataTable objects contained in the Tables collection of the dataset.

The DataAdapter starts by looping through all the tables in the Tables collection in the DataSet, and for each table, it loops through the Rows collection to examine the RowState property. If a row has been inserted, updated, or deleted, the DataAdapter uses one of the command properties to handle the update. This means that the InsertCommand property is used if we are inserting a new row, the UpdateCommand property is used if we are updating an existing row, and the Deletecommand is used if you are deleting an existing row.

The Getchanges method is a overloaded method and is used for retrieving a copy of the dataset that contains all the changes that have been made since the last time the Acceptchanges method.

was called or since the data set was loaded.

Acceptchanges method accepts or commits all the changes that have been made to the DataSet since the last time the method was called or since the DataSet was loaded. AcceptChanges method sets the original value of all fields to the current value. In effect, the new DataSet is the same as the one you would get by reloading it from the database. If your DataSet isn’t excessively large, you can reload it from the database and save yourself some serious debugging.

The statement myAccountAdapter.Update(myAccountDataset.GetChanges, “AccountsTable”) updates the AccoutsTable.

VB.NET:
Private Sub UpdateDatabaseButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateDatabaseButton.Click
Dim i As Integer
myConnection.Open()
For i = 0 To (myAccountDataset.Tables(“AccountsTable”).Rows.Count() - 1)
MsgBox(myAccountDataset.Tables(“AccountsTable”).Rows(i)(0))
MsgBox(myAccountDataset.Tables(“AccountsTable”).Rows(i)(1))
MsgBox(myAccountDataset.Tables(“AccountsTable”).Rows(i)(2))
MsgBox(myAccountDataset.Tables(“AccountsTable”).Rows(i)(3))
Next
myAccountAdapter.Update(myAccountDataset.GetChanges, “AccountsTable”)
End Sub
End Class
Regards
bhar
Knowledge is power
http://www.vkinfotek.com
 
Last edited by a moderator:
jcoyan, you're Primary Platform is shown as .NET 2.0 (VS 2005).
I would suggest using the newer features of framework version 2, the TableAdapters, BindingSources, and DataGridViews.

MSDN provides video training at this link: Express - Visual Web Developer - Easy to Learn. Follow the links below the 'Video training for intermediate Web developers' section for data access videos.
 
Thank you, that helped a lot.

I still have a problem though, about setting my INSERT statement. I'm setting it when I click the button to update the database.

Anyway, the valus I'm inserting into the database are supposed to be the values the user enters into the datagrid. However, I'm not quite sure how to pull these values from individual cells, then dump the value into a variable in order to update the database. Or is this even the correct approach?
 
Not sure who you're thanking. Did you watch the videos at MSDN?

When you databind a DataGridView to a BindingSource, any changes made to the dataSource are automatically reflected in the bound control (in this case the DataGridView) and any changes made to the DataGridView are reflected in the dataSource. The BindingSource in ADO.NET V2 is a suped-up dataView which allows you to filter the records from the dataSource. Look here to see how to fill a dataGridView without any code: http://vbdotnetforums.com/showthread.php?t=11626#14

ADO.NET uses a disconnected architecture which means a copy of the data is stored in memory so the user can work on that data. Then methods are called on the adapter class to update those changes back to the dataBase. The V2 adapter class would be the tableAdapter and the method to call would be Update. The tableAdapter holds all the Update, Delete, and Insert commands and all information associated with those.

I would suggest watching the videos I linked to above.
 
I'm using the 1.1 framework, I just didn't realize it was an option to change.

Does the same technique still apply?
 
ADO.NET V1 does use a disconnected architecture as well but the tableAdapters, BindingSources, and DataGridViews aren't available.

You'll use dataAdapters, (optionally dataViews), and DataGrid.
When you bind a datagrid to a datasource, any change in one is reflected in the other.
 
Sorry about not changing the platform...it should be alright now.

I understand that when I bind a dataset to a datagrid, changes made to the grid are also made to the underlying dataset. What I don't understand is how to update the actual database with the updated dataset. I tried this code.

VB.NET:
myDataAdapter.Update(myDataSet.GetChanges, currentTableError)

currentTableError is a variable that contains the name of the table I want to update.

Should this code work? Because I get the following error.

"Value cannot be null. Paramater name: dataSet"

One thing worth noting is that I haven't set my Insert and Update commands, because I'm not sure how to get the values to insert. If the datagrid doesn't figure this out on it's own, do I have to monitor changes in the datagrid, set variables, and then update manually?

I sincerely apologize for the confusion. I realize that my explanations may not make much sense. This is the first time I have really delved into datagrids.
 
It's way simpler than you think. You need to look into parameterised queries. Essentially what happens is that you create insert, update, delete SQL then you pass these oledbcommands/SQLcommands to the correct dataadapters method. i.e

VB.NET:
MyDataAdapter.InsertCommand = MyInsertCommand

Do this for the update and delete commands as well. The Dataadapter will then use the datatable that you pass in, in the argument of the adapters update method to correctly determine which command to apply. Clever eh. So you just need the correct commands and away you go...
 
I appreciate all the replies.

I'm working on setting the commands, but running into a problem when trying to create my data adapter. Here's the code. When I try to set the commandtext properties after creating the adapter, it complains that I need to an instance of the object.

VB.NET:
Try
            cdbConnect.Initialize()
            'Counts the rows in the error table. DOES reflect changes made by the user.
            Dim i As Integer
            i = myDataSet.Tables("myDataTable").Rows.Count()

            Dim myDataAdapter As New SqlDataAdapter

            myDataAdapter.Update(myDataSet.GetChanges, currentTableError) 
        

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString)

Just as soon as I can get this portion to work, I can start setting the commands. When I create data adapters at other points in my program, I'm creating them with the connection and select command properties. I don't want this data adapter to select anything initially, but it appears that I'm doing something wrong...
 
You need a SQLcommand....

VB.NET:
Dim MySqlCommand as new SQLcommand(SQL text here, Sql connection here)

P.s This is just my opinion but dont use the GetChanges method. I just normally pass the actual datatable to the update method......

VB.NET:
myDataAdapter.Update(Your Datatable)


What line is it complaining about the object reference?
 
It's complaining about the object reference when I attempt to actually set the CommandText of the UpdateCommand.

I don't "need" a SQLcommand. I generally create the data adapter with one because I'll fill the dataset from it, but in this case, I don't want to fill a dataset since I already have one that contains my updated data. I tried using a null command, but that didn't work either.
 
Try
cdbConnect.Initialize()
'Counts the rows in the error table. DOES reflect changes made by the user.
Dim i As Integer
i = myDataSet.Tables("myDataTable").Rows.Count()​

Dim myDataAdapter As New SqlDataAdapter​

myDataAdapter.Update(myDataSet.GetChanges, currentTableError)​


Catch ex As Exception
MessageBox.Show(ex.Message.ToString)​


So where in here are you trying to set the command text of the update command? Also why are you using a different dataadapter than the one you used to fill the dataset, when the dataadapter fills from a database it also collects other info to assist with updates etc.
 
I took it out when I was troubleshooting.

VB.NET:
        Try
            Dim aConnection As SqlConnection
            aConnection = cdbConnect.Initialize()
            'Counts the rows in the error table. DOES reflect changes made by  
            the user.
            Dim i As Integer
            i = myDataSet.Tables("myDataTable").Rows.Count()
 
            Dim sqlString As String
            sqlString = ""
            Dim myDataAdapter As New SqlDataAdapter(sqlString, aConnection)
            myDataAdapter.InsertCommand.CommandText = "INSERT INTO " & 
currentTableError & " (ErrorCode,Description,StartTime,EndTime,Waiting) " & _
                       " VALUES (ErrorCode,Description,StartTime,EndTime,Waiting)"
 
            myDataAdapter.Update(myDataSet.GetChanges, currentTableError)
        Catch ex As Exception

When I DO NOT try to set the InsertCommand, I receive this.
"Value cannot be null. Parameter name: dataSet"
When I DO, I receive the object reference error.

-

I was using a different data adapter because I couldn't find a way to reference the one used in the other section of the program.

-

I created a SQLCommand using the Insert string, then set the InsertCommand equal to the SQLCommand. This got rid of the object reference error, and dumped me back to the other I've been receiving.

I'm honestly not sure what to stick in the InsertCommand string after VALUES. That's where I was asking if I had to monitor changes and use variables, or what...
 
Last edited by a moderator:
Back
Top