Take empty text field as null

Question

I'm working with several forms and so far I've figured that there really is no way to take an empty text field and insert it into a database as null.

for example if the text field is empty in:

<asp:TextBox id="tb0" runat="server"></asp:TextBox>
<asp:TextBox id="tb1" runat="server"></asp:TextBox>
<asp:TextBox id="tb2" runat="server"></asp:TextBox>

But filled in the textboxes surrounding it

Is there any way for the query to, when run, look like this:

INSERT INTO table_name VALUES ('test0', null, 'test2')

Instead of having to loop through and changing the values to whitespace? The whitespace is fine when they are varchar fields but for ints/dates it is very annoying

Here is a part of one of my inserts

"INSERT INTO Customer VALUES(" + custID + " , @mastName, @custName, @addrLn1, @addrLn2, @cCity, @cState, @cZip, @cZip4, @cCountry, @cusSince, @renewalDate)"

BTW custID is not a parameter because it is a float and when I set it as a parameter the value gets messed up (e.g 105.05 will turn into 105.0498..)

Can you show the code where you are actually preparing and executing the database inserts? — Nathan A, Jul 09 '14 at 17:06
If you'd start with parametrized queries (as you must) you'd be easily specify DBNull as parameter type of nullable column... This already covered in http://stackoverflow.com/questions/16717179/how-to-insert-null-value-in-database-through-parameterized-query — Alexei Levenkov, Jul 09 '14 at 17:06
I updated OT and correct me if I'm wrong but DBNull looks like it prepares the field to be null, this is optional? Is that what it does? I'm new to c# so bare with me.. edit: after reading some of the thread I see what you mean, I will give that a shot — brianforan, Jul 09 '14 at 17:23

score 2 · Accepted Answer · answered Jul 09 '14 at 17:10

2

You could use a simple method like this:

object ToDBValue(string str)
{
    return string.IsNullOrEmpty(str) ? DBNull.Value : (object)str;
}

Then you could build your parameters:

command.Parameters.AddWithValue("@VAL1", ToDBValue(tb0.Text));
command.Parameters.AddWithValue("@VAL2", ToDBValue(tb1.Text));
command.Parameters.AddWithValue("@VAL3", ToDBValue(tb2.Text));

And then change your query to:

INSERT INTO table_name VALUES (@VAL1, @VAL2, @VAL3)

answered Jul 09 '14 at 17:10

itsme86

19,266
4
41
57

+1. Please consider sample showing how to create parametrized the query (or just add link to MSDN / SO for that) as OP did not have any. – Alexei Levenkov Jul 09 '14 at 17:18
Sorry I had mine parameterized in my actual code, I updated op – brianforan Jul 09 '14 at 17:24
@itsme86 this made my cmd not work anymore when I added the method in – brianforan Jul 09 '14 at 17:53

score 1 · Answer 2 · answered Jul 09 '14 at 17:11

1

use DBNull.Value which will give you Null for that specific value in the row. So if your checking if the textbox is null. Just use something along the lines of !string.IsNullOrWhitespace(tb0.Text) ? tb0.Text : DBNull.Value

answered Jul 09 '14 at 17:11

AlanWalker

19
1

score 0 · Answer 3 · answered Jul 09 '14 at 17:06

0

If your using a stored procedure with parameters you can make a paramenter value DBNull.Value

answered Jul 09 '14 at 17:06

Hillboy

472
6
19

score 0 · Answer 4 · answered Jul 09 '14 at 17:13

0

Can you change the stored procedure? If so, you can make the parameter optional, defaulting to NULL and just not pass it is the value IsNullOrEmpty.

answered Jul 09 '14 at 17:13

BanksySan

27,362
33
117
216

Take empty text field as null

4 Answers4