By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,195 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,195 IT Pros & Developers. It's quick & easy.

SQL Parameter

P: n/a
Hello,
I sure this is simple, but I cannot see my way through it. I simply want
to add the boolean parameter to the sql string - but can't, as I get the
error message below. Help much appreciated.

MyConnection.Open();

SqlCommand mySqlCommand = new SqlCommand(
"SELECT * FROM Engineer " +
"WHERE STILLEMPLOYED =
@stillemployed");

mySqlCommand.CommandType = CommandType.Text;
mySqlCommand.Parameters.Add(new SqlParameter("@stillemployed",
SqlDbType.Bit));
mySqlCommand.Parameters["@stillemployed"].Value = checkBox11.Checked;
DataSet myDataSet = new DataSet("Engineers");
try
{
(new SqlDataAdapter(mySqlCommand.CommandText,
MyConnection)).Fill(myDataSet, "Engineers");
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("SQL: " + ex.Message,
"SQL fout");
}

MyConnection.Close();

Error
SQL: Must declare the variable '@stillemployed'.
Feb 2 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
As your not using a stored procedure why not do this:

MyConnection.Open();

int bit = 0;

if(checkBox11.Checked)
bit=1;

string sql = "SELECT * FROM Engineer " +
"WHERE STILLEMPLOYED =" + bit;

SqlCommand mySqlCommand = new SqlCommand(sql);

DataSet myDataSet = new DataSet("Engineers");

try
{
(new SqlDataAdapter(mySqlCommand.CommandText,
MyConnection)).Fill(myDataSet, "Engineers");
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("SQL: " + ex.Message,
"SQL fout");
}

MyConnection.Close();
Your original error tho, looks like sql server doesn't know the var. I
believe you use parameters like that for stored procedures. All my database
code is in stored procedures so thats the only time i use it and always
works for me. So i assume thats why you are getting an error.
"Chris Divine" <ds*****@ntlworld.comwrote in message
news:3D******************@newsfe5-win.ntli.net...
Hello,
I sure this is simple, but I cannot see my way through it. I simply want
to add the boolean parameter to the sql string - but can't, as I get the
error message below. Help much appreciated.

MyConnection.Open();

SqlCommand mySqlCommand = new SqlCommand(
"SELECT * FROM Engineer " +
"WHERE STILLEMPLOYED =
@stillemployed");

mySqlCommand.CommandType = CommandType.Text;
mySqlCommand.Parameters.Add(new SqlParameter("@stillemployed",
SqlDbType.Bit));
mySqlCommand.Parameters["@stillemployed"].Value = checkBox11.Checked;
DataSet myDataSet = new DataSet("Engineers");
try
{
(new SqlDataAdapter(mySqlCommand.CommandText,
MyConnection)).Fill(myDataSet, "Engineers");
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("SQL: " + ex.Message,
"SQL fout");
}

MyConnection.Close();

Error
SQL: Must declare the variable '@stillemployed'.


Feb 2 '07 #2

P: n/a
Hello,
thanks for your reply. I rarely used Stored Procedures,
most of my SQL calls in my apps are client side (I am
porting an app from Delphi to C# and there are hundreds of
client side SQL queries, the code I supplied is the simplest
example - and so I must be able to use Parameters).

It appears to me that the parameter value is not being set
in the command string, but why ?

thanks again

Feb 2 '07 #3

P: n/a
On 2 Feb, 17:15, "Chris" <dsof...@ntlworld.comwrote:
It appears to me that the parameter value is not being set
in the command string, but why ?
Chris,

I'd double check everything - this syntax works fine for me.

SqlConnection con = new SqlConnection("...");
string cmdText = "select * from table where field = @prm";
con.Open();
SqlCommand cmd = new SqlCommand(cmdText, con);
cmd.Parameters.Add(new SqlParameter("@prm", 1));
SqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())
{
}

Feb 2 '07 #4

P: n/a
ah i see. Well this is why stored procedures are better, imagine how much
easier your job would be if this was all database side?

"most of my SQL calls in my apps are client side"

Even more reason to use stored procedures for secuirty. If your client app
was hacked they'd get plenty of db info. that aside they are just good
practice. This comment tho almost implies you don't think you can call a
stored procedure from client side? I presume you know you can right?

The error you got was saying that the variable wsn't declared not that it
wasn't being set. it wasn't even getting that far. Either way try this kind
of syntax and see if it works:

SqlParameter param = new SqlParameter();
param.ParameterName = "@stillemployed";
param.Value = checkBox1.Checked;

SqlCommand cmd = new SqlCommand(
"SELECT * FROM Engineer " +
"WHERE STILLEMPLOYED =
@stillemployed");, MyConnection);

cmd.Parameters.Add(param);

DataSet myDataSet = new DataSet("Engineers");
try
{
(new SqlDataAdapter(mySqlCommand.CommandText,
MyConnection)).Fill(myDataSet, "Engineers");
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("SQL: " + ex.Message,
"SQL fout");
}

MyConnection.Close();
Feb 2 '07 #5

P: n/a
On Feb 2, 5:39 pm, "PokerMan" <nos...@pokercat.co.ukwrote:
ah i see. Well this is why stored procedures are better, imagine how much
easier your job would be if this was all database side?

Not necessarily so, as this arrangement may well evolve into a DBA and a developer position
ie two jobs, and companies don't like it.

"most of my SQL calls in my apps are client side"

Even more reason to use stored procedures for secuirty. If your client app
was hacked they'd get plenty of db info. that aside they are just good
practice. This comment tho almost implies you don't think you can call a
stored procedure from client side? I presume you know you can right?

I do use Stored Procedures (in Delphi apps, I am only just migrating to C#). Incidentally Delphi
is so much better at data access than this C# muddle that I am trying to wade through.Regarding
security the use of parameters lessens (eliminates ?) injection attacks.
The error you got was saying that the variable wsn't declared not that it
wasn't being set. it wasn't even getting that far. Either way try this kind
of syntax and see if it works:

I stll get the same error message with the code below. I have tried
using a string instead of a bool - but no difference. Without using a paramter
the code executes OK. Is param.ParameterName = "@stillemployed" not the declaration ?
(PS I'm using SQL Server SP4, and Visual Studio 2005 SP1 on XP)

SqlParameter param = new SqlParameter();
param.ParameterName = "@stillemployed";
param.Value = checkBox1.Checked;

SqlCommand cmd = new SqlCommand(
"SELECT * FROM Engineer " +
"WHERE STILLEMPLOYED =
@stillemployed");, MyConnection);

cmd.Parameters.Add(param);

DataSet myDataSet = new DataSet("Engineers");
try
{
(new SqlDataAdapter(mySqlCommand.CommandText,
MyConnection)).Fill(myDataSet, "Engineers");
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("SQL: " + ex.Message,
"SQL fout");
}

MyConnection.Close();

Feb 2 '07 #6

P: n/a
Umm i am not sure then code wise, the problem lies elsewhere, the help you
have had in this thread should have solved your issue. In response to your
other replies:
>Not necessarily so, as this arrangement may well evolve into a DBA and a
developer position
ie two jobs, and companies don't like it.
Companies don't like it? You aure about that? ;) . This separation allows
for scalabilty and easier maintenance.Also if a sql change is made it can be
done without a code recompile. If your code side gets too busy and your
coders are getting all tied up and are wasting precious time on sql issues,
you can bring in a dba man to worry about the sql and the coders can do
their other jobs, list goes on of benefits.
>I do use Stored Procedures (in Delphi apps, I am only just migrating to
C#). Incidentally Delphi
is so much better at data access than this C# muddle that I am trying to
wade through.
When you are an inexperienced c# coder it is very poor to say the language
is why you are having issues. Personally and i think others will agree c# is
very slick at the database access layer. I made a class with 3 methods, and
pass in a string of params and values and voila nice and easy. One line of
code, all running off....yep...stored procedures. Even without stored
procedures, its still slick.
>>Regarding security the use of parameters lessens (eliminates ?) injection
attacks.
It does, but a stored procedure is better. Client sees your sql code and
gets a snapshot of your database. With a stored procedure they dont.
Good luck
"Chris" <ds*****@ntlworld.comwrote in message
news:11*********************@l53g2000cwa.googlegro ups.com...
On Feb 2, 5:39 pm, "PokerMan" <nos...@pokercat.co.ukwrote:
>ah i see. Well this is why stored procedures are better, imagine how much
easier your job would be if this was all database side?

Not necessarily so, as this arrangement may well evolve into a DBA and a
developer position
ie two jobs, and companies don't like it.

"most of my SQL calls in my apps are client side"

Even more reason to use stored procedures for secuirty. If your client
app
was hacked they'd get plenty of db info. that aside they are just good
practice. This comment tho almost implies you don't think you can call a
stored procedure from client side? I presume you know you can right?

I do use Stored Procedures (in Delphi apps, I am only just migrating to
C#). Incidentally Delphi
is so much better at data access than this C# muddle that I am trying to
wade through.Regarding
security the use of parameters lessens (eliminates ?) injection attacks.
>The error you got was saying that the variable wsn't declared not that it
wasn't being set. it wasn't even getting that far. Either way try this
kind
of syntax and see if it works:

I stll get the same error message with the code below. I have tried
using a string instead of a bool - but no difference. Without using a
paramter
the code executes OK. Is param.ParameterName = "@stillemployed" not the
declaration ?
(PS I'm using SQL Server SP4, and Visual Studio 2005 SP1 on XP)

SqlParameter param = new SqlParameter();
param.ParameterName = "@stillemployed";
param.Value = checkBox1.Checked;

SqlCommand cmd = new SqlCommand(
"SELECT * FROM Engineer " +
"WHERE STILLEMPLOYED =
@stillemployed");, MyConnection);

cmd.Parameters.Add(param);

DataSet myDataSet = new DataSet("Engineers");
try
{
(new SqlDataAdapter(mySqlCommand.CommandText,
MyConnection)).Fill(myDataSet, "Engineers");
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("SQL: " +
ex.Message,
"SQL fout");
}

MyConnection.Close();


Feb 2 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.