Solved

How do I insert NULL values in a SQL database using C#.net

Posted on 2008-10-20
7
1,595 Views
Last Modified: 2011-10-03
I have an insert statement that looks like this.

Insert into databasTable (col1,col2,col3) values(id + '" + callBackTime + "','" + reqCallDuration + "','" )

I accept input from 3 differet text boxes then assign the value to the string variables id/callbackTime/reqCallDuration

I need to insert a null value in for example col3- reqCallDuration if that text box was not filled.

How do I do this?

the col3 accepts null values in the database
0
Comment
Question by:wademi
  • 3
  • 2
7 Comments
 
LVL 8

Expert Comment

by:Wikkard
ID: 22763495
Use dbnull.value this represents a database null.
So check if the text boxes are empty then pass dbnull.value instead of an empty string.
0
 
LVL 8

Expert Comment

by:Wikkard
ID: 22763526
The use of  DBNull.value only really applies if you are using parametised queries (or stored procs).
If you are creating inline sql strings (which I think you are based on your question) you can simply put the text NULL into your inline sql string.

ie.
id =1;
callbackTime = "2008-12-24 23:59:59";
reqcallDuration = "NULL"
string strsql = "insert into databaseTable(col1, col2,col3) value (" + id + ", " + callbackTime + ", " +  reqcallDuration + ")";

0
 

Author Comment

by:wademi
ID: 22764194
I tried reqcallDuration = "NULL" before.
if I look at the table in the database the field will be NULL.
When I display the NULL field in a label in my application ithe label actually displays "NULL".

I believe the label blank when displayed if the value is NULL
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 8

Accepted Solution

by:
Wikkard earned 500 total points
ID: 22765935
How are you inserting records ?
Are you using a command object  to run SQL commands on the database or using a dataadapter configured with the appropriate insert command ?

It sounds like you are using an insert command which when configured via the designer/wizard will create a parametized query. In this case you need to set the value to DBNull.Value.

Depends on how the label is populated as to whether it will display null or an empty string. Could you provide more info on how the label is populated? Is this an ASP.NET or winforms application ?


0
 

Expert Comment

by:norton127
ID: 23621592
using dbnull.value while in parameterized query is suggestable. U have to use parameterized queries for the security purpose.inserting of datadirectly by using text boxes ( First i used them) is not suggestable.
go for parameterized query
I am giving Code  for you.
dim cmd as new sqlcommand(insert into tablename (col1,col2,col3) values (@col1,@col2,@col3),connection)

 cmd.Parameters.Add(New SqlParameter("@col1", SqlDbType.NVarChar, 255))

 cmd.Parameters.Add(New SqlParameter("@col2", SqlDbType.NVarChar, 255))

 cmd.Parameters.Add(New SqlParameter("@col3", SqlDbType.NVarChar, 255))

cmd.Parameters("@col1").Value = txtbox.Text

cmd.Parameters("@col2").Value = dbnull.value
 

or 
 

if txtbox1.text="" then 

cmd.Parameters("@col2").Value = dbnull.value

else

cmd.Parameters("@col2").Value = textbox1.text

end if 
 

cmd.Parameters("@col3").Value = txttbox3.Text
 

connection.open

cmd.executenonquery

connection.close

end sub

Open in new window

0
 

Expert Comment

by:norton127
ID: 23621601
While i am inserting Textbox1.text="NULL" it is taking string NULL into database and it will give string value null for your label not null value.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server; storing data in offline mode. 10 69
SQL Select * from 6 36
Two tables - Sum of values - What is the difference 31 49
passing parameter in sql procedure 9 57
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now