Link to home
Start Free TrialLog in
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


Avatar of Robb Hill
Robb Hill
Flag of United States of America image

command type would be adStoredProc
Avatar of dcass

ASKER

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?
Avatar of dcass

ASKER

Can someone send me a simple vbscript example for an insert stored procedure?
ASKER CERTIFIED SOLUTION
Avatar of jgv
jgv

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dcass

ASKER

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