Home All Groups Group Topic Archive Search About

SQL parameter: what is wrong here?

microsoft.public.dotnet.languages.vb
Author
6 Sep 2006 2:01 PM
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

Author
6 Sep 2006 2:21 PM
Kerry Moorman
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


Show quoteHide quote
"pamelaflue***@libero.it" wrote:

> 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
>
>
Author
6 Sep 2006 2:33 PM
zacks
Kerry Moorman wrote:
> 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.

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.


Show quoteHide quote
>
> "pamelaflue***@libero.it" wrote:
>
> > 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
> >
> >
Author
6 Sep 2006 2:49 PM
pamelafluente
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:

Show quoteHide quote
> 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
>
>
> "pamelaflue***@libero.it" wrote:
>
> > 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
> >
> >
Author
6 Sep 2006 4:15 PM
Kerry Moorman
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


Show quoteHide quote
"pamelaflue***@libero.it" wrote:

> 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:
>
> > 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
> >
> >
> > "pamelaflue***@libero.it" wrote:
> >
> > > 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
> > >
> > >
>
>
Author
6 Sep 2006 4:58 PM
pamelafluente
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:

Show quoteHide quote
> 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
>
>
> "pamelaflue***@libero.it" wrote:
>
> > 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:
> >
> > > 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
> > >
> > >
> > > "pamelaflue***@libero.it" wrote:
> > >
> > > > 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
> > > >
> > > >
> >
> >
Author
6 Sep 2006 6:06 PM
Kerry Moorman
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


Show quoteHide quote
"pamelaflue***@libero.it" wrote:

> 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,
> >
> > 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
> >
> >
> > "pamelaflue***@libero.it" wrote:
> >
> > > 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:
> > >
> > > > 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
> > > >
> > > >
> > > > "pamelaflue***@libero.it" wrote:
> > > >
> > > > > 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
> > > > >
> > > > >
> > >
> > >
>
>
Author
6 Sep 2006 6:36 PM
pamelafluente
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.

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.

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?

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

Show quoteHide quote
>
> Kerry Moorman
>
>
> "pamelaflue***@libero.it" wrote:
>
> > 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,
> > >
> > > 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
> > >
> > >
> > > "pamelaflue***@libero.it" wrote:
> > >
> > > > 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:
> > > >
> > > > > 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
> > > > >
> > > > >
> > > > > "pamelaflue***@libero.it" wrote:
> > > > >
> > > > > > 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
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >
Author
7 Sep 2006 4:05 PM
Kerry Moorman
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


Show quoteHide quote
"pamelaflue***@libero.it" wrote:

> 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.
>
> 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.
>
> 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?
>
> 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
>
> >
> > Kerry Moorman
> >
> >
> > "pamelaflue***@libero.it" wrote:
> >
> > > 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,
> > > >
> > > > 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
> > > >
> > > >
> > > > "pamelaflue***@libero.it" wrote:
> > > >
> > > > > 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:
> > > > >
> > > > > > 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
> > > > > >
> > > > > >
> > > > > > "pamelaflue***@libero.it" wrote:
> > > > > >
> > > > > > > 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
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>
>
Author
7 Sep 2006 4:26 PM
pamelafluente
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:

Show quoteHide quote
> 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
>
>
> "pamelaflue***@libero.it" wrote:
>
> > 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.
> >
> > 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.
> >
> > 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?
> >
> > 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
> >
> > >
> > > Kerry Moorman
> > >
> > >
> > > "pamelaflue***@libero.it" wrote:
> > >
> > > > 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,
> > > > >
> > > > > 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
> > > > >
> > > > >
> > > > > "pamelaflue***@libero.it" wrote:
> > > > >
> > > > > > 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:
> > > > > >
> > > > > > > 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
> > > > > > >
> > > > > > >
> > > > > > > "pamelaflue***@libero.it" wrote:
> > > > > > >
> > > > > > > > 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
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >
Author
6 Sep 2006 2:35 PM
Andrew Morton
pamelaflue***@libero.it wrote:
> 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)

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
Author
6 Sep 2006 4:13 PM
pamelafluente
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:

Show quoteHide quote
> pamelaflue***@libero.it wrote:
> > 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)
>
> 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



Post Other interesting topics
Registry Reading
Remove certain characters from a string/RichTextBox?
Better way to process many conditions with If Then
Calling a VB.NET-DLL FROM VB6
Iterating through the records in a dataset
Crystal Reports in VB.NET
Determining max number of characters in a TextBox
how to get a file to display from relative filespec
Deleting a folder
Save Radio Button setting