VB6 ADO SQL Server Question

mossmis
mossmis used Ask the Experts™
on
I have the following SQL statement I want to execute from within a VB6 App:

SELECT DATENAME(dd, GETDATE())+
left(CONVERT(VARCHAR(8), GETDATE(), 1),2)+
DATENAME(yyyy, GETDATE())+
DATENAME(hh, GETDATE())+
DATENAME(mi, GETDATE())+
DATENAME(ss, GETDATE()) + right(CONVERT(VARCHAR(23), GETDATE(), 121), 3)
AS DateGen


I am trying to get it to work with ADO and with examples I found on the web, I cannot figure out how to formulate it. I am connecting to SQL server 2008 via ODBC.  All I want to do is  return the "DateGen". Any help would be appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Brook BraswellApplication Development Manager

Commented:
DIM SQL AS STRING
DIM RS AS NEW ADODB.RECORDSET
DIM CN AS NEW ADODB.CONNECTION
CN.OPEN "YOUR CONNECTION TO SQL"

SQL = "SELECT DATENAME(dd, GETDATE())+ " & _
"left(CONVERT(VARCHAR(8), GETDATE(), 1),2)+ " & _
"DATENAME(yyyy, GETDATE())+ " & _
"DATENAME(hh, GETDATE())+ " & _
"DATENAME(mi, GETDATE())+ " & _
"DATENAME(ss, GETDATE()) + right(CONVERT(VARCHAR(23), GETDATE(), 121), 3) " & _
"AS DateGen"

RS.CursorLocation = adUseClient
RS.Open SQL, cn, adOpenStatic, adLockReadOnly
What does it mean by formulate?
Brook BraswellApplication Development Manager

Commented:
where do you see formulate ?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Application Development Manager
Commented:
Modify the Year on line 3 of your query...

SELECT DATENAME(dd, GETDATE())+
left(CONVERT(VARCHAR(8), GETDATE(), 1),2)+
DATENAME(yy, GETDATE())+
DATENAME(hh, GETDATE())+
DATENAME(mi, GETDATE())+
DATENAME(ss, GETDATE()) + right(CONVERT(VARCHAR(23), GETDATE(), 121), 3)
AS DateGen
Brook BraswellApplication Development Manager

Commented:
for GetDate = 2012-05-24 08:45:33.384
the query would result  2405201284533384

Author

Commented:
Brook1966,

I got the query to run in VB. How do I get it to return the value so I can use it in my program?
Brook BraswellApplication Development Manager
Commented:
dim S as string
S = RS!DateGen

Author

Commented:
That works! One last question, should I get in the habit of closing the connection when I'm done?

ie. RS.Close

?
Brook BraswellApplication Development Manager
Commented:
rs.close does not close the connection but it does close the recordset and is a good practice if you are finished with it.
Close the connection if you do not use it often enough to keep it open

cn.close

It is also a good practice to destroy when finished

Set rs = nothing
and
set CN = nothing
if you no longer need them

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial