Trying to insert into multiple tables via web forms page (SQL, ASP.net)

Question

Good Morning Stack Overflow,

I'm new to asp.net and have a problem I'm trying to sort out, maybe you can help?

The result im looking for is that the data goes into CANRADcontacts and CANRADcollreg which share a common ID.

I am populating the database via a web form, and can't quite get the SQL correct, please see this example;

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'Vars

        Dim FlightDateArriveString As String = (FlightMonthArrive.SelectedValue.ToString & "/" & FlightDayArrive.SelectedValue.ToString & "/" & "2010")
        Dim FlightDateDepartString As String = (FlightMonthDepart.SelectedValue.ToString & "/" & FlightDayDepart.SelectedValue.ToString & "/" & "2010")
        Dim FlightDateArrive As DateTime
        Dim FlightDateDepart As DateTime
        FlightDateArrive = Convert.ToDateTime(FlightDateArriveString)
        FlightDateDepart = Convert.ToDateTime(FlightDateDepartString)

        'Setup db connection 
        Dim CollID As Integer = 1
        Dim connstring As String = WebConfigurationManager.ConnectionStrings("ConnectionString").ToString()
        Dim conn As SqlConnection = New SqlConnection(connstring)

 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) ") & _
        ("[CANRADcollreg]([CollID], [FlightDateArrive], [FlightNameArrive], [FlightTimeArrive], [FlightDateDepart], [FlightNameDepart], [FlightTimeDepart], [AttendingLaunch], [Accomodation], [DietaryRequirements], [SpecificDietaryRequirement]) ") & _
        ("VALUES (@CollID, @FlightDateArrive, @FlightNameArrive, @FlightTimeArrive, @FlightDateDepart, @FlightNameDepart, @FlightTimeDepart, @AttendingLaunch, @Accomodation, @DietaryRequirements, @SpecificDietaryRequirement)")

        Dim comm As New SqlCommand(sql, conn)

        's1
        comm.Parameters.Add("@Title", SqlDbType.VarChar)
        comm.Parameters.Add("@Initials", SqlDbType.VarChar)
        comm.Parameters.Add("@FirstName", SqlDbType.VarChar)
        comm.Parameters.Add("@LastName", SqlDbType.VarChar)
        comm.Parameters.Add("@Organisation", SqlDbType.VarChar)
        comm.Parameters.Add("@Department", SqlDbType.VarChar)
        comm.Parameters.Add("@Address", SqlDbType.VarChar)
        comm.Parameters.Add("@Code", SqlDbType.VarChar)
        comm.Parameters.Add("@Telephone", SqlDbType.VarChar)
        comm.Parameters.Add("@WorkTelephone", SqlDbType.VarChar)
        comm.Parameters.Add("@Fax", SqlDbType.VarChar)
        comm.Parameters.Add("@CellularTelephone", SqlDbType.VarChar)
        comm.Parameters.Add("@Email", SqlDbType.VarChar)
        comm.Parameters.Add("@ResearchFocus", SqlDbType.VarChar)
        comm.Parameters.Add("@CollID", SqlDbType.VarChar)
        comm.Parameters.Add("@ResearchFunders", SqlDbType.VarChar)

        's2
        comm.Parameters.Add("@FlightDateArrive", SqlDbType.DateTime)
        comm.Parameters.Add("@FlightNameArrive", SqlDbType.VarChar)
        comm.Parameters.Add("@FlightTimeArrive", SqlDbType.VarChar)
        comm.Parameters.Add("@FlightDateDepart", SqlDbType.DateTime)
        comm.Parameters.Add("@FlightNameDepart", SqlDbType.VarChar)
        comm.Parameters.Add("@FlightTimeDepart", SqlDbType.VarChar)
        comm.Parameters.Add("@AttendingLaunch", SqlDbType.VarChar)
        comm.Parameters.Add("@Accomodation", SqlDbType.VarChar)
        comm.Parameters.Add("@DietaryRequirements", SqlDbType.VarChar)
        comm.Parameters.Add("@SpecificDietaryRequirement", SqlDbType.VarChar)

        'bind1
        comm.Parameters("@Title").Value = TitleDD.SelectedItem.ToString
        comm.Parameters("@Initials").Value = Initials.Text
        comm.Parameters("@FirstName").Value = FirstName.Text
        comm.Parameters("@LastName").Value = LastName.Text
        comm.Parameters("@Organisation").Value = Organisation.Text
        comm.Parameters("@Department").Value = DeptDiv.Text
        comm.Parameters("@Address").Value = PostalAddress.Text
        comm.Parameters("@Code").Value = Code.Text
        comm.Parameters("@Telephone").Value = Telephone.Text
        comm.Parameters("@WorkTelephone").Value = WorkTelephone.Text
        comm.Parameters("@Fax").Value = Fax.Text
        comm.Parameters("@CellularTelephone").Value = CellularTelephone.Text
        comm.Parameters("@Email").Value = EmailAddress.Text
        comm.Parameters("@ResearchFocus").Value = ResearchFocus.Text
        comm.Parameters("@CollID").Value = CollID
        comm.Parameters("@ResearchFunders").Value = ResearchFunders.Text

        'bind2
        comm.Parameters("@FlightDateArrive").Value = FlightDateArrive
        comm.Parameters("@FlightNameArrive").Value = FlightNameArrive.Text
        comm.Parameters("@FlightTimeArrive").Value = FlightTimeArrive.Text
        comm.Parameters("@FlightDateDepart").Value = FlightDateDepart
        comm.Parameters("@FlightNameDepart").Value = FlightNameDepart.Text
        comm.Parameters("@FlightTimeDepart").Value = FlightTimeDepart.Text
        comm.Parameters("@AttendingLaunch").Value = AttendingMarch23.SelectedValue.ToString
        comm.Parameters("@Accomodation").Value = Transport.SelectedValue.ToString
        comm.Parameters("@DietaryRequirements").Value = Dietary.SelectedValue.ToString
        comm.Parameters("@SpecificDietaryRequirement").Value = SpecificDietary.Text


        conn.Open()
        comm.ExecuteScalar()
        conn.Close()

    End Sub

Thanks in advance, any assistance greatly appreciated!!

Answer 1

The problem is in your SQL statement -- you need to have TWO INSERT statements, one for each table (notice the semi-colon that separates the two statements).

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, @FlightNameDepart, @FlightTimeDepart, @AttendingLaunch, @Accomodation, @DietaryRequirements, @SpecificDietaryRequirement)")

Answer 2

Yes you need to make two SQL Commands...one for each table, and add parameters to both SQL Commands (both Insert statements).

The problem I have now is that the ContactsID in CANRADCollreg needs to match up with the id column in CANRADcontacts table

Why does it have to match up with the Primary Key field? Why can't the ContactsID be a Foreign Key in the CANRAD table? For what you're describing, that seems like the best approach.

If that won't work, then give us some more details about this database and perhaps we can come up with a better solution.