New to ADO.NET - How to update a database...

[TDS News]

Here's my code...

Private oConn As New OleDb.OleDbConnection()
Private oCommand As New OleDb.OleDbCommand()
Private oDataAdapter As New OleDb.OleDbDataAdapter()
Private oDataSet As New DataSet()

Public Event Err()

Public Sub Init()

Dim Row As DataRow

oConn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Application.StartupPath & _
"\WinMUX.mdb;Persist Security Info=False"

oCommand.CommandText = "SELECT Name FROM Objects"
oCommand.Connection = oConn
oCommand.CommandTimeout = 30
oDataAdapter.SelectCommand = oCommand
oDataAdapter.Fill(oDataSet, "Objects")

For Each Row In oDataSet.Tables("Objects").Rows
MsgBox(Row("Name"))
If Row("Name") = "Brimstone" Then
Row("Name") = "NotBrimstone"
End If
Next
oDataAdapter.Update(oDataSet, "Objects")

End Sub

I know it isn't the prettiest code but I'm just trying to figure this Data
Access stuff out in .NET. How do I update the data in the database with
what has been changed in the Dataset? I'm completely lost. With ADO you
used to just call the adoRecordset.Update method. What do you do now?



Andrew Cooper
(e-mail address removed)

 

[Miha Markic]

Hi TDS,

You should provide three other commands for adapter: insert, update, delete.
You might check
Updating the Database with a DataAdapter and the DataSet
help topic (.NET Help files) for more detailed info.

 

[Andrew Cooper]

Miha,

Thanks for the reply. I did look there. The problem I have is what to
put for the Update command. For example...

oCommand.CommandText = "UPDATE Contacts SET Name = ? WHERE ID = ?"

How do I tell it what to put in place of the question marks? I want it
to update whatever happened to have changed in the DataSet Table. What if
Records 2 and 5 were changed but nothing else? Do I have to iterate through
the entire Table and Update every single record whether it changed or not?
That seems inefficient.

Andrew

 

[Miha Markic]

Hi Andrew,

Miha,

Thanks for the reply. I did look there. The problem I have is what to
put for the Update command. For example...

oCommand.CommandText = "UPDATE Contacts SET Name = ? WHERE ID = ?"

How do I tell it what to put in place of the question marks?

That's why there are paremeters - the order is significant it this case.
I suggest you to create an adapter by data wizard and see the code it has
generated.

I want it

to update whatever happened to have changed in the DataSet Table. What if
Records 2 and 5 were changed but nothing else? Do I have to iterate through
the entire Table and Update every single record whether it changed or not?
That seems inefficient.

No, you don't have to.
This is handled with dataadapter.update method (it updates only modified
rows).
You can even extract all changed rows by calling GetChanges() method.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

Andrew

 

[Ryan Ternier]

Andrew,

Here's some of the Code i've used before to insert data into a DB, and
return the new ID that is has generated. Hopefully it will help you

Dim conMyData As New
SqlConnection(ConfigurationSettings.AppSettings("MyConnection"))
Dim cmdUpdate As SqlCommand
Dim strUpdate As String

Try
cmdUpdate = New SqlCommand("_adminUpdateDB", conMyData)
cmdUpdate.CommandType = CommandType.StoredProcedure
cmdUpdate.Parameters.Add("@dteCreated", objPDFData.dteCreated)
cmdUpdate.Parameters.Add("@decOwedToVS", objPDFData.decOwedToVS)
cmdUpdate.Parameters.Add("@vchFilename", objPDFData.strguidFileName)
cmdUpdate.Parameters.Add("@intID", SqlDbType.Int).Direction =
ParameterDirection.Output)

conMyData.Open()
cmdUpdate.ExecuteNonQuery()
objPDFData.strID = "RB-" & CStr(cmdUpdate.Parameters("@intID").Value)
conMyData.Close()

Catch ex As Exception
Debug.WriteLine(ex.ToString)
ExceptionManager.Publish(ex)

Finally
If conMyData.State <> ConnectionState.Closed Then conMyData.Close()
conMyData = Nothing
cmdUpdate.Dispose()
cmdUpdate = Nothing

End Try

----------------------------------------------------------------------------
--------------------------------------------

And Here is the code for the Stored Procedure I'm using.



CREATE PROCEDURE _adminUpdateDB
(
@dteCreated datetime, @decOwedToVS decimal, @vchFilename varchar(200),
@intID int output
)
AS

INSERT INTO tblInvoice (guidPDFLocation, dteCreated, decOwedToVS)
VALUES (@vchFilename, @dteCreated, @decOwedToVS)

SELECT @intID = SCOPE_IDENTITY()
FROM tblInvoice
GO

 

[William \(Bill\) Vaughn]

I would heartily recommend that you get one of the fine books on ADO.NET
programming. There are about 30 of them (I know, I wrote a couple of them).
All of them cover this in far more detail that we could explain here and you
would come away with a better understanding of ADO.NET. We really don't mind
you asking questions--that's what we're here for, but do a bit of studying
first--even if it's just the documentation. There are plenty of walkthroughs
in the MSDN doc that come with Visual Studio.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Miha,

Thanks for the reply. I did look there. The problem I have is what to
put for the Update command. For example...

oCommand.CommandText = "UPDATE Contacts SET Name = ? WHERE ID = ?"

How do I tell it what to put in place of the question marks? I want it
to update whatever happened to have changed in the DataSet Table. What if
Records 2 and 5 were changed but nothing else? Do I have to iterate through
the entire Table and Update every single record whether it changed or not?
That seems inefficient.

Andrew