www.webdeveloper.com
Results 1 to 6 of 6

Thread: Error while calling a Procedure inside while loop

  1. #1
    Join Date
    Nov 2004
    Location
    Bangalore, India
    Posts
    71

    Error while calling a Procedure inside while loop

    Hello everybody,

    I have to call a procedure inside while loop.

    But while doing this, it is working first time inside loop.

    from second time onwards, it displays an error as,

    "Procedure or function proc_User_Statistics has too many arguments specified"

    I had called the procedure as follows.

    ---------------------------------------------

    While objdrStatList.Read
    TotalRec = objdrStatList(0)
    CustomerCode = objdrStatList(1)
    UserId = objdrStatList(2)
    NetworkId = objdrStatList(3)
    Dim TotalCredits = objdrStatList(4)

    objUserStat.Connection = objcon1.objConnection
    objUserStat.CommandType = CommandType.StoredProcedure
    objUserStat.CommandText = "proc_User_Statistics"

    objUserStat.Parameters.Add("@StatDate", SqlDbType.VarChar).Value = strDate
    objUserStat.Parameters.Add("@Records", SqlDbType.Int).Value = TotalRec
    objUserStat.Parameters.Add("@CustomerCode", SqlDbType.VarChar).Value = CustomerCode
    objUserStat.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
    objUserStat.Parameters.Add("@NetworkId", SqlDbType.VarChar).Value = NetworkId
    objUserStat.Parameters.Add("@TotalCredits", SqlDbType.Int).Value = TotalCredits
    objUserStat.ExecuteNonQuery()
    objUserStat.Dispose()
    objcon1.CloseConnection()
    End While
    objdrStatList.Close()
    ---------------------------------------------

    Pls suggest me a solution. It is very urgent.

    Thanks
    Siva (siva@angleritech.com)

  2. #2
    Join Date
    Nov 2002
    Location
    Auburn, AL
    Posts
    9,222
    Well its just setting those same paramaters over and over it seems. It looks like you need to have a variable that increments in there somewhere.

  3. #3
    Join Date
    Sep 2004
    Location
    Northeast, FL
    Posts
    332
    Replace this

    objUserStat.Connection = objcon1.objConnection
    objUserStat.CommandType = CommandType.StoredProcedure
    objUserStat.CommandText = "proc_User_Statistics"

    with this

    objUserStat = new SQLCommand("proc_User_Statistics", Connection)
    objUserStat.CommandType = CommandType.StoredProcedure

    Basically, the same parameters are added each time it loops. Like 5,10,15,20,25,etc. So, the above re-instantiates the SQLCommand, which removes the previous parameters. Alternatively, there may be a method to clear the parameters from objUserStat.
    Last edited by Cstick; 01-09-2005 at 08:32 PM.

  4. #4
    Join Date
    Nov 2002
    Location
    Auburn, AL
    Posts
    9,222
    I am having trouble just figureing out what is being accomplished with this loop!

  5. #5
    Join Date
    Nov 2004
    Location
    Bangalore, India
    Posts
    71
    Thanks. As of now, I am doing the looping inside the procedure itself.
    Here i am just calling the procedure only one time with date as a parameter. If anyone found a good solution pls reply back.

    Thanks,
    Siva R

  6. #6
    Join Date
    Dec 2004
    Location
    Saint Petersburg, Florida
    Posts
    32
    The problem is that you are adding the parameters inside the loop. The first time of execution you have the right number of parameters, but on the second time your procedure gets double number of parameters.

    Just put the lines where you add the parameters outside of the loop:

    objUserStat.Parameters.Add("@StatDate", SqlDbType.VarChar)

    And inside the loop assign only the value of the parameters:

    objUserStat.Parameters.Item("@StatDate").Value = strDate


    Do this for all of the parameters and you will be fine.

    Good Luck!

    By the way, having the loop inside the procedure works, but it is not a very good programming practice.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles