Parameters Question

[Guest]

Hi,

I'm new to ADO.net, even new to Databases access, and I have a CommandText
that is useful to all of my tables, however I can not find the approppiate
use of paramnter to do this. Like:

"SELECT * FROM ?"

I'm using an Acess database and the OLEDB.

Another Question, 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.

Hope that I was clear.

Any help would be very useful.
Thanks in advance.

 

[Scott M.]

In ADO.NET, you don't use the question mark to indicate a parameter value,
you use @paramaterName as in:

"SELECT * FROM tblUsers WHERE User=@user"

Check out this for more info:

http://msdn.microsoft.com/library/d...rfsystemdataoledboledbparameterclasstopic.asp

 

[William \(Bill\) Vaughn]

Each provider uses different parameter markers. OLE DB providers use "?" (as you have done) or ":" (Oracle)--not named parameters as are used with SQL Server.
However, you can't specify a table or columns to select with a parameter. You can specify arguments to be used in a WHERE clause such as

SELECT Author, YearBorn FROM Authors WHERE YearBorn = ?

In this case you would need to create a Parameter object to handle the parameter.

Yes, there are ways to choose the table and columns, but these require you to build the SQL on the fly either in the client application or on the server using the EXEC SQL technique (which is not recommended).

I discuss this in detail in my books on ADO and ADO.NET.

hth



--
____________________________________
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.
__________________________________

 

[William \(Bill\) Vaughn]

Ah no. The OleDB parameter marker is a "?" for the JET provider. Yes, you
can name these parameters in the Parameter collection, but the name is not
used to match the parameter in the query string.

--
____________________________________
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.]

Uh actually yes it is. I've done it this way for quite some time now.
Here's a snippet of code that works like a charm:

Dim insertSQL As String = _
"INSERT INTO tblOrders " & _
"(Customer,Address,Add2,City,ST,Zip,Phone,Email," & _
"ProductName,Quantity,PriceEach) " & _
"VALUES(@CustName,@Add1,@Add2,@City,@ST,@Zip," & _
"@Phone,@Email,@Product,@Qty,@PriceEach)"

Dim cmdInsert As New OleDb.OleDbCommand(insertSQL, con)

With cmdInsert.Parameters
.Add(New OleDb.OleDbParameter("@CustName", OrderDetails.Customer))
.Add(New OleDb.OleDbParameter("@Add1", OrderDetails.Address1))
.Add(New OleDb.OleDbParameter("@Add2", OrderDetails.Address2))
.Add(New OleDb.OleDbParameter("@City", OrderDetails.City))
.Add(New OleDb.OleDbParameter("@ST", OrderDetails.State))
.Add(New OleDb.OleDbParameter("@Zip", OrderDetails.ZipCode))
.Add(New OleDb.OleDbParameter("@Phone", OrderDetails.Phone))
.Add(New OleDb.OleDbParameter("@Email", OrderDetails.Email))
.Add(New OleDb.OleDbParameter("@Product", OrderDetails.Product))
.Add(New OleDb.OleDbParameter("@Qty", OrderDetails.Quantity))
.Add(New OleDb.OleDbParameter("@PriceEach", OrderDetails.PriceEach))
End With

 

[Scott M.]

Please see my other reply to you in this thread on this because your statement below is incorrect.


Each provider uses different parameter markers. OLE DB providers use "?" (as you have done) or ":" (Oracle)--not named parameters as are used with SQL Server.

 

[Guest]

Hi,

Thanks for your attention.
Hum, at the moment I could use both "?" and "@" however with the "@" not
even I get it successful. I also have an VB.net book here, and its examples
uses "?" to oledb and "@" to slq.

But I still confused. Can I do that or it is just no possible?
If yes, some examples can be very useful

Thanks again for all the support..
ltt19

 

[Scott M.]

You most certainly can use @. I realize that many examples show it with ?,
but I have been using @ without any problems for quite some time. Please
see my other reply in this thread for a code example.

 

[Guest]

Hi Scott

I saw your code before, but that just do not anwser my question. I guess I
was not clear enoght (my fisrt post is a little confusing)

So the User will control which Columns will be uptaded and which table it
will affect, so the table name will change, as well the total columns to
select, this last one is something like ParamArray visual basic keyword,
however i cannot make an appropiate query for any these problems.

Thanks again

 

[Scott M.]

In this scenario, I don't think we're talking about Parameters at all. I
think you are talking about dynamically creating the SQL statement based on
user input, right? How about something like this:

Dim tableName As String = txtTableName.Text
Dim col1Name As String = txtCol1Name.Text
Dim co2Name As String= txtCol2Name.Text

Dim sqlStr As String = "SELECT " & col1Name & ", " & col2Name & " FROM " &
tableName & " WHERE " & " col1Name & "=@userCriteria"

Dim cmdInsert As New OleDb.OleDbCommand(sqlStr, con)
cmdInsert.Parameters.Add(New OleDb.OleDbParameter("@userCriteria",
txtUserCriteria.Text))

Now, in your case there would be more involved since the amount of columns
being used will vary, so you'd need to build splStr with a bit more code to
ensure all the desirec columns have been added.

 

[Cor Ligthert]

ltt19

AFAIK have they acidential copied the SQLClient samples to the OleDb sample
on MSDN where it is in at the OleDbadapter command parameters.

That is not with the commands themselves.

http://msdn.microsoft.com/library/d...dataoledboledbcommandclassparameterstopic.asp

That does not mean that it is another system by the adapter, the sample is
just wrong. Although those samples don't harm because they are very simple
and works, just because as Bill told. Those parameternames are not used.

