Solved

Embedded two SQL statement in ADODC.Recordset.Open

Posted on 2000-04-13
13
224 Views
Last Modified: 2008-03-17
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.

Note:
The command work in query analysier
The recordset.open works when the commandvar contain only one SQL statement
0
Comment
Question by:kahhoe
  • 5
  • 3
  • 2
  • +3
13 Comments
 
LVL 1

Expert Comment

by:ATM
ID: 2714638
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.
0
 
LVL 1

Expert Comment

by:ATM
ID: 2714714
replace chr(10) with vbCrLf, do it work?
0
 
LVL 1

Expert Comment

by:sunj
ID: 2714764
listening...
0
 

Author Comment

by:kahhoe
ID: 2714845
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.
0
 

Expert Comment

by:Olli083097
ID: 2714935
Isn't the date format equal on all SQL systems? (MM/DD/YYYY)
0
 

Author Comment

by:kahhoe
ID: 2714940
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.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 2

Expert Comment

by:BobbyOwens
ID: 2714971
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 "
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2714999
I always pass datetime variables using the following string format (if i don't use Parameters collection of the ADODB.command object)
yyyy/mm/dd
0
 

Author Comment

by:kahhoe
ID: 2715101
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.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2715104
For the stored procedure, you need to add EXEC before it, and it WILL work
0
 

Author Comment

by:kahhoe
ID: 2715178
Thanks you!  It really works.

btw, Who should give my marks to?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
ID: 2715185
Your decision
You may ask the EE Support to split points...
0
 

Author Comment

by:kahhoe
ID: 2715204
I think you solved my question directly.

Many Thanks AngelIII!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
z = x + y – 1 6 67
How to create a duplicate finder Application 9 113
Determine Range to Select 5 42
Access Object Property from VBA Module in Excel 2010 2 27
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now