Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2013
    Posts
    1

    Unanswered: stored proc parameter type mismatch

    When my classic asp calls stored proc, parameter types are not specified. This is fine most of time, but if this funcionality is highly used, applications gets error sometimes.

    Error:
    sql native client procedure or function [sp name] expects parameter [parameter name], which was not supplied.

    This is not a replicatable issue, but happens from time to time.
    I guess the problem is SQL server guesses parameter type every time, and there is small chance it guesses incorrectly.

    I can fix this issue by specifying parameter types, but there are hundreds of places to be fixed.

    Is there a better way to fix this? Thanks you.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Imagine you have a stored procedure that accepts 3 [non-optional] parameters.

    If your ASP code only tells the command about two of parameters then you will receive this error.

    This is often due to coding that checks for a value in ASP and only adds a parameter when a value is present.

    Air-code...
    Code:
    If x <> "" Then
      cmd.Parameters.Add("@x", x)
    End If
    What really needs to happen with all stored procedure calls is that you tell the command object of every parameter in order and then only assign values to the ones that you require.

    Air-code
    Code:
    cmd.Parameters.Add("@x")
    cmd.Parameters.Add("@y")
    cmd.Parameters.Add("@z")
    
    If x <> "" Then
      cmd.Parameters["@x"] = x
    End If
    George
    Home | Blog

Posting Permissions

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