updating a table

[junkmail]

1. Get data from a foxpro table into a dataset
2. Makes changes to the dataset
3. Update the foxpro table with the changes to the dataset

I can get 1 & 2 to work just fine but I don't know the process to get 3 done. Any help would be appreciated. Below is the code I use to get the data from the foxpro table. So "DS" is the dataset that I makes changes to. I don't add or delete records I just modify values.

Dim cmd As New OleDbCommand
Dim conStr As String = "Provider=vfpoledb;Data Source=" & file_path.Trim & file_name.Trim & ".dbf;Collating Sequence=general"
Dim conn As OleDbConnection = New OleDbConnection(conStr)
Dim sqlStr As String = "SELECT * FROM " & file_name
Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlStr, conn)
Dim ds As DataSet = New DataSet
da.FillSchema(ds, SchemaType.Source, "data")
da.TableMappings.Add("data", "data")
da.Fill(ds, "data")

 

[jebenson]

Instead of reading all the data, making changes and trying to write it all back, I would just make the changes directly to the FoxPro table in one pass.

Dim cmd As New OleDbCommand
Dim conStr As String = "Provider=vfpoledb;Data Source=" & file_path.Trim & file_name.Trim & ".dbf;Collating Sequence=general"
Dim conn As OleDbConnection = New OleDbConnection(conStr)

Dim sqlStr As String = "SELECT <unique identifier field(s)> FROM " & file_name

Dim dtIDs as DtatTable
Dim da As OleDbDataAdapter

da = OleDbDataAdapter(sqlStr, conn)
da.Fill(dtIDs)

For Each drID as DataRow In dtIDs.Rows

sqlStr = "Update " & file_name & " Set <some field>='some value', <some other field>=12345 Where <unique identifier field(s)>=" & drIDs.Item("ID Field Name")

cmd = New OleDbCommand(sqlStr, Conn)

cmd.ExecuteNonQuery()

cmd.Dispose

Next

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[junkmail]

That is what I did on my first attempt but with files over 100,00 records it just takes to long. The only thing different with my code is I use a for next. Would your method of using the for each be faster to cycle through the records?

 

[jebenson]

No, probably not.
In this case you should use the DataAdapter's .Update command to write all the data back to the table at once.

Dim da As OleDbDataAdapter
Dim conStr As String = "Provider=vfpoledb;Data Source=" & file_path.Trim & file_name.Trim & ".dbf;Collating Sequence=general"
Dim conn As OleDbConnection = New OleDbConnection(conStr)
Dim cmd As OleDbCommand
Dim SQLStr As String

SQLStr = "Update TableName Set <some field>=@Param1, <some other field>=@Param2 Where <unique identifier field> =@Param3"

cmd = New OleDbCommand(SQLStr, conn)

'MUST add parameters in the same order they are declared in the SQL.
cmd.Parameters.Add("@Param1", OleDbType.VarChar, 25, "<some field>") 'the last parameter is the name of the related field in the table
cmd.Parameters.Add("@Param2", OleDbType.Integer, 4, "<some other field>")
cmd.Parameters.Add("@Param3", OleDbType.Integer, 4, "<unique identifier field>")

da = New OleDbDataAdapter("Select * from TableName")

da.UpdateCommand = cmd

da.Update(ds.Tables("data"))


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[junkmail]

I will give that a shot. thanks.

 

[junkmail]

Ok, I think I am a bit confused. I make the changes to my dataset and I am unclear how the code you gave me takes those changes and updates the foxpro file. Additionally the following line of code seems invalid. Any help would be great.

da = New OleDbDataAdapter("Select * from TableName")

 

[jebenson]

Oops, the line should be:

da = New OleDbDataAdapter("Select * from TableName", conn)

When you are finished making the changes to the datatable/dataset (before saving back to the database), do you call .AcceptChanges() on the datatable/dataset?



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[junkmail]

No, Should I be doing that as well?

 

[jebenson]

No, calling AcceptChanges would make the .Update not work.

As for how the code makes changes to the FoxPro file, it happens in the .Update method.

