How to solve SqlException: Must declare the scalar variable "@RegNo" in ASP.Net

Question

 SqlCommand cmd = new SqlCommand("insert into Student(@RegNo,@Name,@Address,@CreatedTime) values(@RegNo,@Name,@Address,Getdate())");

here it display an error like SqlException:

Must declare the scalar variable "@RegNo".

what to do? Thanks

Answer 1

protected void Add_Click(object sender, EventArgs e)
{
  string constr = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
  try {
    using (SqlConnection con = new SqlConnection(constr))
      using (SqlCommand cmd = new SqlCommand("insert into Student(RegNo,Name,Address,CreatedTime)values(@RegNo,@Name,@Address,Getdate())", con)) {
        cmd.Parameters.AddWithValue("@RegNo", RegNo.Text);
        cmd.Parameters.AddWithValue("@Name", Name.Text);
        cmd.Parameters.AddWithValue("@Address", Address.Text);
        con.Open();

        cmd.ExecuteNonQuery();
      }
  } catch (Exception ex) {
    //handle exception..
    throw;
  }
}

It displays an error:

No mapping exists from object type System.Web.UI.WebControls.TextBox to a known managed provider native type.

Answer 2

Let me explain why did you get the error.

Your sql command is this: SqlCommand cmd = new SqlCommand("insert into Student(@RegNo,@Name,@Address,@CreatedTime) values(@RegNo,@Name,@Address,Getdate())");

This means you are telling sql to execute query that is equivalent to this:

insert into Student(@RegNo,@Name,@Address,@CreatedTime) values(1234,'name value','address value',GetDate()) -- i've put some vlaues for example

As you can see this is not correct.

When executing insert query, you want to specify column names and values, names goes into first bracket and values into second after Values keyword, so logically there is no column name "@RegNo" or "@Name" instead use real column names as specified in database "RegNo", "Name", so the query should look like this:

insert into Student(RegNo,Name,Address,CreatedTime) values(1234,'name value','address value',GetDate())

Why the error message?

In sql variables are declared with @ prefix for example: declare @a int=5; so, when trying to execute your query insert into Student(@RegNo,....... sql recognizes @RegNo as variable, since declaration can't be found you get an error message

Must declare the scalar variable @RegNo.

You should use '@' only to specify parameters not for column names

Try this in your c# code:

SqlCommand cmd = new SqlCommand("insert into Student(RegNo,Name,Address,CreatedTime) values(@RegNo,@Name,@Address,@CreatedTime)");
            cmd.Parameters.AddWithValue("@RegNo", "object value");
            cmd.Parameters.AddWithValue("@Name", "object value");
            cmd.Parameters.AddWithValue("@Address", "object value");
            cmd.Parameters.AddWithValue("@RegNo", "object value");
            cmd.Parameters.AddWithValue("@CreatedTime", DateTime.Now);  //replaced GetDate with DateTime.Now

!Note: When using SqlCommand you can always execute query in sql first, to see that you didn't made some mistake

Answer 3

Sql insert query has basically 2 parts.

1) Table ColumnNames for inserting values into

2) Actual values being inserted or @parameters having values.

Your SQL is mixing 1 with 2.

So instead of

SqlCommand cmd = new SqlCommand("insert into Student(@RegNo,@Name,@Address,@CreatedTime) values(@RegNo,@Name,@Address,Getdate())");

It should be

SqlCommand cmd = new SqlCommand("insert into Student(RegNo,-[Name],Address,CreatedTime) values(@RegNo,@Name,@Address,Getdate())");

After this you only need to add all @variables in the SqlCommand object. I can see others have already suggested you code for that.