Therefore do you have with OleDb and Jet always have to check that your
parameters are in the collection in the sequence as they are in the SQL
string.

Have a look at this thread as well.
http://groups-beta.google.com/group...8febd9129bc/e26b934f296c1be9#e26b934f296c1be9

I hope this helps something in this discussion

Cor

 

[Scott M.]

Here's what I can tell you. The sample code I provided in this thread works
just fine with .NET 1.1 and VS.NET 2003. It works whether or NOT the
parameters in the SQL statement are in the same sequence as they were added
to the command's parameters collection.

This is the reality of what works and what doesn't.

 

[Norman Yuan]

Yes, your code works. I did the samething in one of I project using Jet DB.
That is, you can use named Parameter in SQL statement. But, as Bill pointed
out, when it comes to Jet engine update database using the SQL statement and
the parameters you suuplied from Command object, Parameter's name is not
used, instead, only parameter's ordinal position is used to match parameters
in SQL statement and Parameters collection. Try to change the order a
parameter is added to the Command, or name whatever name to the parameter,
you will get wrong result or error. While in SqlClient namaspace, the order
of add parameter does not meter, as long as the parameter name matches.

 

[Scott M.]

As I stated in my previous post, if I change the order of the parameters in
either the Parameters collection or in the SQL statement, the code still
works perfectly. Just to double-check I tried just that this morning before
I posted my reply.

 

[Guest]

Scott,

In my experience, the OLEDB provider for Access lets you use named
parameters or the question mark character (and maybe additional options).

The OLEDB provider for SQL Server (Provider=SQLOLEDB) will not allow named
parameters. It will work with question mark placeholders. The parameters then
must be in the order of the question marks.

I would love to see some code that uses OLEDB to work with both Access and
SQL Server databases where you can use parameter names in any order for both
Access and SQL Server. Since I have never been able to get this to work, I
always use question marks for both databases (when using OLEDB) so the code
will work with just connection string changes between the 2 databases.

Kerry Moorman

 

[gabe garza]

This will list all of your tables in an Microsoft Access Database, that are
not System Tables.

SELECT MSysObjects.Name
FROM MsysObjects
WHERE (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1

 

[Scott M.]

But think about this for a second...If named parameters will work in OLEDB
for Access (in any sequence) and they will also work in OLEDB for SQL (in
the correct sequence only), then why not just used named parameters in the
correct sequence all the time and both would be happy. In addition,
(realistically speaking) why would you be using OLEDB with SQL rather than
the SQLClient namespace?

 

[Guest]

Scott,

But as I said in my post, I have never been able to get named parameters to
work in OLEDB for SQL Server. In my SQL statements I cannot use, for example
"@ID". Instead I must use "?" as a placeholder. And my parameters must be in
the same order as the "?" placeholders. Again, this is not the case for
Access.

Also as I said in my post, the reason I sometimes use OLEDB for both Access
and SQL Server is so I can upsize the Access database to SQL Server and use
the same ADO.Net code (yes I realize that SQLClient provides better
performance).

Do you have an example of using named parameters with OLEDB for both Access
and SQL Server? Perhaps you have always used the SQLClient for SQL Server
databases and you never noticed how the OLEDB stuff works differently with
SQL Server than with Access?

Kerry

 

[gabe garza]

Kerry,
You are correct. I've worked with both in .NET and with C++ in Visual Studio
6.0
Here's a link to prove that.
http://msdn.microsoft.com/library/d...tml/cpconusingstoredprocedureswithcommand.asp
One thing to note as well, if you create a Visual 6.0 C++ program to access
OleDB the syntax for calling a stored procedure is the same as the ODBC
format:
{ CALL SampleProc(?, ?) }
But in .NET it's just the stored procedure name. Go figure.

Using Parameters with a SqlCommand
When using parameters with a SqlCommand, the names of the parameters added
to the Parameters collection must match the names of the parameter markers
in your stored procedure. The .NET Framework Data Provider for SQL Server
treats parameters in the stored procedure as named parameters and searches
for the matching parameter markers.

The .NET Framework Data Provider for SQL Server does not support the
question mark (?) placeholder for passing parameters to an SQL statement or
a stored procedure. In this case, you must use named parameters, as in the
following example.

SELECT * FROM Customers WHERE CustomerID = @CustomerIDUsing Parameters with
an OleDbCommand or OdbcCommand
When using parameters with an OleDbCommand or OdbcCommand, the order of the
parameters added to the Parameters collection must match the order of the
parameters defined in your stored procedure. The .NET Framework Data
Provider for OLE DB and .NET Framework Data Provider for ODBC treat
parameters in a stored procedure as placeholders and applies parameter
values in order. In addition, return value parameters must be the first
parameters added to the Parameters collection.

The .NET Framework Data Provider for OLE DB and .NET Framework Data Provider
for ODBC do not support named parameters for passing parameters to an SQL
statement or a stored procedure. In this case, you must use the question
mark (?) placeholder, as in the following example.

SELECT * FROM Customers WHERE CustomerID = ?As a result, the order in which
Parameter objects are added to the Parameters collection must directly
correspond to the position of the question mark placeholder for the
parameter.

 

[William \(Bill\) Vaughn]

Named parameters were introduced late in the game--in ADO 2.5 or 2.6 IIRC.
You have to ask for this feature using the SQLOLEDB provider by setting the
Command object's NamedParameters=True. Once activated, the parameters can be
provided in any order.

hth

--
____________________________________
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.
__________________________________