Edit Access database - messy code

  • Thread starter Thread starter Graeme
  • Start date Start date
G

Graeme

Hi

I've managed to edit database in a roundabout way. Really slow and needs
refinement (better still, replacement with something decent!) ...

Private Sub bRecalcODRC_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles bRecalcODRC.Click 'update ODRC
Dim myFeedersDA As OleDbDataAdapter = New OleDbDataAdapter()
Dim myFeedersDS As DataSet = New DataSet()
Dim myFeedersRow As DataRow
Dim myConnectString As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
tbxDataPath.Text)
Dim myFeedersSQL As OleDbCommand = New OleDbCommand("SELECT * FROM
NETWORK_DATA_EQUIPMENT;", myConnectString)
myFeedersSQL.CommandTimeout = 30
myFeedersDA.SelectCommand = myFeedersSQL
myConnectString.Open()
myFeedersDA.Fill(myFeedersDS, "NETWORK_DATA_EQUIPMENT")
myConnectString.Close()
Dim myFeedersTable As DataTable =
myFeedersDS.Tables("NETWORK_DATA_EQUIPMENT")

For Each myFeedersRow In myFeedersTable.Rows

Dim myValue
myValue = 1
Dim myUpdateCommand1 As New OleDbCommand("UPDATE NETWORK_DATA_EQUIPMENT SET
RemoteMult = " & myValue & " WHERE Equipment_ID = " &
myFeedersRow.Item("Equipment_ID"), myConnectString)
myUpdateCommand1.Connection.Open()
myUpdateCommand1.ExecuteNonQuery()
myUpdateCommand1.Connection.Close()
myValue = 3.5
Dim myUpdateCommand2 As New OleDbCommand("UPDATE NETWORK_DATA_EQUIPMENT SET
OptFactor = " & myValue & " WHERE Equipment_ID = " &
myFeedersRow.Item("Equipment_ID"), myConnectString)
myUpdateCommand2.Connection.Open()
myUpdateCommand2.ExecuteNonQuery()
myUpdateCommand2.Connection.Close()

'.... then I do about another 5 on these, where I poke myValue into a series
of fields (RemoteMult and OptFactor are the first two). Then the whole lot
repeats through the 1000 records. So REAL SLOW!

Next

End Sub

I guess my question is, how to edit the database more efficiently? Code
please??? Maybe a link to a code snippet site ... I've hunted for ages but
all I have found is how to edit from datagrid (which I'm not using).

Thanks
Graeme
 
Graeme,
Since you seem to be updating all rows in the table why not just do a
single command, i.e.
UPDATE NETWORK_DATA_EQUIPMENT SET Equipment_ID = 1, OptFactor = 3.5;
and just ExecuteNonQuery() on it. This should be most efficient from the db
statndpoint.
Other than that just setup the Insert, Delete, Update commands for the
DataAdapter and then modify the values in each row and call Update on the
DataAdapter.
I'd suggest getting a copy of "ADO.NET Core Reference" by David Sceppa
as it has a lot of examples and explanations.

You should probably ask these types of questions in
microsoft.public.dotnet.framework.adonet which is designed for these types
of questions.

Ron Allen
 
Thanks Ron. Problem with first suggestion is that 'myValue' changes for each
record (sorry - didn't make that clear). So maybe I'll have a look at
DataAdaptor idea or hope someone else can assist further.

Cheers, Graeme
 
Got it sorted with parameters. Far faster!
Graeme said:
Thanks Ron. Problem with first suggestion is that 'myValue' changes for each
record (sorry - didn't make that clear). So maybe I'll have a look at
DataAdaptor idea or hope someone else can assist further.

Cheers, Graeme

the
Click to expand...
 
Back
Top