Hi I am executing some simple sample code:
Using OleDbCommand As New OleDbCommand(Me .DBQuery.Text,
Me.OleDbConnect ion)
Dim OleDbParameter As OleDbParameter =
OleDbCommand.Pa rameters.Add("@ Pinco", OleDbType.VarWC har)
OleDbParameter. Direction = ParameterDirect ion.Input
OleDbParameter. Value = "Berlin"
Using OleDbDataReader As OleDbDataReader =
OleDbCommand.Ex ecuteReader()
'...
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 11 4329
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
"pa***********@ libero.it" wrote:
Hi I am executing some simple sample code:
Using OleDbCommand As New OleDbCommand(Me .DBQuery.Text,
Me.OleDbConnect ion)
Dim OleDbParameter As OleDbParameter =
OleDbCommand.Pa rameters.Add("@ Pinco", OleDbType.VarWC har)
OleDbParameter. Direction = ParameterDirect ion.Input
OleDbParameter. Value = "Berlin"
Using OleDbDataReader As OleDbDataReader =
OleDbCommand.Ex ecuteReader()
'...
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
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(<maxsiz e>)
before the Select.
>
"pa***********@ libero.it" wrote:
Hi I am executing some simple sample code:
Using OleDbCommand As New OleDbCommand(Me .DBQuery.Text,
Me.OleDbConnect ion)
Dim OleDbParameter As OleDbParameter =
OleDbCommand.Pa rameters.Add("@ Pinco", OleDbType.VarWC har)
OleDbParameter. Direction = ParameterDirect ion.Input
OleDbParameter. Value = "Berlin"
Using OleDbDataReader As OleDbDataReader =
OleDbCommand.Ex ecuteReader()
'...
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 pa***********@l ibero.it wrote:
Hi I am executing some simple sample code:
Using OleDbCommand As New OleDbCommand(Me .DBQuery.Text,
Me.OleDbConnect ion)
Dim OleDbParameter As OleDbParameter =
OleDbCommand.Pa rameters.Add("@ Pinco", OleDbType.VarWC har)
It might want the maximum length of the string as defined when you created
the table, e.g.:
Dim OleDbParameter As OleDbParameter OleDbCommand.Pa rameters.Add("@ Pinco",
OleDbType.VarWC har, 24)
Andrew
Ok Kerry thanks , I tried
where C.City = ?
with this code:
Using OleDbCommand As New OleDbCommand(Me .DBQuery.Text,
Me.OleDbConnect ion)
Dim p As New OleDbParameter
p.Value = "Berlin"
OleDbCommand.Pa rameters.Add(p)
Using OleDbDataReader As OleDbDataReader =
OleDbCommand.Ex ecuteReader()
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
"pa***********@ libero.it" wrote:
Hi I am executing some simple sample code:
Using OleDbCommand As New OleDbCommand(Me .DBQuery.Text,
Me.OleDbConnect ion)
Dim OleDbParameter As OleDbParameter =
OleDbCommand.Pa rameters.Add("@ Pinco", OleDbType.VarWC har)
OleDbParameter. Direction = ParameterDirect ion.Input
OleDbParameter. Value = "Berlin"
Using OleDbDataReader As OleDbDataReader =
OleDbCommand.Ex ecuteReader()
'...
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
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: pa***********@l ibero.it wrote:
Hi I am executing some simple sample code:
Using OleDbCommand As New OleDbCommand(Me .DBQuery.Text,
Me.OleDbConnect ion)
Dim OleDbParameter As OleDbParameter =
OleDbCommand.Pa rameters.Add("@ Pinco", OleDbType.VarWC har)
It might want the maximum length of the string as defined when you created
the table, e.g.:
Dim OleDbParameter As OleDbParameter OleDbCommand.Pa rameters.Add("@ Pinco",
OleDbType.VarWC har, 24)
Andrew
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.Pa rameters.Add(p)
with
OleDbCommand.Pa rameters.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
"pa***********@ libero.it" wrote:
Ok Kerry thanks , I tried
where C.City = ?
with this code:
Using OleDbCommand As New OleDbCommand(Me .DBQuery.Text,
Me.OleDbConnect ion)
Dim p As New OleDbParameter
p.Value = "Berlin"
OleDbCommand.Pa rameters.Add(p)
Using OleDbDataReader As OleDbDataReader =
OleDbCommand.Ex ecuteReader()
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
"pa***********@ libero.it" wrote:
Hi I am executing some simple sample code:
>
Using OleDbCommand As New OleDbCommand(Me .DBQuery.Text,
Me.OleDbConnect ion)
>
Dim OleDbParameter As OleDbParameter =
OleDbCommand.Pa rameters.Add("@ Pinco", OleDbType.VarWC har)
OleDbParameter. Direction = ParameterDirect ion.Input
OleDbParameter. Value = "Berlin"
>
Using OleDbDataReader As OleDbDataReader =
OleDbCommand.Ex ecuteReader()
'...
>
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
>
>
hi Kerry
it says.
System.InvalidO perationExcepti on occurred
Message="OleDbC ommand.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.Pa rameters.Add(p)
with
OleDbCommand.Pa rameters.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
"pa***********@ libero.it" wrote:
Ok Kerry thanks , I tried
where C.City = ?
with this code:
Using OleDbCommand As New OleDbCommand(Me .DBQuery.Text,
Me.OleDbConnect ion)
Dim p As New OleDbParameter
p.Value = "Berlin"
OleDbCommand.Pa rameters.Add(p)
Using OleDbDataReader As OleDbDataReader =
OleDbCommand.Ex ecuteReader()
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
>
>
"pa***********@ libero.it" wrote:
>
Hi I am executing some simple sample code:
Using OleDbCommand As New OleDbCommand(Me .DBQuery.Text,
Me.OleDbConnect ion)
Dim OleDbParameter As OleDbParameter =
OleDbCommand.Pa rameters.Add("@ Pinco", OleDbType.VarWC har)
OleDbParameter. Direction = ParameterDirect ion.Input
OleDbParameter. Value = "Berlin"
Using OleDbDataReader As OleDbDataReader =
OleDbCommand.Ex ecuteReader()
'...
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,
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
"pa***********@ libero.it" wrote:
hi Kerry
it says.
System.InvalidO perationExcepti on occurred
Message="OleDbC ommand.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.Pa rameters.Add(p)
with
OleDbCommand.Pa rameters.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
"pa***********@ libero.it" wrote:
Ok Kerry thanks , I tried
>
where C.City = ?
>
with this code:
>
Using OleDbCommand As New OleDbCommand(Me .DBQuery.Text,
Me.OleDbConnect ion)
>
Dim p As New OleDbParameter
p.Value = "Berlin"
OleDbCommand.Pa rameters.Add(p)
>
Using OleDbDataReader As OleDbDataReader =
OleDbCommand.Ex ecuteReader()
>
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
"pa***********@ libero.it" wrote:
Hi I am executing some simple sample code:
>
Using OleDbCommand As New OleDbCommand(Me .DBQuery.Text,
Me.OleDbConnect ion)
>
Dim OleDbParameter As OleDbParameter =
OleDbCommand.Pa rameters.Add("@ Pinco", OleDbType.VarWC har)
OleDbParameter. Direction = ParameterDirect ion.Input
OleDbParameter. Value = "Berlin"
>
Using OleDbDataReader As OleDbDataReader =
OleDbCommand.Ex ecuteReader()
'...
>
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
>
>
>
>
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
"pa***********@ libero.it" wrote:
hi Kerry
it says.
System.InvalidO perationExcepti on occurred
Message="OleDbC ommand.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.Pa rameters.Add(p)
>
with
>
OleDbCommand.Pa rameters.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
>
>
"pa***********@ libero.it" wrote:
>
Ok Kerry thanks , I tried
where C.City = ?
with this code:
Using OleDbCommand As New OleDbCommand(Me .DBQuery.Text,
Me.OleDbConnect ion)
Dim p As New OleDbParameter
p.Value = "Berlin"
OleDbCommand.Pa rameters.Add(p)
Using OleDbDataReader As OleDbDataReader =
OleDbCommand.Ex ecuteReader()
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
>
>
"pa***********@ libero.it" wrote:
>
Hi I am executing some simple sample code:
Using OleDbCommand As New OleDbCommand(Me .DBQuery.Text,
Me.OleDbConnect ion)
Dim OleDbParameter As OleDbParameter =
OleDbCommand.Pa rameters.Add("@ Pinco", OleDbType.VarWC har)
OleDbParameter. Direction = ParameterDirect ion.Input
OleDbParameter. Value = "Berlin"
Using OleDbDataReader As OleDbDataReader =
OleDbCommand.Ex ecuteReader()
'...
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Dan |
last post by:
I've run into an interesting problem, and seemed to have stumped 3
newsgroups and 2 other forums.
For some reason when I try to insert a record into a SQL table that has a
Text column, the returned autogenerated Identity is wrong (on the VB side).
This only occurs if the length of the value inserted for the text column is
>= 8002.
I've included a simple example below.
|
by: Matt |
last post by:
I have a simple JS function that I want to return a true or false value
based on the parameter passed in. At this point of time I receive the
error "'True' is undefined". Here is my code below.
JS Function -
function ShowSpecifiedPeriod(pShowPeriod){
|
by: J Krugman |
last post by:
I have a form with a couple of submit buttons, plus a "pseudolink"
that is also supposed to submit the form; the submitted form data
feeds to a CGI script. The two submit buttons have the name
"go_for_it", so that when the form is submitted, the CGI script
looks for the value of the CGI parameter "go_for_it". I want the
pseudolink to set the value of this CGI parameter prior to submitting
the form, but I can't get it to work. Here's the...
|
by: Megan |
last post by:
Hi-
I'm creating a database of music bands with their cds and songs.
I'm trying to program an SQL statement so that I can enter a string of
text in a textbox, press the 'Enter' key, and have it return the
associated records to a listbox. Once the listbox has the records, I
want to select a record, which will open a form associated with the
selected record in the listbox.
|
by: Brien King |
last post by:
If I have a parameter that has an Object type (as opposed to something like
a string), can I make that parameter a CONST?
Right now, if you pass an object into a sub/function, that sub/function can
modify the object no matter how it's defined (ByVal or ByRef).
In some cases, I want to make sure that you cannot modify the object. Is
there a way to do that in VS2003 or the up comming VS2005?
| |
by: Ranginald |
last post by:
Hi,
I'm having trouble passing a parameter from my default.aspx page to my
default2.aspx page.
I have values from a query in a list box and the goal is to pass the
"catID" from default.aspx to a stored procedure on the details2.aspx
page.
I can successfully pass the values from the listbox control to a
|
by: hzmonte |
last post by:
Correct me if I am wrong, declaring formal parameters of functions as
const, if they should not be/is not changed, has 2 benefits;
1. It tells the program that calls this function that the parameter
will not be changed - so don't worry.
2. It tells the implementor and the maintainer of this function that
the parameter should not be changed inside the function. And it is for
this reason that some people advocate that it is a good idea to...
|
by: humblemark |
last post by:
Hello,
I try to give a parameter in the function tt; but omething went
wrong?? Any idea what i can do??
int ret_integer()
{
return 76;
|
by: vcquestions |
last post by:
Hi.
Is there way to have a function pointer to a delegate in c++/cli that
would allow me to pass delegates with the same signatures as
parameters to a method?
I'm working with managed code. Let's say we have 2 delegates:
public delegate void FirstDelegate( int i );
public delegate void SecondDelegate( int i );
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |