Re: [Firebird-net-provider] StoredProc with 2 input and 1 output, raises error on ExecuteNonQuery();

[Carlos G. A. <car...@te...>]

Hello:

> I get the following exception when I step thru the dbcmd.ExecuteNonQuery();
> statement in the code shown below.  I've looked at the other posts on
> StoredProcs.  My input fields are CHAR(20), not VARCHAR like the other
> examples.


Yo have two options for solve this:

1. If you want to use the placeholder ? , you need to add parameters to 
the FbCommand object in the same order as they appears in the stored 
proc call:

	FbCommand command = new FbCommand("EXECUTE PROCEDURE 
spValidateUser(?,?)", connection, transaction);

	command.CommandType = CommandType.StoredProcedure;

	command.Parameters.Add("@USERID", FbType.Char).Direction =
				ParameterDirection.Input;
	command.Parameters[0].Size = 20;
	command.Parameters[0].Value = "SYSDBA";
			
	command.Parameters.Add("@USERPASSWORD", FbType.Char).Direction =
				ParameterDirection.Input;
	command.Parameters[1].Size = 20;
	command.Parameters[1].Value = "masterkey";
			
	command.Parameters.Add("@EMPLID", FbType.Integer).Direction =
				ParameterDirection.Output;
			
	command.ExecuteNonQuery();


2. Use named parameters instead of placeholder ?:

	FbCommand command = new FbCommand("EXECUTE PROCEDURE 
spValidateUser(@USERID,@USERPASSWORD)", connection, transaction);

	command.CommandType = CommandType.StoredProcedure;

	command.Parameters.Add("@EMPLID", FbType.Integer).Direction =
				ParameterDirection.Output;

	command.Parameters.Add("@USERID", FbType.Char).Direction =
				ParameterDirection.Input;
	command.Parameters[1].Size = 20;
	command.Parameters[1].Value = "SYSDBA";
			
	command.Parameters.Add("@USERPASSWORD", FbType.Char).Direction =
				ParameterDirection.Input;
	command.Parameters[2].Size = 20;
	command.Parameters[2].Value = "masterkey";

	command.ExecuteNonQuery();




-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

"No tengo dones especiales.Sólo soy apasionadamente curioso"
Albert Einstein, científico.