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 17 2162
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
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
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
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
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
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
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
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
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
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
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
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
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 > > >
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 > > > > > > > > >
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 > > > > > >
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
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 This discussion thread is closed Replies have been disabled for this discussion. Similar topics
2 posts
views
Thread by george |
last post: by
|
9 posts
views
Thread by Martin |
last post: by
|
14 posts
views
Thread by serge |
last post: by
|
reply
views
Thread by jtocci |
last post: by
|
16 posts
views
Thread by Philip Boonzaaier |
last post: by
|
reply
views
Thread by ImraneA |
last post: by
|
1 post
views
Thread by Abareblue |
last post: by
|
2 posts
views
Thread by speralta |
last post: by
| | | | | | | | | | | | |