ADO.NET Code - Simple (?)

[KarlMoore]

Hello Gang!

Hmm, I'm having a couple of problems working with ADO.NET code and wondered if any of you *fantabulous* folk could help me out. It's for part of the VB.NET Uncovered series I'm writing here at the site.

Below you'll find a chunk of VB.NET code. It opens the Customers table in that oh-so-predictable Northwind database. The CompanyField information is extracted, then altered to add a smiley to the end of it (hey, it's for demonstration purposes!).

My question is how do I get the since-altered information in the DataSet back to its original source (the SQL Server Northwind database)? In other words, how do I update with this code?

I've tried allsorts and I'm obviously missing something very simple.

If someone could perhaps test this out and get back with any suggestions, that'd be great!

Thanks Gang!


- - - Karlos


Here's the code:



Dim ConnStr As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Initial Catalog=Northwind;Data Source=ABYDOS"

Dim myConn As New ADOConnection(ConnStr)
Dim myCommand As New ADOCommand("SELECT * FROM Customers", myConn)

Dim myDataSetCommand As New ADODataSetCommand(myCommand)

Dim myDataSet As New DataSet("Test1")

Try

myConn.Open()
myDataSetCommand.FillDataSet(myDataSet, "Customers")

Dim myRow As System.Data.DataRow

For Each myRow In myDataSet.Tables("Customers").Rows
Console.WriteLine("Company Name: " & CStr(myRow("CompanyName")))
myRow("CompanyName") = CStr(myRow("CompanyName")) & " :-)"
Next

' This is where I need assistance -
' the row has been updated, but
' how do you pass this back to the
' data source?

Catch myException As Exception

MsgBox(myException.ToString)

Finally

If myConn.State = DBObjectState.Open Then
myConn.Close()
End If

If Not myDataSet Is Nothing Then
myDataSet = Nothing
End If

End Try

[dimava]

i'm not too sure about SQL, but when you work with Access Databases, it updates it the second you press a button on the keyboard

Dimava

[KarlMoore]

Are you saying it 'updates' as soon as you run...

myRow("CompanyName") = CStr(myRow("CompanyName")) & " :-)"

...?

It *certainly* doesn't with SQL Server. I feel I've either got to run a .AcceptChanges, .Update or something - but I don't know *which* object to run this against (if indeed you can do it at all like this.

What do you think? Anyone have any suggestions?

Please help - future VB.NET Uncovered instalments and budding VB-World-ers rely on it... <hehe... emotional blackmail time> ;))

Thankx Gang!

- - - Karlos

[alex_read]

I've done quite a bit with this new ADO net & will look at this tonight (watch this space tomorrow morning)

