firebird-net-provider

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

[Jim M. <jim...@co...>]

C#, ASP.NET, Firebird 1.02

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.

Exception Details: System.ArgumentNullException: String reference not set to
an instance of a String. Parameter name: s

Here is my code, everything works down to the dbcmd.ExecuteNonQuery();   I
have been running selects with no problem.

FbConnection dbcon = new
FbConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
dbcon.Open();
FbTransaction dbtran = dbcon.BeginTransaction();
FbCommand dbcmd = new FbCommand("EXECUTE PROCEDURE spValidateUser(?,?)",
dbcon, dbtran);
dbcmd.CommandType = CommandType.StoredProcedure;
dbcmd.Parameters.Add("@EMPLID", FbType.Integer).Direction =
ParameterDirection.Output;
dbcmd.Parameters.Add("@USERID", FbType.Char).Direction =
ParameterDirection.Input;
dbcmd.Parameters[1].Size = 20;
dbcmd.Parameters[1].Value = userPassword.ToString();
dbcmd.Parameters.Add("@USERPASSWORD", FbType.Char).Direction =
ParameterDirection.Input;
dbcmd.Parameters[2].Size = 20;
dbcmd.Parameters[2].Value = userName.ToString();
dbcmd.ExecuteNonQuery();
Here is the SP:
PROCEDURE  SPVALIDATEUSER( USERID                           CHAR( 20 )
                               , USERPASSWORD                     CHAR(
20 ) )
RETURNS ( EMPLID                           INTEGER )
AS
BEGIN
   SELECT EMPLID
   FROM EMPLOYEE
   WHERE Upper(USERID) = Upper(:userID)
     AND Upper(PASSWD) = Upper(:userPassword)
     AND EMPLSTATUS = 'Active'
   INTO :emplID;
 SUSPEND;
END

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

[Jim M. <jim...@co...>]

I wanted to add that if I run the SP as a select it works:
select * from spValidateUser('" + userName.ToUpper() + "','" +
userPassword.ToUpper() + "')

""Jim Maguire"" <jim...@co...> wrote in message
news:b6derq$vfn$1...@ne......
> C#, ASP.NET, Firebird 1.02
>
> 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.
>
> Exception Details: System.ArgumentNullException: String reference not set
to
> an instance of a String. Parameter name: s
>
> Here is my code, everything works down to the dbcmd.ExecuteNonQuery();   I
> have been running selects with no problem.
>
> FbConnection dbcon = new
> FbConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
> dbcon.Open();
> FbTransaction dbtran = dbcon.BeginTransaction();
> FbCommand dbcmd = new FbCommand("EXECUTE PROCEDURE spValidateUser(?,?)",
> dbcon, dbtran);
> dbcmd.CommandType = CommandType.StoredProcedure;
> dbcmd.Parameters.Add("@EMPLID", FbType.Integer).Direction =
> ParameterDirection.Output;
> dbcmd.Parameters.Add("@USERID", FbType.Char).Direction =
> ParameterDirection.Input;
> dbcmd.Parameters[1].Size = 20;
> dbcmd.Parameters[1].Value = userPassword.ToString();
> dbcmd.Parameters.Add("@USERPASSWORD", FbType.Char).Direction =
> ParameterDirection.Input;
> dbcmd.Parameters[2].Size = 20;
> dbcmd.Parameters[2].Value = userName.ToString();
> dbcmd.ExecuteNonQuery();
> Here is the SP:
> PROCEDURE  SPVALIDATEUSER( USERID                           CHAR( 20 )
>                                , USERPASSWORD                     CHAR(
> 20 ) )
> RETURNS ( EMPLID                           INTEGER )
> AS
> BEGIN
>    SELECT EMPLID
>    FROM EMPLOYEE
>    WHERE Upper(USERID) = Upper(:userID)
>      AND Upper(PASSWD) = Upper(:userPassword)
>      AND EMPLSTATUS = 'Active'
>    INTO :emplID;
>  SUSPEND;
> END
>
>
>
>
> -------------------------------------------------------
> This SF.net email is sponsored by: ValueWeb:
> Dedicated Hosting for just $79/mo with 500 GB of bandwidth!
> No other company gives more support or power for your dedicated server
> http://click.atdmt.com/AFF/go/sdnxxaff00300020aff/direct/01/
> _______________________________________________
> Firebird-net-provider mailing list
> Fir...@li...
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
>

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.

Re: [Firebird-net-provider] StoredProc with 2 input and 1 output,

[Jim M. <jim...@co...>]

Thanks Carlos!  The key change was the line:

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

I'm obviously new to .NET, but am impressed by it and how well your new
component works.  Thanks!

"Carlos Guzman Alvarez" <car...@te...> wrote in message
news:3E8...@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.
>
>
>
> -------------------------------------------------------
> This SF.net email is sponsored by: ValueWeb:
> Dedicated Hosting for just $79/mo with 500 GB of bandwidth!
> No other company gives more support or power for your dedicated server
> http://click.atdmt.com/AFF/go/sdnxxaff00300020aff/direct/01/
> _______________________________________________
> Firebird-net-provider mailing list
> Fir...@li...
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
>