ado.net update joined dataset

[DebbieInFlorida]

I have been on this for a week, with no luck, I keep getting the same error at the same place. I have gone over all the forums, please give me some help on this...
Basically, I join 3 tables together for reporting, I update one of the tables while reading thru, after I want the updates to stay.

The error is something like "Update requires valid update command on Datarow"

I am in vb.net 9.2, any help is very much appreciated.. thanks



Private OLE_DataAdapter As OleDb.OleDbDataAdapter
Private OLE_DataSet As New DataSet
Private OLE_CN As OleDb.OleDbConnection

private Sub DoStuff()

dim pDT as DataTable
pDT =
Get_Joined_Tables(sQ1, sQ2, sQ3, "2w_1", mTOTAL_PA_FILE, "p3704")
'== do some updates here
Dim DS1 As New DataSet
' Dim builder As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(OLE_DataAdapter) '** Doesn't work with or without this
DS1 = OLE_DataSet.GetChanges

OLE_DataAdapter.Update(DS1) , "P3704")
OLE_DataSet.AcceptChanges() '*** ERROR HERE
OLE_CN.Close()

end sub

Private function Get Joined_Tables(sQuerys and Tablenames are passed in here) as datatable

Dim OLE_Command As OleDb.OleDbCommand
Dim OLE_DataTable As DataTable = Nothing



Dim sConnect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE IV;User ID=Admin;Password=;Data Source=" & sFilePath

Try

OLE_CN = New OleDb.OleDbConnection(sConnect)
OLE_CN.Open()
OLE_Command = New OleDb.OleDbCommand(sQuery1, OLE_CN)
OLE_DataAdapter = New OleDb.OleDbDataAdapter(OLE_Command)

OLE_DataAdapter.Fill(OLE_DataSet, sTableName1)

OLE_Command = New OleDb.OleDbCommand(sQuery2, OLE_CN)
OLE_DataAdapter = New OleDb.OleDbDataAdapter(OLE_Command)
OLE_DataAdapter.Fill(OLE_DataSet, sTableName2)

OLE_Command = New OleDb.OleDbCommand(sQuery3, OLE_CN)
OLE_DataAdapter = New OleDb.OleDbDataAdapter(OLE_Command)
OLE_DataAdapter.Fill(OLE_DataSet, sTableName3)

Dim col(1) As DataColumn

'== set a primary key to table for updating ( doesn't work with or without this)
col(0) = OLE_DataSet.Tables(2).Columns(0)
OLE_DataSet.Tables(2).PrimaryKey = col

OLE_DataSet.Relations.Add("ds", OLE_DataSet.Tables(sTableName1).Columns("Value"), OLE_DataSet.Tables(sTableName2).Columns("Value"), False)

OLE_DataSet.Relations.Add("ds2", OLE_DataSet.Tables(sTableName2).Columns("Value"), OLE_DataSet.Tables(sTableName3).Columns("Value"), False)


Return OLE_DataSet.Tables(0)

end function

[mickey_pt]

Looking to the code you don't have any Update command.

You need:
1º - Garante that the each one of the tables have or a priary key or a unique key
2º - Create one or three OledDCommandBuilder and get the Update commands for each table

And after this you can call the Update method

[DebbieInFlorida]

thanks so much for speedy reply. I added a primary key to each of the tables, and got this error on the Update line


Process custom dynamic SQl generation for the updatecommand is not suported against a Selectcommand that does not return any key column information.

The tables all have the same fields, so the primary key is the same for each, it is also the joining field, so I don't see why this should be a problem. I am getting desperate here with this problem..
thanks

[mickey_pt]

The error tells everything, the Selectcommand that does not return any key column information, so the PK isn't returned correctly by the Select command.

The PK must exist in the original table before the select and the select must return it.

[DebbieInFlorida]

I really appreciate your quick help, but I am still lost and more desparate.
These tables are created during the application by exporting data to dbf. I tried what you said and thru code accessed each table, tried to set a primary key(see code below). Then I do the previous code to join and update. I still get the error. Does anyone actually have a sample near what I am doing here.
I check in the set key sub and there is a key, but in the join function, there is no primary key in the tables. How do I get it to stick??

Remember these are not in a database, but are .dbf files (client reasons, not mine).

Public Sub Set_ADO_Datatable_Key(ByVal sQuery As String, _
ByVal sTableName As String, _
ByVal sFilePath As String, _
ByVal sFileName As String)


Dim OLE_CN As OleDb.OleDbConnection
Dim OLE_Command As OleDb.OleDbCommand
Dim OLE_DataAdapter As OleDb.OleDbDataAdapter
Dim pDataSet As New DataSet
Dim pDataTable As DataTable
Dim col(1) As DataColumn

Dim sConnect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE IV;User ID=Admin;Password=;Data Source=" & sFilePath

Try

OLE_CN = New OleDb.OleDbConnection(sConnect)
OLE_CN.Open()
OLE_Command = New OleDb.OleDbCommand(sQuery, OLE_CN)
OLE_DataAdapter = New OleDb.OleDbDataAdapter(OLE_Command)

OLE_DataAdapter.Fill(pDataSet, sTableName)
'== set the primary key before querying

col(0) = pDataSet.Tables(sTableName).Columns("VALUE")
pDataSet.Tables(sTableName).PrimaryKey = col


Catch ex As Exception
MsgBox("ERROR: " & m_Current_Address & " - Get ADO Datatable - " & ex.Message)
Finally
If OLE_CN.State = ConnectionState.Open Then
OLE_CN.Close()
OLE_CN = Nothing
OLE_Command = Nothing
OLE_DataAdapter = Nothing
pDataTable = Nothing
End If


End Try
End Sub

[mickey_pt]

The problem it's that, the tables don't have any by default...

You're saying that you create the tables at runtime by exporting data? Do you create the DBF tables or the tables are already created?

MSDN

[Shaggy Hiker]

I don't see the actual SQL, and maybe it doesn't matter. You say that the tables are joined. The CommandBuilder states that it will automatically generate the Update, Insert, and Delete commands, but ONLY if the SELECT statement maps to a single database table. If you have Joins, the CommandBuilder won't generate the other commands.

[mickey_pt]

If she generates for each table adapter the commands i think the update will work...
And she says that has one SQL for each table and then in memory she creates the relations between the tables.

But i'm just learning...

[Shaggy Hiker]

Yeah, I'm unclear on what is meant by "joining" in this case. However, I never thought of Debbie as being a he.

[DebbieInFlorida]

Ha HA, I am not a HE for sure.
THanks for the interest, I am getting quite desparate now, my project is way overdue.
I use SQL only on the Select statements that I guess did not show up in the first post. the "sQuery1" were "Select * From table", this was done for each table, they joined on the first record "Value". I don't know how else to do this, I am getting all the data perfectly for reporting, but I have to update the last table during the process.
If this is not possible, then I will have to do something very nasty, not sure what yet???

In response to Mickeypt, the tables are created during the export function.

the tables are "joined" thru the relationship..

OLE_DataSet.Relations.Add("ds", OLE_DataSet.Tables(sTableName1).Columns("Value"), OLE_DataSet.Tables(sTableName2).Columns("Value"), False)

OLE_DataSet.Relations.Add("ds2", OLE_DataSet.Tables(sTableName2).Columns("Value"), OLE_DataSet.Tables(sTableName3).Columns("Value"), False)

thanks

[Shaggy Hiker]

That removes the issue that I was thinking of. Had the SQL statement included a JOIN, then the CommandBuilder wouldn't automatically generate the other commands based on the SELECT statement.