Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New to the CF scene
    Join Date
    Jun 2011
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Procedure or function expects parameter which was not

    I am calling a stored procedure to delete a record:
    Code:
     Protected Sub deleteThisOfficer(ByVal sender As Object, ByVal e As EventArgs)
            Dim sqlConn As SqlConnection = New SqlConnection(strConnString)
            sqlConn.Open()
            Dim sqlText As String = "ILETSB.PTB.uspDeleteIndividual"
            Dim sqlCmd As New SqlCommand(sqlText)
            sqlCmd.CommandType = Data.CommandType.StoredProcedure
            sqlCmd.Parameters.AddWithValue("IndId", Request.QueryString("oid"))
            sqlCmd.Parameters.AddWithValue("UserId", hfUserId.Value)
            sqlCmd.Connection = sqlConn
            sqlCmd.ExecuteNonQuery()
        End Sub
    The procedure deletes the record and executes in SQL Server Mgmt Console with no errors. But when run from a ASP.Net page I get the error:
    Procedure or function 'uspDeleteIndividual' expects parameter '@IndId', which was not supplied.

    Since the record is being deleted and the procedure places a copy of the deleted record in an audit table along with the ID of the person deleting the record, I know the parameters are being passed. Anybody know what is going on?

  • #2
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,725
    Thanks
    41
    Thanked 191 Times in 190 Posts
    i don't know what lang you are using but here is a snippet demo of a C# that calls a stored SQL procedure... you can maybe adapt to ur use... i think you are literally missing the "@" symbol... look at my parameteres and how it's called

    Code:
    SqlConnection conn = new SqlConnection(path);
               
                /* set up call for stored procedure */
                SqlCommand doit = new SqlCommand("usp_insert_tbl_monster", conn);
                doit.CommandType = CommandType.StoredProcedure;  
                // parameter settings 
                doit.Parameters.Add(new SqlParameter ("@mName", name)); 
                doit.Parameters.Add(new SqlParameter("@mType", type));
                doit.Parameters.Add(new SqlParameter("@mFriendly", friend));
                /* end call params */
    
                conn.Open();
                doit.Connection = conn;         
                doit.ExecuteNonQuery();
                conn.Close();

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #3
    New to the CF scene
    Join Date
    Jun 2011
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for responding, this is a very frustrating problem. I have wasted a few hours on it already. I am using VS2010, the page is in ASP.Net with VB. I suspect the error has to do with the parameters being passed (an int for an ID and character string for login ID). I tried using the method you are describing first, but VS told me that method was outdated and to use AddWithValue method instead. It doesn't seem like they worked out the bugs on it.

    By the way, including the "@" sign on the parameters had no effect. The SQL stored proc ran fine, moved records to archive, recorded the delete operation and deleted the records I wanted to remove. Then the browser shot up the same message about the procedure needing the parameter passed to it (that was obviously passed since it archived the data then deleted the records). And as I stated in the first post, the stored proc executes inside of SQL Server with no issues, it is only when the function is called from the web page that the error pops up.

  • #4
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,725
    Thanks
    41
    Thanked 191 Times in 190 Posts
    the method i posted is C#... u need to do the same thing but with VB ( i build on VS2010 as well woo woo ) anyways check here for MSDN code syntax's and snippets then apply accordingly. Sorry I can;t be of further help, I only know C# my boss knows VB and he made me learn C# to make sure we had all bases covered

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #5
    New Coder
    Join Date
    Jul 2011
    Location
    Berkeley, CA
    Posts
    20
    Thanks
    2
    Thanked 2 Times in 1 Post
    Yes,
    To call a stored procedure, set the CommandType of the Command object to StoredProcedure. Once the CommandType is set to StoredProcedure, you can use the Parameters collection to define parameters, as in the following example.

  • #6
    New Coder
    Join Date
    Aug 2011
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Andrew can you please give me the example as you stated here "as in the following example." ?

    Thanks in advance!

  • #7
    New Coder
    Join Date
    Aug 2011
    Posts
    11
    Thanks
    0
    Thanked 2 Times in 2 Posts
    @alykins,
    I tired to use the sample you gave on my SQL and it seems to work fine

  • #8
    New Coder
    Join Date
    Aug 2011
    Location
    New Jersey
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I wouldn't mind seeing that example also Andrew

  • #9
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Are you sure your Request.QueryString is being populated? That will be the first place to look for me, as the SP is expecting a value there.

  • #10
    New Coder
    Join Date
    Sep 2011
    Posts
    21
    Thanks
    1
    Thanked 3 Times in 3 Posts
    Is issue solved? Since according to my analysis I too doubt your Request.QueryString is not getting populated. Further it may be silly on my part to ask but is the parameter hfUserId.Value has been initiated.

  • #11
    New Coder
    Join Date
    Aug 2011
    Posts
    26
    Thanks
    2
    Thanked 1 Time in 1 Post
    I would check my application code and see what value you are setting @IndId to. I suspect it is null and there in lies the problem.
    “Computers are useless. They can only give you answers.”
    (Pablo Picasso) Where To Find Coupons and Video Testimonials

  • #12
    New Coder
    Join Date
    Sep 2011
    Posts
    21
    Thanks
    1
    Thanked 3 Times in 3 Posts
    I've checked the scenario, the issue is not for setting the values null atleast. So you can leave that scenario and look further

  • #13
    New to the CF scene
    Join Date
    Sep 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I checked my guide and found where I do mention that the scripts to load the Adventureworks database will launch the "SQL Server Management Console". Again, this is just referring to SSMS (but perhaps I should change that to avoid any confusion).

    SSMS is the main component of SQL Server 2005...so you're saying SSMS isn't installed on your machine after installing SQL Server? Try going to Start | All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio.

    Last edited by arlinetis; 10-04-2011 at 10:10 AM.

  • #14
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,725
    Thanks
    41
    Thanked 191 Times in 190 Posts
    The OP has not come back and may not come back... Let's stop digging this thread up asking him/her if the issue is still outstanding since there will not likely be any replies

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #15
    New Coder
    Join Date
    Nov 2011
    Posts
    16
    Thanks
    0
    Thanked 1 Time in 1 Post
    Did you double check that you are getting parameter from Request.QueryString("oid") ?


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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