firebird-net-provider — All about development of the MS .Net provider

[Firebird-net-provider] OT: Microsoft .NET Framework 1.1 released

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

Hello:

For download the Redist see:

http://www.microsoft.com/downloads/details.aspx?FamilyId=4C1B6B0B-439E-4DB4-849A-B72416F40300&displaylang=en

For download the SDK see:

http://www.microsoft.com/downloads/details.aspx?FamilyId=9B3A2CA6-3647-4070-9F41-A333C6B9181D&displaylang=en




Best regards
Carlos Guzman Alvarez
Vigo-Spain

RE: [Firebird-net-provider] Re: How to CREATE DATABASE using .Net FB Provider

[Mike S. <mi...@mi...>]

Hi Carlos

The problem is that you cannot send a statement to create a trigger or other
stored procedure because the separator used by the stored proc code is a
semi-colon. Interbase has for years allowed you to change the separator with
the SET TERM statement. If you can't do that, then you can't create or edit
stored procs.

I'm using the latest sources checked out this afternoon using CVS, including
the static CreateDatabase() method & rebuilt with NAnt.

Cheers,

Mike.

-----Original Message-----
From: Carlos Guzman Alvarez [mailto:car...@te...]
Sent: 09 April 2003 18:23
To: Mike Scott; Firebird .Net Provider
Subject: Re: [Firebird-net-provider] Re: How to CREATE DATABASE using
.Net FB Provider


Hello:

> When I try using the code you sent without change, I get the following
> exception on reader.Close():
>
> FbException: Dynamic SQL Error
> SQL error code = -501
> Attempt to reclose a closed cursor
>
> Also none of the domains get created.

It's working well for me, Are you testing it with CVS sources ??, i have
made today some changes to batch statements.


> Can you get the following to work:
>
> SET TERM ^ ;
> SET TERM ; ^
>
> If you can get SET TERM to work, we can execute each command individually.
>
> Currently, we have worked around the problem by writing a DLL that uses a
> Delphi library (IBObjects) to run SQL scripts and calling that from C#.Net
> using P/Invoke :-(
>
> It's far from ideal - I could send you a sample script including creating
a
> domain, a table and a trigger (including SET TERM) and if you can get that
> to run, then ANY script should work. Until then, only standard SQL stuff
> seems to work and that means we can't run a proper DB creation script :-(


No, but i think you will get on problems with this, because batch
updates needs to use ; as separator.



Please if you can send .net provider questions to the developement list:

	fir...@li...




--
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] Re: How to CREATE DATABASE using .Net FB Provider

[<car...@te...>]

Hello:

> The problem is that you cannot send a statement to create a trigger or other
> stored procedure because the separator used by the stored proc code is a
> semi-colon. Interbase has for years allowed you to change the separator with
> the SET TERM statement. If you can't do that, then you can't create or edit
> stored procs.
> 
> I'm using the latest sources checked out this afternoon using CVS, including
> the CreateDatabase() method & rebuilt with NAnt.


I have made more changes, now you can for example alter a stored proc 
using ExecuteNonQuery, an example that is working for me:

FbConnection connection = new FbConnection(connectionString);

connection.Open();

FbTransaction transaction = connection.BeginTransaction();

StringBuilder commandText = new StringBuilder();

commandText.Append(
	"ALTER PROCEDURE SELECT_DATA " 	+
	" RETURNS ( "			+
	" INT_FIELD INTEGER, "		+
	" VARCHAR_FIELD VARCHAR(100), "	+
	" DECIMAL_FIELD DECIMAL(15,2)) " +
	"	AS " +
	" begin " +
		" SELECT INT_FIELD, VARCHAR_FIELD, DECIMAL_FIELD FROM TEST_TABLE_01 " +
		" WHERE INT_FIELD = 2 INTO :INT_FIELD, :VARCHAR_FIELD, :DECIMAL_FIELD; " +
		" suspend; "+
	" end ");

FbCommand command = new FbCommand(commandText.ToString(),
						connection, transaction);

command.ExecuteNonQuery();

transaction.Commit();
connection.Close();









-- 
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] problem with column alias

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

Hello:

