CODEWITHSUNDEEP
c#asp.netsql-server
Tiger
Tiger

Reputation: 39

inserting data into SQL DB from C# ASP.NET

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!

Upvotes: 0

Views: 4485

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

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

Upvotes: 1

Steve
Steve

Reputation: 216243

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.

Upvotes: 2

Related Questions