Avatar of dcass
dcassFlag for United States of America asked on

SQL stored procedure/vbscript

This should an easy one, but I'm not getting any help on my other question for this.
I can't get a stored procedure to work:

Parmnm=""
Var1=""
Var2=""
Var3=""

 For i = 0 to 27
       req=request.form(arrResults(i,0))
        VarX="Var"&i
        If VarX = "Var0" then
         VarX = "ParmNm"
        End If
        If VarX = "ParmNm" then ParmNm = req
        If VarX = "Var1" then Var1 = req
        If VarX = "Var2" then Var2 = req
        If VarX = "Var3" then Var3 = req
 Next
 
     Set SQL_Cn = server.createobject("ADODB.Connection")
     SQL_Cn.Provider = "sqloledb"
     SQL_Cn.Properties("Data Source").Value = "10.10.1.12, 1045"
     SQL_Cn.Properties("Initial Catalog").Value = "Tablename"
     SQL_Cn.Properties("User ID").Value = "user"
     SQL_Cn.Properties("Password").Value = "password"    
     SQL_Cn.open
     SQL_RS.ActiveConnection = SQL_Cn
     Set     cmd = Server.CreateObject ("ADODB.Command")
               cmd.ActiveConnection = SQL_cn
     If strNew = "N" then
               cmd.CommandText = "updtcust"
      else
               cmd.CommandText = "insertcust"
     end if        
               cmd.CommandType = adCmdStoredProc
               cmd.Parameters.Append cmd.CreateParameter("@p1", adVarChar, adInput, 80, parmnm)
               cmd.Parameters.Append cmd.CreateParameter("@p2", adVarChar, adInput, 80, var1)
               cmd.Parameters.Append cmd.CreateParameter("@p3", adVarChar, adInput, 80, var2)
               cmd.Parameters.Append cmd.CreateParameter("@p4", adVarChar, adInput, 80, var3)
               cmd.execute
              set cmd=nothing

And the error is:
ADODB.Command error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another on
          cmd.CommandType = adCmdText
All variables are varchar and the correct length.

The insertcust stored procedure that I'm trying to get to work is:

USE [Promotions]
GO
/****** Object:  StoredProcedure [dbo].[insertcust]    Script Date: 05/28/2008 17:06:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[insertcust]

       @p1 varchar(80),
       @p2 varchar(80),
       @p3 varchar(80),
       @p4 varchar(80)
   
AS
BEGIN
       insert into dbo.db6l1p1 (parmnm,var1,var2,var3) values(@p1,@p2,@p3,@p4)
END


Microsoft SQL Server 2005

Avatar of undefined
Last Comment
dcass

8/22/2022 - Mon
Robb Hill

command type would be adStoredProc
ASKER
dcass

I copied it wrong - I have that.
The error message when looked up says that there is no recordset open.
Do I need that with a stored procedure?
ASKER
dcass

Can someone send me a simple vbscript example for an insert stored procedure?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
jgv

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
dcass

I got it - here it is for future reference for those who can't get this in ASP:
Parmnm=""
Var1=""
Var2=""
Var3=""

 For i = 0 to 27
       req=request.form(arrResults(i,0))
        VarX="Var"&i
        If VarX = "Var0" then
         VarX = "ParmNm"
        End If
        If VarX = "ParmNm" then ParmNm = req
        If VarX = "Var1" then Var1 = req
        If VarX = "Var2" then Var2 = req
        If VarX = "Var3" then Var3 = req
 Next
 
     Set SQL_Cn = server.createobject("ADODB.Connection")
     SQL_Cn.Provider = "sqloledb"
     SQL_Cn.Properties("Data Source").Value = "10.10.1.12, 1045"
     SQL_Cn.Properties("Initial Catalog").Value = "Tablename"
     SQL_Cn.Properties("User ID").Value = "user"
     SQL_Cn.Properties("Password").Value = "password"    
     SQL_Cn.open
     SQL_RS.ActiveConnection = SQL_Cn
     Set     cmd = Server.CreateObject ("ADODB.Command")
               cmd.ActiveConnection = SQL_cn
     If strNew = "N" then
               cmd.CommandText = "updtcust"
      else
               cmd.CommandText = "insertcust"
     end if    
'200 = adVarChar - must look up other types    
               cmd.CommandType = adCmdStoredProc
               cmd.Parameters.Append cmd.CreateParameter("@p1", 200,&H0001, 80, parmnm)
               cmd.Parameters.Append cmd.CreateParameter("@p2", 200,&H0001, 80, var1)
               cmd.Parameters.Append cmd.CreateParameter("@p3", 200,&H0001, 80, var2)
               cmd.Parameters.Append cmd.CreateParameter("@p4", 200,&H0001, 80, var3)
               cmd.execute
              set cmd=nothing

The insertcust stored procedure:

USE [Promotions]
GO
/****** Object:  StoredProcedure [dbo].[insertcust]    Script Date: 05/28/2008 17:06:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[insertcust]

       @p1 varchar(80),
       @p2 varchar(80),
       @p3 varchar(80),
       @p4 varchar(80)
   
AS
BEGIN
       insert into dbo.db6l1p1 (parmnm,var1,var2,var3) values(@p1,@p2,@p3,@p4)
END