I want to insert data into two tables using sql and asp.Net

Question

        SqlConnection con = new SqlConnection();
        con.ConnectionString = @"Data Source=MYDATASOURCE";
        con.Open();

        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "Insert into [Voorraad] values(@IngredientID, 
        @AantalInVoorraad, @MinimumVoorraad";
        cmd.Parameters.AddWithValue("@IngredientID", txt_ID.Text);
        cmd.Parameters.AddWithValue("@AantalInVoorraad", txt_aantal.Text);
        cmd.Parameters.AddWithValue("@MinimumVoorraad", txt_minimum.Text);
        cmd.Connection = con;
        cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
        cmd.CommandText = "insert into [Ingredient] values(@IngredientID, @IngredientNaam";
        cmd.Parameters.AddWithValue("@IngredientID", txt_ID.Text);
        cmd.Parameters.AddWithValue("@IngredientNaam", txt_ingredient.Text);
        cmd.ExecuteNonQuery();

I want to insert data to the tables Voorraad and Ingredient. In the tables Voorraad there must IngredientID, AantalInVoorraad, MinimumVoorraad and Categorie be in the table after instert.

In the table Ingredient there must be an new Ingredientnaam be made. When i filling in the text boxes and after hitting the button insert i get the error: System.Data.SqlClient.SqlException: 'Incorrect syntax near '@MinimumVoorraad'.' Please help me!

        I've edited to this:
       SqlConnection con = new SqlConnection();
        con.ConnectionString = @"Data Source=
        con.Open();

        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "Insert into [Voorraad] values(@IngredientID, 
        @AantalInVoorraad, @MinimumVoorraad)";
        cmd.Parameters.AddWithValue("@IngredientID", txt_ID.ID);
        cmd.Parameters.AddWithValue("@AantalInVoorraad", txt_aantal.Text);
        cmd.Parameters.AddWithValue("@MinimumVoorraad", txt_minimum.Text);
        cmd.Connection = con;
        cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
        cmd.CommandText = "insert into [Ingredient] values(@IngredientID, 
         @IngredientNaam)";
        cmd.Parameters.AddWithValue("@IngredientID", txt_ID.ID);
        cmd.Parameters.AddWithValue("@IngredientNaam", txt_ingredient.Text);
        cmd.ExecuteNonQuery();

Does anybody know maybe another way to insert data to multiple tables in the datbase?? I've searched the whole internet for an answer but i can't find the right solution.

You need to close the bracket at the end of your insert statement. P.S. Make use of a `Using` statement. See [What is the c-sharp using block and why should I use it](https://stackoverflow.com/questions/212198/what-is-the-c-sharp-using-block-and-why-should-i-use-it) — IronAces, Jun 05 '18 at 13:31

score 2 · Answer 1 · answered Jun 05 '18 at 13:41

Introducing ASP.NET Web Pages - Entering Database Data by Using Forms

cmd.CommandText = "Insert into [Voorraad] (IngredientID, AantalInVoorraad, MinimumVoorraad) values(@IngredientID, @AantalInVoorraad, @MinimumVoorraad)";

and

cmd.CommandText = "insert into [Ingredient] (IngredientID, IngredientNaam) values(@IngredientID, @IngredientNaam)";

Peter Campbell · Answer 2 · 2018-06-05T14:25:32.857

Your insert statements are missing the closing bracket for the values.

Add a using Statement for the SQlConnection and SQLCommand, will make it easier to read and debug.

        using (SqlConnection con = new SqlConnection(@"Data Source=MYDATASOURCE"))
        {
           con.Open();

           using(SqlCommand cmd = new SqlCommand(
    "Insert into [Voorraad] values(@IngredientID, @AantalInVoorraad, @MinimumVoorraad)", con))
           {
               cmd.Parameters.AddWithValue("@IngredientID", txt_ID.Text);
               cmd.Parameters.AddWithValue("@AantalInVoorraad", txt_aantal.Text);
               cmd.Parameters.AddWithValue("@MinimumVoorraad", txt_minimum.Text);
               cmd.ExecuteNonQuery();
           }

           using(SqlCommand cmd = new SqlCommand(
    "insert into [Ingredient] values(@IngredientID, @IngredientNaam)", con))
           {
              cmd.Parameters.AddWithValue("@IngredientID", txt_ID.Text);
              cmd.Parameters.AddWithValue("@IngredientNaam", txt_ingredient.Text);
              cmd.ExecuteNonQuery();
           }
        }

Thanks for the comment. I tried this but i got the error: Incorrect syntax near '@MinimumVoorraad'. — Tyrone Fabies, Jun 05 '18 at 14:13
I've copied your insert statement and forgot to add the missing brackets, try with my new edit. — Peter Campbell, Jun 05 '18 at 14:16
Ah I see why, the brackets were outside of the double quotes, I have edited. — Peter Campbell, Jun 05 '18 at 14:26

I want to insert data into two tables using sql and asp.Net

2 Answers2