Parameters Question

[Cor Ligthert]

Norman,

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.

This beneath is from the first message from the OP and I saw it return many
times.

How can I use paramter to Select Fields that can be
chosen by the user? Like a "SELECT ? ? FROM Music" but I don't know wich
are
these "?" and how many will have.

The above is what the code I showed does.

Therefore enlighten me, what do I read wrong?

Cor

 

[Norman Yuan]

According to OP, if a table has, say, 4 columns: LastName, FirstName,
MidName,Alias. The user only want to get TWO columns returned: any two
column combination, FirstName & LastName, or FirstName & Alias, you do not
know which two while you are coding. Note: he put "?" before FROM for the
question.

You code is great to filter out ROWs, but returns preselected columns by you
(in your case, it was all columns). All your code is in WHERE clause after
FROM, hence did not answer his question.

The OP even also want somehow get variant number of columns based on user
input. That is why the simple solution is string concatenation with dynamic
SQL Select statement (with Access DB).

 

[Guest]

It was not what the OP wanted, but It is EXACTLY what I wanted when I
stumbled onto this thread. Is it just my imagination or is this
info/technique not well documented? Perhaps I've been looking in all the
wrong places:-D
--
Jim Parsells

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