Sql instruction doesnt obey...

Israel

Hi!

I am writing sql code to search the number up or less than "x" (dsiplaying on my datagrid). But its doent work. For instance, show me all number up than 2 or less using a textbox. See my codes please:

conn.Open();

OleDbCommand cmd = conn.CreateCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "select * from Cliente where [age] ? ?"; // Its here

var param1 = new OleDbParameter("@idade", OleDbType.Numeric);

param3.Value = txtAge.Text;

cmd.Parameters.Add(param1);

cmd.ExecuteNonQuery();

DataTable dt = new DataTable();

OleDbDataAdapter da = new OleDbDataAdapter(cmd);

da.Fill(dt);

conn.Close();

dvgCons.DataSource = dt;

12 Replies

Know the answer? Post it — somebody with the same question will find it here.

ali tuncer10y ago
Accepted answer
Hi Israel,
'?' operator stands for parameters, but I don't think you can pass '<' or '>' as a parameter..it has to be value..
for instance :
cmd.CommandText = "insert into [dbo].[Table1]([Name],[LastName]) values (?,?)";
cmd.Parameters.AddWithValue("param1", "ali");
cmd.Parameters.AddWithValue("param2", "tuncer");

in same as : insert into [dbo].[Table1]([Name],[LastName]) values ('ali','tuncer')

if you want less you can write this way :

cmd.CommandText = "select * from Cliente where [age] < ?";
cmd.Parameters.AddWithValue("param1", txtAge.Text); //this will show values less than textbox

check this link for details :
https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters%28v=vs.110%29.aspx
Israel10y ago
@Ali

Big thanx. Sorry also because I do give wrong explanation. Now it's works properly.
Israel10y ago
@Ali
Sorry for that.
Its works now. But when I put this operator "?" for less its buggs. Why? Can you explain to me please.
ali tuncer10y ago
After my first reply, you said it shows the numbers up than not less than..
Now you are saying up or less does not work..
In my environment, it works fine..
can you try this way :
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from Cliente where [age] > ?";
cmd.Parameters.AddWithValue("param1", txtAge.Text); //this will show values greater than txtbox
cmd.ExecuteNonQuery();
Israel10y ago
Dears Sirs,
This is not normal. No one cant resolv this logic PLEASE???

With the operator "=" its works perfectly. Means its find in the number equal to the number inside of the textbox. Then why no one cannot correct this code writing the greater (ou lesser) than the number into textbox. All the code I do try doesnt work properly.

cmd.CommandText = "select * from Cliente where [age] = ?"; // this works!
cmd.CommandText = "select * from Cliente where [age] ? ?"; // doesnt works!
or
cmd.CommandText = "select * from Cliente where [age] ? ?"; // doesnt works!

Thanx.
Manas Mohapatra10y ago
Try:
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;

// Here UP is either you need to search greater or lesser from ur UI or others
if(up)
{
    cmd.CommandText = "select * from Cliente where [age] > ?"; // Its here
}
else
{
    cmd.CommandText = "select * from Cliente where [age] < ?"; // Its here
}

var param1 = new OleDbParameter("@idade", OleDbType.Numeric);
param3.Value = txtAge.Text;
Israel10y ago
@Ali
Not between that I was asking. I said that "up" than "1" for example.
For instance, show me all number(s) up than "5". This is point I need.

Thanx
ali tuncer10y ago
it will show numbers between, for example Age between 10 and 30..do you mean something else?
Israel10y ago
@Ali
Your code doesnt work properly.
Its show only the number up than (npt less than) . This is wrong.
Please review your codes.

Thanx
ali tuncer10y ago
Hi Joginder,

I tested now, it works on my machine, I am using SQL Server 2014, .NET 4.5 framework..
I am not very familiar with OleDbConnection, couple places I've seen them put ';' at the end.
can you try this way :

cmd.CommandText = "select * from Table1 where [Age] > ? and [Age] < ?;"
Joginder Banger10y ago
Hi Ali,

I am not sure your query working now. I trying this way I have exception "Incorrect syntax near '?'."

Please check and let me know how its working now.
ali tuncer10y ago
try this way:

cmd.CommandText = "select * from Cliente where [Age] > ? and [Age] < ?";
var param1 = new OleDbParameter("@param1", OleDbType.Numeric);
param1.Value = 2;
var param2 = new OleDbParameter("@param2", OleDbType.Numeric);
param2.Value = txtAge.Text;
cmd.Parameters.Add(param1);
cmd.Parameters.Add(param2);
cmd.ExecuteNonQuery();