471,093 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,093 software developers and data experts.

"No size set for variable length data" Oracle update error with empty string in .NET

Hi - I'm updating an Oracle table with an empty string and getting the
error:

An unhandled exception of type 'System.Exception' occurred in
system.data.dll
Additional information: Parameter 'p1': No size set for variable length data
type: String.

The column is nullable and of type varchar2(50). I'm aware that I can store
DBNull.Value instead and Oracle won't complain, but, I was hoping to avoid
testing and converting for that condition. It seems the .NET class lib
should handle empty strings, no? There are times I'd like to throw the
dataset into a datagrid control and have the user have there way with it,
therefore, its not ideal to have to validate each string value on the
update.

Question 1: Is this a bug or am I doing something wrong?
Question 2: If its a bug, is there an elegant workaround?
Sample C# code is:

// create typed dataset (basic customer list: name, address, phone, etc.)
CustomerDS dsCustomer = new CustomerDS();

// create data adapter
OracleDataAdapter daCustomers = new OracleDataAdapter("SELECT * FROM
Customers", connectionString);

// use command builder to create update, insert and delete commands based on
select
OracleCommandBuilder cb = new OracleCommandBuilder(daCustomers);

// get some data
daCustomers.Fill(dsCustomer.CUSTOMERS)

// delete Middle Name, for example
dsCustomer.CUSTOMERS.Rows[0]["MIDDLE_NAME"] = "";

// store new dataset
daCustomers.Update(dsCustomer, "Customers");

// boom - get error (even though I put it in a try...catch block!)
Jul 19 '05 #1
1 14647
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

arvee wrote:
Hi - I'm updating an Oracle table with an empty string and getting the
error:

An unhandled exception of type 'System.Exception' occurred in
system.data.dll
Additional information: Parameter 'p1': No size set for variable length data
type: String.

The column is nullable and of type varchar2(50). I'm aware that I can store
DBNull.Value instead and Oracle won't complain, but, I was hoping to avoid
testing and converting for that condition. It seems the .NET class lib
should handle empty strings, no? There are times I'd like to throw the
dataset into a datagrid control and have the user have there way with it,
therefore, its not ideal to have to validate each string value on the
update.

Question 1: Is this a bug or am I doing something wrong?
Question 2: If its a bug, is there an elegant workaround?

[snip!]
Why don't you store a NULL? Oracle will treat '' (empty string) as
NULL, and store as NULL anyway - better be prepared for it, and
treat it as such.

Oh yes - it will work when you let Oracle do the conversion(s), but
some understanding of the product you are working with will only
help you in creating a better product.

All documentation is on docs.oracle.com - free! Start with Concepts.

- --
Regards,
Frank van Bortel
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCVm8QfyJ9XXMPY5URArK8AKCRsSGLCWO9VYv3YnUqLc X2rYE1DACfTcCO
f8j04ChsvUGatK4rAjP0wzY=
=nBQ1
-----END PGP SIGNATURE-----
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

134 posts views Thread by James A. Donald | last post: by
2 posts views Thread by jj555s | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.