Parameters and DateTime
 

This question is twofold, but related in a sense so I posted them together.

I am using an ODBC Microsoft SQL driver and ADO. I tried to use parameters
but ended up with a message telling me that the variables @Whatever had not
been set. I know the code was good and when I looked the error message up on
the internet, someone said that named parameters are not supported on th
ODBC Driver for Microsoft SQL and you have to use positional parameters ?
placeholders. So

1.) Is this correct about the placeholders.

2.) If so, is it the order SQL placeholders are found in the sqlString and
can you intersperse them. for instance.

INSERT INTO MYTABLE VALUES (
firstField='Hello',secondField=?,thirdField='World ',lastField=?);

In this case is it the order which I created the parameters in the command
object which dictactes which one to pick up next ?

3.) Lastly, I have the standard situation where the sever takes dates in US
format by string, someone told me that if you use parameters then you can
assign the parameter a UK format and the server will take it as a parameter
an store it in the UK Format.

Is this true ?

Thanks so much for your help !

Parameters and DateTime
 

Use something like

mySQL = "INSERT INTO MYTABLE VALUES ( " & _
"firstField='Hello'," & _
"secondField='" param2 & "@'," & _
"thirdField='World'," & _
"lastField='" & param4 & "');"


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Goofy" wrote in message
...
This question is twofold, but related in a sense so I posted them
together.

I am using an ODBC Microsoft SQL driver and ADO. I tried to use parameters
but ended up with a message telling me that the variables @Whatever had
not
been set. I know the code was good and when I looked the error message up
on
the internet, someone said that named parameters are not supported on th
ODBC Driver for Microsoft SQL and you have to use positional parameters ?
placeholders. So

1.) Is this correct about the placeholders.

2.) If so, is it the order SQL placeholders are found in the sqlString and
can you intersperse them. for instance.

INSERT INTO MYTABLE VALUES (
firstField='Hello',secondField=?,thirdField='World ',lastField=?);

In this case is it the order which I created the parameters in the command
object which dictactes which one to pick up next ?

3.) Lastly, I have the standard situation where the sever takes dates in
US
format by string, someone told me that if you use parameters then you can
assign the parameter a UK format and the server will take it as a
parameter
an store it in the UK Format.

Is this true ?

Thanks so much for your help !

Parameters and DateTime
 

Hi Bob,

I thought parameters are allways preceeded by the @ character not appended,
have I got this wrong ?

I do appreciate your time to write this up; Would it be possible for you to
answer the three questions I posed ?

Many Thanks


"Bob Phillips" wrote in message
...
Use something like

mySQL = "INSERT INTO MYTABLE VALUES ( " & _
"firstField='Hello'," & _
"secondField='" param2 & "@'," & _
"thirdField='World'," & _
"lastField='" & param4 & "');"


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Goofy" wrote in message
...
This question is twofold, but related in a sense so I posted them
together.

I am using an ODBC Microsoft SQL driver and ADO. I tried to use
parameters
but ended up with a message telling me that the variables @Whatever had
not
been set. I know the code was good and when I looked the error message up
on
the internet, someone said that named parameters are not supported on th
ODBC Driver for Microsoft SQL and you have to use positional parameters ?
placeholders. So

1.) Is this correct about the placeholders.

2.) If so, is it the order SQL placeholders are found in the sqlString
and
can you intersperse them. for instance.

INSERT INTO MYTABLE VALUES (
firstField='Hello',secondField=?,thirdField='World ',lastField=?);

In this case is it the order which I created the parameters in the
command
object which dictactes which one to pick up next ?

3.) Lastly, I have the standard situation where the sever takes dates in
US
format by string, someone told me that if you use parameters then you can
assign the parameter a UK format and the server will take it as a
parameter
an store it in the UK Format.

Is this true ?

Thanks so much for your help !

Parameters and DateTime
 

Goofy,

As I see your code, you are not using parameters as such. As I see it, all
you are trying to is use a variable within a SQL string. If I am correct,
then my answer covers 1 and 2.

As to the date issue, I just pass dates as a formatted string, like so

sSQL = sSQL & "#" & Format$(myDate, "yyyy-mm-dd") & "#"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bob Phillips" wrote in message
...
Use something like

