Search code examples
sql-servervbscriptoledbadoadodb

VBScript / ADODB Syntax Issue with adArray?

I'm hoping somebody could provide me with some fresh eyes on my vb script. The main purpose of this script is to execute a stored procedure using some parameters.

The error I get is

'Expected end of statement'

I haven't done much VB scripting but from what I have found so far - this error has been down to some kind of syntax issue. I've looked over this script for many hours and can't see anything obvious. I can only assume it's down to the declaration of adArray (which doesn't look right in my eyes but I haven't been able to find ANY examples of this being declared). This error has only been introduced when I started adding more parameters.

Code:

Const adVarChar = 200
Const adParamInput = 1
Const adArray = 0x2000

Dim cmd
Dim sp
Dim intCode
Dim addIn
Dim groupCode
Dim screens
Dim arrScreens
arrScreens=split(LF06,",")

Set cmd=CreateObject("ADODB.Command")
sp="vfile_dev.dbo.vfp_groupReorder"

Set intCode=CreateObject("ADODB.Parameter")
intCode.Direction=adParamInput
intCode.name="@p_intCode"
intCode.Size=100
intCode.Type=adVarChar
intCode.Value=LF03

Set addIn=CreateObject("ADODB.Parameter")
addIn.Direction=adParamInput
addIn.name="@p_addIn"
addIn.Size=100
addIn.Type=adVarChar
addIn.Value=LF04

Set groupCode=CreateObject("ADODB.Parameter")
groupCode.Direction=adParamInput
groupCode.name="@p_groupCode"
groupCode.Size=100
groupCode.Type=adVarChar
groupCode.Value=LF05

Set screens=CreateObject("ADODB.Parameter")
screens.Direction=adParamInput
screens.name="@p_screens"
screens.Size=100
screens.Type=adArray
screens.Value=arrScreens

With cmd
   .ActiveCOnnection = "Provider='sqloledb';Data source='xxx';Integrated Security='SSPI';"
   .CommandType = 4
   .CommandText = sp
   .Parameters.Append intCode
   .Parameters.Append addIn
   .Parameters.Append groupCode
   .Parameters.Append screens
   .Execute
End With

Set cmd = Nothing
Set sp = Nothing
Set intCode = Nothing
Set addIn = Nothing
Set groupCode = Nothing

Any help would be much appreciated. Thanks.

EDIT:

For those interested - my solution was to ditch adArray and pass my data through as a comma delimited varchar. I then handle this data in my stored procedure with a simple split function.

Solution

  • I'm fairly sure adArray although defined in the ADO constants is not supported by ADO and was added for future compatibility.

    From MSDN - ADO API Reference - DataTypeEnum
    A flag value, always combined with another data type constant, that indicates an array of the other data type. Does not apply to ADOX.

    This definition suggests it should only be used with another ADO DataTypeEnum constant value to denote an Array of that Data Type.

    Although there is some suggestion that ORing the value with another DataTypeEnum constant should work, I found this from the 12 years ago.

    From the ADO public newsgroup (microsoft.public.ado)
    Discussion: how to use AdArray data type with sql server 7 and stored procedures
    Date: 2003-09-29 19:24:10 UTC

    Hi David,

    adArray is not supported in ADO and was created for future compatibility. What do you need to achieve? Maybe we could help with another solution
    --
    Val Mazur
    Microsoft MVP
    Check Virus Alert, stay updated
    http://www.microsoft.com/security/incident/blast.asp

    Useful Links