classic ASP parameterized insert query

[munna_001]

i am writing parameterized insert query uisng asp
but get the following error
--------------//error
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.line 34
--------------------
i have table in MSSQL column name (item_name nvarchar 20)
asp code
----------
itemname=request.form("itemname")
cm.commandtext="insert into test.dbo.item(itemid, itemname) values(231,@item_name)"
Set newParameter = cm.CreateParameter("@item_name", nVarChar, adParamInput,20,itemname)//line 34 where it
shows error
cm.Parameters.Append newParameter
--------------
any help will be really appreciated
thank you

[WebspoonUK]

i am writing parameterized insert query uisng asp
but get the following error
--------------//error
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.line 34
--------------------
i have table in MSSQL column name (item_name nvarchar 20)
asp code
----------
itemname=request.form("itemname")
cm.commandtext="insert into test.dbo.item(itemid, itemname) values(231,@item_name)"
Set newParameter = cm.CreateParameter("@item_name", nVarChar, adParamInput,20,itemname)//line 34 where it
shows error
cm.Parameters.Append newParameter
--------------
any help will be really appreciated
thank you

Have defined the constants: adParamInput and nVarChar?

You don't need to define parameters if you are doing the insert via command text, only if it's a stored procedure. See the code below for both methods of doing this (inline and stored proc): -

'PUT THE CONSTANTS BELOW IN AN INCLUDE FILE CALLED ADOVBS.inc and reference this file from your asp page using the #include directive.
'---- DataTypeEnum Values ----
Const adEmpty = 0
Const adTinyInt = 16
Const adSmallInt = 2
Const adInteger = 3
Const adBigInt = 20
Const adUnsignedTinyInt = 17
Const adUnsignedSmallInt = 18
Const adUnsignedInt = 19
Const adUnsignedBigInt = 21
Const adSingle = 4
Const adDouble = 5
Const adCurrency = 6
Const adDecimal = 14
Const adNumeric = 131
Const adBoolean = 11
Const adError = 10
Const adUserDefined = 132
Const adVariant = 12
Const adIDispatch = 9
Const adIUnknown = 13
Const adGUID = 72
Const adDate = 7
Const adDBDate = 133
Const adDBTime = 134
Const adDBTimeStamp = 135
Const adBSTR = 8
Const adChar = 129
Const adVarChar = 200
Const adLongVarChar = 201
Const adWChar = 130
Const adVarWChar = 202
Const adLongVarWChar = 203
Const adBinary = 128
Const adVarBinary = 204
Const adLongVarBinary = 205

'---- ParameterDirectionEnum Values ----
Const adParamUnknown = &H0000
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adParamInputOutput = &H0003
Const adParamReturnValue = &H0004

'---- CommandTypeEnum Values ----
Const adCmdUnknown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoredProc = &H0004

'Method 1...
itemname=request.form("itemname")
itemid=231

set cn = server.CreateObject("ADODB.Command")
cn.connectionString = Connection
cn.open

set cmd = server.CreateObject("ADODB.Command")
with cmd
.ActiveConnection = cn
.CommandText = "spAddTestItem"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@item_id, adInteger, adParamInput, 4, itemid)
.Parameters.Append .CreateParameter("@item_name, adVarchar, adParamInput, 20, itemname)
.Execute
end with

'Method 2...
function makeSQL(sStr)
sStr = replace(sStr, "'", "''")
makeSQL = sStr
end function

itemname=request.form("itemname")
itemid=231

set cn = server.CreateObject("ADODB.Command")
cn.connectionString = Connection
cn.open

set cmd = server.CreateObject("ADODB.Command")
with cmd
.ActiveConnection = cn
.CommandText = INSERT INTO test.dbo.item(itemid, itemname) values (" & itemid & ", " & makeSQL(itemname) & ")"
.CommandType = adCmdText
.Execute
end with

[munna_001]

thank your for your reply but still shows the same error

ADODB.Command (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.//line 33
ms sql column details
table name - item_detail
column
item_id int
item_name Nvarchar(20)
i created a stored procedure

create procedure addItem
@item_id int,
@item_name nvarchar (20)

As
insert into test.dbo.item_detail (item_id, item_name) values(@item_id,@item_name)
GO

in classic ASP i called the procedure but shows the above error
ASP code

itemID=Request.form("itemID")
itemName=Request.form("itemName")
--connection code--
with cm
.activeconnection=connectsql
.commandtext="addItem"
.commandType=adCmdStoredProc //line 33 the error line
.parameters.append.createparameter("@item_id,adInteger,adparameterinput,4,nagentid")
.parameters.append.createparameter("@item_name,adNvarchar,adparameterinput,20,oagentid")

.execute
end with

connectsql.close
set connectsql=nothing

e.g if user input 2 values i.e---- item _id=221
item_name=samsung

instead of inserting it shows above error

please help me to sort this error, can't find the bug.

[WebspoonUK]

thank your for your reply but still shows the same error

ADODB.Command (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.//line 33
ms sql column details
table name - item_detail
column
item_id int
item_name Nvarchar(20)
i created a stored procedure

create procedure addItem
@item_id int,
@item_name nvarchar (20)

As
insert into test.dbo.item_detail (item_id, item_name) values(@item_id,@item_name)
GO

in classic ASP i called the procedure but shows the above error
ASP code

itemID=Request.form("itemID")
itemName=Request.form("itemName")
--connection code--
with cm
.activeconnection=connectsql
.commandtext="addItem"
.commandType=adCmdStoredProc //line 33 the error line
.parameters.append.createparameter("@item_id,adInteger,adparameterinput,4,nagentid")
.parameters.append.createparameter("@item_name,adNvarchar,adparameterinput,20,oagentid")

.execute
end with

connectsql.close
set connectsql=nothing

e.g if user input 2 values i.e---- item _id=221
item_name=samsung

instead of inserting it shows above error

please help me to sort this error, can't find the bug.

It sounds like you haven't included the ADOVBS.inc file.

You can download it here: http://www.4guysfromrolla.com/webtech/code/adovbs.txt

Once downloaded, rename it to ADOVBS.inc and then add the following line to the top of your ASP: -

<!-- #include file="[PATH]/adobvs.inc" -->

[munna_001]

hi thank you for your quick reply, i got the file, copy and paste without any change
but now get the following error :(
Microsoft VBScript runtime (0x800A01C2)
Wrong number of arguments or invalid property assignment: 'parameters.append' line 27

dim itemID,itemName


itemID=Request.form("itemID")
itemName=Request.form("itemName")

set connectsql=server.createobject("ADODB.connection")
connectionstring="mssql connection"
connectsql.open connectionstring

set cm=server.createobject("ADODB.command")


with cm
.activeconnection=connectsql
.commandtext="add_Item"
.commandType=adCmdStoredProc
.parameters.append.createparameter("@item_id,adInteger,adParamInput,4,itemID")  //line 27
.parameters.append.createparameter("@item_name,adNvarchar,adParamInput,20,itemName")

.execute
end with

connectsql.close
set connectsql=nothing

thanks for helping

[munna_001]

at last found the error , the code should be

dim itemID,itemName


itemID=Request.form("itemID")

itemName=Request.form("itemName")
with cm
.activeconnection=connectsql
.commandtext="add_Item"
.commandType=adCmdStoredProc

.parameters.append cm.createparameter("@item_id",adInteger,adParamInput,4,itemID)
.parameters.append cm.createparameter ("@item_name",advarchar,adParamInput,20,itemName)


.execute
end with

hope someone might find it useful

[BandiRanjith]

make sure that storproc parameters and parametirized params are same(if your using Oracle 10g)