Solved

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

Posted on 2008-10-20
7
1,604 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Agent Timeout 5 65
Addition to SQL for dynamic fields 6 47
Help Required 2 39
Are triggers slow? 7 12
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 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