Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Location
    Oxford, England
    Posts
    38

    Unanswered: Stored procedures and some data type problem

    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

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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:
    Code:
    INSERT into PressReleases
    (pressreleasedescription,pressreleasedate,username,lastupdated,filesize, publish)
    Values 
    (@pressreleaseid,@pressreleasedescription,@pressreleasedate,@username,@lastupdated,@filesize,@publish)
    
    return @@IDENTITY
    Last edited by Seppuku; 05-12-04 at 12:39.
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Dec 2003
    Location
    Oxford, England
    Posts
    38
    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

  4. #4
    Join Date
    Dec 2003
    Posts
    454
    You need to change the following code

    cmdAddpr.CommandType = 4

    into

    cmdAddpr.CommandType = &H0004

  5. #5
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Quote Originally Posted by Lukelrc
    Dim cmdAddPR__pressreleasedescription
    cmdAddPR__pressreleasedescription = Response.Write(Request.Form("txtDescription"))
    if(Request("pressreleasedescription") <> "") then cmdAddPR__pressreleasedescription = Request("pressreleasedescription")
    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")
    That which does not kill me postpones the inevitable.

  6. #6
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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
    That which does not kill me postpones the inevitable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •