Yes, as James said, using the OleDb data provider parameter position is important, names are irrelevent and return code from a stored proc or UDF must be parameter 0 if specified. Using the SqlClient data provider, parameter position is irrelevent, but names must match. Parameter markers differ among .NET data providers also, in parameterized statements (ie in the CommandText if CommandType = CommandType.Text). OleDb, Odbc, and DataDirect data providers use question marks, SqlClient uses @-prefixed (@someparm) and the Microsoft and Oracle Corp providers for Oracle use colon-prefixed (:someparm).
Makes things interesting for users of different data providers, but it is more flexible. Hope this helps, Bob Beauchemin http://staff.develop.com/bobb -----Original Message----- From: James Geall [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 12:27 AM To: [EMAIL PROTECTED] Subject: Re: [ADVANCED-DOTNET] 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. You can read messages from the Advanced DOTNET archive, unsubscribe from Advanced DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com.
