Compare Remote and Local Database table feilds

[AndyGable]

Hi Everyone,

I hope someone can help me out here

I have a central Database (called SvrData) and I also have a local version of the database (called PoSDB) i have a sync app (that runs headless) that I have created to sync the data from the Local database to the server and to sync all Data from the Server to the local database.

This works fine but what I would like to do is compare data from the table called products (field Called lasteditedon) to see if the last edit date has changed if it has to run a update commands


The lasteditedon field is stored like this yyyy/mm/dd so what i want to know is how do i use VB to compare the Data that is stored in my Local database with what is stored in the Server database (the Server database MUST be the Master database)

I do not want to use Replication as some of the Local Computers can not run MySQL very quickly so I am using Firebird on them (but they still support SQL queries)

i was thinking something like this

count the number of records that have changed
Retrieve the changed Records from the server
Update local Records


Does anyone have any vb code that they could share with me to show me how to work this out (or even to give me a heads up)


The Server is connecton is delcared like this

Public DBConn As MySqlConnection

and the Local database is declared like this

Public fbDBConn As FbConnection

I hope this helps.

[jmcilhinney]

The lasteditedon field is stored like this yyyy/mm/dd

Does that mean that it's stored as text? If so, that is your first mistake. That format should actually work, because alphabetical order would match chronological order, but you should still be storing dates as binary dates, not text. If it is stored as binary dates then it's not sto9red the way you showed, because format is just a display issue, not a storage issue.

If you want to update database A with any changed records from database B, you simply query database A for the latest change date and then query database B to get all records with a change date later than that.

[AndyGable]

Does that mean that it's stored as text? If so, that is your first mistake. That format should actually work, because alphabetical order would match chronological order, but you should still be storing dates as binary dates, not text. If it is stored as binary dates then it's not sto9red the way you showed, because format is just a display issue, not a storage issue.

If you want to update database A with any changed records from database B, you simply query database A for the latest change date and then query database B to get all records with a change date later than that.

Hi jmcilhinney

Thanks for the advise I shall look into adapting the date to a date Feilds.

Now I have to ask how can I use data that is in the record set from database 1 (server) to see if it needs updating in database 2 (local)

I can get the results into a data reader (as I've been using them for ever) do I just look though the results and do a update / add etc on the records?