OleDbDataAdapter Update failure

[Al]

Hi,
I need to update tables in access 97. The table names have spaces (not my
choice). My update fails even though I use the OleDbCommandBuilder.
Here is a code I am using

myDataAdapter = New OleDbDataAdapter
myDataAdapter.SelectCommand = New OleDbCommand("Select * from [Patient
Table]", MyConnection)
Dim cb As OleDbCommandBuilder = New
OleDbCommandBuilder(PatientsBillingDataAdapter)

‘Updating dataset ……….
myDataAdapter.Update(aDataset, aTableName)

I get syntax error in update statement…

Any suggestion is very much appreciated

Thanks

Al

[Al]

the last line of code is
Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(myDataAdapter )
"Al" wrote:

Hi,
I need to update tables in access 97. The table names have spaces (not my
choice). My update fails even though I use the OleDbCommandBuilder.
Here is a code I am using

myDataAdapter = New OleDbDataAdapter
myDataAdapter.SelectCommand = New OleDbCommand("Select * from [Patient
Table]", MyConnection)
Dim cb As OleDbCommandBuilder = New
OleDbCommandBuilder(PatientsBillingDataAdapter)

‘Updating dataset ……….
myDataAdapter.Update(aDataset, aTableName)

I get syntax error in update statement…

Any suggestion is very much appreciated

Thanks

Al

[Charlie]

If you look at the OleDbCommandBuilder GetUpdateCommand.CommandText property,
you will see that it places question marks where the OleDbParameters would
assign the values.

The next question on this board is similar to yours. You might like to read
my answer there.

"Al" wrote:

Hi,
I need to update tables in access 97. The table names have spaces (not my
choice). My update fails even though I use the OleDbCommandBuilder.
Here is a code I am using

myDataAdapter = New OleDbDataAdapter
myDataAdapter.SelectCommand = New OleDbCommand("Select * from [Patient
Table]", MyConnection)
Dim cb As OleDbCommandBuilder = New
OleDbCommandBuilder(PatientsBillingDataAdapter)

‘Updating dataset ……….
myDataAdapter.Update(aDataset, aTableName)

I get syntax error in update statement…

Any suggestion is very much appreciated

Thanks

Al

[Al]

Hi Charlie,
The problem as you pointed out is table name [patient Table] where it
convert it to '[Patient Table]' and then cannot find it. I didn't follow
Update is there a sample code out there i can use? I am at lost
Thanks
Al

"Charlie" wrote:

If you look at the OleDbCommandBuilder GetUpdateCommand.CommandText property,
you will see that it places question marks where the OleDbParameters would
assign the values.

The next question on this board is similar to yours. You might like to read
my answer there.

"Al" wrote:

Hi,
I need to update tables in access 97. The table names have spaces (not my
choice). My update fails even though I use the OleDbCommandBuilder.
Here is a code I am using

myDataAdapter = New OleDbDataAdapter
myDataAdapter.SelectCommand = New OleDbCommand("Select * from [Patient
Table]", MyConnection)
Dim cb As OleDbCommandBuilder = New
OleDbCommandBuilder(PatientsBillingDataAdapter)

‘Updating dataset ……….
myDataAdapter.Update(aDataset, aTableName)

I get syntax error in update statement…

Any suggestion is very much appreciated

Thanks

Al

[Charlie]

For your Select, write your own SELECT statement, and WHERE clause, using the
"[]" where needed. Pass the SELECT statement and your Connection as
parameters to a DataAdapter. Then use the DataAdapter.Fill method, passing
the DataTable you want to fill as a parameter to the DataAdapter. If the
DataTable has been filled before, use DataTable.Rows.Clear first. When you
use DataAdapter.Fill, pass the Connection in the Closed state. The
DataAdapter will open and close it for you.

To write to the database, use a Command object. Create an UPDATE, INSERT,
or DELETE statement by concatenating the values to be affected into the
syntax of the statement. The syntax of any of those statements is easy to
find on the web. BE SURE to use a WHERE clause (e.g. WHERE PrimaryKey =
1000) for UPDATE or DELETE. Pass the statement and the Connection to the
Command object as you instantiate the Command. Open the Connection. Use
YourCommand.ExecuteNonQuery. Close the Connection.

Also, I tested to make sure that a space in a bracketed table name would
work, and it did. Put the YourCommand.ExecuteNonQuery in a Try Block and see
what error you get. Post your exact statement in a new thread if there is a
syntax error that you can't resolve. Be sure to use single quotes for string
values, no quotes for numeric, #date# for dates.

You don't need the CommandBuilder. You don't need to have the DataAdapter
involved when writing back to the database, just the OleDbCommand.

"Al" wrote:

Hi,
I need to update tables in access 97. The table names have spaces (not my
choice). My update fails even though I use the OleDbCommandBuilder.
Here is a code I am using

myDataAdapter = New OleDbDataAdapter
myDataAdapter.SelectCommand = New OleDbCommand("Select * from [Patient
Table]", MyConnection)
Dim cb As OleDbCommandBuilder = New
OleDbCommandBuilder(PatientsBillingDataAdapter)

‘Updating dataset ……….
myDataAdapter.Update(aDataset, aTableName)

I get syntax error in update statement…

Any suggestion is very much appreciated

Thanks

Al

[Charlie]

For your Select, write your own SELECT statement, and WHERE clause, using the
"[]" where needed. Pass the SELECT statement and your Connection as
parameters to a DataAdapter. Then use the DataAdapter.Fill method, passing
the DataTable you want to fill as a parameter to the DataAdapter. If the
DataTable has been filled before, use DataTable.Rows.Clear first. When you
use DataAdapter.Fill, pass the Connection in the Closed state. The
DataAdapter will open and close it for you.

To write to the database, use a Command object. Create an UPDATE, INSERT,
or DELETE statement by concatenating the values to be affected into the
syntax of the statement. The syntax of any of those statements is easy to
find on the web. BE SURE to use a WHERE clause (e.g. WHERE PrimaryKey =
1000) for UPDATE or DELETE. Pass the statement and the Connection to the
Command object as you instantiate the Command. Open the Connection. Use
YourCommand.ExecuteNonQuery. Close the Connection.

Also, I tested to make sure that a space in a bracketed table name would
work, and it did. Put the YourCommand.ExecuteNonQuery in a Try Block and see
what error you get. Post your exact statement in a new thread if there is a
syntax error that you can't resolve. Be sure to use single quotes for string
values, no quotes for numeric, #date# for dates.

You don't need the CommandBuilder. You don't need to have the DataAdapter
involved when writing back to the database, just the OleDbCommand.

"Al" wrote:

Hi,
I need to update tables in access 97. The table names have spaces (not my
choice). My update fails even though I use the OleDbCommandBuilder.
Here is a code I am using

myDataAdapter = New OleDbDataAdapter
myDataAdapter.SelectCommand = New OleDbCommand("Select * from [Patient
Table]", MyConnection)
Dim cb As OleDbCommandBuilder = New
OleDbCommandBuilder(PatientsBillingDataAdapter)

‘Updating dataset ……….
myDataAdapter.Update(aDataset, aTableName)

I get syntax error in update statement…

Any suggestion is very much appreciated

Thanks

Al