troubleshooting Question

SQL stored procedure/vbscript

Avatar of dcass
dcassFlag for United States of America asked on
Microsoft SQL Server 2005
5 Comments1 Solution1520 ViewsLast Modified:
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


Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros