1
protected void btnSubmit_Click(object sender, EventArgs e)
{
    connectionString = ConfigurationManager.ConnectionStrings["LeaveMangementSystemCS"].ConnectionString;
    conn = new SqlConnection(connectionString);
    string sql = "UPDATE LeaveType  SET LeaveType.Type=@Type, LeaveType.Description=@Description, LeaveType.NumOfDays=@NumOfDays, LeaveCategory.Category=@Category FROM LeaveType INNER JOIN LeaveCategory on LeaveType.LeaveCategoryId = LeaveCategory.Id  WHERE LeaveType.Id=@id";

    try
    {
            cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@Type", tbType.Text);
            cmd.Parameters.AddWithValue("@Description", tbDescription.Text);
            cmd.Parameters.AddWithValue("@NumOfDays",tbNumOfDays.Text);
            cmd.Parameters.AddWithValue("@Category", ddlLeaveCategory.Text);
            cmd.Parameters.AddWithValue("@id", lblIdOut.Text);

            conn.Open();
            int rows = cmd.ExecuteNonQuery();

            if (rows > 0)
            {
                lblOutput.Text = " Updated successfully.";
            }
    }
    catch (Exception ex)
    {
        lblOutput.Text = "Error Message : " + ex.Message;
    }
    finally
    {
        if (conn != null)
            conn.Close();
    }
}

I have an error in my SQL query:

The multi-part identifier "LeaveCategory.Category" could not be bound.

I had try using leavetype as a and leavecategory as b but still this error.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Baioretto
  • 15
  • 1
  • 8
  • Possible duplicate of [The multi-part identifier could not be bound](http://stackoverflow.com/questions/7314134/the-multi-part-identifier-could-not-be-bound) – trailmax Jul 12 '16 at 09:19

2 Answers2

5

You can't update multiple tables in one statement! See on your sql variable:

UPDATE LeaveType  
SET LeaveType.Type=@Type
,   LeaveType.Description=@Description
,   LeaveType.NumOfDays=@NumOfDays
,   LeaveCategory.Category=@Category 
FROM LeaveType 
INNER JOIN LeaveCategory on LeaveType.LeaveCategoryId = LeaveCategory.Id  
WHERE LeaveType.Id=@id";

Here you try to update table LeaveType and one column from table LeaveCategory

Please see this asnwer

Community
  • 1
  • 1
Roman Marusyk
  • 23,328
  • 24
  • 73
  • 116
  • I tried too but my table will not be updated after i tried updating the status in html. – Baioretto Jul 12 '16 at 09:10
  • What do you mean? Are we talking about SQL or HTML???? In the question I do not see any information about HTML! – Roman Marusyk Jul 12 '16 at 09:27
  • I meant this code is under my aspx.cs file using master page in c# microsoft visual studio which i launch into chrome and tried updating which where the error shows The multi-part identifier "LeaveCategory.Category" could not be bound. – Baioretto Jul 12 '16 at 09:35
  • Do you understand the answer? I wrote that you cann't use your `sql` variable as it is. You should click the link "Please see this answer" then read about possible solution and use it for your query. In the asnwer I've just showed your value of `sql` variable but with formatting to show where is an issue – Roman Marusyk Jul 12 '16 at 10:08
0

You need to prepare two separate sql statements, you can not update two tables at once. You can wrap them in stored procedure, update them one after another and the call the proc from the C# code.

n_ananiev
  • 189
  • 1
  • 3
  • 12