I couldn't post a feedback on the tutorial, but a quick note - forth page (http://www.vbworld.com/articles/vbnetuncovered/index4.html) there's a missing link you might want to look at (right at the end of the 2nd paragraph up - you've got (link) I guess you were gonna put a link in there after it was written) ? :eek:

[Active]

I think you have to use..

myDataSetCommand.Update(myDataSet, "Customers");

[Active]

NOte : There's a Very good ADO.NET Tutorial written by
Rob Macdonald {of 'Serious ADO' fame }

http://www.dnjonline.com/articles/essentials/iss22_essentials.html

[alex_read]

Been trying this Active, as that's what I thought I used before, can't seem to get it. There is a ADODataSetCommand.UpdateCommand () which looks familiar & I'm looking into at the moment. There's only a sample in C#, so I'm having a play with it.

Karl - My SQL servers decided to play up, so I'm gonna have to do a sample with the OLEDB option :rolleyes: I'm going offline in a little while, so this might well be tomorrow morning before I post back.

[alex_read]

Just gonna paste this before I lose it. This 1/2 gets it, but gives an error at a dirrefernt field each time.

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim ConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB;" & _
"Mode=ReadWrite;Persist Security Info=False;"
Dim myConn As New ADO.ADOConnection(ConnStr)
Dim myCommand As New ado.ADOCommand("SELECT * FROM Customers", myConn)
Dim myDataSetCommand As New ado.ADODataSetCommand(myCommand)
Dim myDataSet As New DataSet("Test1")

Try
myConn.Open()
myDataSetCommand.FillDataSet(myDataSet, "Customers")
Dim myRow As System.Data.DataRow

For Each myRow In myDataSet.Tables("Customers").Rows
Console.WriteLine("Company Name: " & CStr(myRow("CompanyName")))
myRow("CompanyName") = CStr(myRow("CompanyName")) & " :c)"
Next myrow

myDataSetCommand.Update(myDataSet, "Customers")
Msgbox("Done !")

Catch myException As Exception
MsgBox(myException.ToString)
Finally
If myConn.State = DBObjectState.Open Then
myConn.Close()
End If

If Not myDataSet Is Nothing Then
myDataSet = Nothing
End If
End Try
End Sub

[alex_read]

Okay, Karl, I've sucumbed to the emotional blackmail :D Here's the #'$%*@!!! code (needed to put the update after each record / in the loop :rolleyes: :

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim ConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB;" & _
"Mode=ReadWrite;Persist Security Info=False;"
Dim myConn As New ADO.ADOConnection(ConnStr)
Dim myCommand As New ado.ADOCommand("SELECT * FROM Customers", myConn)
Dim myDataSetCommand As New ado.ADODataSetCommand(myCommand)
Dim myDataSet As New DataSet("Test1")

Try
myConn.Open()
myDataSetCommand.FillDataSet(myDataSet, "Customers")
Dim myRow As System.Data.DataRow

For Each myRow In myDataSet.Tables("Customers").Rows
Console.WriteLine("Company Name: " & CStr(myRow("CompanyName")))
myRow("CompanyName") = CStr(myRow("CompanyName")) & " :c)"
myDataSetCommand.Update((myDataSet), ("Customers"))
Next myrow

Msgbox("Done !")

Catch myException As Exception
MsgBox(myException.ToString)
Finally
If myConn.State = DBObjectState.Open Then
myConn.Close()
End If

If Not myDataSet Is Nothing Then
myDataSet = Nothing
End If
End Try
End Sub
As above, my SQL server crashed so you'll need to modify the connection string - and you missed an End Try at the end, though I guess this is just part of the code you posted.

Good luck & I look forward to reading the tutorial if it's anything like your others.
Just 1 point more - why did you name this thread as "ADO.NET Code - SIMPLE ?!?!?!?! ;)

[alex_read]

Okay, Karl, I've sucumbed to the emotional blackmail :D Here's the #'$%*@!!! code (needed to put the update after each record / in the loop :rolleyes: :

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim ConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB;" & _
"Mode=ReadWrite;Persist Security Info=False;"
Dim myConn As New ADO.ADOConnection(ConnStr)
Dim myCommand As New ado.ADOCommand("SELECT * FROM Customers", myConn)
Dim myDataSetCommand As New ado.ADODataSetCommand(myCommand)
Dim myDataSet As New DataSet("Test1")

Try
myConn.Open()
myDataSetCommand.FillDataSet(myDataSet, "Customers")
Dim myRow As System.Data.DataRow

For Each myRow In myDataSet.Tables("Customers").Rows
Console.WriteLine("Company Name: " & CStr(myRow("CompanyName")))
myRow("CompanyName") = CStr(myRow("CompanyName")) & " :c)"
myDataSetCommand.Update((myDataSet), ("Customers"))
Next myrow

Msgbox("Done !")

Catch myException As Exception
MsgBox(myException.ToString)
Finally
If myConn.State = DBObjectState.Open Then
myConn.Close()
End If

If Not myDataSet Is Nothing Then
myDataSet = Nothing
End If
End Try
End Sub
As above, my SQL server crashed so you'll need to modify the connection string - and you missed an End Try at the end, though I guess this is just part of the code you posted.

Good luck & I look forward to reading the tutorial if it's anything like your others.
Just 1 point more - why did you name this thread as "ADO.NET Code - SIMPLE ?!?!?!?! ;)

For anyone that'll need this thread in future :
http://msdn.microsoft.com/library/dotnet/cpref/frlrfsystemdatainternaldbdatasetcommandclassupdatetopic.htm

[KarlMoore]

Hello Again!

Thanks for your response, Alex - it's really appreciated.

Hmm, so you have to update on *each* change? Surely that can't be right? I haven't yet tried the code but isn't that awfully 'expensive' - it's a network round-trip per row (or maybe even field) update then? They *must* have thought of something else!

And I don't really want to say... "Dearest reader... this is how I'm going to tell you to do it, but it might not be right. I just dunno"... sounds a tad unprofessional ;)

I'm really started to hate this chapter. Absolutely no-one seems to know how to go updating data (with DataSets, not bound controls) - VB.NET Programming book, not even the recommended Rob MacDonald article.

Any more suggestions? I'll keep plodding on for now... but will continue to beg for solutions! Alex? ;> <cute smile goes here>

- - - Karlos

[Active]

Karl ...It seems you have to call

myRow.AcceptChanges() (http://msdn.microsoft.com/library/dotnet/cpref/frlrfsystemdatadatarowclassacceptchangestopic.htm)
within the loop after making the changes.

Before calling

myDataSetCommand.Update((myDataSet), ("Customers"))

so the code is...



Dim ConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB;" & _
"Mode=ReadWrite;Persist Security Info=False;"
Dim myConn As New ADO.ADOConnection(ConnStr)
Dim myCommand As New ado.ADOCommand("SELECT * FROM Customers", myConn)
Dim myDataSetCommand As New ado.ADODataSetCommand(myCommand)
Dim myDataSet As New DataSet("Test1")

Try
myConn.Open()
myDataSetCommand.FillDataSet(myDataSet, "Customers")
Dim myRow As System.Data.DataRow

For Each myRow In myDataSet.Tables("Customers").Rows
Console.WriteLine("Company Name: " & CStr(myRow("CompanyName")))
myRow("CompanyName") = CStr(myRow("CompanyName")) & " :c)"
myRow.AcceptChanges()
Next myrow

myDataSetCommand.Update(myDataSet, "Customers")
Msgbox("Done !")

Catch myException As Exception
MsgBox(myException.ToString)
Finally
If myConn.State = DBObjectState.Open Then
myConn.Close()
End If

If Not myDataSet Is Nothing Then
myDataSet = Nothing
End If
End Try

[alex_read]

That works, well done Active.
Fair point Karl, I was just glad I got something working (think you've put me off .net though ;) )

[KarlMoore]

Thanks for the code, Gang!

... however... <boo hoo> ... it doesn't seem to work with SQL Server!

I note you're using a local Access database and it appears to sort itself just fine. But on SQL Server? It's a big no-no.

DAMN... and yeah Alex... it's putting me off .NET too ;) <hehe>

Righto, I'm going to try and chat with Microsoft about this. Will post the solution... plus, please drop a line if you figure out a solution... thankx!

- - - Karlos

[Ianpbaker]

Hi all, just found this from MSDN might be useful


Updating Data Web applications generally update their data using plain SQL statements or, better yet, using parameterized stored procedures. However, when it comes to using disconnected data, you might want to exploit built-in services to update all the records that need to be revised. To accomplish this, ADO provides the batch update mechanism.

The UpdateBatch method is used to send Recordset changes held in the copy buffer to the server to update the data source. It makes use of an optimistic type of lock and allows all of the pending local changes. It also sends all the changes to the data source in a single operation. An optimistic lock occurs when the data source locks the records being changed only when the changes are committed. As a result, two users can access the same record at the same time and enter changes that are soon overwritten by the other. Of course, this approach works as long as the data source is capable of detecting and rejecting data conflicts. It also assumes that the whole data source is not extremely volatile and subject to frequent changes. Otherwise, the cost of the inferred reconciliation soon outweighs the savings of a few full, pessimistic locks. In fact, with the UpdateBatch method, an error is returned should any of the changes fail. Then, you access the error using the Errors collection and Error object.

Understanding how optimistic locks work in ADO is a key element in understanding how the ADO.NET model for updating data is significantly more powerful. From the ADO code, you call UpdateBatch and the rest that happens is beyond your control. That is, updates are performed on the server by scrolling the rows that have been changed, comparing the original value with the current value in the corresponding record of the data source. If all coincide, then the proper SQL statement (INSERT, UPDATE or DELETE) executes against the table.

The issue is that you can't control the SQL statement that actually applies the changes for you. The server-side update code is neither better than the one you write, nor does it work if you target a non-SQL provider. At the beginning of this section, I stated that Web applications typically update their data through parameterized stored procedures. However, this isn't what happens if you use a batch update.

In ADO.NET, this model has been expanded quite a bit. Now it follows a more generic schema that lets you specify your own commands for basic operations like insertion, deletion, update, and selection. It's easy to see the intention to abstract from the data source and provide the same support, regardless of the data source's nature. Batch updating in ADO.NET requires you to create a DataSetCommand object—either SQLDataSetCommand or ADODataSetCommand.

Note In Beta 2, DataSetCommand objects will be called DataAdapter objects.

Once you hold a DataSetCommand object in your hands, you call its Update method. DataSetCommand exposes properties like InsertCommand, DeleteCommand, UpdateCommand, and SelectCommand. They're Command objects but you don't have to set them unless the default behavior doesn't fulfill your expectations. This is the same as in ADO. During Update, if any of the xxxCommand properties is not set but primary key information is present, the Command object will be automatically generated. Notice that for this to work, having a primary key set for the involved data tables is mandatory.

The following code shows how to set a primary key for the EmployeesList table of a DataSet:

DataColumn[] keys = new DataColumn[1];
keys[0] = m_oDS.Tables["EmployeesList"].Columns["EmployeeID"];
m_oDS.Tables["EmployeesList"].PrimaryKey = keys;

A primary key is basically an array of DataColumn objects.

If you want to use a stored procedure to update a table, or if you're working against a proprietary, non-SQL data provider, you will be making frequent use of these command properties.


Ian

[KarlMoore]

Hey there Gang!

Just a quick update message. I actually sorted this problem a while ago - you *can* do it with SQL Server without writing full parameters queries and all that jazz.

The solution isn't too difficult (thankfully) - and I'll be covering it all in my "VB.NET Uncovered: Doing Data" feature, which'll be posted on the site in about... uhm... three weeks or so.

Still, it was a bloomin' puzzler - even I had to get unofficial help from the source. D'OH!!!

Thanks for your help, gang!

- - - Karlos

[deano1]

This is all good and well, but how do you update a database using ole db?

[Frans C]

I hope you noticed that this is quite an old thread. I guess it is based on the Beta 1 release of visual studio.
System.Data.ADO is now called System.Data.OleDB (and if you use SQL server, you could use System.Data.SqlClient instead)
ADODataSetCommand is now called OleDBDataAdapter.
And all the other objects starting with ADO will now start with OleDB. You should try the code to see if anything else has changed.

But you can find some samples on Microsoft's MSDN site. eg Using ADO.NET (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/Dndotnet/html/Usingadonet.asp?frame=true)

[deano1]

maybe you can help me then, in short im trying to learn how to update tables. The code below always errors out on the .fill line which to me would indicate there something from with the sql statements. See if you can find the error, it would be much appreciated!

Dim db As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\helpdesk.mdb;")
Dim faultsDA As OleDbDataAdapter = New OleDbDataAdapter("update uniqueIdent, userName FROM faults Where uniqueIdent = 124 ", db)

faultsDA.UpdateCommand = New OleDbCommand("UPDATE fault SET userName = " & _
"WHERE uniqueIdent = 124", db)

Dim faultsDS As DataSet = New DataSet()

db.Open()
faultsDA.Fill(faultsDS, "faults")

Dim faultsRow As System.Data.DataRow

For Each faultsRow In faultsDS.Tables("faults").Rows

faultsRow("userName") = TextBox1.Text
faultsRow("userName") = "dean"
faultsRow.AcceptChanges()
Next faultsRow

faultsDA.Update(faultsDS, "faults")
MsgBox("finito !")

Thanks!!

[Frans C]

Although I haven't tested your code, I can point out some mistakes:

The constuctor of the OleDBDataAdapter accepts only select statements. This is the statement that is used to fill the dataset.
So the line:
Dim faultsDA As OleDbDataAdapter = New OleDbDataAdapter("update uniqueIdent, userName FROM faults Where uniqueIdent = 124 ", db)
should be something like:
Dim faultsDA As OleDbDataAdapter = New OleDbDataAdapter("select uniqueIdent, userName FROM faults Where uniqueIdent = 124 ", db)

Your updatecommand is missing a parameter.
faultsDA.UpdateCommand = New OleDbCommand("UPDATE fault SET userName = " & _
"WHERE uniqueIdent = 124", db)
should be something like:
faultsDA.UpdateCommand = New OleDbCommand("UPDATE fault SET userName = @User_Name" & _
"WHERE uniqueIdent = 124", db)


If all you want to do is execute an update sql statement, an OleDbDataAdapter is a bit of overkill.
The easiest way is to just create a command object, and run the ExecuteNonQuery method.

eg.
Dim db As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\helpdesk.mdb;")
Dim faultsCO As OleDbCommand = New OleDbCommand("UPDATE fault SET userName = '" & TextBox1.Text & _
"' WHERE uniqueIdent = 124", db)
db.Open()
faultsCO.ExecuteNonQuery()
db.Close()

[deano1]

Hi Frans, thanks for your reply. Only thing is i tried the code as shown below, but keeps erroring out on the faultsCO.ExecuteNonQuery() line. Which to me would indicate a prob with sql syntax, i can't see it maybe you can. The error i recieve is An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll.

by the way whats Frans short for?

Thanks!!!

Private Sub RadioButton1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton1.CheckedChanged

Dim var1 As String
var1 = "testing"

Dim db As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\helpdesk.mdb;")
Dim faultsCO As OleDbCommand = New OleDbCommand("UPDATE fault SET userName = '" & var1 & _
"' WHERE userProblem = 149", db)

db.Open()
faultsCO.ExecuteNonQuery()
db.Close()

End Sub

[Frans C]

Sorry for the late reply, but I don't have VB.NET at my work, and at home I don't always feel like coding.

I tested your code, and I could not find a problem.
I created an Access database with the name helpdesk.mdb, created a table with the name fault, and two fields: userProblem (Long Integer) and userName (text, 50). I inserted a record with userProblem 149.

Then I copied your code, and added two radiobuttons. It worked like a charme.
The only things I can think about are that the userProblem field may not be numeric (if it is a text field, you need to add quotes), or the userName is limited to 6 characters or less (you try to update it to the value "testing", which is 7 characters), or maybe some other weird problems, like a recordlock, insufficient privileges etc.

I know this is far fetched, but it might help if you try to find out which error you get. You could add a Try block (which should be there anyway), and catch the exception. The exception can give you a lot of information.
eg:

Try
faultsCO.ExecuteNonQuery()
Catch ex As System.Data.OleDb.OleDbException
MsgBox(ex.Message)
MsgBox(ex.ToString)
End Try



By the way, Frans is a Dutch name, and comes from Franciscus. Like Francis or Frank in english, or Franz in German, or François in French.