Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    124

    Unanswered: integer datatype and null value error

    I'm getting a datatype error: "Application uses a value of the wrong type for the current operation" when executing the following stored procedure:

    Code:
    CREATE PROCEDURE dbo.Insert_Temp_ContactInfo
    @sessionid varchar(50),
    @FirstName varchar(50) = NULL,
    @LastName varchar(50) = NULL,
    @SchoolName varchar(50) = NULL,
    @address varchar(50) = NULL,
    @City varchar(50) = NULL,
    @State int = NULL,
    @Zip varchar(5) = NULL,
    @Phone varchar(10) = NULL,
    @Email varchar(50) = NULL,
    @CurrentCustomer varchar(3) = NULL,
    @ImplementationType int = NULL,
    @ProductType int = NULL,
    @Comment varchar(500) = NULL
    AS
    --check if a current record exists
    SET NOCOUNT ON
    begin
     UPDATE dbo.Temp_ContactInfo
     SET 
      FirstName = @FirstName,
      LastName = @LastName,
      SchoolName = @SchoolName,
      Address = @address,
      City = @City,
      State = @State,
      Zip = @Zip,
      Phone = @Phone,
      Email = @Email,
      CurrentCustomer = @CurrentCustomer,
      ImplementationType = @ImplementationType,
      ProductType = @ProductType,
      Comment = @Comment
     WHERE 
      sessionid = @sessionid
    
    If @@Rowcount = 0
     INSERT INTO dbo.Temp_ContactInfo 
     (sessionid, 
      FirstName, 
      LastName, 
      SchoolName, 
      address, 
      City, 
      State, 
      Zip, 
      Phone, 
      Email, 
      CurrentCustomer, 
      ImplementationType, 
      ProductType, 
      Comment)
     VALUES 
     (@sessionid, 
      @FirstName, 
      @LastName, 
      @SchoolName, 
      @address, 
      @City, 
      @State, 
      @Zip, 
      @Phone, 
      @Email, 
      @CurrentCustomer, 
      @ImplementationType, 
      @ProductType, 
      @Comment)
    end
    GO
    This is code I'm using to call the procedure:

    Code:
    set InsertTempInfo = Server.CreateObject("ADODB.Command")
    With InsertTempInfo
    .ActiveConnection = MM_DBConn_STRING
    .CommandText = "dbo.Insert_Temp_ContactInfo"
    .CommandType = 4
    .CommandTimeout = 0
    .Prepared = true
    .Parameters.Append .CreateParameter("@sessionid", 200, 1,50, usrid)
    .Parameters.Append .CreateParameter("@FirstName", 200, 1,50,fname)
    .Parameters.Append .CreateParameter("@LastName", 200, 1,50,lname)
    .Parameters.Append .CreateParameter("@SchoolName", 200, 1,50,schoolname)
    .Parameters.Append .CreateParameter("@address", 200, 1,50,address)
    .Parameters.Append .CreateParameter("@City", 200, 1,50,city)
    .Parameters.Append .CreateParameter("@State", 3, 1,4,state)
    .Parameters.Append .CreateParameter("@Zip", 200, 1,5,zip)
    .Parameters.Append .CreateParameter("@Phone", 200, 1,10,phone)
    .Parameters.Append .CreateParameter("@Email", 200, 1,50,email)
    .Parameters.Append .CreateParameter("@CurrentCustomer", 200, 1,3,currentcustomer)
    .Parameters.Append .CreateParameter("@ImplementationType", 3, 1,4,implementationtype)
    .Parameters.Append .CreateParameter("@ProductType", 3, 1,4,producttype)
    .Parameters.Append .CreateParameter("@Comment", 200, 1,500,comment)
    .Execute()
    End With
    Set InsertTempInfo = Nothing
    the error is thrown on the following line:

    .Parameters.Append .CreateParameter("@State", 3, 1,4,state)


    I'm using a table to hold data that I can pass back to the original form page and re-populate the fields that were not validated correctly. The stored procedure either inserts or updates the record in the temp table I've created.

    So, currently, as I'm testing, I'm just passing empty values to all the parameters and the @state parameter is failing and throwing the error.


    I've double checked that the table has the state column set to integer datatype

    The column is set as follows:

    Name datatype length Allow Nulls
    ----------------------------------------------
    State int 4 checked


    I have tried setting the default value for every column to Null in the table and then also not using a default value. Either way, I still recieve the same error?

    Not sure what else to look at?

    It seems the problem might be that instead of a null value being passed to the parameter that it is actually empty. Can passing an empty value to a column of datatype integer cause this problem? If so, is there a way to correct it?

    Thanks for any help.

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Basic thing to check: Make sure the data type you are passing to the stored procedure is of the same type as the stored procedure is expecting
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

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