What does it mean "must declare scalar variable" error in C# form?

Question

I tried to update form but get error:

Must declare the scalar variable "@isyeri".

My code and SQL Server table code is below.

 using (OleDbConnection con = new OleDbConnection("Provider=sqloledb;SERVER=NEVZAT-PC;DATABASE=DENEME;User ID=sa;password=sapass;"))
 using (OleDbCommand cmd = new OleDbCommand("update istakip set isyeri=@isyeri,isaciklama=@isaciklama,durum=@durum,personel=@personel,tarih=@tarih,baslamasaati=@baslamasaati,netsure=@netsure,kullanilanmalzeme=@kullanilanmalzeme,isbolum=@isbolum,arizatanim=@arizatanim,isemrino=@isemrino,kadi=@kadi where id=@id)", con))
 {
     cmd.Parameters.AddWithValue("@isyeri", comboBox1.Text);
     cmd.Parameters.AddWithValue("@isaciklama", textBox2.Text);
     cmd.Parameters.AddWithValue("@durum", comboBox2.Text);
     cmd.Parameters.AddWithValue("@personel", comboBox3.Text);
     cmd.Parameters.AddWithValue("@tarih", dateTimePicker1.Value);
     cmd.Parameters.AddWithValue("@baslamasaati", dateTimePicker2.Value.ToString());//veritabanında varchar türünde bu
     cmd.Parameters.AddWithValue("@netsure", textBox3.Text);
     cmd.Parameters.AddWithValue("@kullanilanmalzeme", textBox4.Text);
     cmd.Parameters.AddWithValue("@isbolum", comboBox4.Text);
     cmd.Parameters.AddWithValue("@arizatanim", comboBox5.Text);
     cmd.Parameters.AddWithValue("@isemrino", textBox5.Text);
     cmd.Parameters.AddWithValue("@kadi", label13.Text);
     cmd.Parameters.AddWithValue("@id", dataGridView1.CurrentRow.Cells[0].Value);

     con.Open();
     cmd.ExecuteNonQuery();
     con.Close();
}

and my SQL Server table structure:

CREATE TABLE [dbo].[istakip]
(
    [id] [numeric](5, 0) IDENTITY(1,1) NOT NULL,
    [isyeri] [nvarchar](50) NULL,
    [isaciklama] [varchar](max) NULL,
    [durum] [nvarchar](50) NULL,
    [personel] [nvarchar](50) NULL,
    [tarih] [datetime] NULL,
    [baslamasaati] [nvarchar](50) NULL,
    [netsure] [nvarchar](50) NULL,
    [kullanilanmalzeme] [varchar](max) NULL,
    [isbolum] [nvarchar](100) NULL,
    [arizatanim] [nvarchar](100) NULL,
    [isemrino] [nvarchar](50) NULL,
    [kadi] [nvarchar](50) NULL
)

Providing your sa-password on a public site is not the best idea — Tim Schmelter, Sep 21 '18 at 13:34
iirc OleDbCommand requires unnamed parameters so use ordinal `?` characters instead of @names (or if your using just MSSQL use System.Data.SqlClient) — Alex K., Sep 21 '18 at 13:35
@AlexK.: but shouldn't it also work if you use `@` and the positions are correct? — Tim Schmelter, Sep 21 '18 at 13:41
I don't think so, the server sees the @isyeri token & thinks is an undeclared variable because it wasn't substituted within the command. — Alex K., Sep 21 '18 at 13:48
You can see the code.what is wrong with the code?ı declare @isyeri — nevzat altıparmak, Sep 21 '18 at 13:50
http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ — Sean Lange, Sep 21 '18 at 14:15
See the duplicate. The exception text is the different because the platform is different but the cause is the same. — Igor, Sep 21 '18 at 14:19
dont use AddWithValue, it can produce exact these kind of problems. See this https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ — GuidoG, Sep 21 '18 at 14:27

score 1 · Answer 1 · edited Sep 21 '18 at 14:13

1

One possibility is some of your parameter value are null. When you use null values for parameters you must set them to DBNull, otherwise the parameter will not be passed to the SQL statement:

cmd.Parameters.AddWithValue("@isyeri", ((object) comboBox1.Text ?? DBNull.Value));

That said, I do not think the Text property of a ComboBox can be null, but it's a good habit to get into when dealing with SQL parameters.

edited Sep 21 '18 at 14:13

Igor

60,821
10
100
175

answered Sep 21 '18 at 14:03

D Stanley

149,601
11
178
240

sorry but didn't work.it says cannot be applied – nevzat altıparmak Sep 21 '18 at 14:07
@nevzataltıparmak - you need to cast to object so they are the same type (see answer update). This is the answer you are looking for though, I am searching for a duplicate because I know I have seen this before on [so]. – Igor Sep 21 '18 at 14:13

Hassan · Answer 2 · 2018-09-21T14:34:44.917

1

Reference to following link:

MSDN Link for OleDBCommand

For the case of OleDbCommand:

Change @ with ? and use same sequence for adding parameter.

As said in the comment by @Igor, both symbols can be used. Check below parameters adding for OleDb. Also it seems you need to cast cell value into integer for the @id parameter.

cmd.Parameters.Add(
        "@isyeri", OleDbType.VarChar, 50).Value = comboBox1.Text;
//...

cmd.Parameters.Add(
        "@id", OleDbType.Integer).Value = Convert.ToInt32(dataGridView1.CurrentRow.Cells[0].Value);

edited Sep 21 '18 at 14:34

answered Sep 21 '18 at 14:05

Hassan

5,360
2
22
35

1

Although `?` can be used so can `@pname` although the ordinal position is the only thing that matters in either case. The error above is not caused by the name of the parameter or the position as the positions in the parameter collection match the position in the statement. – Igor Sep 21 '18 at 14:08

What does it mean "must declare scalar variable" error in C# form?

2 Answers2