?
Solved

assign null value to string in C# and insert SQL Server decimal column that accepts null values

Posted on 2012-12-31
13
Medium Priority
?
4,541 Views
Last Modified: 2013-01-02
Hi,
Need some help. This apparently is difficult to do.
I've tried many things which do not work

I'll outlline what I've tried so you don't list it as possible solution.

PROBLEM:  I'm trying to assign a null value to a string in C# that already has a value (the reason is for certain conditions to force real NULL value)
                    Then insert into SQL Server into a column that is decimal format (6,2) and accepts nulls.

 When I assign string to = "0" it sends to SQL table but I want actual NULL value to show up. And I've tried "NULL" and that doesn't work because it thinks it's a varchar into numeric(decimal).  It's important that the sQL column stays decimal because most of data is decimal format and works, but some cases I need to force an actual NULL value into SQL column. Have tried many suggestions on interent and nothing seems to work.

Thanks
0
Comment
Question by:SSschultz
[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
  • 8
  • 5
13 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 38732926
Assign DBNull.Value to the parameter you are inserting.

e.g.

SqlCommand cmd = ...

cmd.Parameters.AddWithValue("@theParam", DBNull.Value);

Open in new window

0
 

Author Comment

by:SSschultz
ID: 38732952
thanks for response.

Is there a way to do it above the insert sql command line? because the majority of the data is in decimal format and inserts correctly.

I tried your suggestion and still get error on the cmd insert exception line as "error in converting varchar to numeric.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 38733039
Can you post that bit of logic so that I (we) can see what you are working with?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:SSschultz
ID: 38733055
string x;
x= "123";

if(x=="Nothing") {x=null;}

later in C#

cmd... insert the string x

Is it possible that something is wrong in SQL server 2005?
I have it set for decimal(6,2) and accepts Nulls.

The program works when the string is "4.43" and it inserts correctly into decimal(6,2) column in SQL server 2005.
0
 

Author Comment

by:SSschultz
ID: 38733067
when I change
 if(x=="Nothing") {x="0";}

it will insert the 0 into SQL server when the sting ="Nothing"
problem is setting the x to null and getting it to accept the insert into SQL as Null
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 38733296
I'm sorry, I meant can you post the part where you are interacting with the database. The connection string is unimportant; just the part dealing with the SqlCommand object (along with the query and any parameters) is what I was hoping to see.
0
 

Author Comment

by:SSschultz
ID: 38733322
SqlConnection conn = new SqlConnection(connectionString);
string mySQLstring = "INSERT INTO [Table1] (Col1,Col2)values " +
"(" + "'" +stringTEST1 +"'" +"," + "'"+ stringTEST2 + "'" + ");";

try{ SqlCommand cmd = new SqlCommand(mySQLstring,conn);
        cmd.ExecuteNonQuery();
}
catch (Exception ex)
{streamwriter to capture exception here}
conn.Close()
0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 2000 total points
ID: 38733421
OK, I was afraid you were doing string concatenation. You certainly can continue with this approach, but it's a bit easier to read and work with (not to mention safer) if you use parameterized queries. Try this:

SqlConnection conn = new SqlConnection(connectionString);
string mySQLstring = "INSERT INTO [Table1] (Col1,Col2) VALUES (@col1Param, @col2Param)";

try
{
    SqlCommand cmd = new SqlCommand(mySQLstring,conn);
    
    cmd.Parameters.AddWithValue("@col1Param", DBNull.Value);
    cmd.Parameters.AddWithValue("@col2Param", 0.00);

    cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
    streamwriter to capture exception here
}

conn.Close()

Open in new window


One benefit of this approach is that you can let the framework worry about how to format strings and numbers to fit the query. You will notice that I don't have any quotes around either parameter within the query. If you were to pass a number (in the AddWithValue call), it will be filled in properly; likewise, if you were to pass a string, then it would be quoted properly when it arrives at the DB.

Note:  I put in some placeholder data above to demonstrate using DBNull.Value. Change these accordingly. If you want to send a numeric value, and that value starts off as string, then you will need to cast it to a numeric type before assigning it to the parameter.

e.g.

...
    cmd.Parameters.AddWithValue("@col2Param", Convert.ToInt32(stringTEST2));
...

Open in new window

0
 

Author Comment

by:SSschultz
ID: 38733535
Ok, thanks..
Do you have idea how to do it with current string concatenation method i'm using?
I'll try the parameters as you have shown.. I seem to remember where was a reason why I had to use string concatenation, but I will try your idea.

But if you have quick idea on how to pass null for string concatenation in meantime that would be great.  It will take me couple hours to get back to this to test out.
thanks for your help.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 38733604
To do it with string concatenation , you will have to use the word NULL, but make sure that there are no quotes around it.

e.g.

string example1 = "NULL";
string query1 = "INSERT INTO [table] VALUES (" + example1 + ")";  // there are no single quotes around the value here

string example2 = "hello world!";
string query2 = "INSERT INTO [table] VALUES ('" + example2 + "')";  // there are single quotes around the value here because you are passing an actual value, and not NULL

string this_is_the_string_null = "'NULL'";  // single quotes inside of double-quotes
string this_is_DB_null = "NULL";  // double quotes only

Open in new window

0
 

Author Comment

by:SSschultz
ID: 38733655
Oh fudge...  so the insert statement would have to know whether it was null or not in order for the correct formating with or without " "...?  (I have many variable per insert line)  
I now get your point about @parameters.  And will switch to see if I can get that to work.

I will end awarding points to you.. I want to test and get it to work and may have couple of quick followup ?'s.

thanks. I'll send more if I'm stuck with parameters or award points and close if I can get right away.
0
 

Author Comment

by:SSschultz
ID: 38737108
kaufmed,
Thanks much.. it works great. thanks again for your help and explaining the issue clearly.
Closing question with your solution.
0
 

Author Closing Comment

by:SSschultz
ID: 38737112
Thanks for excellent solution and explanation
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

752 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