SQL Insert Query (syntax question)

I have an sql query which is working, it does insert the correct data, but the ID's are out of synch:

 Dim sql As String = ("INSERT INTO [CANRADcontacts]") & _
        (" ([Title], [Initials], [FirstName], [LastName], [Organisation], [Department], [Address], [Code],") & _
        (" [Telephone], [WorkTelephone], [Fax], [CellularTelephone],[Email], [ResearchFocus], [CollID], [ResearchFunders])") & _
        (" VALUES (@Title, @Initials, @FirstName, @LastName, @Organisation, @Department, @Address, @Code, ") & _
        ("@Telephone, @WorkTelephone, @Fax, @CellularTelephone, @Email, @ResearchFocus, @CollID, @ResearchFunders); ") & _
        ("INSERT INTO [CANRADcollreg] ([CollID], [FlightDateArrive], [FlightNameArrive], [FlightTimeArrive], [FlightDateDepart], [FlightNameDepart], [FlightTimeDepart], [AttendingLaunch], [Accomodation], [DietaryRequirements], [SpecificDietaryRequirement]) ") & _
        ("VALUES (@CollID, @FlightDateArrive, @FlightNameArrive, @FlightTimeArrive, @FlightDateDepart, @Fligh开发者_如何学CtNameDepart, @FlightTimeDepart, @AttendingLaunch, @Accomodation, @DietaryRequirements, @SpecificDietaryRequirement)")

In the database I have two columns (ContactsID on CANRADcollreg and ID on CANRADcontacts) which I need to synch up and have the same ID inserted when a record is inserted. Please can you assist on how I would go about achieving this?

Thanks and regards.

Before you do your two Inserts I would get the MAX value of ContactID and add 1 to it. I would then store this in a temp field, which could be used in both INSERT INTO statements.

You would have to include something like this at the start, or do this as a separate step just before you run these inserts.

Dim sql As String = ("DECLARE @ContactID AS INT" ) & _
  (" SET @ContactID = ((SELECT MAX(ContactID) FROM CANRADcontacts) + 1) & _ 
(" INSERT INTO [CANRADcontacts]") & _ 
(" ([ContactID], [Title], [Initials], [FirstName], [LastName]...
... etc
(" VALUES (@ContactID, @Title, @Initials, @FirstName, @LastName,

You would then have to include your ContactID in the INSERT INTO command. Of course if your using an IDENTITY for you ContactID, then this method wont work.

If you want to use an automatically created id (auto_increment / identity) from one query in another query, there is usually a function available to get that.

In SQL Server for example you use the function scope_identity() to get the identity created in the previous query.

In Access you use @identity. In MySQL you use last_insert_id().

Is the ID of CANRADcontacts marked as an identifier? If so, have a look at the documentation for @@identity.

This should give you the value of the most recently created ID (at least in Transact-SQL, possibly also for other systems).

For this to work, you do ofc need to do the insert on CANRADcontacts before the insert on CANRADcollreg.