locked
ASP.net and a sql server stored procedure... RRS feed

  • Question

  • User427149065 posted

    I have an aspx page, that is a loop back page for storing data from a web page.

    On that page, I need to call a stored procedure. Code below:

    <code>

    Dim cmdsp = Server.CreateObject("ADODB.Command")

    With cmdsp
    .ActiveConnection = conn
    .Commandtext = "PostUpdate"
    .CommandType = adCmdStoredProc
    .Parameters.Append.CreateParameter("@LookBackDays", adInteger, adParamInput, LookBackDays)
    .Parameters.Refresh
    .Execute

    End With

    Response.Write("Done")

    cmdsp = Nothing

    </code>

    Looks good, but when I try to run the page I get this error:

    <error>

    Number of parameters specified does not match the expected number.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

    Exception Details: System.Reflection.TargetParameterCountException: Number of parameters specified does not match the expected number.

    </error>

    On this line: 

    .Parameters.Append.CreateParameter("@LookBackDays", adInteger, adParamInput, LookBackDays)

    Ata loss for why this is happening, any help appreciated! Thanks!

    Julie

    Wednesday, May 16, 2018 2:26 PM

Answers

  • User753101303 posted

    It may not work but now you should have a whole new different error message ? It's important to tell us how the situation evolves as you try something.

    Based on the ADO.NET part I tried the following which works for me :

            Const strconnx = "server=.;database=xxx;integrated security=sspi"
            Dim sqlCon = New System.Data.SqlClient.SqlConnection(strconnx)
    
            Using (sqlCon)
                Dim sqlComm As New System.Data.SqlClient.SqlCommand()
                sqlComm.Connection = sqlCon
                sqlComm.CommandText = "MultiplyBy2"
                sqlComm.CommandType = System.Data.CommandType.StoredProcedure
                sqlComm.Parameters.AddWithValue("@value", 5)
    
                ' ADDED
                Dim returnValue = New SqlParameter() With {.Direction = ParameterDirection.ReturnValue}
                sqlComm.Parameters.Add(returnValue)
    
    
                sqlCon.Open()
                sqlComm.ExecuteNonQuery()
                Response.Write(returnValue.Value) ' Shows 10 as expected
            End Using
    

    Does it work ? Could tell explicitly that you are 100% sure that your LookBackDays is not "Nothing"as suggested earlier.

    You want to give a try at ADODB ? Could it be that ADODB Constants are missing. I would use Option Explicit On if not done already.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 17, 2018 9:20 PM

All replies

  • User475983607 posted

    The error is very clear.  The code is passing more parameter than expected by the stored procedure.  Can yo post the stored procedure source?

    Maybe you are running the code in a loop and did not clear the previous parameter collection?

    Wednesday, May 16, 2018 3:42 PM
  • User427149065 posted

    One param in and one return out. That's it.

    The code is inline of a page that alters data in a DB from the originating page, then returns and reloads the first page.

    The code runs once so clearing the params wouldn't be necessary.

    I even added the return param: It sticks on the same line.

    <code>

    With cmdsp
    .ActiveConnection = conn
    .Commandtext = "PostUpdate"
    .CommandType = adCmdStoredProc
    .Parameters.Append.CreateParameter("@LookBackDays", adInteger, adParamInput, 10)
    .Parameters("@LookBackDays") = LookBackDays
    .Parameters.Append.CreateParameter("@return_value", adInteger, adParamOutput, 10)
    .Parameters.Refresh
    Retcount = .Parameters("@return_value")

    .Execute

    End With

    </code>

    Wednesday, May 16, 2018 5:20 PM
  • User427149065 posted

    Does it matter that this page is run on a PC running Win10 with IIS active?

    It shouldn't.....

    Thursday, May 17, 2018 1:04 PM
  • User753101303 posted

    Hi,

    I noticed the syntax you are using is :

    For now you have .Parameters.Append.CreateParameter("@LookBackDays", adInteger, adParamInput, LookBackDays)

    If you look at the earlier link posted by https://forums.asp.net/members/mgebhard.aspx it seems you can do either :

    Set prmByRoyalty = cmdByRoyalty.CreateParameter("percentage", adInteger, adParamInput)  
    cmdByRoyalty.Parameters.Append prmByRoyalty 

    or use Append with parameters :https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/append-method-ado?view=sql-server-2017

    What if you try to use one of those other syntax ?

    Not sure if using ADODB rather than ADO.NET is intentional ? Or is this even "ASP Classic" rather than ASP.NET ;-)

    Thursday, May 17, 2018 4:37 PM
  • User427149065 posted

    Just looking for a code script that works  :)

    The page was originally an .ASP page, then I upgraded it.

    The code needs to call a procedure on a SQL Server, pass it a integer variable, and let it run. The procedure then passes back a "1" to say its finished.

    I've tried every thing I can think of and not getting very far.

    Thursday, May 17, 2018 6:06 PM
  • User753101303 posted

    Including adding the value as an additional parameter ? And then trying the other syntax I suggested? Or could it be that you pass a null parameter ? Please be explicit about each time about which suggestions you tried and if the error message stays the same or not.

    Else I'll give this a try to see if I can repro the issue.

    Thursday, May 17, 2018 6:46 PM
  • User427149065 posted

    Below is the ways I've tried to get the code to work. Even the remarked out attempts. (I'd rather remark out than delete and forget I tried that method)

    <code>

    Dim strconnx = SomeStr
    Dim sqlCon = New SqlConnection(strconnx)


    Using (sqlCon)

    Dim sqlComm As New SqlCommand()
    sqlComm.Connection = strconnx

    sqlComm.CommandText = "PostUpdate"
    sqlComm.CommandType = adCmdStoredProc

    sqlComm.Parameters.AddWithValue("@LookBackDays", LookBackDays)

     sqlCon.Open()

    sqlComm.ExecuteNonQuery()

    End Using


    'Dim CMD = CreateObject("ADODB.Command")

    'CMD.CommandType = adCmdStoredProc
    'CMD.CommandText = "PostUpdate"
    'CMD.ActiveConnection = conn

    'CMD.parameters.Item("LookBackDays").value = LookBackDays
    'CMD.Parameters.Append.CreateParameter("@LookBackDays", adInteger, adParamInput, 10, LookBackDays)
    ''CMD.Parameters.Add("LookBackDays", adInteger, 11).Value = LookBackDays
    ''CMD.Parameters("LookBackDays").Direction = adParamInput
    ''Dim return_value = CMD.Parameters.Add("return_value", adInteger, 14)
    ''return_value.Direction = adParamOutput
    'CMD.ExecuteNonQuery()

    'Dim cmdsp = CreateObject("ADODB.Command")
    'Dim Retcount
    'With cmdsp
    ' .ActiveConnection = conn
    ' .Commandtext = "PostUpdate"
    ' .CommandType = adCmdStoredProc
    ' .Parameters.Append.CreateParameter("@LookBackDays", adInteger, adParamInput, 10)
    ' .Parameters("@LookBackDays") = LookBackDays
    ' .Parameters.Append.CreateParameter("@return_value", adInteger, adParamOutput, 10)
    ' .Parameters.Refresh
    ' Retcount = .Parameters("@return_value")

    ' .Execute

    'End With

    'Response.Write("Done")

    'cmdsp = Nothing

    </code>

    Thursday, May 17, 2018 7:31 PM
  • User753101303 posted

    It may not work but now you should have a whole new different error message ? It's important to tell us how the situation evolves as you try something.

    Based on the ADO.NET part I tried the following which works for me :

            Const strconnx = "server=.;database=xxx;integrated security=sspi"
            Dim sqlCon = New System.Data.SqlClient.SqlConnection(strconnx)
    
            Using (sqlCon)
                Dim sqlComm As New System.Data.SqlClient.SqlCommand()
                sqlComm.Connection = sqlCon
                sqlComm.CommandText = "MultiplyBy2"
                sqlComm.CommandType = System.Data.CommandType.StoredProcedure
                sqlComm.Parameters.AddWithValue("@value", 5)
    
                ' ADDED
                Dim returnValue = New SqlParameter() With {.Direction = ParameterDirection.ReturnValue}
                sqlComm.Parameters.Add(returnValue)
    
    
                sqlCon.Open()
                sqlComm.ExecuteNonQuery()
                Response.Write(returnValue.Value) ' Shows 10 as expected
            End Using
    

    Does it work ? Could tell explicitly that you are 100% sure that your LookBackDays is not "Nothing"as suggested earlier.

    You want to give a try at ADODB ? Could it be that ADODB Constants are missing. I would use Option Explicit On if not done already.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 17, 2018 9:20 PM
  • User427149065 posted

    Perfect! I did make the following change

    <code>

    Dim returnValue = New SqlParameter()
    returnValue.Direction = ParameterDirection.ReturnValue

    </code>

    Many thanks!

    Friday, May 18, 2018 4:26 PM