Hello,

I need to add records to a Sql Sever database using a Stored Procedure.

I have code (actually generated by dreamweaver) which looks fine, however when i open the page in my browser i get the following error:

ADODB.Command (0x800A0D5D)
Application uses a value of the wrong type for the current operation.

I have checked the data types and they seem to be right, i.e 200 = varchar etc. Ive really struggling to find a solution to this error, can anyone help?

Heres my code:

<%

set cmdAddpr = Server.CreateObject("ADODB.Command")
cmdAddpr.ActiveConnection = MM_Intranet_STRING
cmdAddpr.CommandText = "dbo.AddPressRelease"
cmdAddpr.Parameters.Append cmdAddpr.CreateParameter("@RETURN_VALUE", 3, 4)
cmdAddpr.Parameters.Append cmdAddpr.CreateParameter("@pressreleasedescription ", 200, 1,200,cmdAddpr__pressreleasedescription)
cmdAddpr.Parameters.Append cmdAddpr.CreateParameter("@pressreleasedate", 135, 1,25,cmdAddpr__pressreleasedate)
cmdAddpr.Parameters.Append cmdAddpr.CreateParameter("@username", 200, 1,50,cmdAddpr__username)
cmdAddpr.Parameters.Append cmdAddpr.CreateParameter("@lastupdated", 135, 1,25,cmdAddpr__lastupdated)
cmdAddpr.Parameters.Append cmdAddpr.CreateParameter("@filesize", 131, 1,20,cmdAddpr__filesize)
cmdAddpr.Parameters.Append cmdAddpr.CreateParameter("@publish", 901, 1,1,cmdAddpr__publish)
cmdAddpr.CommandType = 4
cmdAddpr.CommandTimeout = 0
cmdAddpr.Prepared = true
cmdAddpr.Execute()

%>

And my stored Procedure:

CREATE Procedure dbo.AddPressRelease

@pressreleasedescription varchar(250),
@pressreleasedate datetime,
@username varchar(50),
@lastupdated datetime,
@filesize numeric,
@publish bit
As


Declare @pressreleaseid int

Select @pressreleaseid = nextpressreleaseid from nextids

Insert into PressReleases(pressreleaseID,pressreleasedescripti on,pressreleasedate,username,lastupdated,filesize, publish)
Values (@pressreleaseid,@pressreleasedescription,@pressre leasedate,@username,@lastupdated,@filesize,@publis h)

Update nextids set nextpressreleaseid = nextpressreleaseid + 1



return @pressreleaseid
GO

Thanks

Luke

Can you show us example values for the cmdAddpr variables? Just do a Response.Write for them. Also, make sure none of those fields are empty if you don't allow NULLs in any of the columns.

This is off of your question, but I noticed in your SP that you have a table just for tracking the next ID. There's an easier way. Change your pressreleaseID column in PressReleases to an Identity column with an Identity Seed of 1. This will automatically generate an ID incremented from the previous one. Then you can use the "@@IDENTITY" keyword to retrieve that new value. Ex:

I didn't actually write the SP, my boss did. But i think the NextId table is used so that the NextID can be added to some document names somewhere down the line.

Anyway, thanks for the reply, I've managed to get the code to add rows to the database now by making all the fields varchar. I'm gonna worry about that later.

My cmdAddpr variables are taken from form objects. The problem i'm having now is to find the right syntax to make the SP variables equal the contents of the form objects

Heres an example of the code to do that for PressReleaseDescription:

Dim cmdAddPR__pressreleasedescription
cmdAddPR__pressreleasedescription = Response.Write(Request.Form("txtDescription"))
if(Request("pressreleasedescription") <> "") then cmdAddPR__pressreleasedescription = Request("pressreleasedescription")

Its the bit *Response.Write(Request.Form("txtDescription"))* that i'm struggling with. I just get an error saying that compiler was expecting an identifier. Its probably something flat-out obvious.

any ideas,

Thanks again

Luke

You need to change the following code

cmdAddpr.CommandType = 4

into

cmdAddpr.CommandType = &H0004

You cannot use Response.Write when assigning a value to a variable. And you can't wrap it in parens (it's a method, not a function).

Response.Write Request.Form("txtDescription")
or
cmdAddPR__pressreleasedescription = Request.Form("txtDescription")

As gyuan also mentioned, here are some contants to use instead of hardcoding the values:

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

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

'---- CursorTypeEnum Values ----
adOpenForwardOnly = 0
adOpenKeyset = 1
adOpenDynamic = 2
adOpenStatic = 3

'---- LockTypeEnum Values ----
adLockReadOnly = 1
adLockPessimistic = 2
adLockOptimistic = 3
adLockBatchOptimistic = 4

'---- DataTypeEnum Values ----
adEmpty = 0
adTinyInt = 16
adSmallInt = 2
adInteger = 3
adBigInt = 20
adUnsignedTinyInt = 17
adUnsignedSmallInt = 18
adUnsignedInt = 19
adUnsignedBigInt = 21
adSingle = 4
adDouble = 5
adCurrency = 6
adDecimal = 14
adNumeric = 131
adBoolean = 11
adError = 10
adUserDefined = 132
adVariant = 12
adIDispatch = 9
adIUnknown = 13
adGUID = 72
adDate = 7
adDBDate = 133
adDBTime = 134
adDBTimeStamp = 135
adBSTR = 8
adChar = 129
adVarChar = 200
adLongVarChar = 201
adWChar = 130
adVarWChar = 202
adLongVarWChar = 203
adBinary = 128
adVarBinary = 204
adLongVarBinary = 205