mySQL = "INSERT INTO MYTABLE VALUES ( " & _
"firstField='Hello'," & _
"secondField='" param2 & "@'," & _
"thirdField='World'," & _
"lastField='" & param4 & "');"


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Goofy" wrote in message
...
This question is twofold, but related in a sense so I posted them
together.

I am using an ODBC Microsoft SQL driver and ADO. I tried to use
parameters
but ended up with a message telling me that the variables @Whatever had
not
been set. I know the code was good and when I looked the error message
up
on
the internet, someone said that named parameters are not supported on th
ODBC Driver for Microsoft SQL and you have to use positional parameters
?
placeholders. So

1.) Is this correct about the placeholders.

2.) If so, is it the order SQL placeholders are found in the sqlString
and
can you intersperse them. for instance.

INSERT INTO MYTABLE VALUES (
firstField='Hello',secondField=?,thirdField='World ',lastField=?);

In this case is it the order which I created the parameters in the
command
object which dictactes which one to pick up next ?

3.) Lastly, I have the standard situation where the sever takes dates in
US
format by string, someone told me that if you use parameters then you
can
assign the parameter a UK format and the server will take it as a
parameter
an store it in the UK Format.

Is this true ?

Thanks so much for your help !

Parameters and DateTime
 

Thanks for your reply Bob,

My code was actually incorrect as an example, but the code I have tried was
similar to this

dim cmd as new ADODB.Command
dim myParam as ADODB.Parameter

myParam = cmd.CreateParameter "@pname", otherstuf, otherstuf, etc

cmd.parametes.append myParam

My Insert was like this

INSERT INTO MYTABLE VALUES( 'f1',@pname,'f2' )

I am swiftly coming to the opinion that parameter passing is not supported
by the Microsoft SQL ODBC driver. Allthough it does not give you that as an
error, and instead it tells you that you have to create the parameter
@pname which is clearly being passed to the cmd object.


Cheers







"Bob Phillips" wrote in message
...
Goofy,

As I see your code, you are not using parameters as such. As I see it, all
you are trying to is use a variable within a SQL string. If I am correct,
then my answer covers 1 and 2.

As to the date issue, I just pass dates as a formatted string, like so

sSQL = sSQL & "#" & Format$(myDate, "yyyy-mm-dd") & "#"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bob Phillips" wrote in message
...
Use something like

mySQL = "INSERT INTO MYTABLE VALUES ( " & _
"firstField='Hello'," & _
"secondField='" param2 & "@'," & _
"thirdField='World'," & _
"lastField='" & param4 & "');"


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Goofy" wrote in message
...
This question is twofold, but related in a sense so I posted them
together.

I am using an ODBC Microsoft SQL driver and ADO. I tried to use
parameters
but ended up with a message telling me that the variables @Whatever had
not
been set. I know the code was good and when I looked the error message
up
on
the internet, someone said that named parameters are not supported on
th
ODBC Driver for Microsoft SQL and you have to use positional parameters
?
placeholders. So

1.) Is this correct about the placeholders.

2.) If so, is it the order SQL placeholders are found in the sqlString
and
can you intersperse them. for instance.

INSERT INTO MYTABLE VALUES (
firstField='Hello',secondField=?,thirdField='World ',lastField=?);

In this case is it the order which I created the parameters in the
command
object which dictactes which one to pick up next ?

3.) Lastly, I have the standard situation where the sever takes dates
in
US
format by string, someone told me that if you use parameters then you
can
assign the parameter a UK format and the server will take it as a
parameter
an store it in the UK Format.

Is this true ?

Thanks so much for your help !

Parameters and DateTime
 

You may well be right, I only use the OLE DB driver. But as I understand it,
you can only use parameters with stored procedures, not in an inline SQL
statement.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Goofy" wrote in message
...
Thanks for your reply Bob,

My code was actually incorrect as an example, but the code I have tried
was
similar to this

dim cmd as new ADODB.Command
dim myParam as ADODB.Parameter

myParam = cmd.CreateParameter "@pname", otherstuf, otherstuf, etc

cmd.parametes.append myParam

My Insert was like this

INSERT INTO MYTABLE VALUES( 'f1',@pname,'f2' )

I am swiftly coming to the opinion that parameter passing is not
supported
by the Microsoft SQL ODBC driver. Allthough it does not give you that as
an
error, and instead it tells you that you have to create the parameter
@pname which is clearly being passed to the cmd object.


Cheers







