Why I get "Must declare the scalar variable "@rollnum"" error when I try to insert?

Question

my code given is--------

                SqlConnection cn = new SqlConnection(@"Data Source=CHROMA\SQLEXPRESS;Initial Catalog=marksheet;Integrated Security=True"); 
                cn.Open();

                if (cn != null)
                {

                    Console.WriteLine("Enter roll number");
                   string rollnum =Console.ReadLine();

                    Console.WriteLine("Enter name");
                    string name = Console.ReadLine();

                    Console.WriteLine("Enter gender");
                    string gen = Console.ReadLine();

                    Console.WriteLine("Enter DOB");
                    string DOB = Console.ReadLine();

                    Console.WriteLine("Enter father name");
                    string father = Console.ReadLine();

                    Console.WriteLine("Enter course");
                    string course = Console.ReadLine();

                    Console.WriteLine("Enter address");
                    string add = Console.ReadLine();

                    Console.WriteLine("Enter city");
                    string city = Console.ReadLine();

                    Console.WriteLine("Enter state");
                    string state = Console.ReadLine();

                    Console.WriteLine("Enter phone");
                    string phone = Console.ReadLine();
        SqlCommand cmd = new SqlCommand("insert into student (rollno,name,gender,dob,fname,course,address,city,state,phone) values(@rollnum,@name,@gen,@DOB,@father,@course,@add,@city,@state,@phone,)",cn);

                   int row= cmd.ExecuteNonQuery();
                    if(row >0)

                    Console.WriteLine("Record inserted...");
                    cn.Close();
                    cn.Dispose();
                }
                else
                    Console.WriteLine("please open connection first!!");

Isn't that obvious? You declare your parameters in your `SqlCommand` but you _never_ add them as a parameter. Add all your parameters and their values like `cmd.Parameters.Add("@rollnum", SqlDbType.NVarChar).Value = rollnum;` But before writing more code, please read a good book about ADO.NET. — Soner Gönül, Mar 04 '15 at 09:42
Possible duplicated of [Here](http://stackoverflow.com/questions/16168565/must-declare-the-scalar-variable) and [Here](http://stackoverflow.com/questions/21363415/must-declare-the-scalar-variable). — Aria, Mar 04 '15 at 09:44
By the way, why you tagged with `mysql`? If so, why you are using `SqlCommand`? — Soner Gönül, Mar 04 '15 at 11:38

score 0 · Answer 1 · answered Mar 04 '15 at 09:48

0

you should bind your parameters after creating the command and before executing the command. for example: cmd.Parameters.AddWithValue("@rollnum", rollnum); (do the same for all other parameters)

answered Mar 04 '15 at 09:48

Liad

413
2
4
18

score 0 · Answer 2 · answered Mar 04 '15 at 14:06

Since you didn't get full answer, here is mine..

As I said in my comment, you declared your parameter names in your SqlCommand but you never add them as a parameter name and their scalar values. That's why your error message says;

Must declare the scalar variable "@rollnum”

since it's a first parameter you defined in your command. And when you try to execute it, it is too normal to expect a scalar variable instead of a parameter name.

Also use using statement to dispose your SqlConnection and SqlCommand automatically instead of calling .Close() or .Dispose() methods manually.

And STATE can be reserved word in future SQL Server versions. You might need to use it as [STATE].

I assume your columns are nvarchar types, here is my example;

using(var cn = new SqlConnection(conString))
using(var cmd = cn.CreateCommand())
{
   cmd.CommandText = @"insert into student (rollno,name,gender,dob,fname,course,address,city,[state],phone) 
                       values(@rollnum,@name,@gen,@DOB,@father,@course,@add,@city,@state,@phone)";
   cmd.Parameters.Add("@rollnum", SqlDbType.NVarChar).Value = rollnum;
   cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = name;
   cmd.Parameters.Add("@gen", SqlDbType.NVarChar).Value = gen;
   cmd.Parameters.Add("@DOB", SqlDbType.NVarChar).Value = DOB;
   cmd.Parameters.Add("@father", SqlDbType.NVarChar).Value = father;
   cmd.Parameters.Add("@course", SqlDbType.NVarChar).Value = course;
   cmd.Parameters.Add("@add", SqlDbType.NVarChar).Value = add;
   cmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = city;
   cmd.Parameters.Add("@state", SqlDbType.NVarChar).Value = state;
   cmd.Parameters.Add("@phone", SqlDbType.NVarChar).Value = phone;

   cn.Open();
   if(cmd.ExecuteNonQuery() > 0)
      Console.WriteLine("Record inserted...");
}

Why I get "Must declare the scalar variable "@rollnum"" error when I try to insert?

2 Answers2