Tim, try declaring the return parameter first before adding any other parameters.
James -----Original Message----- From: tim [mailto:[EMAIL PROTECTED]] Sent: 16 August 2002 00:17 To: [EMAIL PROTECTED] Subject: [ADVANCED-DOTNET] Hi all, I am having a problem invoking a Stored Procedure via OleDbCommand - Receiving error "Too many arguments specified". I have a SQL Server 2000 stored procedure defined as follows: CREATE PROCEDURE ST_USER_INS @Name varchar(27), @System_User_Id varchar(20), @Location_Id int, @Created_By_Id int, @Id int OUTPUT, @ErrMsg varchar(100)='' OUTPUT AS IF @Location_Id <= 0 SET @Location_Id = NULL INSERT INTO T_USER ( Status_Cd, Name, System_User_Id, Location_Id, Created_By_Id, Created_Dtm ) VALUES ( 'A', @Name, @System_User_Id, @Location_Id, @Created_By_Id, getdate() ) SELECT @Id=@@IDENTITY RETURN 0 Note there are 4 input parameters, 2 output parameters, and the return value. I am using the OLE DB Provider for SQL Server to access. The reason I am not using the SQL managed provider is because the database will shortly be migrated to a Oracle, and I intend to use the Oracle OLE DB Provider by just changing the connection string (Yes, I know there is an Oracle managed provider, but I'm not currently planning on using this, as performance will not be super-critical for this app). Here is the code I invoke it with: moCmd = New OleDbCommand() moCmd.Connection = moCn moCmd.CommandText = "st_User_INS" moCmd.CommandType = CommandType.StoredProcedure '--- build the parameters moCmd.Parameters.Add("Name", OleDbType.VarChar, 27) moCmd.Parameters("Name").Value = msName moCmd.Parameters.Add("System_User_Id", OleDbType.VarChar, 20) moCmd.Parameters("System_User_Id").Value = msSystemUserId moCmd.Parameters.Add("Location_Id", OleDbType.Integer, 4) moCmd.Parameters("Location_Id").Value = mlLocationId moCmd.Parameters.Add("Created_By_Id", OleDbType.Integer, 4) moCmd.Parameters("Created_By_Id").Value = clsUtilities.CurrentUser.UserId '--- output parameters moCmd.Parameters.Add("Id", OleDbType.Integer, 4) moCmd.Parameters("Id").Direction = ParameterDirection.Output moCmd.Parameters.Add("RetCd", OleDbType.Integer, 4) moCmd.Parameters("RetCd").Direction = ParameterDirection.ReturnValue moCmd.Parameters.Add("ErrMsg", OleDbType.Char, 100) moCmd.Parameters("ErrMsg").Direction = ParameterDirection.Output moCmd.ExecuteNonQuery() moCmd.Parameters("ErrMsg").Direction = ParameterDirection.ReturnValue lRetCd = moCmd.Parameters("RetCd").Value I receive an exception indicating: System.Data.OleDb.OleDbException: Procedure or function ST_USER_INS has too many arguments specified... blah blah blah If I comment out the lines that define the "RetCd" parameter, it works fine (except of course I can't get the return value). It seems as though the Ole DB provider mistreats a ReturnValue as an Input or Output parameter ? I swear this works fine with the SQL managed provider. I searched on Microsoft, google, and the archives and didn't find any mention of this problem. Any input is appreciated. Tim Platt VisualGov Solutions, LLC Clearwater, FL You can read messages from the Advanced DOTNET archive, unsubscribe from Advanced DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com. You can read messages from the Advanced DOTNET archive, unsubscribe from Advanced DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com.
