dcass
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(arrResult s(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 .Connectio n")
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("Passwor d").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
I can't get a stored procedure to work:
Parmnm=""
Var1=""
Var2=""
Var3=""
For i = 0 to 27
req=request.form(arrResult
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
SQL_Cn.Provider = "sqloledb"
SQL_Cn.Properties("Data Source").Value = "10.10.1.12, 1045"
SQL_Cn.Properties("Initial
SQL_Cn.Properties("User ID").Value = "user"
SQL_Cn.Properties("Passwor
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",
cmd.Parameters.Append cmd.CreateParameter("@p2",
cmd.Parameters.Append cmd.CreateParameter("@p3",
cmd.Parameters.Append cmd.CreateParameter("@p4",
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
command type would be adStoredProc
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?
The error message when looked up says that there is no recordset open.
Do I need that with a stored procedure?
ASKER
Can someone send me a simple vbscript example for an insert stored procedure?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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(arrResult s(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 .Connectio n")
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("Passwor d").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
Parmnm=""
Var1=""
Var2=""
Var3=""
For i = 0 to 27
req=request.form(arrResult
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
SQL_Cn.Provider = "sqloledb"
SQL_Cn.Properties("Data Source").Value = "10.10.1.12, 1045"
SQL_Cn.Properties("Initial
SQL_Cn.Properties("User ID").Value = "user"
SQL_Cn.Properties("Passwor
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",
cmd.Parameters.Append cmd.CreateParameter("@p2",
cmd.Parameters.Append cmd.CreateParameter("@p3",
cmd.Parameters.Append cmd.CreateParameter("@p4",
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