CODEWITHSUNDEEP
c#asp.netsql
brianforan
brianforan

Reputation: 182

Take empty text field as null

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..)

Upvotes: 0

Views: 1733

Answers (4)

BanksySan
BanksySan

Reputation: 28510

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.

Upvotes: 0

AlanWalker
AlanWalker

Reputation: 19

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

Upvotes: 1

itsme86
itsme86

Reputation: 19496

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)

Upvotes: 2

Hillboy
Hillboy

Reputation: 695

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

Upvotes: 0

Related Questions