CODEWITHSUNDEEP
c#sql
Baker
Baker

Reputation: 39

This SQL query won't work

I Have three tables: 

Clients: Client_ID, Client_Name, Client_Status,  
Employees:Emp_ID, Emp_Name, Emp_Role  
EmpJobs:Emp_ID, Emp_Name, Client_ID, Client_Name, Hours_Spent, Job_Date

I'm trying to insert data (Hours_Spent, Job_Date) to EmpJobs based on related columns in Clients and Employees

Should I use Insert or Update Set?

this my code:

private void button1_Click(object sender, EventArgs e)
{
    SqlConnection sqlConnection = new SqlConnection("Data Source=baker-pc;Initial Catalog=BakodahDB;Integrated Security=True");
    SqlCommand sqlCommand = new SqlCommand("");
    sqlCommand.Connection = sqlConnection;
    sqlConnection.Open();
    sqlCommand.CommandText = (@"UPDATE EmpJobs SET (Hours_Spent, Job_Date) VALUES ('" + comboBox3.SelectedItem + "','" + dateTimePicker1.Text + "') WHERE Client_Name='"+comboBox1.SelectedItem+"' AND Emp_Name='"+comboBox2.SelectedItem+"'");
    sqlCommand.ExecuteNonQuery();
    sqlConnection.Close();
    MessageBox.Show("Loged!")

Upvotes: 0

Views: 131

Answers (2)

sujith karivelil
sujith karivelil

Reputation: 29006

Let me point out few mistakes in the code that you have posted.

  1. Syntax errors in the Update Query.

I think you forgot the basic syntax of an update query, You can't give values like insert instead of that you have to specify the values for each columns separately. the basic syntax for Update is like this:

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

2. Beware of SQL Injection The second thing that you have to note is that your code opens a wide door for hackers through Injection. You have to use parameterized queries to avoid injection. Then the code will be like the following:

string sqlQuery = "UPDATE EmpJobs SET Hours_Spent=@Hours_Spent, Job_Date =@Job_Date" + 
                  " WHERE Client_Name=@Client_Name AND Emp_Name=@Emp_Name"

sqlCommand.CommandText = sqlQuery;
sqlCommand.Parameters.Add("@Hours_Spent",SqlDbType.Int).Value = comboBox3.SelectedItem;
sqlCommand.Parameters.Add("@Job_Date",SqlDbType.DateTime).Value = Convert.ToDateTime(dateTimePicker1.Text);
sqlCommand.Parameters.Add("@Client_Name",SqlDbType.Varchar).Value = comboBox1.SelectedItem;
sqlCommand.Parameters.Add("@Emp_Name",SqlDbType.Varchar).Value = comboBox2.SelectedItem;
sqlCommand.ExecuteNonQuery();

Upvotes: 2

KyLim
KyLim

Reputation: 476

sqlCommand.CommandText = (@"UPDATE EmpJobs SET Hours_Spent='" + comboBox3.SelectedItem + "',Job_Date='" + dateTimePicker1.Text + "') WHERE Client_Name='"+comboBox1.SelectedItem+"' AND Emp_Name='"+comboBox2.SelectedItem+"'");

something wrong with your sql update query i think

Upvotes: 0

Related Questions