Access Table update Error

[New_Coder]

I get the following error when I try to update my access table with a datagrid An unhandled exception of type 'system.invalidoperation" occured in system.data.dll' The code I am using looks like this: If CODE_DataSet.HasChanges Then Dim CON As New OleDbConnection(CONNECT_STRING) Dim MY_SQL As String Dim CMD As OleDbCommand Dim DR As OleDbDataReader Dim data_adapter As OleDbDataAdapter Dim command_builder As OleDbCommandBuilder Dim select_string As String = "SELECT TMP_ADMIN.QUOTE_LINE_NO, TMP_ADMIN.OPTION, TMP_ADMIN.PRODUCT_CODE, TMP_ADMIN.DESCRIPTION, TMP_ADMIN.QTY, TMP_ADMIN.UNIT_COST, TMP_ADMIN.SELL, TMP_ADMIN.TAG FROM TMP_ADMIN;" CMD = New OleDbCommand(select_string, CON) data_adapter = New OleDbDataAdapter(select_string, CONNECT_STRING) data_adapter.TableMappings.Add("Table", CODE_TABLE) command_builder = New OleDbCommandBuilder(data_adapter) command_builder.GetUpdateCommand() data_adapter.Update(CODE_DataSet) End If Any ideas? is there a better way to upgrade my MSAccess tables from a datagrid? :confused:

[FeRtoll]

put those on start of class:

Dim CON As New OleDbConnection(CONNECT_STRING)
Dim MY_SQL As String
Dim CMD As OleDbCommand
Dim DR As OleDbDataReader
Dim data_adapter As OleDbDataAdapter
Dim command_builder As OleDbCommandBuilder

what for is this one?

Dim select_string As String = "SELECT TMP_ADMIN.QUOTE_LINE_NO, TMP_ADMIN.OPTION, TMP_ADMIN.PRODUCT_CODE, TMP_ADMIN.DESCRIPTION, TMP_ADMIN.QTY, TMP_ADMIN.UNIT_COST, TMP_ADMIN.SELL, TMP_ADMIN.TAG FROM TMP_ADMIN;"

you should put that in MY_SQL string up there:

Dim MY_SQL As String="SELECT TMP_ADMIN.QUOTE_LINE_NO, TMP_ADMIN.OPTION, TMP_ADMIN.PRODUCT_CODE, TMP_ADMIN.DESCRIPTION, TMP_ADMIN.QTY, TMP_ADMIN.UNIT_COST, TMP_ADMIN.SELL, TMP_ADMIN.TAG FROM TMP_ADMIN;" 'And without this one!!! i think...

The third part:

Cmd = New OleDbCommand(MY_SQL, CON)
data_adapter = New OleDbDataAdapter(select_string, CONNECT_STRING)
data_adapter.TableMappings.Add("Table", CODE_TABLE)
command_builder = New OleDbCommandBuilder(data_adapter)

command_builder.GetUpdateCommand()

data_adapter.Update(CODE_DataSet)
End If

And i dont see annywhere that u have defined the "CODE_TABLE" variable. I think i have helped you... :~ Good luck FeRtoll Software.net -------------------- I fertoll@net.hr I --------------------

[Dave Kreskowiak]

In your TMP_ADMIN table, do you have a primary key field? Are you returning that field in your SELECT statement? If not, you'll have to make one to use it with a DataAdapter. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

[New_Coder]

I adjusted the Tmp_Admin table and set quote_line_no to the primary key and am now getting this error. Any Ideas:doh: An unhandled exception of type 'System.Data.DBConcurrencyException' occurred in system.data.dll Additional information: Concurrency violation: the UpdateCommand affected 0 records.

[Dave Kreskowiak]

I ran into that once before. I can't remember what I did to cause it though... :~ Hmmmm....Well, let's start with cleaning up your code a bit:

If CODE_DataSet.HasChanges Then
Dim select_string As String = "SELECT TMP_ADMIN.QUOTE_LINE_NO, TMP_ADMIN.OPTION, " & _
"TMP_ADMIN.PRODUCT_CODE, TMP_ADMIN.DESCRIPTION, TMP_ADMIN.QTY, TMP_ADMIN.UNIT_COST, " & _
"TMP_ADMIN.SELL, TMP_ADMIN.TAG FROM TMP_ADMIN;"
Dim data_adapter As New OleDbDataAdapter(select_string, CONNECT_STRING)
Dim command_builder As New OleDbCommandBuilder(data_adapter)

data\_adapter.TableMappings.Add("Table", CODE\_TABLE)

data\_adapter.Update(CODE\_DataSet)
CODE\_DataSet.Clear()
data\_adapter.Fill(CODE\_DataSet)

End If

RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

[New_Coder]

THANKS THAT WORKED GREAT!! I APPRECIATE THE HELP!! :-O:-O

[Dave Kreskowiak]

No problem! :-D RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome