Solved

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

Posted on 2008-10-20
7
1,606 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Truncate vs Delete 63 108
Need help debbuging stored procedure 21 48
Are triggers slow? 7 23
SQL- GROUP BY 4 23
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore 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.
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…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

756 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