The DataAdapter needs a Command object to perform the .Update. There are 4 Command objects in the DataAdapter: SelectCommand, InsertCommand, UpdateCommand, DeleteCommand. Since you are only doing an update, not inserting or deleting records, you only need the SelectCommand and UpdateCommand, as in the code I supplied.

Let's break down the Parameters:

cmd.Parameters.Add(Parameter Name, Parameter Type, Parameter Size, Corresponding field in the database)

Parameter Name needs to be the same as the corresponding parameter in the SQL for the UpdateCommand.
Parameter Type - comes from an Enum, select the correct type for the data
Parameter Size - this can be tricky...Google is your friend
Corresponding field in the database - this is name (string) of the database field that this parameter will provide data for

If you set up the parameters properly, each row in your DataTable will provide the data for the Parameters' Values, which with the DataAdapters UpdateCommand will write the data back to the database when .Update is called.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[junkmail]

jebeson, Sorry to be a pest but below is my entire code but the foxpro table never gets updated with new information. I confirmed the dataset is getting updated. Can you take a quick look at it and let me know your thoughts.

Dim newimb As String
Dim track As String
Dim route As String
Dim file_type As String
Dim file_path As String
Dim file_name As String
Dim imb As New ENCODEFSBLib.Encode
file_type = "DBF"
file_name = "p58263"
file_path = "C:\work\programming\"
Dim cmd As OleDbCommand
Dim ds As New DataSet


Dim conStr As String = "Provider=vfpoledb;Data Source=" & file_path.Trim & file_name.Trim & ".dbf;Collating Sequence=general"
Dim conn As OleDbConnection = New OleDbConnection(conStr)
Dim sqlStr As String = "SELECT * FROM " & file_name
Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlstr, conn)
da.Fill(ds, "data")
cmd = New OleDbCommand(sqlStr, conn)
conn.Open()
For i = 0 To ds.Tables(0).Rows.Count - 1
track = (ds.Tables(0).Rows(i)("imb_num").ToString.Substring(0, 20))
route = (ds.Tables(0).Rows(i)("imb_num").ToString.Substring(19, 11))
imb.Encode(track, route)
newimb = imb.BarcodeString()

ds.Tables(0).Rows(i)("imb") = newimb
'sqlStr = "update " & file_path.Trim & file_name.Trim & " set imb =" & "'" & ds.Tables(0).Rows(i)("imb").ToString.Trim & "' where " & "'" & ds.Tables(0).Rows(i)("seq_no").ToString.Trim & "'" & "= seq_no"
Next
sqlStr = "Update p58263 Set imb=@Param1 Where seq_no=@Param3"

cmd = New OleDbCommand(SQLStr, conn)

'MUST add parameters in the same order they are declared in the SQL.
cmd.Parameters.Add("@Param1", OleDbType.VarChar, 65, "imb") 'the last parameter is the name of the related field in the table
'cmd.Parameters.Add("@Param2", OleDbType.Integer, 4, "<some other field>")
cmd.Parameters.Add("@Param3", OleDbType.VarChar, 10, "seq_no")

sqlStr = "SELECT * FROM " & file_name
cmd = New OleDbCommand(sqlStr, conn)

da.UpdateCommand = cmd

da.Update(ds.Tables("data"))
MsgBox("done")

 

[jebenson]

Here's the problem:

sqlStr = "Update p58263 Set imb=@Param1 Where seq_no=@Param3" <-- this is correct

cmd = New OleDbCommand(SQLStr, conn)

'MUST add parameters in the same order they are declared in the SQL.
cmd.Parameters.Add("@Param1", OleDbType.VarChar, 65, "imb") 'the last parameter is the name of the related field in the table
'cmd.Parameters.Add("@Param2", OleDbType.Integer, 4, "<some other field>")
cmd.Parameters.Add("@Param3", OleDbType.VarChar, 10, "seq_no")

sqlStr = "SELECT * FROM " & file_name <-- remove this line
cmd = New OleDbCommand(sqlStr, conn) <-- remove this line

You are creating the command object correctly, then overwriting it immediately afterwards with a command with select SQL, so the update SQL and parameters are no longer there.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!