Baioretto
Baioretto

Reputation: 15

C# & SQL Server query error: The multi-part identifier cannot be bound

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.

Upvotes: 1

Views: 721

Answers (2)

n_ananiev
n_ananiev

Reputation: 309

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.

Upvotes: 0

Roman Marusyk
Roman Marusyk

Reputation: 24569

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

Upvotes: 5

Related Questions