3

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.

Ash W
  • 55
  • 1
  • 8
  • Does SQL Server even support `screens.Type=adArray`? Is this the whole script? – user692942 Feb 17 '16 at 09:52
  • Possible duplicate of [How to use ADODB parameterized query with adArray data type in VBScript?](http://stackoverflow.com/questions/9315002/how-to-use-adodb-parameterized-query-with-adarray-data-type-in-vbscript) – user692942 Feb 17 '16 at 09:56
  • Which line exactly is raising the error? – Ansgar Wiechers Feb 17 '16 at 09:58
  • Here's a useful resource for you that I have used for a long time as my [ADO Data Type Bible](http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx) I'm fairly sure that `adArray` is not supported in ADO and was added for future compatibility. – user692942 Feb 17 '16 at 09:59
  • I assumed it would be supported? And yes this is the whole script.. at least the VBscript side of it. (The LFXX variables are declared outside of the script and passed in). – Ash W Feb 17 '16 at 09:59
  • Even if it was supported it was designed as a [bit flag](https://en.wikipedia.org/wiki/Flag_field) to be combined with another data type constant to denote an Array of that data type. So would expect `Type = adArray` to work. – user692942 Feb 17 '16 at 10:02
  • @Lankymart I see so I should declare it using a type as well? I.e 'Const adArray = 0x2000 200'? – Ash W Feb 17 '16 at 10:04
  • @AnsgarWiechers The development environment I'm using is a product my company offer. VB scripting is a very small feature of the product and is not a heavy focus; we only get error messages and no references to what line is producing the error. – Ash W Feb 17 '16 at 10:07
  • Run the code standard alone and see what you get, create a txt file with a `vbs` extension copy the code in and run it *(remember to substitute the `LF0...` variables for dummy data)*. – user692942 Feb 17 '16 at 10:08
  • You would need to `OR` it. Something like `Type = (adVarChar OR adArray)`. – user692942 Feb 17 '16 at 10:19

1 Answers1

1

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

Community
  • 1
  • 1
user692942
  • 16,398
  • 7
  • 76
  • 175
  • 1
    In which case, I may need another solution. The array will always be fairly small so I suppose I could pass it through as a comma delimited string. Thanks for the information! – Ash W Feb 17 '16 at 10:08