I have table "Student"
P_ID LastName FirstName Address City
1 Hansen Ola
2 Svendson Tove
3 Petterson Kari
4 Nilsen Johan
...and so on
How do i change edit code in C#
string firstName = "Ola";
string lastName ="Hansen";
string address = "ABC";
string city = "Salzburg";
string connectionString = System.Configuration.ConfigurationManager
.ConnectionStrings["LocalDB"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO Student (LastName, FirstName, Address, City)
VALUES (@ln, @fn, @add, @cit)";
command.Parameters.AddWithValue("@ln", lastName);
command.Parameters.AddWithValue("@fn", firstName);
command.Parameters.AddWithValue("@add", address);
command.Parameters.AddWithValue("@cit", city);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
to edit entry where Lastname field has lastname value and FirstName field has firstname value.
I dont want to use like this
UPDATE Persons SET Address='Nissestien 67', City='Sandnes'
WHERE LastName='Tjessem' AND FirstName='Jakob'
and i edited my original statement to
command.CommandText = "UPDATE Student(LastName, FirstName, Address, City)
VALUES (@ln, @fn, @add, @cit) WHERE LastName='" + lastName +
"' AND FirstName='" + firstName+"'";
but the statement is not getting executed, why is it throwing SQL exception ? Is there nay solution to it ?
Please, never use this concat form:
String st = "UPDATE supplier SET supplier_id = " + textBox1.Text + ", supplier_name = " + textBox2.Text
+ "WHERE supplier_id = " + textBox1.Text;
use:
command.Parameters.AddWithValue("@attribute", value);
Always work object oriented
Edit: This is because when you parameterize your updates it helps prevent SQL injection.
If you don't want to use the SQL syntax (which you are forced to), then switch to a framework like Entity Framework or Linq-to-SQL where you don't write the SQL statements yourself.
string constr = @"Data Source=(LocalDB)\v11.0;Initial Catalog=Bank;Integrated Security=True;Pooling=False";
SqlConnection con = new SqlConnection(constr);
DataSet ds = new DataSet();
con.Open();
SqlCommand cmd = new SqlCommand(" UPDATE Account SET name = Aleesha, CID = 24 Where name =Areeba and CID =11 )";
cmd.ExecuteNonQuery();
There is always a proper syntax for every language. Similarly SQL(Structured Query Language) has also specific syntax for update query which we have to follow if we want to use update query. Otherwise it will not give the expected results.
I dont want to use like this
That is the syntax for Update
statement in SQL, you have to use that syntax otherwise you will get the exception.
command.Text = "UPDATE Student SET Address = @add, City = @cit Where FirstName = @fn AND LastName = @ln";
and then add your parameters accordingly.
command.Parameters.AddWithValue("@ln", lastName);
command.Parameters.AddWithValue("@fn", firstName);
command.Parameters.AddWithValue("@add", address);
command.Parameters.AddWithValue("@cit", city);
private void button4_Click(object sender, EventArgs e)
{
String st = "DELETE FROM supplier WHERE supplier_id =" + textBox1.Text;
SqlCommand sqlcom = new SqlCommand(st, myConnection);
try
{
sqlcom.ExecuteNonQuery();
MessageBox.Show("????");
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
private void button6_Click(object sender, EventArgs e)
{
String st = "SELECT * FROM suppliers";
SqlCommand sqlcom = new SqlCommand(st, myConnection);
try
{
sqlcom.ExecuteNonQuery();
SqlDataReader reader = sqlcom.ExecuteReader();
DataTable datatable = new DataTable();
datatable.Load(reader);
dataGridView1.DataSource = datatable;
//MessageBox.Show("LEFT OUTER??");
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
command.Text = "UPDATE Student
SET Address = @add, City = @cit
Where FirstName = @fn and LastName = @add";
String st = "UPDATE supplier SET supplier_id = " + textBox1.Text + ", supplier_name = " + textBox2.Text
+ "WHERE supplier_id = " + textBox1.Text;
SqlCommand sqlcom = new SqlCommand(st, myConnection);
try
{
sqlcom.ExecuteNonQuery();
MessageBox.Show("update successful");
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
Source: Stackoverflow.com