Baker Baker - 9 months ago 39
SQL Question

Updating/inserting data

I created 3 tables

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

now I need a function where the user enters data (Hours_Spent, Job_Date) to EmpJobs based on Client_Name and Emp_Name. This is what I tried

string constring = "Data Source=baker-pc;Initial Catalog=BakodahDB;Integrated Security=True";
string sqlQuery = (@"INSERT INTO EmpJobs (Hours_Spent, Job_Date)
values (@Hours_Spent,@Job_Date)
ON EmpJobs.Client_ID=Clients.Client_ID
INNER JOIN Employees
ON EmpJobs.Emp_ID=Employees.Emp_ID
WHERE Clients.Client_Name=@Client_Name AND Employees.Emp_Name=@Emp_Name");

SqlConnection conDataBase = new SqlConnection(constring);
SqlCommand sqlCommand = new SqlCommand(sqlQuery, conDataBase);
sqlCommand.CommandText = sqlQuery;
sqlCommand.Parameters.Add("@Hours_Spent",SqlDbType.Int).Value = comboBox3.SelectedItem;
sqlCommand.Parameters.Add("@Job_Date",SqlDbType.Date).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;

What am i doing wrong? what's the correct way to do it?

Answer Source

If you really want to insert new rows, then you want INSERT . . . SELECT. It would look like this:

INSERT INTO EmpJobs (Emp_ID, Client_ID, Hours_Spent, Job_Date)
SELECT e.Emp_ID, c.Client_ID, @Hours_Spent, @Job_Date 
FROM Clients c JOIN
         Employees e
         ON c.Client_Name = @Client_Name AND e.Emp_Name = @Emp_Name;