Parameters Question

[William \(Bill\) Vaughn]

I stand corrected. It turns out that while you don't need "?", the "@"
prefix on the "named" parameter works fine--but the names are not used for
anything but placeholders. The marker strings simply need to be unique to be
differentiated. This query returns 3 rows. If you reverse the order of how
the parameters are added to the Parameters collection, it still runs, but
returns 0 rows as the parameters don't match up with the SQL in the query.



Try
Dim cn As OleDbConnection
cn = OleDbConnection1
cn.Open()
Dim da As New OleDbDataAdapter("SELECT * " _
& " FROM Customers WHERE Country = @ AND ContactTitle = @1", cn)
da.SelectCommand.Parameters.Add("@Cntry", "Mexico")
da.SelectCommand.Parameters.Add("@Title", "Owner")
Dim ds As New DataSet
da.Fill(ds)
DataGrid1.DataSource = ds.Tables(0)
Catch ex As Exception
MsgBox(ex.ToString)
End Try

Sorry for the confusion. I guess I'm spending too much time with SQL Server
to help much here in JET land.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Scott M.]

I find that when used with Access, the order of the Parameters in either the
Parameters collection or the SQL string itself makes no difference. I have
tested this just this morning.

 

[Scott M.]

According to Bill's latest post, you can get it to work in OLEDB for SQL by
setting the Command object's "NameParameters" property to True.

 

[gabe garza]

I learn something new everyday.
The class library I wrote in C++ 6.0 predated that so that's why I never
really looked into named parameters.
I'll look at updating that C++ library.
Thanks for the info.

Is it me or did the original post want to know how to get table names from
an MS Access database.

 

[Scott M.]

After a little more prodding, I found that the OP wanted to know how to
build the SQL string dynamically with the end user supplying the field
names. So, it turned out that the whole parameters thing was not what the
OP was looking for.

He did say in the OP though that he is using Access and wasn't sure whether
to use ? or @ for parameters. This is what got me going on the whole "you
can use parameter names and it doesn't matter what order they are in" thing.

-Scott

 

[Guest]

Hi everyone,

Thanks for your all effort, I'm using the old way of concatenation ("&")...

Thanks againg
ltt19

 

[William \(Bill\) Vaughn]

As you might have guessed, there are a lot of ways to handle parameters. We
(universally) recommend using Command objects to avoid the ever-present SQL
inject attacks. Concatenation does not deal with many issues. We've all seen
the sites where concatenation is being used--they often fail when you enter
a single quote in the string. Command objects deal with this issue
automatically...



--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Guest]

Hi,

But for my case concatenation is the only alternative, isn't it? Or I can do
it by another safely way? If so, please give me examples...
Thanks in advance

 

[William \(Bill\) Vaughn]

Sure. Here is an example. In this case the SELECT statement needs to have
two parameters passed--there are two Parameter objects set to manage these
parameters.

Try
Dim cn As OleDbConnection
cn = OleDbConnection1
cn.Open()
Dim da As New OleDbDataAdapter("SELECT * " _
& " FROM Customers WHERE Country = @ AND ContactTitle = @1", cn)
da.SelectCommand.Parameters.Add("@Cntry", "Mexico")
da.SelectCommand.Parameters.Add("@Title", "Owner")
Dim ds As New DataSet
da.Fill(ds)
DataGrid1.DataSource = ds.Tables(0)
Catch ex As Exception
MsgBox(ex.ToString)
End Try


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Scott M.]

Bill,

I thought this was what the OP wanted as well, but if you look back through
the thread, you'll see that what he really wants is NOT to pass a value into
a SQL string that would serve as a field value. He wants to build the SQL
statement dynamically based on users indicated how many and which fields
they would like to query on.

 

[Norman Yuan]

After seeing your reply yesterday, I did not dare to reply immediately,
'cause the last time I used Jet DB with OleDBClient was two years ago and I
memory could have been a bit of fussy. But I still believed you were wrong
on this. So, I just did a quick test using both VS2002 and VS2003 (meaning
ADO.NET 1.0 and 1.1).

Here is what I did:

1. Build a single table Access DB with following fields:
ID (AutoMuner)
LastName (Text)
FirstName(Text)
Address(Text)
City(Text)
Phone(Text)

2. Start a VS Windows App project with a single form
3. Place a Button on the form, with following Button_Click code:

