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_Paramet er

{

get

{

return extract_paramet er;

}

set

{

extract_paramet er = value;

}

}

public void Load_Lunch_Menu _Extract()

{

try

{

//Connetion to DataBase

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

//Open Connection to DataBase

myConnection.Op en();

//SQL Command for myConnection

OleDbCommand myCommand = myConnection.Cr eateCommand();

//Initialize SQL SELECT command to retrieve data

myCommand.Comma ndText = "SELECT * FROM [Food Lunch Menu] WHERE
[ItemCatagory] = 'Extract_Parame ter'";

//Create DataReader

OleDbDataReader myDataReader = myCommand.Execu teReader();

//Cycle through data and display

test = new ArrayList();

while(myDataRea der.Read())

{

}

//Close Connection to DataBase

myDataReader.Cl ose();

myConnection.Cl ose();

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

}

catch(Exception myException)

{

MessageBox.Show (myException.Me ssage);

}

}

}

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.Cr eateCommand();

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

// Add the parameter.
myCommand.Param eters.Add(this. Extract_Paramet er);

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

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"MikeY" <mi*******@yaho .comwrote in message
news:ut******** ******@TK2MSFTN GP06.phx.gbl...

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_Paramet er

{

get

{

return extract_paramet er;

}

set

{

extract_paramet er = value;

}

}

public void Load_Lunch_Menu _Extract()

{

try

{

//Connetion to DataBase

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

//Open Connection to DataBase

myConnection.Op en();

//SQL Command for myConnection

OleDbCommand myCommand = myConnection.Cr eateCommand();

//Initialize SQL SELECT command to retrieve data

myCommand.Comma ndText = "SELECT * FROM [Food Lunch Menu] WHERE
[ItemCatagory] = 'Extract_Parame ter'";

//Create DataReader

OleDbDataReader myDataReader = myCommand.Execu teReader();

//Cycle through data and display

test = new ArrayList();

while(myDataRea der.Read())

{

}

//Close Connection to DataBase

myDataReader.Cl ose();

myConnection.Cl ose();

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

}

catch(Exception myException)

{

MessageBox.Show (myException.Me ssage);

}

}

}

Thanks,

MikeY

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

Hi,

myCommand.Comma ndText = "SELECT * FROM [Food Lunch Menu] WHERE
[ItemCatagory] = 'Extract_Parame ter'";

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

you could change it to:

myCommand.Comma ndText = "SELECT * FROM [Food Lunch Menu] WHERE
[ItemCatagory] = '" + Extract_Paramet er + "'";

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.Comma ndText = "SELECT * FROM [Food Lunch Menu] WHERE
[ItemCatagory] = @cat";
myCommand.Param eters.Add( "@cat" SqlDataType.Var char, 50).Value =
Extract_Paramet er ;


--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

[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.Cr eateCommand();

//Initialize SQL SELECT command to retrieve data

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

myCommand.Param eters.Add(new OleDbParameter ("@cat",Extract _Parameter));

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

//myCommand.Comma ndText = "SELECT * FROM [Food Lunch Menu] WHERE
[ItemCatagory] = '" + Extract_Paramet er + "'";

//Create DataReader

OleDbDataReader myDataReader = myCommand.Execu teReader();

MikeY

"MikeY" <mi*******@yaho .comwrote in message
news:ut******** ******@TK2MSFTN GP06.phx.gbl...

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_Paramet er

{

get

{

return extract_paramet er;

}

set

{

extract_paramet er = value;

}

}

public void Load_Lunch_Menu _Extract()

{

try

{

//Connetion to DataBase

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

//Open Connection to DataBase

myConnection.Op en();

//SQL Command for myConnection

OleDbCommand myCommand = myConnection.Cr eateCommand();

//Initialize SQL SELECT command to retrieve data

myCommand.Comma ndText = "SELECT * FROM [Food Lunch Menu] WHERE
[ItemCatagory] = 'Extract_Parame ter'";

//Create DataReader

OleDbDataReader myDataReader = myCommand.Execu teReader();

//Cycle through data and display

test = new ArrayList();

while(myDataRea der.Read())

{

}

//Close Connection to DataBase

myDataReader.Cl ose();

myConnection.Cl ose();

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

}

catch(Exception myException)

{

MessageBox.Show (myException.Me ssage);

}

}

}

Thanks,

MikeY