INSERT INTO table with foreign key contraints

Question

I have read a couple things on this and came up with the following code. I am using an Access database and am coding in C# through Visual Studio.

I am getting a syntax error in this. I have tried to create a query in Access to test this but its difficult to create this in that.

Can anyone help me figure out why this isnt working?

using(OleDbConnection conn1 = new OleDbConnection(global::InsulationProjectTracker.Properties.Settings.Default.InsulationDB))
{
    using(OleDbCommand command1 = conn1.CreateCommand())
    {
        conn.Open();

        command.CommandText = "INSERT INTO Jobsites (CustomerID, JobsiteName) VALUES ((SELECT Customers.CustomerID FROM Customers WHERE Customers.CustomerName = @cname1 AND Customers.BranchNumber = @bNumber1), @jName1)";
        command.Parameters.Add(new OleDbParameter("@cName1", cboCustomerName.Text));
        command.Parameters.Add(new OleDbParameter("@bNumber1", cboBranch.Text));
        command.Parameters.Add(new OleDbParameter("@jName1", txtJobsiteName.Text));

        command.ExecuteNonQuery();

        conn.Close();
        command.Parameters.Clear();

    }
}

Tables setup

Branches

PK BranchNumber
BranchName

Customers

PK CustomerID
CustomerName
FK BranchNumber

Jobsites

PK JobsiteID
JobsiteName
FK CustomerID

EDIT***

the below commmand produces no error, but also doesnt insert data into database

command.CommandText = "INSERT INTO Jobsites (CustomerID, JobsiteName) SELECT @jName1, c.CustomerID FROM Customers c WHERE c.CustomerName = @cname1 AND c.BranchNumber = @bNumber1";

Answer 1

Access SQL does not accept an alias for the destination table.

For example, either of these statements trigger "Syntax error in INSERT INTO statement." ...

INSERT INTO Jobsites j (j.CustomerID, j.JobsiteName) VALUES (1, 'foo');
INSERT INTO Jobsites AS j (j.CustomerID, j.JobsiteName) VALUES (1, 'foo');

Without the alias, this one executes without error ...

INSERT INTO Jobsites (CustomerID, JobsiteName) VALUES (1, 'foo');

In order to get a query Access will accept, I think you may need to switch to an INSERT ... SELECT instead of INSERT ... VALUES ...

command.CommandText = "INSERT INTO Jobsites (CustomerID, JobsiteName) SELECT c.CustomerID, @jName1 FROM Customers AS C WHERE c.CustomerName = @cname1 AND c.BranchNumber = @bNumber1";

So basically what @ConradFrix suggested earlier but without the alias for the Jobsites table.