Reputation: 15
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
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
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