ASP.Net C# CreateParameter with empty string

Question

In my ASP.net C# web project I have a query command object that has parameters. I use the following code to fill the parameters:

DbCommand command = conn.CreateCommand();
command.CommandText = query; 
DbParameter param = command.CreateParameter();
param.ParameterName = parameter;
param.DbType = DbType.String;
param.Value = value;

This code works for all strings except for empty ones. If I would leave an input field blank, it would pass the value as "". If this happens I receive the following exception:

ORA-01400: cannot insert NULL into (string)

Is there a way that would allow me to insert blank strings into the database?

I use an Oracle database and I'm using System.Data.OracleClient as provider.

are you positive the value variable is indeed an empty string and not a null? How do you assign from a field (I'm guessing from an html form) to the variable? — Shiv Kumar, Mar 10 '11 at 21:23

score 3 · Accepted Answer · edited May 23 '17 at 12:19

3

If you want to insert an empty string, you have to allow NULL values. Otherwise Oracle silently converts the empty string into NULL and you'll get the exception.

Another option would be to insert an empty string with a space ' ' but i think that would be a pain.

Here are further informations on why Oracle does it this (non standard) way: Why does Oracle 9i treat an empty string as NULL?

edited May 23 '17 at 12:19

Community

1
1

answered Mar 10 '11 at 21:20

Tim Schmelter

450,073
74
686
939

score 0 · Answer 2 · answered Mar 10 '11 at 21:20

0

If the value is null, set param.Value = DBNull.Value, rather than setting it to null:

if (value == null)
{
    param.Value = DBNull.Value;
}
else
{
    param.Value = value;
}

answered Mar 10 '11 at 21:20

JoshL

10,737
11
55
61

score 0 · Answer 3 · answered Mar 10 '11 at 21:20

0

try

param.Value = string.IsNullOrEmpty(value) ? DBNull.Value : value;

answered Mar 10 '11 at 21:20

Bala R

107,317
23
199
210

there is no implicit conversion between System.DBNull and string – WholeLifeLearner Oct 22 '14 at 14:25

ASP.Net C# CreateParameter with empty string

3 Answers3