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 MiddleNameTextBox.Text.Trim.Length > 0 Then
SqlCommand1.Parameters.Add("@MiddleName", SqlDbType.NvarChar, 50).Value
= MiddleNameTextBox.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*@discussions.microsoft.com> wrote in message
news:95**********************************@microsof t.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.String, 128).Value = (firstName ==
null || firstName.Length == 0) ? DBNull.Value : firstName;
You can do it cleaner in 2.0

cmd.Parameters.Add(....).Value = (string.IsNullOrEmpty(firstName) ??
DBNull.Value;

karl

--
MY ASP.Net tutorials
http://www.openmymind.net/
"NuB" <Nu*@discussions.microsoft.com> wrote in message
news:95**********************************@microsof t.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 databaseFirstName as object
if firstName is nothing OrElse firstName.Length = 0 then
databaseFirstName = DBNull.Value
else if
databaseFirstName = firstName
end if

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/
"NuB" <Nu*@discussions.microsoft.com> wrote in message
news:95**********************************@microsof t.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**************@TK2MSFTNGP15.phx.gbl...

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

cmd.Parameters.Add(....).Value = (string.IsNullOrEmpty(firstName) ??
DBNull.Value;

karl

--
MY ASP.Net tutorials
http://www.openmymind.net/
"NuB" <Nu*@discussions.microsoft.com> wrote in message
news:95**********************************@microsof t.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*@discussions.microsoft.com> wrote in message
news:95**********************************@microsof t.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*@discussions.microsoft.com> wrote in message
news:95**********************************@microsof t.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*@discussions.microsoft.com> wrote in message
news:D6**********************************@microsof t.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

[NuB]

Its an UPDATE not an insert, and the developer that oringally did this did
the SQL like this:

string SQL;
SQL+= "UPDATE table1 SET name = '" + sName + "' WHERE ID=" + ID"

then he calls a class that connects and executes the SQLCommand

so how would this example work with this SQL statement?

"S. Justin Gengo [MCP]" wrote:

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*@discussions.microsoft.com> wrote in message
news:D6**********************************@microsof t.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

[Gordon]

NOTE: v1.1 uses sqlCmd.Parameters.Add
SqlClient.SqlCommand sqlCmd = new SqlClient.SqlCommand();
string sSQL;
if (sName.Trim.Length == 0) {
sSQL = "UPDATE table1 SET name = NULL WHERE ID = @ID";
sqlCmd.Parameters.Add("@ID", ID);
} else {
sSQL = "UPDATE table1 SET name = @Name WHERE ID = @ID";
sqlCmd.Parameters.Add("@Name", Replace(sName.Trim, "'", "''"));
sqlCmd.Parameters.Add("@ID", ID);
}
sqlCmd.CommandText = sSQL;
sqlCmd.CommandType = CommandType.Text;

NOTE: v2.0 uses sqlCmd.Parameters.AddWtihValue
SqlClient.SqlCommand sqlCmd = new SqlClient.SqlCommand();
string sSQL;
if (sName.Trim.Length == 0) {
sSQL = "UPDATE table1 SET name = NULL WHERE ID = @ID";
sqlCmd.Parameters.AddWithValue("@ID", ID);
} else {
sSQL = "UPDATE table1 SET name = @Name WHERE ID = @ID";
sqlCmd.Parameters.AddWithValue("@Name", Replace(sName.Trim, "'", "''"));
sqlCmd.Parameters.AddWithValue("@ID", ID);
}
sqlCmd.CommandText = sSQL;
sqlCmd.CommandType = CommandType.Text;

"NuB" wrote:

Its an UPDATE not an insert, and the developer that oringally did this did
the SQL like this:

string SQL;
SQL+= "UPDATE table1 SET name = '" + sName + "' WHERE ID=" + ID"

then he calls a class that connects and executes the SQLCommand

so how would this example work with this SQL statement?

"S. Justin Gengo [MCP]" wrote:

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*@discussions.microsoft.com> wrote in message
news:D6**********************************@microsof t.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

[NuB]

I understand all of this below, BUT all the code is doing is passing the SQL
query string to a method in another class that has all of the SQL Command,
Command Type, etc in it. So how will below work in my scenario?

here is what i have:

btnUpdate()
{
string sName = textName.text;
SQL+= "UPDATE table1 SET name = '" + sName + "' WHERE ID=" + ID"

dbClass.UpdateProcess(connnectionString, SQL);
}

then in the Update method in the dbCLass file i have this:

ExecuteQuery(connectionString, SQL)
{
System.Data.SqlClient.SqlCommand cd=ConnectionString.CreateCommand
cd.CommandType = System.Data.CommandType.Text;
cd.CommandText = SQL;
cd.ExecuteNonQuery();
}

so with that stuff in a separate class, how will your example work? Thats
the part i'm stuck on

"Gordon" wrote:

NOTE: v1.1 uses sqlCmd.Parameters.Add
SqlClient.SqlCommand sqlCmd = new SqlClient.SqlCommand();
string sSQL;
if (sName.Trim.Length == 0) {
sSQL = "UPDATE table1 SET name = NULL WHERE ID = @ID";
sqlCmd.Parameters.Add("@ID", ID);
} else {
sSQL = "UPDATE table1 SET name = @Name WHERE ID = @ID";
sqlCmd.Parameters.Add("@Name", Replace(sName.Trim, "'", "''"));
sqlCmd.Parameters.Add("@ID", ID);
}
sqlCmd.CommandText = sSQL;
sqlCmd.CommandType = CommandType.Text;

NOTE: v2.0 uses sqlCmd.Parameters.AddWtihValue
SqlClient.SqlCommand sqlCmd = new SqlClient.SqlCommand();
string sSQL;
if (sName.Trim.Length == 0) {
sSQL = "UPDATE table1 SET name = NULL WHERE ID = @ID";
sqlCmd.Parameters.AddWithValue("@ID", ID);
} else {
sSQL = "UPDATE table1 SET name = @Name WHERE ID = @ID";
sqlCmd.Parameters.AddWithValue("@Name", Replace(sName.Trim, "'", "''"));
sqlCmd.Parameters.AddWithValue("@ID", ID);
}
sqlCmd.CommandText = sSQL;
sqlCmd.CommandType = CommandType.Text;

"NuB" wrote:

Its an UPDATE not an insert, and the developer that oringally did this did
the SQL like this:

string SQL;
SQL+= "UPDATE table1 SET name = '" + sName + "' WHERE ID=" + ID"

then he calls a class that connects and executes the SQLCommand

so how would this example work with this SQL statement?

"S. Justin Gengo [MCP]" wrote:

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*@discussions.microsoft.com> wrote in message
news:D6**********************************@microsof t.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

[Gordon]

Since the code is just passing in a query string, you just need to do this:

string sName = textName.text.trim;
if (sName.length == 0) {
SQL += "UPDATE table1 SET name = NULL WHERE ID = " + ID;
} else {
SQL += "UPDATE table1 SET name = '" + sName + "' WHERE ID = " + ID;
}


"NuB" wrote:

I understand all of this below, BUT all the code is doing is passing the SQL
query string to a method in another class that has all of the SQL Command,
Command Type, etc in it. So how will below work in my scenario?

here is what i have:

btnUpdate()
{
string sName = textName.text;
SQL+= "UPDATE table1 SET name = '" + sName + "' WHERE ID=" + ID"

dbClass.UpdateProcess(connnectionString, SQL);
}

then in the Update method in the dbCLass file i have this:

ExecuteQuery(connectionString, SQL)
{
System.Data.SqlClient.SqlCommand cd=ConnectionString.CreateCommand
cd.CommandType = System.Data.CommandType.Text;
cd.CommandText = SQL;
cd.ExecuteNonQuery();
}

so with that stuff in a separate class, how will your example work? Thats
the part i'm stuck on

"Gordon" wrote:

NOTE: v1.1 uses sqlCmd.Parameters.Add
SqlClient.SqlCommand sqlCmd = new SqlClient.SqlCommand();
string sSQL;
if (sName.Trim.Length == 0) {
sSQL = "UPDATE table1 SET name = NULL WHERE ID = @ID";
sqlCmd.Parameters.Add("@ID", ID);
} else {
sSQL = "UPDATE table1 SET name = @Name WHERE ID = @ID";
sqlCmd.Parameters.Add("@Name", Replace(sName.Trim, "'", "''"));
sqlCmd.Parameters.Add("@ID", ID);
}
sqlCmd.CommandText = sSQL;
sqlCmd.CommandType = CommandType.Text;

NOTE: v2.0 uses sqlCmd.Parameters.AddWtihValue
SqlClient.SqlCommand sqlCmd = new SqlClient.SqlCommand();
string sSQL;
if (sName.Trim.Length == 0) {
sSQL = "UPDATE table1 SET name = NULL WHERE ID = @ID";
sqlCmd.Parameters.AddWithValue("@ID", ID);
} else {
sSQL = "UPDATE table1 SET name = @Name WHERE ID = @ID";
sqlCmd.Parameters.AddWithValue("@Name", Replace(sName.Trim, "'", "''"));
sqlCmd.Parameters.AddWithValue("@ID", ID);
}
sqlCmd.CommandText = sSQL;
sqlCmd.CommandType = CommandType.Text;

"NuB" wrote:

Its an UPDATE not an insert, and the developer that oringally did this did
the SQL like this:

string SQL;
SQL+= "UPDATE table1 SET name = '" + sName + "' WHERE ID=" + ID"

then he calls a class that connects and executes the SQLCommand

so how would this example work with this SQL statement?

"S. Justin Gengo [MCP]" wrote:

> 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*@discussions.microsoft.com> wrote in message
> news:D6**********************************@microsof t.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
>
>
>

[Karl Seguin [MVP]]

Gordon, not to be harsh, but what you have there is the WORST CODE possible.
It'll be hacked in about 2 seconds and depending on the rights of the user
in the connection string, you're entire network could be compromised.

Please use parameterized queries...

UPDATE Table1 SET name = '@Name' WHERE ID = @ID

command.Paramters.Add("@Name", SqlDbType.Varchar).Value = sname
....

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/
"Gordon" <Go****@discussions.microsoft.com> wrote in message
news:0A**********************************@microsof t.com...

Since the code is just passing in a query string, you just need to do
this:

string sName = textName.text.trim;
if (sName.length == 0) {
SQL += "UPDATE table1 SET name = NULL WHERE ID = " + ID;
} else {
SQL += "UPDATE table1 SET name = '" + sName + "' WHERE ID = " + ID;
}


"NuB" wrote:

I understand all of this below, BUT all the code is doing is passing the
SQL
query string to a method in another class that has all of the SQL
Command,
Command Type, etc in it. So how will below work in my scenario?

here is what i have:

btnUpdate()
{
string sName = textName.text;
SQL+= "UPDATE table1 SET name = '" + sName + "' WHERE ID=" + ID"

dbClass.UpdateProcess(connnectionString, SQL);
}

then in the Update method in the dbCLass file i have this:

ExecuteQuery(connectionString, SQL)
{
System.Data.SqlClient.SqlCommand cd=ConnectionString.CreateCommand
cd.CommandType = System.Data.CommandType.Text;
cd.CommandText = SQL;
cd.ExecuteNonQuery();
}

so with that stuff in a separate class, how will your example work? Thats
the part i'm stuck on

"Gordon" wrote:

> NOTE: v1.1 uses sqlCmd.Parameters.Add
> SqlClient.SqlCommand sqlCmd = new SqlClient.SqlCommand();
> string sSQL;
> if (sName.Trim.Length == 0) {
> sSQL = "UPDATE table1 SET name = NULL WHERE ID = @ID";
> sqlCmd.Parameters.Add("@ID", ID);
> } else {
> sSQL = "UPDATE table1 SET name = @Name WHERE ID = @ID";
> sqlCmd.Parameters.Add("@Name", Replace(sName.Trim, "'", "''"));
> sqlCmd.Parameters.Add("@ID", ID);
> }
> sqlCmd.CommandText = sSQL;
> sqlCmd.CommandType = CommandType.Text;
>
> NOTE: v2.0 uses sqlCmd.Parameters.AddWtihValue
> SqlClient.SqlCommand sqlCmd = new SqlClient.SqlCommand();
> string sSQL;
> if (sName.Trim.Length == 0) {
> sSQL = "UPDATE table1 SET name = NULL WHERE ID = @ID";
> sqlCmd.Parameters.AddWithValue("@ID", ID);
> } else {
> sSQL = "UPDATE table1 SET name = @Name WHERE ID = @ID";
> sqlCmd.Parameters.AddWithValue("@Name", Replace(sName.Trim, "'",
> "''"));
> sqlCmd.Parameters.AddWithValue("@ID", ID);
> }
> sqlCmd.CommandText = sSQL;
> sqlCmd.CommandType = CommandType.Text;
>
>
>
> "NuB" wrote:
>
> > Its an UPDATE not an insert, and the developer that oringally did
> > this did
> > the SQL like this:
> >
> > string SQL;
> > SQL+= "UPDATE table1 SET name = '" + sName + "' WHERE ID=" + ID"
> >
> > then he calls a class that connects and executes the SQLCommand
> >
> > so how would this example work with this SQL statement?
> >
> > "S. Justin Gengo [MCP]" wrote:
> >
> > > 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*@discussions.microsoft.com> wrote in message
> > > news:D6**********************************@microsof t.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
> > >
> > >
> > >

[NuB]

I have that, I tried that and it will not put NULL in the field in the
database, BUT if i give it a value it will insert that into the db?

"Gordon" wrote:

Since the code is just passing in a query string, you just need to do this:

string sName = textName.text.trim;
if (sName.length == 0) {
SQL += "UPDATE table1 SET name = NULL WHERE ID = " + ID;
} else {
SQL += "UPDATE table1 SET name = '" + sName + "' WHERE ID = " + ID;
}


"NuB" wrote:

I understand all of this below, BUT all the code is doing is passing the SQL
query string to a method in another class that has all of the SQL Command,
Command Type, etc in it. So how will below work in my scenario?

here is what i have:

btnUpdate()
{
string sName = textName.text;
SQL+= "UPDATE table1 SET name = '" + sName + "' WHERE ID=" + ID"

dbClass.UpdateProcess(connnectionString, SQL);
}

then in the Update method in the dbCLass file i have this:

ExecuteQuery(connectionString, SQL)
{
System.Data.SqlClient.SqlCommand cd=ConnectionString.CreateCommand
cd.CommandType = System.Data.CommandType.Text;
cd.CommandText = SQL;
cd.ExecuteNonQuery();
}

so with that stuff in a separate class, how will your example work? Thats
the part i'm stuck on

"Gordon" wrote:

NOTE: v1.1 uses sqlCmd.Parameters.Add
SqlClient.SqlCommand sqlCmd = new SqlClient.SqlCommand();
string sSQL;
if (sName.Trim.Length == 0) {
sSQL = "UPDATE table1 SET name = NULL WHERE ID = @ID";
sqlCmd.Parameters.Add("@ID", ID);
} else {
sSQL = "UPDATE table1 SET name = @Name WHERE ID = @ID";
sqlCmd.Parameters.Add("@Name", Replace(sName.Trim, "'", "''"));
sqlCmd.Parameters.Add("@ID", ID);
}
sqlCmd.CommandText = sSQL;
sqlCmd.CommandType = CommandType.Text;

NOTE: v2.0 uses sqlCmd.Parameters.AddWtihValue
SqlClient.SqlCommand sqlCmd = new SqlClient.SqlCommand();
string sSQL;
if (sName.Trim.Length == 0) {
sSQL = "UPDATE table1 SET name = NULL WHERE ID = @ID";
sqlCmd.Parameters.AddWithValue("@ID", ID);
} else {
sSQL = "UPDATE table1 SET name = @Name WHERE ID = @ID";
sqlCmd.Parameters.AddWithValue("@Name", Replace(sName.Trim, "'", "''"));
sqlCmd.Parameters.AddWithValue("@ID", ID);
}
sqlCmd.CommandText = sSQL;
sqlCmd.CommandType = CommandType.Text;

"NuB" wrote:

> Its an UPDATE not an insert, and the developer that oringally did this did
> the SQL like this:
>
> string SQL;
> SQL+= "UPDATE table1 SET name = '" + sName + "' WHERE ID=" + ID"
>
> then he calls a class that connects and executes the SQLCommand
>
> so how would this example work with this SQL statement?
>
> "S. Justin Gengo [MCP]" wrote:
>
> > 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*@discussions.microsoft.com> wrote in message
> > news:D6**********************************@microsof t.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
> >
> >
> >

[NuB]

I got it

"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

[Otis Mukinfus]

On Tue, 31 Jan 2006 11:40:38 -0800, NuB
<Nu*@discussions.microsoft.com> wrote:

I have that, I tried that and it will not put NULL in the field in the
database, BUT if i give it a value it will insert that into the db?

Use a stored procedure and set the default of the parameter to NULL.
Dynamic SQL is evil... and a security risk unless you use parameters
as someone else pointed out.

Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com