insert NULL

[NuB]

I have a sql query that is doing an update of records, how can I add NULL to
the field in the database if the field on my screen is blank?

example:
I have 5 textboxes, and a user can leave some blank, delete data from a text
box then hit update, how can I have NULL inserted into the field on the
database instead of having a blank record in the db

[S. Justin Gengo [MCP]]

NuB,

The simplest way is to not include that parameter at all unless it has data
in it.

So if you're adding parameters to your insert statement (using a sql command
object) it would look like this:

If MiddleNameTextB ox.Text.Trim.Le ngth > 0 Then
SqlCommand1.Par ameters.Add("@M iddleName", SqlDbType.NvarC har, 50).Value
= MiddleNameTextB ox.Text.Trim
End If

If a field in the sql database is nullable then not setting the parameter
will leave it null.

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche
"NuB" <Nu*@discussion s.microsoft.com > wrote in message
news:95******** *************** ***********@mic rosoft.com...

I have a sql query that is doing an update of records, how can I add NULL
to
the field in the database if the field on my screen is blank?

example:
I have 5 textboxes, and a user can leave some blank, delete data from a
text
box then hit update, how can I have NULL inserted into the field on the
database instead of having a blank record in the db

[Karl Seguin [MVP]]

cmd.Parameters. Add("@Blah", SqlDbType.Strin g, 128).Value = (firstName ==
null || firstName.Lengt h == 0) ? DBNull.Value : firstName;
You can do it cleaner in 2.0

cmd.Parameters. Add(....).Value = (string.IsNullO rEmpty(firstNam e) ??
DBNull.Value;

karl

--
MY ASP.Net tutorials
http://www.openmymind.net/
"NuB" <Nu*@discussion s.microsoft.com > wrote in message
news:95******** *************** ***********@mic rosoft.com...

I have a sql query that is doing an update of records, how can I add NULL
to
the field in the database if the field on my screen is blank?

example:
I have 5 textboxes, and a user can leave some blank, delete data from a
text
box then hit update, how can I have NULL inserted into the field on the
database instead of having a blank record in the db

[Karl Seguin [MVP]]

And if ur using VB, you'll simply need to if/else

dim databaseFirstNa me as object
if firstName is nothing OrElse firstName.Lengt h = 0 then
databaseFirstNa me = DBNull.Value
else if
databaseFirstNa me = firstName
end if

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/
"NuB" <Nu*@discussion s.microsoft.com > wrote in message
news:95******** *************** ***********@mic rosoft.com...

I have a sql query that is doing an update of records, how can I add NULL
to
the field in the database if the field on my screen is blank?

example:
I have 5 textboxes, and a user can leave some blank, delete data from a
text
box then hit update, how can I have NULL inserted into the field on the
database instead of having a blank record in the db

[S. Justin Gengo [MCP]]

I like the C# code for this. Especially the 2.0 version.

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche
"Karl Seguin [MVP]" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME
net> wrote in message news:ui******** ******@TK2MSFTN GP15.phx.gbl...

cmd.Parameters. Add("@Blah", SqlDbType.Strin g, 128).Value = (firstName ==
null || firstName.Lengt h == 0) ? DBNull.Value : firstName;
You can do it cleaner in 2.0

cmd.Parameters. Add(....).Value = (string.IsNullO rEmpty(firstNam e) ??
DBNull.Value;

karl

--
MY ASP.Net tutorials
http://www.openmymind.net/
"NuB" <Nu*@discussion s.microsoft.com > wrote in message
news:95******** *************** ***********@mic rosoft.com...

I have a sql query that is doing an update of records, how can I add NULL
to
the field in the database if the field on my screen is blank?

example:
I have 5 textboxes, and a user can leave some blank, delete data from a
text
box then hit update, how can I have NULL inserted into the field on the
database instead of having a blank record in the db

[NuB]

Thanks, I've tried everything listed below, I even did

if (textbox.text == string.Empty)
{
textbox.text = System.DBNull;

}

its an update SQL statement not a proc, so its something like this

update table set name ='" + textbox.text"
now, textbox can be blank on the form, so if it is I need word NULL to show
in the name field in the table

"NuB" wrote:

I have a sql query that is doing an update of records, how can I add NULL to
the field in the database if the field on my screen is blank?

example:
I have 5 textboxes, and a user can leave some blank, delete data from a text
box then hit update, how can I have NULL inserted into the field on the
database instead of having a blank record in the db

[Eliyahu Goldin]

Just don't include the field in the update statement.

Eliyahu

"NuB" <Nu*@discussion s.microsoft.com > wrote in message
news:95******** *************** ***********@mic rosoft.com...

I have a sql query that is doing an update of records, how can I add NULL
to
the field in the database if the field on my screen is blank?

example:
I have 5 textboxes, and a user can leave some blank, delete data from a
text
box then hit update, how can I have NULL inserted into the field on the
database instead of having a blank record in the db

[NuB]

something else i just noticed is that the developer that coded this orignally
is using a Control Array of textboxes, will that make a difference in this?
its a collection of textboxes and he's passing the data in the insert query
as an array.

is there anyway to get this working to show NULL in the table instead of a
blank field?

"Eliyahu Goldin" wrote:

Just don't include the field in the update statement.

Eliyahu

"NuB" <Nu*@discussion s.microsoft.com > wrote in message
news:95******** *************** ***********@mic rosoft.com...

I have a sql query that is doing an update of records, how can I add NULL
to
the field in the database if the field on my screen is blank?

example:
I have 5 textboxes, and a user can leave some blank, delete data from a
text
box then hit update, how can I have NULL inserted into the field on the
database instead of having a blank record in the db

[Gordon]

Try this...

UPDATE table SET name = NULL

"NuB" wrote:

Thanks, I've tried everything listed below, I even did

if (textbox.text == string.Empty)
{
textbox.text = System.DBNull;

}

its an update SQL statement not a proc, so its something like this

update table set name ='" + textbox.text"
now, textbox can be blank on the form, so if it is I need word NULL to show
in the name field in the table

"NuB" wrote:

I have a sql query that is doing an update of records, how can I add NULL to
the field in the database if the field on my screen is blank?

example:
I have 5 textboxes, and a user can leave some blank, delete data from a text
box then hit update, how can I have NULL inserted into the field on the
database instead of having a blank record in the db

[S. Justin Gengo [MCP]]

Nub,

Did you know that you can still use parameters in a text SqlCommand?

It makes them even easier to create:

SqlCommand.Text = "INSERT INTO myTable SET col1 = ?, col2 = ?, col3 = ?,
col4 = ?, col5 = ? WHERE ID = ?"

Each parameter will fill one of the placeholder represented with a question
mark. When doing this you need to specify the parameters in order and you
have to include them all. So combine this with Karl's example and you'll be
all set.

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche
"NuB" <Nu*@discussion s.microsoft.com > wrote in message
news:D6******** *************** ***********@mic rosoft.com...

Thanks, I've tried everything listed below, I even did

if (textbox.text == string.Empty)
{
textbox.text = System.DBNull;

}

its an update SQL statement not a proc, so its something like this

update table set name ='" + textbox.text"
now, textbox can be blank on the form, so if it is I need word NULL to
show
in the name field in the table

"NuB" wrote:

I have a sql query that is doing an update of records, how can I add NULL
to
the field in the database if the field on my screen is blank?

example:
I have 5 textboxes, and a user can leave some blank, delete data from a
text
box then hit update, how can I have NULL inserted into the field on the
database instead of having a blank record in the db