473,322 Members | 1,307 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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
Jan 31 '06 #1
17 2337
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

Jan 31 '06 #2
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

Jan 31 '06 #3
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

Jan 31 '06 #4
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


Jan 31 '06 #5
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

Jan 31 '06 #6
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

Jan 31 '06 #7
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


Jan 31 '06 #8
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

Jan 31 '06 #9
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

Jan 31 '06 #10
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


Jan 31 '06 #11
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


Jan 31 '06 #12
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

Jan 31 '06 #13
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
>
>
>

Jan 31 '06 #14
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
> > >
> > >
> > >

Jan 31 '06 #15
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
> >
> >
> >

Jan 31 '06 #16
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

Jan 31 '06 #17
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
Feb 1 '06 #18

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: george | last post by:
This is like the bug from hell. It is kind of hard to explain, so please bear with me. Background Info: SQL Server 7.0, on an NT box, Active Server pages with Javascript, using ADO objects. ...
9
by: Martin | last post by:
Hello, I'm new with triggers and I can not find any good example on how to do the following: I have two tables WO and PM with the following fields: WO.WONUM, VARCHAR(10) WO.PMNUM,...
14
by: serge | last post by:
I have a scenario where two tables are in a One-to-Many relationship and I need to move the data from the Many table to the One table so that it becomes a One-to-One relationship. I need to...
0
by: jtocci | last post by:
I'm having a big problem with CREATE RULE...ON INSERT...INSERT INTO...SELECT...FROM...WHERE when I want to INSERT several (20~50) records based on a single INSERT to a view. Either I get a 'too...
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
0
by: ImraneA | last post by:
Hi there I had pleasure of upsizing Access v97 db to Access v2K/SQL 2K. Wish to provide some knowledge gained back to community - hopefully help others. 1.Question how do you test stored...
1
by: Abareblue | last post by:
I have no clue on how to insert a record into access. here is the whole thing using System; using System.Drawing; using System.Collections; using System.ComponentModel;
2
by: speralta | last post by:
My tired old eyes may be failing me, but the following insert statements look correct to me, but I can't seem to get a clean insert from a fairly large text file database into mysql. I was...
0
ak1dnar
by: ak1dnar | last post by:
There is a Error getting while i am entering records using this jsp file. <%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" errorPage="" %> <%@ include...
0
by: gpspocket | last post by:
help me -CURSOR backward insert from End Date > to Start Date how to insert dates from end to start like this SELECT 111111,1,CONVERT(DATETIME, '17/03/2008', 103), CONVERT(DATETIME,...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.