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 — IronAces
– IronAces, Commented Jun 5, 2018 at 13:31

AlwaysLearning · Accepted Answer · 2018-06-05 13:41:36Z

2

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

answered Jun 5, 2018 at 13:41

AlwaysLearning

1741 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Peter Campbell · Accepted Answer · 2018-06-05 14:25:32Z

0

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

edited Jun 5, 2018 at 14:25

answered Jun 5, 2018 at 14:03

Peter Campbell

6711 gold badge7 silver badges35 bronze badges

3 Comments

Tyrone Fabies Over a year ago

Thanks for the comment. I tried this but i got the error: Incorrect syntax near '@MinimumVoorraad'.

Peter Campbell Over a year ago

I've copied your insert statement and forgot to add the missing brackets, try with my new edit.

Peter Campbell Over a year ago

Ah I see why, the brackets were outside of the double quotes, I have edited.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related