c# debugging getting scalar variable error

Question

I need your assistance. I have created form and insert some data into SQL Server. But I always get an error. I think I made a mistake when creating the SQL Server connection.

code is below

OleDbConnection con = new OleDbConnection("Provider=sqloledb;SERVER=NEVZAT-PC;DATABASE=DENEME;User ID=11;password=1111;");

OleDbCommand cmd = new OleDbCommand("insert into isemri (isemrino,isyeri,isalani,isemridet,bastar,bittar)values(@isemrino,@isyeri,@isalani,@isemridet,@bastar,@bittar)", con);

cmd.Parameters.AddWithValue("@isemrino", Convert.ToInt32(textBox1.Text));
cmd.Parameters.AddWithValue("@isyeri", comboBox1.Text);
cmd.Parameters.AddWithValue("@isalani", comboBox2.Text);
cmd.Parameters.AddWithValue("@isemridet", richTextBox1.Text);
cmd.Parameters.AddWithValue("@bastar", dateTimePicker1.Value);
cmd.Parameters.AddWithValue("@bittar", dateTimePicker2.Value);

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

and I get this error:

enter image description here

**WHY** are you using `OleDbConnection` ?? You should use `SqlConnection` and `SqlCommand` for communicating with SQL Server, whenever possible — marc_s, Sep 08 '18 at 06:37
thank you.but ı used this code to open connection.Ididn't get it. — nevzat altıparmak, Sep 08 '18 at 06:38
Dear sir,You mean OLDB connection is not useful for connection or what? — nevzat altıparmak, Sep 08 '18 at 06:40
Save yourself a lot of effort and just search the internet FIRST before asking. [Here](https://stackoverflow.com/questions/2675610/how-to-update-a-table-using-oledb-parameters) is one example that discusses using parameters. In addition, [stop using addwithvalue](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) — SMor, Sep 08 '18 at 12:46

score 0 · Answer 1 · answered Sep 08 '18 at 07:21

0

From what it seems to me the issue is that you're using undeclared variables in your query, you have to either declare the variables before insert, or use other methodes that don't use variables.

answered Sep 08 '18 at 07:21

Tonner Mààn

133
1
11

1

If fairly positive that the issue is that OleDb doesn't support named parameters, only positional ones. The parameters in the query itself should look like `?` and *not* `@paramName`. Guessing at an answer doesn't help; at best this should've been a comment on the OP – pinkfloydx33 Sep 08 '18 at 08:55
that was my point, i just recently got the permission to comment so i'm still not used to that and i go directly to the answer section. – Tonner Mààn Sep 08 '18 at 09:01

saAction · Answer 2 · 2018-09-08T08:45:10.583

You are receiving "Must declare the scalar variable" error.

OleDb does not support named parameters. I presume this is what is causing the errors. Instead, within the SQL query, use ? instead of the param name, and ensure the order of parameters added matches the order they appear in the query.

Apply ? sign in insert query
Tried with using statement
[Optional step : ] Applied all parameter, also used DBNull.Value in case of the table field is nullable

Please check below code:

OleDbConnection con = new OleDbConnection("Provider=sqloledb;SERVER=NEVZAT-PC;DATABASE=DENEME;User ID=11;password=1111;");

using(OleDbCommand cmd = new OleDbCommand("insert into isemri (isemrino,isyeri,isalani,isemridet,bastar,bittar) values (?, ?, ?, ?, ?, ?)", conn))
{
       cmd.Parameters.AddWithValue("@isemrino", Convert.ToInt32(textBox1.Text));
       cmd.Parameters.AddWithValue("@isyeri", comboBox1.Text ?? DBNull.Value);
       cmd.Parameters.AddWithValue("@isalani", comboBox2.Text ?? DBNull.Value);
       cmd.Parameters.AddWithValue("@isemridet", richTextBox1.Text ?? DBNull.Value);
       cmd.Parameters.AddWithValue("@bastar", dateTimePicker1.Value ?? DBNull.Value);
       cmd.Parameters.AddWithValue("@bittar", dateTimePicker2.Value ?? DBNull.Value);


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

`Convert.ToInt32(textBox1.Text) ?? DBNull.Value);` is invalid. `Convert.ToInt32` returns an int, which can never be null so null coalescing operator is a syntax error here. — pinkfloydx33, Sep 08 '18 at 08:21
Apply just `cmd.Parameters.AddWithValue("@isemrino", Convert.ToInt32(textBox1.Text));` and remove `?? DBNull.Value` — saAction, Sep 08 '18 at 08:43
I know that. But the OP doesn't (based on the response they posted as an answer). Update *your* answer to reflect that instead of trying to explain in the comments. — pinkfloydx33, Sep 08 '18 at 08:44

c# debugging getting scalar variable error

2 Answers2