SQLCE, DataAdaptors, MultipleTables, Updates and Inserts

  • Thread starter Thread starter Paul [Paradise Solutions]
  • Start date Start date
P

Paul [Paradise Solutions]

Hey all

I know the above subjects have all been covered one way or another, but
not always all together and fewer still that answer the question.

Usual situation - one *SQLCE* database two tables requiring updates,
implimenting a 'save as you go' entry system.

One main question is that in some posts I've read about using multiple
update queries with the DataAdapter.Update method, but as SQLCE doesn't
allow batched commands I couldn't see how this could be done.

I was also thwarted when trying to use two DataAdapters when I realised
that the first Adapter locks the database.

Using one Adapter and populating two datatables is easy enough, but then
you're back to how to get the database to update if there's inly one
command behind the update and insert commands of the update method.

Any input apprieciated.


Paul
 
Paul said:
I know the above subjects have all been covered one way or another, but
not always all together and fewer still that answer the question.

Usual situation - one *SQLCE* database two tables requiring updates,
implimenting a 'save as you go' entry system.

One main question is that in some posts I've read about using multiple
update queries with the DataAdapter.Update method, but as SQLCE doesn't
allow batched commands I couldn't see how this could be done.

I was also thwarted when trying to use two DataAdapters when I realised
that the first Adapter locks the database.

Using one Adapter and populating two datatables is easy enough, but then
you're back to how to get the database to update if there's inly one
command behind the update and insert commands of the update method.

Any input apprieciated.
Click to expand...

Do one update and then the other, within a transaction if you want to.

Not sure what you mean by saying that "the first adapter locks the
database" - I've never seen that, unless you're trying to use the same
connection from two different threads at the same time.
 
Jon said:
Do one update and then the other, within a transaction if you want to.

Not sure what you mean by saying that "the first adapter locks the
database" - I've never seen that, unless you're trying to use the same
connection from two different threads at the same time.
Click to expand...

How you mean? As in (don't mean to dumb it down - just getting me head
round it clearly):

Set multitable select query
Fill tables
Do table inserts/updates
Set Insert/Update commands on adapter for table1
Update
Set Insert/Update commands on adapter for table2
Update



Paul
 
Jon said:
Do one update and then the other, within a transaction if you want to.

Not sure what you mean by saying that "the first adapter locks the
database" - I've never seen that, unless you're trying to use the same
connection from two different threads at the same time.
Click to expand...

My First reply to this seemed to vanish, so posting again:


How you mean? As in (don't mean to dumb it down - just getting me head
round it clearly):

Set multitable select query
Fill tables
Do table inserts/updates
Set Insert/Update commands on adapter for table1
Update
Set Insert/Update commands on adapter for table2
Update



Paul
 
Paul said:
My First reply to this seemed to vanish, so posting again:

How you mean? As in (don't mean to dumb it down - just getting me head
round it clearly):

Set multitable select query
Fill tables
Do table inserts/updates
Set Insert/Update commands on adapter for table1
Update
Set Insert/Update commands on adapter for table2
Update
Click to expand...

Well, I'd suggest:

Set up adapter 1 and commands
Set up adapter 2 and commands

then later:

Fill tables
Modify tables
Open connection
Begin transaction
Set connection and transaction for commands in both adapters
Call update in adapter 1
Call update in adapter 2
Commit transaction
Close connection
 
Jon said:
Well, I'd suggest:

Set up adapter 1 and commands
Set up adapter 2 and commands

then later:

Fill tables
Modify tables
Open connection
Begin transaction
Set connection and transaction for commands in both adapters
Call update in adapter 1
Call update in adapter 2
Commit transaction
Close connection
Click to expand...
I did try using the two adapters, but as I said, as soon as one adapter
is used to fill a table, the fill on the other fails due to a locked DB
(adapter setup works ok, just using them causes the issue) - I'm running
in shared subs if that makes any diference, no additional threading.
 
Jon said:
Well, I'd suggest:

Set up adapter 1 and commands
Set up adapter 2 and commands

then later:

Fill tables
Modify tables
Open connection
Begin transaction
Set connection and transaction for commands in both adapters
Call update in adapter 1
Call update in adapter 2
Commit transaction
Close connection
Click to expand...
Please disregard last post - as per ususal two days of staring at my
code and I find the problem ~after~ I click send.. always the same...

Many thanks for your input on the solution though.


Paul
 
Back
Top