private void button1_Click(object sender, System.EventArgs e)
{
string cnString=@"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data Source=C:\Documents and Settings\Norman\Desktop\db1.mdb;Persist
Security Info=False";

OleDbConnection cn=new OleDbConnection(cnString);
OleDbCommand cmd=cn.CreateCommand();
cmd.CommandType=CommandType.Text;

cmd.CommandText="INSERT INTO Table1
(LastName,FirstName,Address,City,Phone)" +
"VALUES (@LastName,@FirstName,@Address,@City,@Phone)";

OleDbParameter pmt;

pmt=cmd.Parameters.Add("@LastName",OleDbType.VarChar,50);
pmt.Value="FirstName";

pmt=cmd.Parameters.Add("@FirstName",OleDbType.VarChar,50);
pmt.Value="LastName";

pmt=cmd.Parameters.Add("@Address",OleDbType.VarChar,50);
pmt.Value="City";

pmt=cmd.Parameters.Add("@City",OleDbType.VarChar,50);
pmt.Value="Address";

pmt=cmd.Parameters.Add("@Phone",OleDbType.VarChar,50);
pmt.Value="Phone";

try
{
cn.Open();
cmd.ExecuteNonQuery();

MessageBox.Show("New record inserted!");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
cn.Close();
}
}

Run the app and click the button several times. Go to the Access DB and
opent the form, you would see what you expected, the record are inserted
into table correctly.

Now do the following changes: change the order parameter is added to the
command but remain the parameter's name:

pmt=cmd.Parameters.Add("@FirstName",OleDbType.VarChar,50);
pmt.Value="FirstName";

pmt=cmd.Parameters.Add("@LastName",OleDbType.VarChar,50);
pmt.Value="LastName";

pmt=cmd.Parameters.Add("@City",OleDbType.VarChar,50);
pmt.Value="City";

pmt=cmd.Parameters.Add("@Address",OleDbType.VarChar,50);
pmt.Value="Address";

pmt=cmd.Parameters.Add("@Phone",OleDbType.VarChar,50);
pmt.Value="Phone";

Run the app again and click the button several times. Open the table in
Access, you would be supprised (I am not, though): "FirstName" is inserted
to field LastName, "City" is inserted into filed Address. Why, because you
changed Paramter's order, although its name remains, and OleDbClient does
not use Paramter name here.

To make is more persuasive, do following changes:

pmt=cmd.Parameters.Add("@AAAAAAAAFirstName",OleDbType.VarChar,50);
pmt.Value="FirstName";

pmt=cmd.Parameters.Add("@AAAAAAALastName",OleDbType.VarChar,50);
pmt.Value="LastName";

pmt=cmd.Parameters.Add("@AAAAAAACity",OleDbType.VarChar,50);
pmt.Value="City";

pmt=cmd.Parameters.Add("@AAAAAAAddress",OleDbType.VarChar,50);
pmt.Value="Address";

pmt=cmd.Parameters.Add("@AAAAAAAPhone",OleDbType.VarChar,50);
pmt.Value="Phone";

From above change, you would say, the code will cause error because these
ill-named parameters do not exist in SQL statement.Well, being even
supprised, it works well. It proves again that for OleDbClient, the
paramter's order metters, not its name, at least when you use OleDbClient
with Jet DB.

Unless you are using .NET2.0 (beta) that I have not used, hence cannot prove
on that.

 

[Guest]

Exactly Scott, I gues I was not very clear in my first post,
So there is not any way to do this by using parameter? The onyl way is
concatenation?

 

[Norman Yuan]

For Jet DB (Access DB), I am afraid, you have no way to do that except for
using concatenation to build dynamic SQL SELECT...statement.

OTH, if you use a database that support stored procedure, such as SQL
Server/MSDE, you can pass some parameters and based on the parameter value
to run different SQL statement. This actually is to move your dynamic SQL
statement into Stored Procedure, which has some advantages as to dynamic SQL
Statement in app code.

Example:

Create Procedure usp_DynamicSelect
(
@Para int
)
AS

IF (@Para=1)
SELECT Col1 FROM tbl1
ELSE IF (@Para=2)
SELECT Col1,Col2 FROM tbl1
ELSE
SELECT Col1,Col2,Col3 FROM tbl1

RETURN

 

[gabe garza]

That's not true.
Here's how to do parameterize queries in Microsoft Access 2003.
The syntax is slightly different in OleDB for working with a Microsoft
Access 2003 database than it would be for SQL Server using OleDB. See if you
notice the differences.

I've compiled and tested this code against the Microsoft Access 2003
Northwinds database. If anyone has problems post your issue after you've
compiled and tested the code.
I hope now we can close this POST.