"Bob Phillips" wrote in message
...
Goofy,

As I see your code, you are not using parameters as such. As I see it,
all
you are trying to is use a variable within a SQL string. If I am
correct,
then my answer covers 1 and 2.

As to the date issue, I just pass dates as a formatted string, like so

sSQL = sSQL & "#" & Format$(myDate, "yyyy-mm-dd") & "#"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bob Phillips" wrote in message
...
Use something like

mySQL = "INSERT INTO MYTABLE VALUES ( " & _
"firstField='Hello'," & _
"secondField='" param2 & "@'," & _
"thirdField='World'," & _
"lastField='" & param4 & "');"


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Goofy" wrote in message
...
This question is twofold, but related in a sense so I posted them
together.

I am using an ODBC Microsoft SQL driver and ADO. I tried to use
parameters
but ended up with a message telling me that the variables @Whatever
had
not
been set. I know the code was good and when I looked the error
message
up
on
the internet, someone said that named parameters are not supported on
th
ODBC Driver for Microsoft SQL and you have to use positional
parameters
?
placeholders. So

1.) Is this correct about the placeholders.

2.) If so, is it the order SQL placeholders are found in the
sqlString
and
can you intersperse them. for instance.

INSERT INTO MYTABLE VALUES (
firstField='Hello',secondField=?,thirdField='World ',lastField=?);

In this case is it the order which I created the parameters in the
command
object which dictactes which one to pick up next ?

3.) Lastly, I have the standard situation where the sever takes dates
in
US
format by string, someone told me that if you use parameters then you
can
assign the parameter a UK format and the server will take it as a
parameter
an store it in the UK Format.

Is this true ?

Thanks so much for your help !

Parameters and DateTime
 

OK, thanks for all your help Bob, much appreciated.



"Bob Phillips" wrote in message
...
You may well be right, I only use the OLE DB driver. But as I understand
it,
you can only use parameters with stored procedures, not in an inline SQL
statement.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Goofy" wrote in message
...
Thanks for your reply Bob,

My code was actually incorrect as an example, but the code I have tried
was
similar to this

dim cmd as new ADODB.Command
dim myParam as ADODB.Parameter

myParam = cmd.CreateParameter "@pname", otherstuf, otherstuf, etc

cmd.parametes.append myParam

My Insert was like this

INSERT INTO MYTABLE VALUES( 'f1',@pname,'f2' )

I am swiftly coming to the opinion that parameter passing is not
supported
by the Microsoft SQL ODBC driver. Allthough it does not give you that as
an
error, and instead it tells you that you have to create the parameter
@pname which is clearly being passed to the cmd object.


Cheers







"Bob Phillips" wrote in message
...
Goofy,

As I see your code, you are not using parameters as such. As I see it,
all
you are trying to is use a variable within a SQL string. If I am
correct,
then my answer covers 1 and 2.

As to the date issue, I just pass dates as a formatted string, like so

sSQL = sSQL & "#" & Format$(myDate, "yyyy-mm-dd") & "#"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bob Phillips" wrote in message
...
Use something like

mySQL = "INSERT INTO MYTABLE VALUES ( " & _
"firstField='Hello'," & _
"secondField='" param2 & "@'," & _
"thirdField='World'," & _
"lastField='" & param4 & "');"


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Goofy" wrote in message
...
This question is twofold, but related in a sense so I posted them
together.

I am using an ODBC Microsoft SQL driver and ADO. I tried to use
parameters
but ended up with a message telling me that the variables @Whatever
had
not
been set. I know the code was good and when I looked the error
message
up
on
the internet, someone said that named parameters are not supported
on
th
ODBC Driver for Microsoft SQL and you have to use positional
parameters
?
placeholders. So

1.) Is this correct about the placeholders.

2.) If so, is it the order SQL placeholders are found in the
sqlString
and
can you intersperse them. for instance.

INSERT INTO MYTABLE VALUES (
firstField='Hello',secondField=?,thirdField='World ',lastField=?);

In this case is it the order which I created the parameters in the
command
object which dictactes which one to pick up next ?

3.) Lastly, I have the standard situation where the sever takes
dates
in
US
format by string, someone told me that if you use parameters then
you
can
assign the parameter a UK format and the server will take it as a
parameter
an store it in the UK Format.

Is this true ?

Thanks so much for your help !