How do I insert a NULL value inside an INSERT statement

[Dino M. Buljubasic]

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



Thank you,

Dino

 

[Scott M.]

You could use a variable that has been set to = DBNull.Value in the
statement.

 

[Miha Markic]

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

 

[Dino M. Buljubasic]

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

You could use a variable that has been set to = DBNull.Value in the
statement.

 

[Dino M. Buljubasic]

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

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

 

[Bill Styles]

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>.

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.

 

[Dino M. Buljubasic]

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

 

[Bill Styles]

Hi,
thank you for your reply. The code looks like:

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

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?

 

[Miha Markic]

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

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

 

[Scott M.]

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?

 

[Scott M.]

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 & ")"