SQL Parameter

[Chris Divine]

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'.

 

[PokerMan]

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]

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

 

[Bobbo]

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())
{
}

 

[PokerMan]

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();

 

[Chris]

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.

 

[PokerMan]

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:

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.

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.

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