SQL parameter: what is wrong here?

  • Thread starter Thread starter pamelafluente
  • Start date Start date
P

pamelafluente

Hi I am executing some simple sample code:

Using OleDbCommand As New OleDbCommand(Me.DBQuery.Text,
Me.OleDbConnection)

Dim OleDbParameter As OleDbParameter =
OleDbCommand.Parameters.Add("@Pinco", OleDbType.VarWChar)
OleDbParameter.Direction = ParameterDirection.Input
OleDbParameter.Value = "Berlin"

Using OleDbDataReader As OleDbDataReader =
OleDbCommand.ExecuteReader()
'...

I use this query:

SELECT
C.Country AS "Country",
C.City AS "City"
FROM
Customers C
where

C.City = @Pinco

It works with ACCESS, but gives error with SQL Server (says variable
has not been defined).

What's the problem here? How do I fix it to work with a general OleDB
connection?

-P
 
Pamela,

When using the OleDb namespace with SQL Server, I don't think you can use
named parameters. To use named parameters you would need to use the SQLClient
namespace.

If you change your Select statement by replacing @Pinco with the
question-mark placeholder, ?, I think your code should work.

Kerry Moorman
 
Kerry said:
Pamela,

When using the OleDb namespace with SQL Server, I don't think you can use
named parameters. To use named parameters you would need to use the SQLClient
namespace.

If you change your Select statement by replacing @Pinco with the
question-mark placeholder, ?, I think your code should work.
Click to expand...

I really don't know the differences between OleDb and ODBC or SQL, but
I would have suggested adding a:

declare @Pinco as varchar(<maxsize>)
before the Select.
 
Hi I am executing some simple sample code:

Using OleDbCommand As New OleDbCommand(Me.DBQuery.Text,
Me.OleDbConnection)

Dim OleDbParameter As OleDbParameter =
OleDbCommand.Parameters.Add("@Pinco", OleDbType.VarWChar)
Click to expand...

It might want the maximum length of the string as defined when you created
the table, e.g.:
Dim OleDbParameter As OleDbParameter OleDbCommand.Parameters.Add("@Pinco",
OleDbType.VarWChar, 24)

Andrew
 
Ok Kerry thanks , I tried

where C.City = ?

with this code:

Using OleDbCommand As New OleDbCommand(Me.DBQuery.Text,
Me.OleDbConnection)

Dim p As New OleDbParameter
p.Value = "Berlin"
OleDbCommand.Parameters.Add(p)

Using OleDbDataReader As OleDbDataReader =
OleDbCommand.ExecuteReader()

this returns no record, although records with C.City = "Berlin" are
there.

What I am still missing. I'd like a method which works generally with
OleDB (irrelevant of the database, if possible) ?

-P






Kerry Moorman ha scritto:
 
Thanks Z and Andrew for your suggestions too.
It seems I have some problems to make this work.
I want to avoid the variable definition because I would like
to write some code to work with several DB. The OleDB connection
could be with SQLserver, Oracle, Access, as400 and other dbms.
This is my most important point I need some *general* code.

Do you think that in my case would be more advisable that I do
a manual substitution of the parameter with the value in the query?

I mean I could examine the query and, when I find @Param1, @OtherParam,
@Etc
I could substitute those with an ordered list of values supplied by the
user
through some interface. Would that be advisable and, most importantly
would it work on all platforms?

What is the best way to do the string replacement. I am afraid that if
one
has for instance: @Pinco and @PincoPallo the substitution of the first
parameter could prevent the substitution of the second ...

@Pinco = "Berlin" would cause the destruction of the second identifier
: BerlinPallo

-p

Andrew Morton ha scritto:
 
Pamela,

I am not at a machine with SQL Server, so I can't try this code. But you
might try replacing:

Dim p As New OleDbParameter
p.Value = "Berlin"
OleDbCommand.Parameters.Add(p)

with

OleDbCommand.Parameters.Add ("City", "Berlin")

In this example, "City" is just a placeholder name for the parameter and
does not need to match the actual column name in the table.

Kerry Moorman
 
hi Kerry

it says.

System.InvalidOperationException occurred
Message="OleDbCommand.Prepare method requires all parameters to have
an explicitly set type."

Do you think that a "direct substitution" would be better and more
robust?

-p

Kerry Moorman ha scritto:
 
Pamela,

If by direct substitution you mean placing your data values directly into
the SQL Select statement instead of using parameters, then definitely not.

Parameters help prevent SQL injection attacks and are absolutely needed for
security.

I'll see if I can get an example of working code when I am able to be on a
machine with SQL Server.

But perhaps you have some additional code that you have not shown that might
be causing the problem?

Kerry Moorman
 
Kerry Moorman ha scritto:
Pamela,

If by direct substitution you mean placing your data values directly into
the SQL Select statement instead of using parameters, then definitely not.

Parameters help prevent SQL injection attacks and are absolutely needed for
security.
Click to expand...

Ok Thanks. good advice.
I'll see if I can get an example of working code when I am able to be on a
machine with SQL Server.
Click to expand...

Thank you, that would be really nice of you.
But perhaps you have some additional code that you have not shown that might
be causing the problem?
Click to expand...

No. Everything works fine. The only problem is with parameters. What I
need is a simple way to let the user define 1 or more parameters in a
query passed via OleDB. And that should be not platform-specific, but
should work whatever is the dbms to which I am connected and whatever
is the field. I assume to know nothing about the data type to which the
parameter will refer to.

Thanks indeed Kerry.

-P
 
Pamela,

Here is an example that selects movie directors based on nationality, using
a parameter:

Dim cn As New OleDbConnection("Provider=SQLOLEDB;Data
Source=(local)\SQLExpress;Initial Catalog=MoviesToGoSQL;User
ID=sa;Password=xxxx")
Dim cmd As New OleDbCommand
Dim dr As OleDbDataReader
Dim NationalityValue As String = "American"

cmd.CommandText = "Select * From Directors Where Nationality = ?"
cmd.Parameters.Add("NationalityParameter", NationalityValue)

cn.Open()
cmd.Connection = cn

dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

If dr.HasRows Then
MsgBox("Got data")
Else
MsgBox("No data")
End If

dr.Close()

Kerry Moorman
 
Thanks Kerry. Very sweet. I am gonna try it immediately.

-Is this supposed to work for any database ?
-Can I use multiple parameters? Just add them?


-P


Kerry Moorman ha scritto:
 
Back
Top