Solved

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

Posted on 2008-10-20
7
1,587 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 8

Accepted Solution

by:
Wikkard earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

762 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

11 Experts available now in Live!

Get 1:1 Help Now