Parameters and DateTime

[Goofy]

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 !

 

[Bob Phillips]

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]

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]

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)

 

[Goofy]

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]

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]

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