• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1784
  • Last Modified:

How to use the adArray parameter in ADO command object

Hello experts,

My question seems to be a very common scenario, but i was unable to find an answer from the net. I have a VB array that needs to inserted into an oracle table. I need to pass this array as a parameter to a stored procedure. Somebody please help me out with a sample code. Also how should i declare the parameter in oracel to accept this array ?

1 Solution
What kind of array is it?  String, integer, byte?
TheHartfordAuthor Commented:
Its a combination of String and Integer array
In otherwords a variant array?
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

TheHartfordAuthor Commented:
You could say that...the reason is i want to call the stored procedure only once from my VB code but execute the array the number of times it has the elements in the backend

Execute the array?  What does that mean exactly?  What's being stored in the array, and where is the data going in the database?  Single field, multiple fields?  

If you want to combine multiples of different type values into a single field, I suggest you use a format like XML.  If they are going to separate fields, but you want to limit the number of trips to the database, I would suggest a batch update instead.
Naveen SwamyCommented:

Public Function ExecuteSP(ByVal sProcName As String, ParamArray aParams()) As ADODB.Recordset

    Dim cmd As ADODB.Command
    Dim cmdParam As New ADODB.Parameter
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = gConn
    cmd.CommandText = sProcName
    cmd.CommandType = adCmdStoredProc
    If UBound(aParams) < 0 Then
        ' if NO parameters to execute the sp
        Set ExecuteSP = cmd.Execute
        ' if we use parameters to execute the sp
        Set ExecuteSP = cmd.Execute(, aParams)
    End If
    Set cmd = Nothing
'    Exit Function
'    MsgBox "Error: " & vbCrLf & "Reason: " & Err.Description, vbCritical + vbSystemModal, gAppTitle
''    Resume
'    Exit Function
End Function

first parameter to call the stored procedure and then the second is a list of arrays (can be string or integer)

    Set grsTemp = ExecuteSP("SSP_INSERT_RELATIONSHIPLIST", strMainId, sCurrentTableName, iPrevID, sPrevFldName, sFldOrderVal)

M.Naveen Swamy
I think the reason he was unable to find his answer on the Internet is because it is not possible to pass an array of values as a single parameter.  I believe the adArray datatype is to support the possibility of data access drivers that would support an array datatype.  The correct workaround depends on what you are trying to acheive.

PRB: Microsoft Oracle OLEDB Provider and ODBC Drivers Do Not Support Passing Array Data Types

PAQed with no points refunded (of 100)

Community Support Moderator

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now