"Must declare the scalar variable "@pat" "

Question

0.00/5 (No votes)

See more:

I am kinda new to C# and am trying to make a form that gets info from the user and saves it to a database. I keep getting this error and don't know why or how to fix it.
below is the code. Thanks for any help.

var pat = textBox1.Text.Trim();
var res = textBox2.Text.Trim();
if (@pat.Length == 0 || @res.Length == 0)
{
MessageBox.Show("Please enter something in the boxes.");
return;
}
SqlConnection cnn = new SqlConnection(OMSDB_Connection_String);
string command = "Insert INTO OTM_unsubscriptions (dbPatCnt, dbRespCnt, Email, PracticeID) " +
" VALUES (@pat,@res, 1, 1)";
SqlCommand cmd = new SqlCommand(command, cnn);
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();

Posted 2-May-11 19:17pm

Derek1214

Updated 2-May-11 19:21pm

Rajesh Anuhya

v2

Add a Solution

5 solutions

Solution 1

//you are declaring 

var pat = textBox1.Text.Trim();
var res = textBox2.Text.Trim();

and checking 

if (@pat.Length == 0 || @res.Length == 0)
//instead  of it write 

if (pat.Length == 0 || res.Length == 0)

//As Abhinav has suggest 

string command = "Insert INTO OTM_unsubscriptions (dbPatCnt, dbRespCnt, Email, PracticeID) " +
" VALUES (@pat,@res, 1, 1)";
SqlCommand cmd = new SqlCommand(command, cnn);

Cmd.parameters.addwithvalue("@pat",pat);
Cmd.parameters.addwithvalue("@res",res);

Posted 2-May-11 19:25pm

Mahendra.p25

Updated 2-May-11 19:33pm

v2

Solution 2

You need to add the parameter -

command.Parameters.Add(new SqlParameter("dbPatCnt", @pat));<br />
command.Parameters.Add(new SqlParameter("dbRespCnt", @res));

Posted 2-May-11 19:27pm

Abhinav S

Comments

Sandeep Mewara 3-May-11 1:35am

I guess the parametername and value should swap the positions.

Abhinav S 3-May-11 5:12am

Yes. Writing pseudo code has its own drawbacks. :)

Sandeep Mewara 3-May-11 5:19am

After reading yours, I had to go back and look up by myself if I was thinking wrong - you made me do it! :)

CodeHawkz 3-May-11 2:01am

Yes, they should :)

Solution 4

here is your Corrected code

C++

string strtxt1 = textBox1.Text.Trim();
string strtxt2 = textBox2.Text.Trim();
if (strtxt1 == string.Empty || strtxt2== string.Empty)
{
MessageBox.Show("Please enter something in the boxes.");
return;
}
SqlConnection cnn = new SqlConnection(OMSDB_Connection_String);
string command = "Insert INTO OTM_unsubscriptions (dbPatCnt, dbRespCnt, Email, PracticeID) " +
" VALUES ('"+strtxt1+"','"+strtxt2+"', 1, 1)";
SqlCommand cmd = new SqlCommand(command, cnn);
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();

try with above code
Hope it helps

Posted 2-May-11 19:50pm

Ashishmau

Comments

CodeHawkz 3-May-11 2:02am

Please, do not suggest code with string concatenation for SQL. It is very bad practice. The OP already is using SQL parameters to pass data and help debug errors in his approach :)

Sandeep Mewara 3-May-11 5:19am

Agreed and virtual 5 for the comment.

CodeHawkz 3-May-11 5:26am

:) Thank you

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Accepted Answer · 2011-05-02T19:31:00

Solution 3

Try:

C#

SqlConnection cnn = new SqlConnection(OMSDB_Connection_String);
string command = "Insert INTO OTM_unsubscriptions (dbPatCnt, dbRespCnt, Email, PracticeID) " +
" VALUES (@pat,@res, 1, 1)";
SqlCommand cmd = new SqlCommand(command, cnn);
command.Parameters.Add(new SqlParameter(@pat, "valueOf_dbPatCnt"));
command.Parameters.Add(new SqlParameter(@res, "valueOf_dbRespCnt"));
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();

Look here for details:
Lesson 06: Adding Parameters to Commands[^]
Configuring Parameters and Parameter Data Types (ADO.NET)[^]

Posted 2-May-11 19:31pm

Sandeep Mewara

Updated 2-May-11 19:34pm

v2

Comments

Derek1214 3-May-11 17:23pm

When I tried the following code i recevied this error.
Error 2 'string' does not contain a definition for 'Parameters' and no extension method 'Parameters' accepting a first argument of type 'string' could be found (are you missing a using directive or an assembly reference?)

<pre>SqlCommand cmd = new SqlCommand(command, cnn);
command.Parameters.Add(new SqlParameter(@pat, "valueOf_dbPatCnt"));
command.Parameters.Add(new SqlParameter(@res, "valueOf_dbRespCnt"));
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close(); </pre>

Derek1214 · Accepted Answer · 2011-05-03T11:24:00

When I tried the following code i recevied this error.
Error 2 'string' does not contain a definition for 'Parameters' and no extension method 'Parameters' accepting a first argument of type 'string' could be found (are you missing a using directive or an assembly reference?)

SqlCommand cmd = new SqlCommand(command, cnn);
            command.Parameters.Add(new SqlParameter(@pat, "valueOf_dbPatCnt"));
            command.Parameters.Add(new SqlParameter(@res, "valueOf_dbRespCnt"));
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();