ADO.NET Code - Simple (?)
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:
PHP 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
Whatz the story, morning glory?
Are you saying it 'updates' as soon as you run...
PHP Code:
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