> I have a query like this:
>  
> SELECT col1 AS column FROM table
>  
> in the provider the column name is "col1" and not "column" as the alias 
> I have written. Is this my problem, an error in the provider or the 
> column alias is not supported?


It's not a provider problem, column ( reserved word ) is not a valid 
column alias try with a different one.


-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

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

[Firebird-net-provider] problem with column alias

[stefano m. <ste...@fa...>]

Hi all

I have a query like this:

SELECT col1 AS column FROM table

in the provider the column name is "col1" and not "column" as the alias =
I have written. Is this my problem, an error in the provider or the =
column alias is not supported?

RE: [Firebird-net-provider] Re: How to CREATE DATABASE using .Net FB Provider

[Mike S. <mi...@mi...>]

The problem is that you cannot send a statement to create a trigger or other
stored procedure because the separator used by the stored proc code is a
semi-colon. Interbase has for years allowed you to change the separator with
the SET TERM statement. If you can't do that, then you can't create or edit
stored procs.

I'm using the latest sources checked out this afternoon using CVS, including
the CreateDatabase() method & rebuilt with NAnt.

Cheers,

Mike.

-----Original Message-----
From: Carlos Guzman Alvarez [mailto:car...@te...]
Sent: 09 April 2003 18:23
To: Mike Scott; Firebird .Net Provider
Subject: Re: [Firebird-net-provider] Re: How to CREATE DATABASE using
.Net FB Provider


Hello:

> When I try using the code you sent without change, I get the following
> exception on reader.Close():
>
> FbException: Dynamic SQL Error
> SQL error code = -501
> Attempt to reclose a closed cursor
>
> Also none of the domains get created.

It's working well for me, Are you testing it with CVS sources ??, i have
made today some changes to batch statements.


> Can you get the following to work:
>
> SET TERM ^ ;
> SET TERM ; ^
>
> If you can get SET TERM to work, we can execute each command individually.
>
> Currently, we have worked around the problem by writing a DLL that uses a
> Delphi library (IBObjects) to run SQL scripts and calling that from C#.Net
> using P/Invoke :-(
>
> It's far from ideal - I could send you a sample script including creating
a
> domain, a table and a trigger (including SET TERM) and if you can get that
> to run, then ANY script should work. Until then, only standard SQL stuff
> seems to work and that means we can't run a proper DB creation script :-(


No, but i think you will get on problems with this, because batch
updates needs to use ; as separator.



Please if you can send .net provider questions to the developement list:

	fir...@li...




--
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] Re: How to CREATE DATABASE using .Net FB Provider

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

Hello:

>> That's a pity, as GDS access is generally available and if someone 
>> wants to
>> do it with .Net, there will be a second alternate (and incompatible)
>> solution to yours.
>
>
>
> Probably you are right, i think this is not needed in ADO .NET, i can 
> be wrong but it's my point of view :).


A last question, in each case if i decide to make this it will be made 
after v1.0 release.





Best regards
Carlos Guzman Alvarez
Vigo-Spain

Re: [Firebird-net-provider] Re: How to CREATE DATABASE using .Net FB Provider

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

HEllo:

> That's a pity, as GDS access is generally available and if someone wants to
> do it with .Net, there will be a second alternate (and incompatible)
> solution to yours.


Probably you are right, i think this is not needed in ADO .NET, i can be 
wrong but it's my point of view :).





-- 
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] Re: How to CREATE DATABASE using .Net FB Provider

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

Hello:

>>>But are you executing it using FbCommand.ExecuteReader and
> 
> FbDataReader.NextResult() as in the example of my prior email ?? <<
> 
> We're using ExecuteNonQuery as there's no data returned, e.g. create tables
> and domains.
> 
> Should we use ExecuteReader?!

Yes.


-- 
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] Re: How to CREATE DATABASE using .Net FB Provider

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

Hello:

> When I try using the code you sent without change, I get the following
> exception on reader.Close():
> 
> FbException: Dynamic SQL Error
> SQL error code = -501
> Attempt to reclose a closed cursor
> 
> Also none of the domains get created.

It's working well for me, Are you testing it with CVS sources ??, i have 
made today some changes to batch statements.


