Must be declared scalar value "@iD_User

Question

`ql = "select ID from Users where Username = '" + txtusername.Text + "';";
                                    cmd = new SqlCommand(sql, Sel_Menu.con);                                       
                                    Sel_Menu.con.Open();
                                    IDD = int.Parse(cmd.ExecuteScalar().ToString()); //here I get int32
                                    Sel_Menu.con.Close();
                                                                        IDD = 15;
                                    sql = "insert into Action_Log ([ID_User],[Action_NR],[AtWhatTime]) values (@iDUser,@action_NR,getdate())";
                                    cmd = new SqlCommand(sql, Sel_Menu.con);
                                    cmd.Parameters.AddWithValue("@iDUser", IDD+1-1);
                                    cmd.Parameters.AddWithValue("@action_NR", 1);
                                    cmd = new SqlCommand(sql, Sel_Menu.con);
                                    Sel_Menu.con.Open();
                                        cmd.ExecuteNonQuery(); //done also with cmd.ExecuteScalar(); ...
                                    Sel_Menu.con.Close();`

How can I fix this? still I have "Must be declared scalar value "@iD_User"" error - everything I do - does not change this error - even not to any other error.

You instantiate a new SqlCommand after adding your parameters. So obviously your command doesn't have your parameters anymore. — krimog, Jun 25 '14 at 14:56
just to make @Ksven happy: *please please please* parameterize that `txtusername.Text` — Marc Gravell, Jun 25 '14 at 15:02

Steve · Accepted Answer · 2014-06-25T15:09:51.857

3

Just move the line that reinitialize the SqlCommand before the declarations of the parameters

  cmd = new SqlCommand(sql, Sel_Menu.con);
  cmd.Parameters.AddWithValue("@iD_User",IDD); 
  cmd.Parameters.AddWithValue("@action_NR", 1);
  cmd.Parameters.AddWithValue("@atWhatTime","getdate()");

You code is adding the parameters to the previous instance of cmd not to the actually executed command. Notice also that the method SqlParameterCollection.Add(string, SqlDbType, int) means, add a parameter with name, type and SIZE. But it doesn't set the value for the parameter.

There is another error. The getDate() function is a T-SQL function. As you are writing it you are passing the string "getDate()" to your last parameter. Move it directly in the Sql command text and remove the third parameter.

sql = @"insert into Action_Log (ID_User,Action_NR,AtWhatTime) values  
                                (@iD_User,@action_NR,getDate())";

Last but not least. In this query you use a parameterized approach (good), while the first one use a string concatenation (bad). Use always parameters to avoid Sql Injection and parsing problems.

edited Jun 25 '14 at 15:09

answered Jun 25 '14 at 14:53

Steve

213,761
22
232
286

Thanks... I didn't noticed this mistake :/ – AssassinLV Jun 25 '14 at 14:57
still same error.... I added "cmd = new SqlCommand(sql, Sel_Menu.con);" excatly after sql="..." – AssassinLV Jun 25 '14 at 15:02
Done everything like in here - but still I have problems with that iD_User – AssassinLV Jun 25 '14 at 15:08
Go back to the syntax for AddWithValue – Steve Jun 25 '14 at 15:09
The syntax `Add(string, SqlDbType, int32)` means add a parameter with name, type and SIZE, NOT VALUE. – Steve Jun 25 '14 at 15:11
did already - but still no good... "cmd.Parameters.AddWithValue("@iD_User", IDD);" – AssassinLV Jun 25 '14 at 15:11
Thanks, but with "cmd.Parameters.AddWithValue("@iD_User", IDD);" still I have problems. :/ Like it doesn't like int inside variable... – AssassinLV Jun 25 '14 at 15:20
And the error is still the same? _Must be declared scalar value "@iD_User_ – Steve Jun 25 '14 at 15:28
And you should use ExecuteNonQuery to execute this command, not ExecuteScalar (also if I think that it is the same) – Steve Jun 25 '14 at 15:29
Yes error is same. (and that ExecuteNonQuery didn't helped.) – AssassinLV Jun 25 '14 at 15:32
OK, now it is the time to start the debugger, put a breakpoint on the line the define the sql text (F9) and the run your program (F5) until you hit the breakpoint, then proceed step by step (F10) looking at the actual values of your variables. (watch window, local window) – Steve Jun 25 '14 at 15:35
here is sql="insert into Action_Log (ID_User,Action_NR,AtWhatTime) values (@iD_User,@action_NR,getdate())" – AssassinLV Jun 25 '14 at 15:48
and the cmd.Parameters collection contains all the parameters declared when your code reaches the `cmd.ExecuteNonQuery` line? – Steve Jun 25 '14 at 15:52
It does - I have no idea where is problem. :/ I will try one trick to be sure that int is not a problem (I will make +1-1) maybe like this it will work... – AssassinLV Jun 25 '14 at 15:56
whatever I do - it does not work... now I will define int manualy like IDD=15; – AssassinLV Jun 25 '14 at 15:59
Check also the Action_Log table for the exact correspondence of column names. – Steve Jun 25 '14 at 16:01
Even with manualy defined IDD - it still makes the problem :/ Still except scalar value :/ – AssassinLV Jun 25 '14 at 16:02
I checked it (multiple times). - I can see it on side - but still error. – AssassinLV Jun 25 '14 at 16:03
Now I changed to: "sql = "insert into Action_Log ([ID_User],[Action_NR],[AtWhatTime]) values (@iDUser,@action_NR,getdate())";" still same problem :/ wtf? – AssassinLV Jun 25 '14 at 16:06
Try removing the parameter and passing a constant in the sql text `values(15,@action_NR....)` – Steve Jun 25 '14 at 16:20
I found the mistake - I had wroted "cmd = new SqlCommand(sql, Sel_Menu.con);" after parsing the variables - not before.... Now everything works (I will check in database - did the time had been wrote correct) – AssassinLV Jun 25 '14 at 16:28

Habib · Answer 2 · 2014-06-25T18:30:26.150

Your statement cmd = new SqlCommand(sql, Sel_Menu.con); should come before adding parameters to the command.

Since you are using a single object cmd to execute both commands, it is adding parameters to previous reference and later when you initialize it again using new SqlCommand(sql, Sel_Menu.con) your parameters are lost.

sql = "insert into Action_Log (ID_User,Action_NR,AtWhatTime) values (@iD_User,@action_NR,@atWhatTime)";
cmd = new SqlCommand(sql, Sel_Menu.con);
cmd.Parameters.AddWithValue("@iD_User",SqlDbType.Int, IDD); //I had also tried: "cmd.Parameters.AddWithValue("@iD_User", IDD)"
cmd.Parameters.AddWithValue("@action_NR", 1);
cmd.Parameters.AddWithValue("@atWhatTime",DateTime.Now);
// Either pass `getdate` in your string query or send `DateTime.Now` as parameter. 
//Note that DateTime.Now could result in a different value than getdate. 
//Thanks to @Steve answer

Consider using parameters with your first command as well, otherwise your code is prone to SQL Injection. Also consider enclosing your command and connection object in using statement, that will ensure the disposal of resources.

score 2 · Answer 3 · answered Jun 25 '14 at 14:55

You need to initialize your cmd before you try to add your parameters. Move this line before your parameter lines.

cmd = new SqlCommand(sql, Sel_Menu.con);

And SqlParameterCollection.Add(String, SqlDbType, Int32) overload takes size as a third parameter not value. You might need to use other overloads.

You should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

By the way, use using statement to dispose your database connections and commands.

Must be declared scalar value "@iD_User

3 Answers3