CODEWITHSUNDEEP
c#asp.netsql-server
mjay
mjay

Reputation: 11

How to fix invalid column name?

I want to update my table but this error keeps showing.

Server Error in '/' Application. Invalid column name 'id'.

Table

CREATE TABLE [dbo].[Employee] ( [Emp_Id] INT IDENTITY (1, 1) NOT NULL, [FirstName] NVARCHAR (50) NOT NULL, [LastName] NVARCHAR (50) NOT NULL, [Date_Employed] DATE NOT NULL, [Monthly_Salary] FLOAT (53) NOT NULL, [SSSNum] INT NOT NULL, [depNo] INT NOT NULL, PRIMARY KEY CLUSTERED ([Emp_Id] ASC) );

Code

//SELECTED ROW WILL FILL THE FORM INSIDE THE MODAL
protected void tableEmployee_SelectedIndexChanged(object sender, GridViewCommandEventArgs e)
{
  int index = Convert.ToInt32(e.CommandArgument.ToString());
  upId.Text = tableEmployee.Rows[index].Cells[1].Text.TrimEnd();
  upLname.Text = tableEmployee.Rows[index].Cells[2].Text.TrimEnd();
  upFname.Text = tableEmployee.Rows[index].Cells[3].Text.TrimEnd();
  UpDate.Text = tableEmployee.Rows[index].Cells[4].Text.TrimEnd();
  upSal.Text = tableEmployee.Rows[index].Cells[5].Text.TrimEnd();
  upSSS.Text = tableEmployee.Rows[index].Cells[6].Text.TrimEnd();
  upDept.Text = tableEmployee.Rows[index].Cells[7].Text.TrimEnd();

  btnUpdate_ServerClick(); 
}

Modal

protected void btnUpdate_ServerClick()
{
  ScriptManager.RegisterStartupScript(Page, Page.GetType(), "MyUpdateModal", "$('#UpdateModal').modal()",true);
}

Update

protected void btnUpdate_Click(object sender, EventArgs e)
{
  string id=upId.Text;
  string fName=upFname.Text;
  string lName=upLname.Text;
  string date=UpDate.Text;
  string sal=upSal.Text;
  string sss=upSSS.Text;
  string dep=upDept.Text;

  string query = "UPDATE Employee set id=@Emp_Id, fName=@FirstName, lName=@LastName, date=@Date_Employed, sal=@Monthly_Salary, ss=@SSSNum, dep=@depNo where id=@Emp_Id";

   string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
   using (SqlConnection con = new SqlConnection(constr))
   {
     using (SqlCommand cmd = new SqlCommand(query))
     {
       cmd.Parameters.AddWithValue("@Emp_Id", id);
       cmd.Parameters.AddWithValue("@FirstName", fName);
       cmd.Parameters.AddWithValue("@LastName", lName);
       cmd.Parameters.AddWithValue("@Date_Employed", date.Trim());
       cmd.Parameters.AddWithValue("@Monthly_Salary", sal);
       cmd.Parameters.AddWithValue("@SSSNum", sss);
       cmd.Parameters.AddWithValue("@depNo", dep);
       cmd.Connection = con;
       con.Open();
       cmd.ExecuteNonQuery();
       con.Close();

      }
    }
       BindGrid();
       Response.Redirect(Request.Url.AbsoluteUri);
}

If I try removing the variables and replace them with .Text, the same error would occur. What could be the problem?

Upvotes: 0

Views: 2540

Answers (2)

saber shaikh
saber shaikh

Reputation: 9

string query = "UPDATE Employee set Emp_Id=@Emp_Id, fName=@FirstName, lName=@LastName, date=@Date_Employed, sal=@Monthly_Salary, ss=@SSSNum, dep=@depNo where Emp_Id=@Emp_Id";

Use Emp_Id not id if you don`t have an id

Upvotes: 0

devio
devio

Reputation: 37215

Your UPDATE uses the C# variable names, rather than the column names of the CREATE TABLE statement you provided.

Fix the SQL statement to

UPDATE Employee 
SET FirstName = @FirstName, 
    LastName = @LastName, 
    Date_Employed = @Date_Employed, 
    Monthly_Salary = @Monthly_Salary, 
    SSSNum = @SSSNum, 
    depNo = @depNo 
WHERE Emp_id = @Emp_Id

Also, you cannot update an IDENTITY column. (and don't need to, in this case)

Upvotes: 3

Related Questions