Solved

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

Posted on 2008-10-20
7
1,610 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

734 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