• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1953
  • 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?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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 SwamyYash Infinite Solutions Private LimitedCommented:

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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