> Can you get the following to work:
> 
> SET TERM ^ ;
> SET TERM ; ^
> 
> If you can get SET TERM to work, we can execute each command individually.
> 
> Currently, we have worked around the problem by writing a DLL that uses a
> Delphi library (IBObjects) to run SQL scripts and calling that from C#.Net
> using P/Invoke :-(
> 
> It's far from ideal - I could send you a sample script including creating a
> domain, a table and a trigger (including SET TERM) and if you can get that
> to run, then ANY script should work. Until then, only standard SQL stuff
> seems to work and that means we can't run a proper DB creation script :-(


No, but i think you will get on problems with this, because batch 
updates needs to use ; as separator.



Please if you can send .net provider questions to the developement list:

	fir...@li...
	



-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] Re: How to CREATE DATABASE using .Net FB Provider

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

Hello:

> But we're having problems - sending a batch of commands separated by semi
> colons doesn't work - only the first command gets executed.

But are you executing it using FbCommand.ExecuteReader and 
FbDataReader.NextResult() as in the example of my prior email ??




-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

Re: [Firebird-net-provider] Re: How to CREATE DATABASE using .Net FB Provider

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

Hello:

> One more suggestion: your GDS stuff is declared internal. If you made it
> public, it would be possible for people to use it to do database stuff that
> the higher level classes don't allow.


Well, i don't like this, i want to have as public only the standard set 
of classes of ADO.NET.





-- 
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] Re: How to CREATE DATABASE using .Net FB Provider

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

Hello:

> Can we not execute a whole script that has many statements, e.g.
> 
> CREATE DOMAIN .... ;
> CREATE DOMAIN .... ;
> CREATE TABLE .... ;

You can do anything like this:


FbConnection connection = new FbConnection(connectionString);

connection.Open();

FbTransaction transaction = connection.BeginTransaction();

StringBuilder batchCommand = new StringBuilder();

batchCommand.Append("CREATE DOMAIN LOGICO AS SMALLINT DEFAULT 0;");
batchCommand.Append("CREATE DOMAIN NIF AS CHAR(10) DEFAULT '';");
batchCommand.Append("CREATE DOMAIN ID AS INTEGER DEFAULT 0");

FbCommand command = new FbCommand(batchCommand.ToString(),
			connection, transaction);

FbDataReader reader = command.ExecuteReader();

while ( reader.NextResult() )
{
}

reader.Close();

transaction.Commit();
connection.Close();





-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

[Firebird-net-provider] Re: How to CREATE DATABASE using .Net FB Provider

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

Hello:

> So surely it doesn't matter whether ADO.NET supports creating databases -
> all you need to do is allow your FbConnection to execute a script without an
> open connection and transaction. Maybe you have to have a flag to say that
> this is what you want to do, so a user can set this flag when they want to
> execute SQL containing CREATE DATABASE?


FbConnection class in CVS have now a CreateDatabase static method.



-- 
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] Re: How to CREATE DATABASE using .Net FB Provider

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

Hello:

