Dynamic SQL generation for the UpdateCommand.. (1 Viewer)

neoklis

Registered User.
Local time
Today, 12:58
Joined
Mar 12, 2007
Messages
80
Hi guys
I need your help..
When I close the form I get the message “Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.” at line obj_oledb_da.Update(dataSet). Can someone provide help..?

Thank you

Private selectConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test\;Extended Properties='text;HDR=Yes;FMT=TabDelimited';")
Private filetable As String
Private selectCommandText As String
Private dataSet As New DataSet

Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing

If dataSet.HasChanges() Then

Dim obj_oledb_da As System.Data.OleDb.OleDbDataAdapter
Dim command_builder As System.Data.OleDb.OleDbCommandBuilder
filetable = "data.txt"
selectCommandText = "select * from [" & filetable & "]"

obj_oledb_da = New OleDbDataAdapter(selectCommandText, selectConnection)
obj_oledb_da.TableMappings.Add("Table", "Data")
command_builder = New OleDbCommandBuilder(obj_oledb_da)

obj_oledb_da.Update(dataSet)

End If
End Sub
 

MarkK

Super Moderator
Local time
Today, 02:58
Joined
Mar 17, 2004
Messages
7,761
The command builder needs to find a column that has a unique ID otherwise it can't establish with any certainty which record it ought to update. In this case it fails by design, and since it appears that you are wanting to update a text file it will always fail, since all fields will be strings and none will be identified as a Primary Key. You can avoid the command builder and specify a custom Update Command, but if you have two records that are exactly the same you will never be able to just update one of them. With two identical records the WHERE clause of an update command will always update both records.
 

smithlanger

New member
Local time
Today, 02:58
Joined
Feb 15, 2010
Messages
2
Its a common SQL generation problem. I am a victim as well.
 

Users who are viewing this thread

Top Bottom