--- Start of C# Console Application Code
static void Main(string[] args)
{
/*
In the Northwinds.mdb microsoft access 2003 database add a query
called FindCustomer.
Set the SQL to the following:

SELECT Customers.CustomerID, Customers.CompanyName, Customers.ContactName,
Customers.City
FROM Customers
WHERE (((Customers.CompanyName) Like [Company]) AND ((Customers.ContactName)
Like [Name]));

*/

string sid = "";
string sname = "";
string scompany = "";
OleDbConnection cn = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=northwind.mdb;");

cn.Open();

OleDbCommand cmd = new OleDbCommand(" FindCustomer ", cn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;

// What you set here is what you get.
// You want every company name that starts with Gourment% with a
contact name
// starting with An%
// Here's how you set it.
cmd.Parameters.Add("@p1", OleDbType.Char, 40).Value = "Gourmet%";
cmd.Parameters.Add("@p2", OleDbType.Char, 30).Value = "An%";

// Suppose you just wanted everything that matched for An% in contact
name regardless of company name
// Then set @p1 to the following:
// cmd.Parameters.Add("@p1", OleDbType.Char, 40).Value = "%";
//
// This will return everything for company name but with your second
// parameter of An%
// You'll get every company that has a contact name starting with An%

OleDbDataReader oreader = null;

oreader =
cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

while(true == oreader.Read())
{
sid = (string)oreader["CustomerID"];
sname = (string)oreader["ContactName"];
scompany = (string)oreader["CompanyName"];

Console.WriteLine("id=[" + sid + "] name=[" + sname + "] company=["
+ scompany + "]");
}

cmd.Dispose();
cmd = null;

cn.Close();
cn.Dispose();
cn = null;

}

--- End of C# Console Application Code

 

[Cor Ligthert]

ltt19

Did you try to set your textboxes and the accoording parameter for those in
a collection or use inheritted textboxes where you place the right
parameters in.

Your question marks in your dynamicly build SQL String are than equal to the
amount of questions.

The parameters you use are comming from the textbox that is used in the
collection.

This can in my opinion in a very easy procedure.

In never tried this, however would not know why not.

Cor

 

[Scott M.]

But you 2 are missing the OP's point. He's not trying to pass dynamic
values to existing fields. He's trying to build the SQL string dynamically
based on users supplying what fields they want to query on. Some users may
only want to query on 2 fields and other users may want to query on 3
different fields. For this, the OP need to dynamically build (via
concatenation) his SQL string.

That's not true.
Here's how to do parameterize queries in Microsoft Access 2003.
The syntax is slightly different in OleDB for working with a Microsoft
Access 2003 database than it would be for SQL Server using OleDB. See if
you notice the differences.

I've compiled and tested this code against the Microsoft Access 2003
Northwinds database. If anyone has problems post your issue after you've
compiled and tested the code.
I hope now we can close this POST.

--- Start of C# Console Application Code
static void Main(string[] args)
{
/*
In the Northwinds.mdb microsoft access 2003 database add a query
called FindCustomer.
Set the SQL to the following:

SELECT Customers.CustomerID, Customers.CompanyName, Customers.ContactName,
Customers.City
FROM Customers
WHERE (((Customers.CompanyName) Like [Company]) AND
((Customers.ContactName) Like [Name]));

*/

string sid = "";
string sname = "";
string scompany = "";
OleDbConnection cn = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=northwind.mdb;");

cn.Open();

OleDbCommand cmd = new OleDbCommand(" FindCustomer ", cn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;

// What you set here is what you get.
// You want every company name that starts with Gourment% with a
contact name
// starting with An%
// Here's how you set it.
cmd.Parameters.Add("@p1", OleDbType.Char, 40).Value = "Gourmet%";
cmd.Parameters.Add("@p2", OleDbType.Char, 30).Value = "An%";

// Suppose you just wanted everything that matched for An% in contact
name regardless of company name
// Then set @p1 to the following:
// cmd.Parameters.Add("@p1", OleDbType.Char, 40).Value = "%";
//
// This will return everything for company name but with your second
// parameter of An%
// You'll get every company that has a contact name starting with An%

OleDbDataReader oreader = null;

oreader =
cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

while(true == oreader.Read())
{
sid = (string)oreader["CustomerID"];
sname = (string)oreader["ContactName"];
scompany = (string)oreader["CompanyName"];

Console.WriteLine("id=[" + sid + "] name=[" + sname + "] company=["
+ scompany + "]");
}

cmd.Dispose();
cmd = null;

cn.Close();
cn.Dispose();
cn = null;

}

--- End of C# Console Application Code

For Jet DB (Access DB), I am afraid, you have no way to do that except
for
using concatenation to build dynamic SQL SELECT...statement.

OTH, if you use a database that support stored procedure, such as SQL
Server/MSDE, you can pass some parameters and based on the parameter
value
to run different SQL statement. This actually is to move your dynamic SQL
statement into Stored Procedure, which has some advantages as to dynamic
SQL
Statement in app code.

Example:

Create Procedure usp_DynamicSelect
(
@Para int
)
AS

IF (@Para=1)
SELECT Col1 FROM tbl1
ELSE IF (@Para=2)
SELECT Col1,Col2 FROM tbl1
ELSE
SELECT Col1,Col2,Col3 FROM tbl1

RETURN

 

[gabe garza]

He can do that with what I sent. Let's just use my below code as example,
which is going against a Microsoft Access 2003 Northwinds database.

Suppose user1 wanted to only query for a contact name of 'Martín Sommer'
set @p1 to '%' and @p2 to 'Martín Sommer'

Suppose user2 wanted to only query for a contact name of 'Aria Cruz'
set @p1 to '%' and @p2 to 'Aria Cruz'

Suppose user3 wanted to only query for a company name of 'Frankenversand'
set @p1 to 'Frankenversand' and @p2 to '%'

Suppose user4 wanted to only query for a company name of 'In and Out' and a
contact name of 'Gabe Garza'
set @p1 to 'In and Out' and @p2 to 'Gabe Garza'

So for the OP's point he's querying on what values that the user wants to
query on. The code has to set both parameters but it'll get him what he
wants. Results based on what the user is selecting on.

So if he has 2 text boxes on a ASPX page, one called CompanyName and another
called ContactName.
A user supplies a value for CompanyName and not for ContactName then the
code would set @p1 to the CompanyName that the user supplied and set @p2 to
'%' cause the user didn't supply a ContactName.

Even though it would seem that the WHERE clause:

(((Customers.CompanyName) Like [Company]) AND ((Customers.ContactName)
Like [Name]));

may be fixed instead of dynamic, it really is dynamic because of how you set
the values.

ONLY (((Customers.CompanyName) Like [Company]) = setting @p1 to a
CompanyName and setting @p2 to '%'
ONLY ((Customers.ContactName) Like [Name])); = setting @p1 to '%' and
setting @p2 to a ContactName
BOTH (((Customers.CompanyName) Like [Company]) AND ((Customers.ContactName)
Like [Name])); = setting @p1 to a CompanyName and setting @p2 to a
ContactName.

You have your dynamic WHERE clause based on how you set the parameters, @p1
and @p2.

But you 2 are missing the OP's point. He's not trying to pass dynamic
values to existing fields. He's trying to build the SQL string
dynamically based on users supplying what fields they want to query on.
Some users may only want to query on 2 fields and other users may want to
query on 3 different fields. For this, the OP need to dynamically build
(via concatenation) his SQL string.

That's not true.
Here's how to do parameterize queries in Microsoft Access 2003.
The syntax is slightly different in OleDB for working with a Microsoft
Access 2003 database than it would be for SQL Server using OleDB. See if
you notice the differences.

I've compiled and tested this code against the Microsoft Access 2003
Northwinds database. If anyone has problems post your issue after you've
compiled and tested the code.
I hope now we can close this POST.

--- Start of C# Console Application Code
static void Main(string[] args)
{
/*
In the Northwinds.mdb microsoft access 2003 database add a query
called FindCustomer.
Set the SQL to the following:

SELECT Customers.CustomerID, Customers.CompanyName,
Customers.ContactName, Customers.City
FROM Customers
WHERE (((Customers.CompanyName) Like [Company]) AND
((Customers.ContactName) Like [Name]));

*/

string sid = "";
string sname = "";
string scompany = "";
OleDbConnection cn = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=northwind.mdb;");

cn.Open();

OleDbCommand cmd = new OleDbCommand(" FindCustomer ", cn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;

// What you set here is what you get.
// You want every company name that starts with Gourment% with a
contact name
// starting with An%
// Here's how you set it.
cmd.Parameters.Add("@p1", OleDbType.Char, 40).Value = "Gourmet%";
cmd.Parameters.Add("@p2", OleDbType.Char, 30).Value = "An%";

// Suppose you just wanted everything that matched for An% in
contact name regardless of company name
// Then set @p1 to the following:
// cmd.Parameters.Add("@p1", OleDbType.Char, 40).Value = "%";
//
// This will return everything for company name but with your second
// parameter of An%
// You'll get every company that has a contact name starting with
An%

OleDbDataReader oreader = null;

oreader =
cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

while(true == oreader.Read())
{
sid = (string)oreader["CustomerID"];
sname = (string)oreader["ContactName"];
scompany = (string)oreader["CompanyName"];

Console.WriteLine("id=[" + sid + "] name=[" + sname + "]
company=[" + scompany + "]");
}

cmd.Dispose();
cmd = null;

cn.Close();
cn.Dispose();
cn = null;

}

--- End of C# Console Application Code

For Jet DB (Access DB), I am afraid, you have no way to do that except
for
using concatenation to build dynamic SQL SELECT...statement.

OTH, if you use a database that support stored procedure, such as SQL
Server/MSDE, you can pass some parameters and based on the parameter
value
to run different SQL statement. This actually is to move your dynamic
SQL
statement into Stored Procedure, which has some advantages as to dynamic
SQL
Statement in app code.

Example:

Create Procedure usp_DynamicSelect
(
@Para int
)
AS

IF (@Para=1)
SELECT Col1 FROM tbl1
ELSE IF (@Para=2)
SELECT Col1,Col2 FROM tbl1
ELSE
SELECT Col1,Col2,Col3 FROM tbl1

RETURN




Exactly Scott, I gues I was not very clear in my first post,
So there is not any way to do this by using parameter? The onyl way is
concatenation?


:

Bill,

I thought this was what the OP wanted as well, but if you look back
through
the thread, you'll see that what he really wants is NOT to pass a
value
into
a SQL string that would serve as a field value. He wants to build
the
SQL
statement dynamically based on users indicated how many and which
fields
they would like to query on.


message
Sure. Here is an example. In this case the SELECT statement needs
to
have
two parameters passed--there are two Parameter objects set to
manage
these
parameters.

Try
Dim cn As OleDbConnection
cn = OleDbConnection1
cn.Open()
Dim da As New OleDbDataAdapter("SELECT * " _
& " FROM Customers WHERE Country = @ AND ContactTitle =
@1",
cn)
da.SelectCommand.Parameters.Add("@Cntry", "Mexico")
da.SelectCommand.Parameters.Add("@Title", "Owner")
Dim ds As New DataSet
da.Fill(ds)
DataGrid1.DataSource = ds.Tables(0)
Catch ex As Exception
MsgBox(ex.ToString)
End Try


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________

Hi,

But for my case concatenation is the only alternative, isn't it?
Or I
can
do
it by another safely way? If so, please give me examples...
Thanks in advance


:

As you might have guessed, there are a lot of ways to handle
parameters.
We
(universally) recommend using Command objects to avoid the
ever-present
SQL
inject attacks. Concatenation does not deal with many issues.
We've
all
seen
the sites where concatenation is being used--they often fail when
you
enter
a single quote in the string. Command objects deal with this
issue
automatically...



--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers
no
rights.
__________________________________

Hi everyone,

Thanks for your all effort, I'm using the old way of
concatenation
("&")...

Thanks againg
ltt19

 

[Guest]

Hi again,

So, gabe garza, you post is useful, but it is not working for my problem,
since I cant specify which table it will look for, but this is useful for my
other problem, that is exatally what you wrote for.
Cor Lightert, I reaaly could not understand what you want me to do. Thanks
anyway.
Soccot, yes, i think that for this case the oly way is concatenation, I've
been searching all over the web I i could not ifnd anything that helps me.

Thanks all your effort.
ltt19.

 

[Cor Ligthert]

Ltt19

Do you understand this

dim FirstDone as boolean
dim mySelectString = "Select * from Customers Where "
If textboxCountry <> "" then
mySelectString = mySelectString & "Country=? ".
da.SelectCommand.Parameters.Add("", textboxCountry")
FirstDone = True.

If textboxContact <> "" AndAlso Not FirstDone then mySelectString =
mySelectString & "AND ".
If textboxContact <> "" then
mySelectString = mySelectString & "ContactTittle=? ".
da.SelectCommand.Parameters.Add("", textboxContact").
etc etc

However this is as clumsy as creating a concatinated string.

Therefore I said to set it first in an array, than you can make this routine
generic, or maybe can a kind of own created textbox with some extra
parameters, can do that or even a combination of that.

The statement that the only solution is a concatenated string, is with this
sample therefore not true, although it is concatenated, is it not given as
an concatenated string to the adapter.

I hope this clears it more now that I have showed the basic of the idea.

Cor

 

[Norman Yuan]

That is not OP's problem. Not how many records returned by making dynamic
WHERE. He return different columns sccording to parameter's values,
regardless how many rows.