> At this moment you can't do that, there are nothing in ADO.NET 
> interfaces for creating databases :( , i'm thinking in add an static 
> method to FbConnection for allow this after v1.0 release.


Finally i have added it to the CVS yet ( because this doesn't affect to 
actual code ).





-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

[Firebird-net-provider] Re: How to CREATE DATABASE using .Net FB Provider

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

Hello:

> First of all, thank you for creating the ADO.NET provider for Firebird -
> very much appreciated!
> 
> We've run into a problem in that we cannot find a way to create a database
> using the provider. Other tools that we've used (e.g. IBObjects for Delphi)
> seem to leave the connection closed, and then you just execute a script with
> a CREATE DATABASE command.
> 
> If we leave the connection closed, the FbCommand complains that it needs an
> open connection. This seems to be a "catch-22" - how can we have an open
> connection to a database that doesn't exist yet! :-)
> 
> Can you create a database using your provider?
> 
> If not, can you point us to any docs on how we can write code to do this?

At this moment you can't do that, there are nothing in ADO.NET 
interfaces for creating databases :( , i'm thinking in add an static 
method to FbConnection for allow this after v1.0 release.


-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

[Firebird-net-provider] OT: ISO Certifies Microsoft's C# Language

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

HEllo:


http://www.informationweek.com/news/IWK20030403S0005





-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

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
>

Re: [Firebird-net-provider] error Message "Command Connection is

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

Hello:

> But now, when I try to change the value of the primary key ,
> i get the error message
> "Concurrency violation: the UpdateCommand affected 0 records."

Fixed in CVS. Thanks very much.



-- 
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, 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] error Message "Command Connection is

[George L. <ge...@ra...>]

thank you, Carlos,
actually, the
custCB.RefreshSchema();
was the issue.

But now, when I try to change the value of the primary key ,
i get the error message
"Concurrency violation: the UpdateCommand affected 0 records."

Am I missing a statement for the command builder?
Or should I not use it (the command builder) ?


"Carlos Guzman Alvarez" <car...@te...> wrote in message
news:3E8...@te......
> Hello:
>
> > Actually, now I can update ONCE the data in my database. The second time
I
> > try to
> > DataAdapter.Update
> > I get the same error message.
>
> Because you are making a Commit after update this destroys the
> transaction context you need to make anything like this ( i have no
> tested it ):
>
> private void button_Update_Click(object sender, System.EventArgs e)
> {
> try
> {
> TestDA.Update(dataSetAdmin.Tables["Judete"]);
> txn = conn.BeginTransaction();
> TestDA.SelectCommand.Transaction=txn;
> custCB.RefreshSchema();
> }
> ...
> }
>
>
>
>
>
> --
> 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
>

Re: [Firebird-net-provider] error Message "Command Connection is

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

Hello:

> Actually, now I can update ONCE the data in my database. The second time I
> try to
> DataAdapter.Update
> I get the same error message.

Because you are making a Commit after update this destroys the 
transaction context you need to make anything like this ( i have no 
tested it ):

private void button_Update_Click(object sender, System.EventArgs e)
{
	try
	{
		TestDA.Update(dataSetAdmin.Tables["Judete"]);
		txn = conn.BeginTransaction();
		TestDA.SelectCommand.Transaction=txn;
		custCB.RefreshSchema();
	}
	...
}





-- 
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] error Message "Command Connection is

[George L. <ge...@ra...>]

Pavol, thank you.

Actually, now I can update ONCE the data in my database. The second time I
try to
DataAdapter.Update
I get the same error message.

If I close the application  and start it again, it works just fine.


this is a part of code:

  {
   conn.ConnectionString=myConnectionString;
   TestDA.SelectCommand = new FbCommand("SELECT id_judet, Denumire_judet
FROM  judete");
   TestDA.SelectCommand.Connection=conn;

   if (conn.State == ConnectionState.Closed)
   {
    conn.Open();
   }

   txn = conn.BeginTransaction();
   TestDA.SelectCommand.Transaction=txn;
   FbCommandBuilder custCB  = new FbCommandBuilder(TestDA);
   TestDA.Fill(dataSetAdmin,"Judete");
   this.dataGrid1.DataSource=this.dataSetAdmin.Tables["Judete"];
   this.dataGrid1.Refresh();
  }

 private void button_Update_Click(object sender, System.EventArgs e)
  {
   try
   {
    TestDA.Update(dataSetAdmin.Tables["Judete"]);
    txn.Commit();
   }
   catch (System.Exception eUpdate)
   {
    System.Windows.Forms.MessageBox.Show(eUpdate.Message);
   }
  }

George

"Pavol STAREK" <pa...@st...> wrote in message
news:3E8...@st......
> George LAZAR wrote:
>
> >Hello all!
> >
> >I try to use the Firebird.Net provider.
> >
> >i have a connection, a transaction and a data adapter.
> >i have also a command builder.
> >
> >when i try to update the data into the database using the dataadapter, i
> >receive the error message:
> >"Command Connection is not equal to Transaction Connection" .
> >
> >any ideea?
> >
> >thank you
> >
> >
> >
> >
> >
> hi,
>
> you have to post us code,
>
> but i think, that problem is here:
> probably you created trancaction by new clausule... Tray to create it by
> FbConnction.BeginTransaction().
>
> p.
>
> >=============================================
> >George LAZAR
> >=============================================
> >
> >
> >
> >
> >-------------------------------------------------------
> >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
> >
> >
>
>
>
>
>
> -------------------------------------------------------
> 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();

[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
>

[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