Reputation: 37
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
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
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
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
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
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