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

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
– Tim Schmelter, Commented Sep 21, 2018 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.
– Alex K., Commented Sep 21, 2018 at 13:35
@AlexK.: but shouldn't it also work if you use @ and the positions are correct? — Tim Schmelter
– Tim Schmelter, Commented Sep 21, 2018 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.
– Alex K., Commented Sep 21, 2018 at 13:48

Igor · Accepted Answer · 2018-09-21 14:13:22Z

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, 2018 at 14:13

Igor

62.5k10 gold badges111 silver badges181 bronze badges

answered Sep 21, 2018 at 14:03

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

nevzat altıparmak Over a year ago

sorry but didn't work.it says cannot be applied

Igor Over a year ago

@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 Stack Overflow.

Hassan Nisar · Accepted Answer · 2018-09-21 14:34:44Z

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, 2018 at 14:34

answered Sep 21, 2018 at 14:05

Hassan Nisar

5,4502 gold badges26 silver badges35 bronze badges

1 Comment

Igor Over a year ago

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.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Linked

Related