How do I insert a NULL value inside an INSERT statement

  • Thread starter Thread starter Dino M. Buljubasic
  • Start date Start date
D

Dino M. Buljubasic

How do I insert a NULL value inside an INSERT statement in an SQL Server
database?



Thank you,

Dino
 
Hi Dino,

Either use parametrized query and pass DBNull.Value as parameter value or
use a sql statement like:
insert into mytable (field1) values (null);
 
I tried it but it does not work. My field is of varchar type, and
DBNull.Value will insert an empthy string. Here is how I did it:

INSERT INTO tblTable (fldField)
VALUES(Iif(cboCombo.enabled, cboCombo.SelectedItem, DBNull.Value))

Any suggestion will be appreciated.

--


-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com


Scott M. said:
You could use a variable that has been set to = DBNull.Value in the
statement.
Click to expand...
 
Hi Miha,

Using 'null' inside INSERT is not supported anymore. I get error to use
System.DBNull.Value instead but that one inserts an empty string instead of
<NULL>.
Thank you,
Dino

--


-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com


Miha Markic said:
Hi Dino,

Either use parametrized query and pass DBNull.Value as parameter value or
use a sql statement like:
insert into mytable (field1) values (null);
Click to expand...
 
Dino M. Buljubasic said:
Hi Miha,

Using 'null' inside INSERT is not supported anymore. I get error to
use System.DBNull.Value instead but that one inserts an empty string
instead of <NULL>.
Click to expand...

can you post the actual code that you have to build and execute the insert?
It sounds like you are mixing levels in what you are doing but it's hard to
tell exactly what's going on from the description.
 
Hi,
thank you for your reply. The code looks like:

INSERT INTO tblTable(fldField) VALUES(Iif(cboCombo.Enabled,
cboCombo.SelectedItem, DBNull.Value))

, the fldField is Varchar(10), it allows nulls.

Thank you,
Dino
 
Dino M. Buljubasic said:
Hi,
thank you for your reply. The code looks like:

INSERT INTO tblTable(fldField) VALUES(Iif(cboCombo.Enabled,
cboCombo.SelectedItem, DBNull.Value))
Click to expand...

OK... but where is this code? Inside a stored procedure? It isn't a valid
C# or VB.Net statement so what's the context around it?
 
Hi Dino,

You are mixing code and sql.

You should do something like:
INSERT INTO tblTable(fldField) VALUES(" + Iif(cboCombo.Enabled,
cboCombo.SelectedItem.ToString(), "null") + ")"

assuming that tblTable and fldField are database names.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rhand.com


Dino M. Buljubasic said:
Hi,
thank you for your reply. The code looks like:

INSERT INTO tblTable(fldField) VALUES(Iif(cboCombo.Enabled,
cboCombo.SelectedItem, DBNull.Value))

, the fldField is Varchar(10), it allows nulls.

Thank you,
Dino
Click to expand...
 
Ah, well you've got some VB problems here which could be the culprit. Try
this:

INSERT INTO tblTable (fldField) VALUES(IIF(cboCombo.enabled,
cboCombo.SelectedItem.Value.ToString, DBNull.Value))

You were never bringing back the value of the item in the combobox. Also,
since your DB data type is varchar, then what's wrong with sending an empty
string back?
 
Oops! and I meant to also mention that the INSERT was built wrong as well!
It should be like this:

Dim ValueToInsert as String

IF cboCombo.enabled then
ValueToInsert = cboCombo.SelectedItem.Value.ToString
ELSE
ValueToInsert = DBNull.Value
END IF

InsertCommand.CommandText = "INSERT INTO tblTable (fldField) VALUES (" &
ValueToInsert & ")"
 
Back
Top