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 (<!-- e --><a href="mailto:[email protected]">[email protected]</a><!-- e -->)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.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.I am having trouble just figureing out what is being accomplished with this loop!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 RThe 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.
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 (<!-- e --><a href="mailto:[email protected]">[email protected]</a><!-- e -->)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.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.I am having trouble just figureing out what is being accomplished with this loop!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 RThe 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.