CODEWITHSUNDEEP
c#asp.netsql
Matt Jones
Matt Jones

Reputation: 37

SQL Insert not working

When the event Button is pressed nothing updates in the SQL Table and no errors display.

protected void SubmitBTN_Click(object sender, EventArgs e)
{
    SqlConnection conn = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Matt\Documents\coffeeShop.mdf;Integrated Security=True;Connect Timeout=30");

    String coffeeName = NameTXT.Text;
    String coffeeGrid = GrindTXT.Text;
    String coffeeOrigin = OriginTXT.Text;
    String coffeePrice = PriceTXT.Text;
    String coffeeQty = QuantityTXT.Text;
    String coffeeRRP = RRPTXT.Text;

    SqlCommand comm = new SqlCommand("INSERT INTO Table (coffeeName, coffeeGrid, coffeeOrigin, coffeePrice, coffeeQty, coffeeRRP) VALUES ('%" + coffeeName + "%','%" + coffeeGrid + "%','%" + coffeeOrigin + "%','%" + coffeePrice + "%','%" + coffeeGrid + "%','%" + coffeeQty + "%','%" + coffeeRRP + "%' ", conn);

    conn.Open();
    //SqlDataReader reader = comm.ExecuteReader();

    //lblDBData.Text += "<table border=0>";
    //while (reader.Read())
    //{
    //    lblDBData.Text += "<tr>";
    //    lblDBData.Text += "<td>" + reader["coffeeName"] + "</td>";
    //    lblDBData.Text += "</tr>";
    //}
    //lblDBData.Text += "</table>";

    //reader.Close();
    conn.Close();                     
}

Any advice would be much appreciated, Many thanks

Upvotes: 3

Views: 3126

Answers (5)

Stefano Pascazi
Stefano Pascazi

Reputation: 389

When you executes a Transact-SQL statement, the correct way is:

    private const string connection = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Matt\Documents\coffeeShop.mdf;Integrated Security=True;Connect Timeout=30";

    protected void SubmitBTN_Click(object sender, EventArgs e)
    {
        string query = "INSERT INTO Table (coffeeName, coffeeGrid, coffeeOrigin, coffeePrice, coffeeQty, coffeeRRP) VALUES (@name, @grid, @origin, @price, @qty, @rrp)";
        using(SqlConnection conn = new SqlConnection(connection))
        using(SqlCommand command = new SqlCommand(query, connection))
        {        

            String coffeeName = NameTXT.Text;
            String coffeeGrid = GrindTXT.Text;
            String coffeeOrigin = OriginTXT.Text;
            String coffeePrice = PriceTXT.Text;
            String coffeeQty = QuantityTXT.Text;
            String coffeeRRP = RRPTXT.Text;

            command.Parameters.AddWithValue("@name", coffeeName);
            command.Parameters.AddWithValue("@grid", coffeeGrid);
            command.Parameters.AddWithValue("@origin", coffeeOrigin);
            command.Parameters.AddWithValue("@price", coffeePrice);
            command.Parameters.AddWithValue("@qty", coffeeQty);
            command.Parameters.AddWithValue("@rrp", coffeeRRP);

            try
            {
                command.Connection.Open();
                command.ExecuteNonQuery();
            }
            catch (SqlException Ex)
            {

                console.WriteLine( "Error message: " + Ex);
            }
            finally
            {
                command.Connection.Close();
            }        

        }

    }

Upvotes: 1

Kaf
Kaf

Reputation: 33839

You need to execute the command as;

conn.Open(); //Open the connection to the database
comm.ExecuteNonQuery(); //This line does the insert
conn.Close(); //Close the connection once your command executed.

Also think about parameterised queries and to open connection object within a using block as a good practice to avoid leaving connection objects open.

Ex;

using(SqlConnection conn = new SqlConnection("connectionString"))
{
   SqlCommand cmd = new SqlCommand("your query string with @para", conn);
   cmd.Parameters.AddWithValue("@para", "value");
   conn.Open();
   cmd.ExecuteNonQuery();

}

Upvotes: 2

Matt Varblow
Matt Varblow

Reputation: 7901

You need to execute the SQL command. Before closing the connection, add this:

comm.ExecuteNonQuery();

For an example, see MSDN: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx

Upvotes: 0

Retired_User
Retired_User

Reputation: 1595

Add:

comm.ExecuteNonQuery();

After:

conn.Open();

By the way, you would want to use parameters instead of " + parameter + " on query to avoid sql injection. Read this:

http://www.csharp-station.com/Tutorial/AdoDotNet/Lesson06

Upvotes: 4

hcb
hcb

Reputation: 8357

You can't read an insert statement. You have to use comm.executeNonQuery() to execute the insert command, then make a new select statement to read the data

Upvotes: 0

Related Questions