Solved

Passing Oracle's SYSDATE using ADO's CreateParameter

Posted on 2000-04-24
5
2,589 Views
Last Modified: 2012-06-22
Does anyone know how to pass Oracle's SYSDATE as an input parameter to a stored procedure, using ADO's CreateParameter statement?

Example:

Set testParm = testCmd.CreateParameter("Time_Stamp", adDate, adParamInput, , SYSDATE)

Thanks!
0
Comment
Question by:marcmest
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 1

Expert Comment

by:epretti
ID: 2745682
There is no way to do that in this way .. because sysdate is a Oracle function, if you want to set the sysdate there what you have to do is, in the SQL put this, for ex.

"INSERT INTO [Table Name] Values (?, ?, SYSDATE())"

add to the command the parameters that you need and put the function in the SQL, not in a parameter. Bye :-)
0
 
LVL 18

Accepted Solution

by:
mdougan earned 50 total points
ID: 2747455
I've created a VB function to get the SysDate from Oracle:

Function SysDate() As Variant
'*************************************************************************************************************************
'   Retrieves the current date/time from the database server
'   Example call:
'       SQL = SQL + "SET LASTUPDATED = " & ToDate(SysDate())
'*************************************************************************************************************************
Dim sErrors As String
Dim sSQL As String
Dim vReturn As Variant
Dim lrs      As Recordset

    On Error GoTo SysDateErr
   
    SysDate = ""
    vReturn = ""
   
    sSQL = "SELECT SYSDATE AS SERVERDATE FROM DUAL"
   
    Set lrs = New ADODB.Recordset
    lrs.CacheSize = 100
    CN.CursorLocation = adUseServer
       
    lrs.Open sSQL, CN, adOpenForwardOnly, adLockReadOnly, adCmdText
   
    If Not lrs.BOF And Not lrs.EOF Then
        If Not IsNull(lrs("SERVERDATE")) Then
            If IsDate(lrs("SERVERDATE")) Then
                vReturn = Format(lrs("SERVERDATE"), "m/d/yyyy hh:nn:ss AM/PM")
            End If
        End If
    End If

    SysDate = vReturn
    lrs.Close
    Set lrs = Nothing
   
SysDateExit:
    Exit Function
SysDateErr:
    Screen.MousePointer = 0
    sErrors = "SysDate " & Err.Description
    frmMessage.MessageBox sErrors, vbCritical, Err & ""
    Resume SysDateExit

End Function

So, you're function call would be:

Set testParm = testCmd.CreateParameter("Time_Stamp", adDate, adParamInput, , SYSDATE())
0
 
LVL 1

Expert Comment

by:epretti
ID: 2748334
Sorry, the Sql is
"INSERT INTO Table_Name Values (?, ?, SYSDATE)"

Why do you say it is wrong? What are your trying to do?
0
 

Author Comment

by:marcmest
ID: 5667446
Many months later...

Another way I thought of to accomplish this is to pass either Null or some bogus date like 1/1/1900 to indicate to the stored procedure that Sysdate should be used.

Then in the stored procedure, check for these values and use SYSDATE as a substitute value using NVL or DECODE when appropriate...

May need to pass dates in as Varchar2 instead of date to accomplish some of this...
0
 
LVL 18

Expert Comment

by:mdougan
ID: 5731646
I guess the question is, will the date parameter sometimes not be the SYSDATE?  If it will always be the SYSDATE then the stored procedure shouldn't need to have this passed in as a parameter, you'd just include it in your select or update statement.

Another thought though, some guys on my latest SQL Server project have been talking about using Optional parameters to the Stored Proc.  I don't know if Oracle supports optional parameters or not, but you might check to see if it does.  If so, then you might be able to define SYSDATE as the default.  Something like:

Create Procedure MyProc(
@UserID    varchar(10),
@HireDate  datetime default SYSDATE)
AS

Select * from Users
Where UserID = @UserID
And HireDate >= @HireDate
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Put text in a picture ASP.NET C# 2 73
RUNRMTCMD from AS/400 13 129
which modules are active in VB6 project? 6 57
Export PDF Form fields to Access  or Excel  in Tab order 16 129
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

739 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