SQL WHERE Command Help [C# Win]

[MikeY]

Hi everyone,

I'm having problems with my WHERE Clause syntax with in my SQL CommandText.
The error that it is display is "You Have No Data". My problem lies with in
the WHERE clause not finding up my passed variable from my get/set function.
I have done watch's on the variable and the data name I'm looking for is
present.

I thought my syntax was correct, but maybe it's not. Can someone look it
over and see if there is anything wrong with it and give me a correct
solution if there is something wrong. Thank you all in advance.

public string Extract_Parameter

{

get

{

return extract_parameter;

}

set

{

extract_parameter = value;

}

}



public void Load_Lunch_Menu_Extract()

{

try

{

//Connetion to DataBase

OleDbConnection myConnection = new OleDbConnection(@"Provider =
Microsoft.Jet.OLEDB.4.0;Data Source = C:\Host 017.mdb");

//Open Connection to DataBase

myConnection.Open();

//SQL Command for myConnection

OleDbCommand myCommand = myConnection.CreateCommand();

//Initialize SQL SELECT command to retrieve data

myCommand.CommandText = "SELECT * FROM [Food Lunch Menu] WHERE
[ItemCatagory] = 'Extract_Parameter'";

//Create DataReader

OleDbDataReader myDataReader = myCommand.ExecuteReader();

//Cycle through data and display

test = new ArrayList();



while(myDataReader.Read())

{

}

//Close Connection to DataBase

myDataReader.Close();

myConnection.Close();



test.Sort(0, test.Count, new ArrayList_Sort());

}

catch(Exception myException)

{

MessageBox.Show(myException.Message);

}

}

}



Thanks,



MikeY

 

[Nicholas Paldino [.NET/C# MVP]]

Mike,

When you want to parameterize a query, you have to manually add the
parameter to the command, as well as set the value. The command does not
reflect on anything to get the value you are expecting.

What you need to do is this:

OleDbCommand myCommand = myConnection.CreateCommand();

//Initialize SQL SELECT command to retrieve data
myCommand.CommandText = "SELECT * FROM [Food Lunch Menu] WHERE
[ItemCatagory] = '@itemCategory'";

// Add the parameter.
myCommand.Parameters.Add(this.Extract_Parameter);

The call to add should extract a parameter with the appropriate value
and then execute properly.

Hope this helps.

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

myCommand.CommandText = "SELECT * FROM [Food Lunch Menu] WHERE
[ItemCatagory] = 'Extract_Parameter'";

Here is where your problem is, you are saying that {itemcategory] has to be
"Extran_Parameter"

you could change it to:

myCommand.CommandText = "SELECT * FROM [Food Lunch Menu] WHERE
[ItemCatagory] = '" + Extract_Parameter + "'";

I would suggest you to use parameterized queries it will avoid such problems
and avoid the risk of sql injection

This is how it would looks like:
myCommand.CommandText = "SELECT * FROM [Food Lunch Menu] WHERE
[ItemCatagory] = @cat";
myCommand.Parameters.Add( "@cat" SqlDataType.Varchar, 50).Value =
Extract_Parameter ;

 

[MikeY]

Just typing an update for others that might need this line of code. Hope
this helps others as these two fellows helped me. Txs Again.

//SQL Command for myConnection

OleDbCommand myCommand = myConnection.CreateCommand();

//Initialize SQL SELECT command to retrieve data

myCommand.CommandText = "SELECT * FROM [Food Lunch Menu] WHERE
[ItemCatagory] = @cat";

myCommand.Parameters.Add(new OleDbParameter ("@cat",Extract_Parameter));



//This Syntax works, but opens your program up too Syntax Attacks "sql
injection"

//myCommand.CommandText = "SELECT * FROM [Food Lunch Menu] WHERE
[ItemCatagory] = '" + Extract_Parameter + "'";



//Create DataReader

OleDbDataReader myDataReader = myCommand.ExecuteReader();



MikeY