insert NULL

[Guest]

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

 

[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

 

[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

 

[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:[email protected]...

 

[Guest]

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

 

[Eliyahu Goldin]

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

Eliyahu

 

[Guest]

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?

 

[Guest]

Try this...

UPDATE table SET name = NULL

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

 

[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

 

[Guest]

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?

 

[Guest]

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;

 

[Guest]

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

 

[Guest]

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;
}

 

[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

 

[Guest]

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?

 

[Guest]

I got it

 

[Otis Mukinfus]

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

 

[sherry]

Why cannot insert data to database

Hi all,

I face the problem that why my data of textbox and cehckbox cannot insert to my database. Can anyone give me the idea?

I use the sql command:

INSERT INTO publication(TitleArticle, TitlePublication, [Group], AuthorFirstName, AuthorLastName, Year, [File], Classification) VALUES (@TitleArticle, @TitlePublication, @Group , @AuthorFirstName, @AuthorLastName, @Year, @File , @Classification)


Am i wrong?

Thanks!