VB>NET app interfacing with WebSystem

[dazlerd]

Hi

I've got a few problems concerning how to interface with a web side database that is currently maintained by a php web site.

At the moment I am trying to maintain a table full of standing data from within my VB.NET app. I have downloaded all the remote currencies into my local db. The remote db has PK on currencyId which is also an autonumber. My local PK is currencyId but this has no autonumber.

The 1st problem arose when I tried to create a new currency locally using a datagrid. As currencyId isnt a autonumber the update failed. Fair Enough.

But then I started thinking about how the whole process can work. I think a limitation would be to only allow changes to be made locally. e.g. Remove the currency page from the website.

But if i have 10 currencys locally and remotely and I create an extra 2 (id 11, 12) and then removed id=11 before saving, the code would try to create a currency with id = 12 on the remote web site db. As this is an autonumber it would change it to id =11 so the remote and the local db would be out of sync.

Should I remove the autonumber from the remote db?

Has anybody done anything like this before? Any tips from anyone would be good !

Thanks

Darren

 

[dazlerd]

I changed the remote currency table to be a normal integer for the PK and the access db to be autonumber on the PK.

In this instance this worked fine as the PK ids on the website were 1 to 10.

In my next table i only have 1 record with id 200. This is because all the rest have been deleted. If I download this it will have a new PK id of 1 in my local table.

What about if neither table (remote or local) has an autonumber and its up to the user to specify an appropriate id?

Or on insert to the datatable through the tableadapter I get the max(id) and increment this for the new id? From the insert statement of the TableAdapter:

INSERT INTO CubeCart_currencies
                      (currencyId, name, code, symbol, [value], decPlaces, active)
VALUES     (SELECT     MAX(currencyId) + 1
                       FROM          CubeCart_currencies, ?, ?, ?, ?, ?, ?)

Ive added the "SELECT MAX(currencyId) + 1 FROM CubeCart_currencies" for the 1st parameter hoping it would return a unique Id for the PK to use. Instead I get error

Error in values list in INSERT INTO clause.
Unable to parse query text.

Any ideas please?

Darren