Jevon Davis Jevon Davis - 1 month ago 8
C# Question

My query is not executing properly

This is a method within my c# code that should execute on a particular button click:

private void button2_Click(object sender, EventArgs e)
{
try
{

string connectionString = "Data Source=LPMSW09000012JD\\SQLEXPRESS;Initial Catalog=Pharmacies;Integrated Security=True";
SqlConnection con = new SqlConnection(connectionString);
con.Open();
string query = "SELECT Code, Description, Next_Code FROM Liguanea_Lane2 WHERE code LIKE '%" + search.Text + "%'; ";
SqlCommand cmd = new SqlCommand(query, con);

SqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())
{
string scode = dr.GetString(dr.GetOrdinal("next_code"));
textBox2.Text = scode;

}
}
catch (Exception ex)
{

MessageBox.Show(ex.ToString());
}
//next description
try
{

string connectionString1 = "Data Source=LPMSW09000012JD\\SQLEXPRESS;Initial Catalog=Pharmacies;Integrated Security=True";
SqlConnection con1 = new SqlConnection(connectionString1);
con1.Open();
string query1 = "SELECT Code, Description, Next_Description FROM Liguanea_Lane2 WHERE code LIKE '%" + search.Text + "%'; ";


SqlCommand cmd1 = new SqlCommand(query1, con1);

SqlDataReader dr1 = cmd1.ExecuteReader();

while (dr1.Read())
{
string sdes = dr1.GetString(dr1.GetOrdinal("Next_Description"));
textBox3.Text = sdes;

}
}
catch (Exception ex)
{

MessageBox.Show(ex.ToString());
}
search.ResetText();
textBox1.Clear();
search.SelectedIndex = search.SelectedIndex + 1;
textBox2.Clear();
textBox3.Clear();

string connectionString2 = "Data Source=LPMSW09000012JD\\SQLEXPRESS;Initial Catalog=Pharmacies;Integrated Security=True";
SqlConnection con2 = new SqlConnection(connectionString2);
con2.Open();
string query2 = "UPDATE Liguanea_Lane2 SET Update_val= '0' where code = '" + search.Text + "'; ";


}


}


This particular block within it is giving the issue:

string connectionString2 = "Data Source=LPMSW09000012JD\\SQLEXPRESS;Initial Catalog=Pharmacies;Integrated Security=True";
SqlConnection con2 = new SqlConnection(connectionString2);
con2.Open();
string query2 = "UPDATE Liguanea_Lane2 SET Update_val= '0' where code = '" + search.Text + "'; ";


To add more insight to it is that the function of it is to insert into a column entitled "update_val" which is in my MSSQL database table. This value is inserted based on the input of a comboBox that is called "search." I ran the query within MSSQL and it works. only difference is that rather than receive from a comboBox, I specify the value by using the "WHERE" command.
The problem in c# is that it does not update the tables in MSSQL at all. So am asking if my syntax is wrong.

PS. Yes I am aware that parameterized queries should be implemented as to avoid SQL injection. This is just for my own practice. So no comment as it relates to that is relevant at this point.

Answer

To execute your update command, you'll want to do something more like this:

using (SqlConnection connection = new SqlConnection(
               connectionstring1)) // You won't need a second connection string if both are the same
    {
        SqlCommand command = new SqlCommand(query2, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
Comments