DataGrid Update not Updating the DataSource

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

am trying to updated the source data(Access2000) for my data grid. But keep getting this error

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dl

Additional information: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information

I can get the error message to disapper by using the AcceptChanges action to the dataset, but this still does not update the Access file

The Dataset has all of the information in it, but I can not figure this one out. Please help.

I know the Dataset has the corrections in it because, I created another DataGrid and used the same dataset and I see the corrections

WindowsForm DataGrid. MDAC 2.8. .Net200

thanks in advance
 
Hi Bill,

OledB or ODBC?

Cor
An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: Dynamic SQL generation for the UpdateCommand is
Click to expand...
not supported against a SelectCommand that does not return any key column
information.
I can get the error message to disapper by using the AcceptChanges action
Click to expand...
to the dataset, but this still does not update the Access file.
The Dataset has all of the information in it, but I can not figure this one out. Please help..

I know the Dataset has the corrections in it because, I created another
Click to expand...
DataGrid and used the same dataset and I see the corrections.
 
Bill Salling said:
am trying to updated the source data(Access2000) for my data grid. But
keep getting this error:

An unhandled exception of type 'System.InvalidOperationException'
occurred in system.data.dll

Additional information: Dynamic SQL generation for the UpdateCommand
is not supported against a SelectCommand that does not return any key
column information.
Click to expand...

So have you tried giving a SelectCommand that *does* return key
information?
I can get the error message to disapper by using the AcceptChanges
action to the dataset, but this still does not update the Access file.
Click to expand...

That's because AcceptChanges effectively says, "The DataSet is now up-
to-date with the database."
The Dataset has all of the information in it, but I can not figure
this one out. Please help..

I know the Dataset has the corrections in it because, I created
another DataGrid and used the same dataset and I see the corrections.
Click to expand...

You need to either specify an UpdateCommand explicitly, or give the
SelectCommand enough information so that the UpdateCommand can be built
automatically.
 
I have attached the code that checks to see if Dataset has changes. If
so run Update. You will see that I remarked the xdata.acceptchanges().
This is where I get the before mentioned error.

StrQuery = "SELECT UnResolved_Pipe.Joint, UnResolved_Pipe.Heat,
UnResolved_Pipe.MillWeld, UnResolved_Pipe.FileName,
UnResolved_Pipe.Spread, UnResolved_Pipe.JobNumber" & _
" FROM UnResolved_Pipe" & _
" WHERE ((UnResolved_Pipe.Spread)=" & Chr(34) & Spread &
Chr(34) & ")"
Dim mdbadapter As New OleDb.OleDbDataAdapter
Dim custcb As OleDb.OleDbCommandBuilder = New
OleDb.OleDbCommandBuilder(mdbadapter)

mdbadapter.SelectCommand = New OleDb.OleDbCommand(StrQuery,
mdbConnect)


' Check for changes with the HasChanges method first.
If Not UnrsvData.HasChanges(DataRowState.Modified) Then Exit Sub
' Create temporary DataSet variable.
Dim xDataSet As DataSet
' GetChanges for modified rows only.
xDataSet = UnrsvData.GetChanges(DataRowState.Modified)
' Check the DataSet for errors.
If xDataSet.HasErrors Then
MsgBox("errors")
End If
' After fixing errors, update the data source with the
DataAdapter
' used to create the DataSet.
'xDataSet.AcceptChanges()
Dim myDataView As DataView = New
DataView(xDataSet.Tables("unresolved_pipe"))
Dim bill As Integer
bill = myDataView.Count

mdbadapter.Update(xDataSet, "UnResolved_Pipe")
 
Hi William,

Maybe you can try this first?

StrQuery = "SELECT UnResolved_Pipe.Joint, UnResolved_Pipe.Heat,
UnResolved_Pipe.MillWeld, UnResolved_Pipe.FileName,
UnResolved_Pipe.Spread, UnResolved_Pipe.JobNumber" & _
" FROM UnResolved_Pipe" & _
" WHERE ((UnResolved_Pipe.Spread)=" & Chr(34) & Spread &
Chr(34) & ")"

Dim mdbadapter As New OleDb.OleDbDataAdapter(StrQuery, mdbConnect)

Dim custcb As New OleDb.OleDbCommandBuilder(mdbadapter)
Try
If UnrsvData.HasChanges Then
mdbadapter.update(UnrsvData)
end if
catch
here you can put your check and repair when there is an error, to test
it I would test it in this situation first without it, and add it when it
goes normaly good.
end try

Know that in this situation the acceptchanges is done as last action
automaticly by the dataadapter and therefore you cannot get anymore the
changes, when you are doing it like this.

But as I said just as a test.

Cor
 
William Salling said:
I have attached the code that checks to see if Dataset has changes. If
so run Update. You will see that I remarked the xdata.acceptchanges().
This is where I get the before mentioned error.
Click to expand...

So what is the key in the UnResolved_Pipe table?
 
William Salling said:
Unresolved_Pipe.ID
Click to expand...

.... which doesn't appear in your select statement, as far as I can see.
Put it in there, and everything should be fine.
 
Back
Top