Embedded two SQL statement in ADODC.Recordset.Open

I need to set the date format to based on the system locale and once I have captured the date, I will update it to a SQL Server Database with an unknown date format.

Hence I am thinking of doing the following:

commandvar="set dateformat ymd" + Chr(10) + "sp_updatesometable blar blar blar"
recordset.open commandvar, connectionobject

However, when I tried that, it gives error.

Please help!  Thanks you in advance.

The command work in query analysier
The recordset.open works when the commandvar contain only one SQL statement
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
Your decision
You may ask the EE Support to split points...
Have You try :
Convert (datetime,@strval,dformat)

I prefer to use that one when deal with datetime.

for example:
@strval='14/04/2000' passed as varchar(10) to procedure and converted with 103 code.
replace chr(10) with vbCrLf, do it work?
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

kahhoeAuthor Commented:
not sure if this is valid.

I have tried running the two statement separately and it seems that that behaviour is as expect.  The date format is changed and the second statement can go through smoothly.
Isn't the date format equal on all SQL systems? (MM/DD/YYYY)
kahhoeAuthor Commented:
if you are using SQL Server, you can set the dateformat at syslanguage at master table.  Depending on the langid you are using, you can use different date format.

Actually the dateformat in the SQL Server should be dmy or ymd or blar blar blar. I don't remember seeing MM/DD/YYYY.
Have you tried sometrhing like the following?

dim strSQL as string

strSQL="set dateformat ymd " & vbcrlf
strSQL=strSQL & "go " & vbcrlf
strSQL=strSQL & "sp_updatesometable blar blar blar "
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I always pass datetime variables using the following string format (if i don't use Parameters collection of the ADODB.command object)
kahhoeAuthor Commented:
I have tried.  Observation.

1. Go statement wouldn't work
2. If the statement contain stored procedure, it wouldn't work also
3. It only work if everything is purely SQL statement but it is not necessary to cascade those statement together because you can execute the two statement together.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
For the stored procedure, you need to add EXEC before it, and it WILL work
kahhoeAuthor Commented:
Thanks you!  It really works.

btw, Who should give my marks to?
kahhoeAuthor Commented:
I think you solved my question directly.

Many Thanks AngelIII!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.