CODEWITHSUNDEEP
c#sqlasp.netsql-server
Arsen Milosev
Arsen Milosev

Reputation: 29

ASP.NET project with INSERT AND INNER JOIN queries

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.

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);
    }

Upvotes: 0

Views: 4925

Answers (3)

Glorfindel
Glorfindel

Reputation: 22651

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 ...

Upvotes: 3

Kritner
Kritner

Reputation: 13765

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.

Upvotes: 0

bigtlb
bigtlb

Reputation: 1572

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.

Upvotes: 4

Related Questions