Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Ads
Starting Member
23 Posts |
 Posted - 2012-06-19 : 11:03:58
|
| Hi,I have values being entered from an EXCEL spread sheet: VALUES ('"& paddrcode &"', '"& paddritem &"', '"& FormatStr(rsGetInvoiceContact.Fields.Item("Address Line 1").Value) + ", " + (rsGetInvoiceContact.Fields.Item("Address Line 2").Value) &"',When both values exist in address line 1 and 2 they insert to the database perfectly 'TEST, TEST' however when only address line 1 exists no data is inserted? How can I allow it to enter just address line 1 'TEST,' |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-06-19 : 11:23:13
|
| probably better to store these address parts in separate database columns rather than keeping them all concatenated together as a single value. But for your concatenation you will need to check each component value for NULL and replace the NULLs with an empty string.Be One with the OptimizerTG |
 |
|
|
Ads
Starting Member
23 Posts |
Posted - 2012-06-19 : 12:25:50
|
| Thanks for the quick response!Unfortunately I'm unable to adjust the columns as it's a massive MRP system, why they developed it like that I will never know! How do a test to see if it's a null value? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-06-19 : 17:35:18
|
| Does that code you posted resolve to a valid cell value when only address line 1 exists? If so, then how exactly does this value "get inserted" to Sql Server? If that Excel expression does not successfully resolve to a value then you'll have to break that down to individual expressions to see which one is causing the empty value. Once you know which expression is breaking down you'll have to fix it with some excel function like ISBLANK or ISERROR...Be One with the OptimizerTG |
|
|
|
Ads
Starting Member
23 Posts |
Posted - 2012-06-20 : 05:08:50
|
| Address line 2 is the issue as it's only an optional field on our website! Will I need to take that out and some how convert it to an empty value rather than a NULL value? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-06-20 : 13:24:19
|
I can't answer your question because you didn't answer any of mine. But as far as t-sql code goes yes, if you concatenate a NULL with a string the result is NULL. You have to replace the NULL with anything non-null:select 'addr1' + nullselect 'addr1' + coalesce(null,'')OUTPUT:------NULL------addr1 Be One with the OptimizerTG |
|
|
|
|
|
|
Values not showing