ASP.NET project with INSERT AND INNER JOIN queries

Question

I need to make a project in ASP.NET that, among other functions will be able to add new data to two tables at once.

One table is named 'products' with columns 'name' and 'id' as PK
The other one is named 'auctions' with columns 'bidValue' and 'productId'.

If someone could tell me what am I doing wrong in this code. When I click the button it gives an error page with this text:

Incorrect syntax near ','.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ','.

Source Error:

Line 86: comm.Parameters.AddWithValue("@valueOfBid1",  valueOfBid);
Line 87: 
Line 88:                     ***comm.ExecuteNonQuery();***
Line 89:                 }
Line 90:             }

Here is my code:

 public void makeBid(string nameOfProduct, int valueOfBid)
    {
        string query = @"INSERT INTO products.name, auctions.bidValue VALUES @nameOfProduc,
                       @valueOfBid1
                       FROM products 
                       INNER JOIN auctions ON products.id = auctions.productId";
        using (SqlConnection conn = DbBroker.conn())
        {
            using (SqlCommand comm = new SqlCommand(query, conn))
            {
                comm.Parameters.AddWithValue("@nameOfProduc", nameOfProduct);
                comm.Parameters.AddWithValue("@valueOfBid1", valueOfBid);

                comm.ExecuteNonQuery();
            }
        }
      
    }

 protected void Button1_Click(object sender, EventArgs e)
    {
        Auction1 newBid = new Auction1();
        string productName = TextBox1.Text.Trim().ToString();
        string lbItem = ListBox1.SelectedValue;
        int bidValue = Int32.Parse(lbItem);
        newBid.makeBid(productName, bidValue);
    }

score 4 · Answer 1 · edited May 23 '17 at 11:43

4

T-SQL doesn't allow inserting into 2 tables in the same statement.

If you want to insert into two tables in an atomic manner, you can do it in a transaction.

That question is answered here.

edited May 23 '17 at 11:43

Community

1
1

answered Aug 06 '15 at 17:40

bigtlb

1,512
10
16

Glorfindel · Accepted Answer · 2015-08-06T17:47:50.877

3

You can't INSERT into multiple tables in a single SQL statement. You'll have to break it down into two separate queries - start with

INSERT INTO products (name) OUTPUT INSERTED.ID VALUES @nameOfProduc

and retrieve the ID with

int productID = (Int32)command.ExecuteScalar();

Then, use this ID in the other query:

INSERT INTO auctions (productID, bidValue) VALUES (@productID, @valueOfBid1)

Also, it is not clear to me why you should add a new record to the products table with each bid. It makes more sense to retrieve the product id from the products table, and then only insert a new record in the auctions table. That would look something like INSERT INTO auctions(productId, bidValue) VALUES ...

edited Aug 06 '15 at 17:47

answered Aug 06 '15 at 17:37

Glorfindel

21,988
13
81
109

With every button click I need to insert new auction data into tables, so new Product's name and it's value. Beginner's mistake regarding the single SQL statement. So, I need to make two queries and two sqlCommand ? – Arsen Milosev Aug 06 '15 at 17:42
It showed again error because the column does not allow null, so i changed that in table and now work's. Thank you! – Arsen Milosev Aug 06 '15 at 18:06

Kritner · Answer 3 · 2015-08-06T17:45:05.203

It looks like you're attempting to do a insert into select... - you do indeed have a few issues.

insert into with a from table involved requires a select rather than values. You can't actually insert values into two tables at once (you could use a trigger, but not exactly the same thing)

Something like this should work:

INSERT INTO auctions (productId, bidValue)
select p.Id,
       @valueOfBid1
FROM products p
where name = @nameOfProduc

note this will work dependent on "name of product" being unique. Additionally, you should only be inserting a "product" when a new product is added to the inventory (or whatever) - each bid should not create a new product.

The above statement will successfully insert a bid for a product name in the auctions table, but it is dependent on the product already existing.

ASP.NET project with INSERT AND INNER JOIN queries

3 Answers3