asked on
assign null value to string in C# and insert SQL Server decimal column that accepts null values
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
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
C#ASP.NETMicrosoft SQL Server 2005
Last Comment
SSschultz
ASKER
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.
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.
Can you post that bit of logic so that I (we) can see what you are working with?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
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.
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.
ASKER
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
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
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
SqlConnection conn = new SqlConnection(connectionSt
string mySQLstring = "INSERT INTO [Table1] (Col1,Col2)values " +
"(" + "'" +stringTEST1 +"'" +"," + "'"+ stringTEST2 + "'" + ");";
try{ SqlCommand cmd = new SqlCommand(mySQLstring,con
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{streamwriter to capture exception here}
conn.Close()
string mySQLstring = "INSERT INTO [Table1] (Col1,Col2)values " +
"(" + "'" +stringTEST1 +"'" +"," + "'"+ stringTEST2 + "'" + ");";
try{ SqlCommand cmd = new SqlCommand(mySQLstring,con
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{streamwriter to capture exception here}
conn.Close()
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a questionASKER
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.
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.
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.
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
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.
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.
ASKER
kaufmed,
Thanks much.. it works great. thanks again for your help and explaining the issue clearly.
Closing question with your solution.
Thanks much.. it works great. thanks again for your help and explaining the issue clearly.
Closing question with your solution.
ASKER
Thanks for excellent solution and explanation
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
e.g.
Open in new window