Vbscript Sql Error

[BrianW]

I am trying to send a value to a stored procedure to a sql server. I have looked at all the voiceguide examples for vb script and still no luck.

 

When trying to use the example syntax i receive an error msg saying "arguments are of the wrong type, are out of acceptable range, or are inconflict with one another.

 

Code: 800A0BB9

Source: ADODB.Command

 

for this line .CommandType = adCmdStoredProc

 

 

With cmd

set .ActiveConnection = cn

.CommandType = adCmdStoredProc

.CommandText = "dbo.[ivr_hti_phoneval]"

 

Any help or other example sql stored procedure scripts would be greatly appreciated.

 

Thanks

[ktruk]

Not sure on the exact syntax of what you are doing here, but try using the proper RV substitution of "$RV[ModuleName]" intead of just [ModuleName], eg:

 

With cmd

set .ActiveConnection = cn

.CommandType = adCmdStoredProc

CommandText = "dbo.$RV[ivr_hti_phoneval]

 

To see if this works as expected, look in the voiceguide directory for the temporary VBS files that hold the actual code run after RV substitution. (They either held in the TEMP directory for newer VG versions or the DATA directory. The one with the latest timestamps are the ones run most recently and the exact filenames can be found in the debug-trace.

 

Hope this helps - Tim.

[BrianW]

What im trying to do is run a stored procedure on an sql server called ivr_hti_phoneval.

 

If i ran it in a sql query it would look like this "exec ivr_hti_phoneva someparameter"

 

I have a customer sql database that has a stored procedure that will accept the customer number and return 5 values such as balance, date of payment, etc.

 

I could not get the Database Query module to return the proper results so I trying to write a VB script to do the same thing.

 

 

This is some sample testing code to make the connection. The problem is with line 16 char 1

 

 

dim cmd, prm, rs, cn

 

 

set vg = CreateObject("VoiceGuide.CommandLink")

set cn = CreateObject("ADODB.Connection")

set rs = CreateObject("ADODB.Recordset")

set cmd = CreateObject("ADODB.Command")

 

cn.ConnectionString = "Provider=SQLOLEDB;Server=XXX.XXX.XXX.XXX;UID=XXXX;PWD=XXXX;Database=XXXX"

cn.open

 

 

With cmd

 

set .ActiveConnection = cn

.CommandType = adCmdStoredProc 'line 16

.CommandText = "dbo.ivr_hti_phoneval"

 

 

set prm= .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 4)

.Parameters.Append prm

 

 

set prm = .CreateParameter("@phone_nbr", adVarChar, adParamInput, 12)

.Parameters.Append prm

 

 

set prm= .CreateParameter("@match", adVarChar, adParamOutput, 1)

.Parameters.Append prm

 

 

set prm = .CreateParameter("@paid_thru_date", adVarChar, aadParamOutput, 8)

.Parameters.Append prm

 

 

set prm= .CreateParameter("@active", adVarChar, adParamOutput, 1)

.Parameters.Append prm

 

 

set prm = .CreateParameter("@current_balance", adVarChar, adParamOutput, 6)

.Parameters.Append prm

 

 

set prm = .CreateParameter("@cust_id_nbr", adVarChar, adParamOutputt, 20)

.Parameters.Append prm

 

 

 

set rs= cmd.execute

End With

 

 

cn.Close

set rs = Nothing

set cn = Nothing

set vg = Nothing

Set cmd = Nothing

WScript.Quit

Edited 09/16/2005 01:37 AM by SupportTeam

[SupportTeam]

The problem is with line 16 char 1

 

Have you defined the adCmdStoredProc constant?

Here are some of the constants you may need in your VBScript

 

Const adParamInput = 1

Const adParamOutput = 2

Const adInteger = 3

Const adCmdStoredProc = 4

Const adExecuteNoRecords = 128

Const adVarChar = 200

 

you will still need to find out value of adParamReturnValue and any others...

 

PS. we edited your previous post as per your request.

 

PPS.

This line has a spelling mistake (highlighted in bold):

 

set prm = .CreateParameter("@cust_id_nbr", adVarChar, adParamOutputt, 20)

[BrianW]

thats it....thanks for your help

[Guest Etem Emrah Alicli]

You dont need to create and add each stored procedure paramater manually.

 

just call

 

cmd.Parameters.Refresh

 

This will fetch all parameters