Stored Procedures HELP

[jurgen]

Hi,

How can I execute a stored procedure on oracle ?

I created a stored procedure with 5 parameters. But when I execute it, I get an error message : -2147217887
"ODBC driver does not support the requested properties."
[Namespace=VbErr][Description=ODBC driver does not support the requested properties.][Number=-2147217887][Source=Microsoft OLE DB Provider for ODBC Drivers]

This is my code

Dim cmdSQL As New ADODB.Command
Dim strConn As String
Dim cnnDB As ADODB.Connection


cmdSQL.Parameters.Append cmdSQL.CreateParameter(pSelectedGasDay, adDBDate, adParamInput)
cmdSQL.Parameters.Append cmdSQL.CreateParameter(lVersionCreateUser, adBSTR, adParamInput)
cmdSQL.Parameters.Append cmdSQL.CreateParameter(lVersionCreateDate, adDBDate, adParamInput)
cmdSQL.Parameters.Append cmdSQL.CreateParameter(AppGetComputerNameCached, adBSTR, adParamInput)
cmdSQL.Parameters.Append cmdSQL.CreateParameter(lVersionId, adNumeric, adParamInput)

' Set up a command object for the stored procedure.
cmdSQL.CommandText = "SP_T.ImportTrdVal"
cmdSQL.CommandType = adCmdStoredProc

Set cnnDB = New ADODB.Connection

strConn = "DSN=" & AppGetDSN & ";User ID=" & SecCurrentUserGetUserToken.mbLogin & ";Password=" & SecCurrentUserGetUserToken.mbPassword & " ;"

cnnDB.Open strConn

'Set the connection for the command to your opened connection
cmdSQL.ActiveConnection = cnnDB

'Execute the command
cmdSQL.Execute

'Close the connection
cnnDB.Close

Can someone help me please

 

[Mute101]

Looking at this code, it seems like you have created the five parameters but haven't set any values. Therefore you are sending nothing to your stored procedure for it to function properly.

----------------------------------------
My doctor says that I have a malformed public duty gland and a natural deficiency in moral fibre and that I'm therefore excused from saving universes.
----------------------------------------

 

[jurgen]

I Found that already but i still won't work ...

cmdSQL.Parameters.Append cmdSQL.CreateParameter("SelectedGasDay", adDBDate, adParamInput, , pSelectedGasDay)
cmdSQL.Parameters.Append cmdSQL.CreateParameter("Creator", adBSTR, adParamInput, , lVersionCreateUser)
cmdSQL.Parameters.Append cmdSQL.CreateParameter("CreateDate", adDBDate, adParamInput, , lVersionCreateDate)
cmdSQL.Parameters.Append cmdSQL.CreateParameter("CreateOnMachine", adBSTR, adParamInput, , AppGetComputerNameCached)
cmdSQL.Parameters.Append cmdSQL.CreateParameter("VERSIONID", adDecimal, adParamInput, , lVersionId)

This is the result but nothing

I just need to execute a stored procedure that has 5 parameters, and nothing has to be given back to me. So there are only input parameters in my stored procedure.

I'm trying to use {call ....} but without any success ...

How do i do that

PACKAGE SP_TDSIMPORT_VALIDATION
IS

PROCEDURE ImportTpaTradesInValidation
(
SelectedGasDay IN date,
Creator IN varchar2,
CreateDate IN date,
CreateOnMachine IN varchar2,
VersionId IN number
);
END; -- Package spec

Regards JJ

 

[Mute101]

In your original post you refer to 'SP_T.ImportTrdVal' as the stored procedure name, yet in your last post the indicated procedure is called 'ImportTpaTradesInValidation'. Are you sure your calling the correct procedure in the code?

If what you have posted is your full code none of the variables that coutain your values to be sent are populated, is there more code?

I have no experience of Oracle as I've always developed in MS SQL so I can't help you with anything server side.

----------------------------------------
My doctor says that I have a malformed public duty gland and a natural deficiency in moral fibre and that I'm therefore excused from saving universes.
----------------------------------------

 

[cmmrfrds]

Instead of using ODBC, check out a couple of OLEDB options. You could try the oracle provider MSDAORA or the Microsoft provier for oracle ORAOLEDB.

See this site for sample connections strings.

http://www.able-consulting.com/mdac/ado/connection/oledb_providers.htm