inserting data into SQL DB from C# ASP.NET

Question

I am trying to insert values into my SQL database, the query works on the SQL side but when it comes to implement it from C# ASP.NET, it will not insert anything into the SQL database. The code is as follows:

public partial class About : Page
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

    protected void Page_Load(object sender, EventArgs e)
    {
        con.Open();
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlCommand cmd = new SqlCommand("insert into sanctuary(SName) values('test')", con);
        cmd = new SqlCommand("insert into species(Name) values('test1')", con);
        cmd = new SqlCommand("insert into breed(SpeciesID, BreedName, FoodCost, HousingCost)  SELECT SpeciesID, ('breed'), ('12'), ('21') FROM species", con);

        cmd.ExecuteNonQuery();
        con.Close();
    }
}

}

Your help will be much appreciated!

I'll expect at least the last command to run. Did you get any error message? — Steve
– Steve, Commented Oct 31, 2017 at 14:19
Why did you tag this as MySQL? You are using the SqlConnection and SqlCommand classes, which are for MS SQL and have nothing to do with MySQL. — mason
– mason, Commented Oct 31, 2017 at 14:19
You only execute the last command, so there is nothing in species. Since there is nothing in species, the select returns no results so nothing gets inserted into breed. — Zohar Peled
– Zohar Peled, Commented Oct 31, 2017 at 14:23
@Steve, actually the first command was running and no the rest. But thanks so much for your answer below, helped a lot!!! I've spent four days researching this. — Tiger
– Tiger, Commented Oct 31, 2017 at 18:09

Steve · Accepted Answer · 2017-10-31 14:24:13Z

2

If you want to execute three commands together you merge the sql of the three commands in a single string separating them with a semicolon (See Batch of Sql Commands)

   string cmdText = @"insert into sanctuary(SName) values('test');
                      insert into species(Name) values('test1');
                      insert into breed(SpeciesID, BreedName, FoodCost, HousingCost)  
                      SELECT SpeciesID, ('breed'), ('12'), ('21') FROM species";
   SqlCommand cmd = new SqlCommand(cmdText, con);
   cmd.ExecuteNonQuery();

The first problem in your code is that you need to execute each single command and not just the last one. Finally, if you don't see even the insert for the last command could be because your table species is empty and thus the final command has nothing to insert.

Last note, the point underlined by Zohar Peled about NOT keeping a global connection object around, is very important, follow the advice.

answered Oct 31, 2017 at 14:24

Steve

217k22 gold badges242 silver badges296 bronze badges

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

2 Comments

Tiger Over a year ago

What if I wanted to call a textbox for its value a, how can I do this, please? something along the lines of: @"insert into sanctuary(SName) values('"+country+"')"; "insert into species(Name) values('"+specie+ "')";

Steve Over a year ago

Now this is different. instead of a constant string you need to insert a user input value. This could be done writing a parameterized query. I suggest you to search for this words c# parameterized query and then try to create one by yourself. If you have problems post a new question (this way more people will look at your problem than in a closed and accepted question)

Peyman Majidi · Accepted Answer · 2024-07-01 10:41:26Z

1

You only execute the last command, so there is nothing in species. Since there is nothing in species, the select returns no results so nothing gets inserted into breed.

Also, keeping a SqlConnection object on the page level is not a good idea. SQL connections should be opened right before executing queries and disposed of immediately after.

A better code would look like this:

using(var con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
    using(var com = new SqlCommand("insert into sanctuary(SName) values('test');insert into species(Name) values('test1');insert into breed(SpeciesID, BreedName, FoodCost, HousingCost)  SELECT SpeciesID, ('breed'), ('12'), ('21') FROM species", con))
    {
         con.Open();
         com.ExecuteNonQuery();
    }
}

You can, of course, execute each SQL statement separately (though in this case, it's not the best course of action since it means 3 round trips to the database instead of just one):

using(var con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
    using(var com = new SqlCommand("insert into sanctuary(SName) values('test');", con))
    {
         con.Open();
         com.ExecuteNonQuery();
         com.CommandText = "insert into species(Name) values('test1');";
         com.ExecuteNonQuery();
         com.CommandText = "insert into breed(SpeciesID, BreedName, FoodCost, HousingCost)  SELECT SpeciesID, ('breed'), ('12'), ('21') FROM species;";
         com.ExecuteNonQuery();
    }
}

edited Jul 1, 2024 at 10:41

Peyman Majidi

2,0272 gold badges23 silver badges36 bronze badges

answered Oct 31, 2017 at 14:25

Zohar Peled

82.9k10 gold badges79 silver badges134 bronze badges

7 Comments

Rob Over a year ago

separate commands IS the best, if an error occurs it can be traced. Combining the statements into 1 SQL command is a bad practice.

Zohar Peled Over a year ago

@Rob That's usually correct, but I'm not sure it's correct here.

Rob Over a year ago

yes it is bad practice, it's like saying GOTO is bad, but if it's only 1 once in a while then that's OK - Wrong: no, Bad: yes. The temptation to grow/add-on multi-command statements is there, the temptation to do it in future projects is there, the temptation to make that a habit is there, and then what of the next guy that comes along to debug your code. As to the "round trip time" - negligible compared to the actual database execution time.

Zohar Peled Over a year ago

@Rob You do realize that a lot of database activity involves more than a single statement, right? Are you also against using stored procedures? If not, are you against using multi-statements stored procedures?

Zohar Peled Over a year ago

@Rob that I can relate to. I even agreed with your first statement. This is why I've shown both options in my answer. However, I do believe that it's good to know the options - and concatenating simple, almost "full proof" statements into a single command object doesn't strike me as something to fear in debug. I agree that it's better to start off with best practices but it's also good to know there are other alternatives (that might be useful some times)

|

Collectives™ on Stack Overflow

inserting data into SQL DB from C# ASP.NET

2 Answers 2

2 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related