SQL 2012 :: Concatenate Fields And Insert Comma Between - How To Handle Nulls
Sep 5, 2014
I thought it's easy but somehow got lost, what is the good way to insert that Comma intelligently, depending if any of var value is NULL.
-- DECLARE @var1 varchar(10) = 'alpha1', @var2 varchar(10) = 'bravo2', @var3 varchar(10) = 'charlie3',@var4 varchar(10) = 'delta4'
DECLARE @var1 varchar(10) = 'alpha1', @var2 varchar(10) = NULL, @var3 varchar(10) = 'charlie3',@var4 varchar(10) = 'delta4'
SELECT ss= (CONCAT(@var1, + iif(COALESCE(@var1,@var2,@var3,@var4) IS NULL,'',', '),
@var2, + iif(COALESCE (@var2,@var3,@var4) IS NULL,'',', '),
@var3, + iif(COALESCE( @var3,@var4) IS NULL,'',', '),
@var4) )
View 3 Replies
ADVERTISEMENT
May 15, 2008
I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.
The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.
The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.
So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).
I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.
Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?
From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.
View 2 Replies
View Related
May 17, 2004
I have a problem with a view in MS SQL Server 2000. The View concatenates 3 fields (prefix, partnumber, suffix) using hte "+" operator and one or more of these fields may sometimes be null. The problem is that a null value in any of the three fields causes the concatenation to return null even if there are valid values in on or both of the other fields. I thought I might be able to work around this by creating a view containing CASE statements to render the null values as zero length strings, which concatenate properly (e.g., "SELECT CASE WHEN PREFIX IS NULL THEN '' ELSE PREFIX END 'prefix2' FROM [table_name]"). But SQL server will not let me save a view containing a CASE statement. Anybody know how to resolve this problem?
By the way, I also tried to use UNION views to work around this but SQL server 2000 will not let me save views with UNION sataments even though it runs them properly when views with UNION statements that were created in SQL server 7.0 are imported. What's up with the inability to save a view just because it can't be rendered in the gui pane of the query builder??
View 7 Replies
View Related
Oct 27, 2007
Hello
I have this newbie question regarding SSIS:
I have a table named People(#Code, Name, eMailList), where the column eMailList has a list of email addresses separated by commas, like "someone@hotmail.com,someone@gmail.com,someone@yahoo.com".
I would like that for each Person, extract the email address info in such a way that I can insert into the following table: EMail(#Code, #ID, Address), where Address has a single email address.
How can I accomplish that?
Thanks a lot.
View 5 Replies
View Related
Oct 27, 2007
Hello
I have this newbie question regarding SSIS:
I have a table named People(#Code, Name, eMailList), where the column eMailList has a list of email addresses separated by commas, like "someone@hotmail.com,someone@gmail.com,someone@yaho o.com".
I would like that for each Person, extract the email address info in such a way that I can insert into the following table: EMail(#Code, #ID, Address), where Address has a single email address.
How can I accomplish that?
Thanks a lot.
View 6 Replies
View Related
Nov 1, 2007
we defined a xml schema. ONe node was defined as decimal data type.
When the coming data of this node in this XML file is more than 999, then it will add a comma in the figure , looking like 1,024.00.
then SSIS can not regard this as a decimal.
Any experience to share ?
i dont wanna change the schema from decimal to string.
Thanks in advance
View 4 Replies
View Related
Feb 13, 2008
Is there any way of concatenating 2 fields and a seperator, an id (integer) then "|||" then a nvarchar field, it would make my life much easier if I could come up with SQL that works in ms access as well as sql server.. Thanks
View 7 Replies
View Related
Nov 17, 2014
I have a hierarchy of product categories and I want to string them together to show the complete path (breadcrumbs) to the item. Each category/subcat must be separated buy a comma. The catch is that not all items have the same number of cats/subcats. Here is what I am currently doing, but as you can see, this results in extra commas where the subcats are null.
Code:
create table #ItemCat(
itemNo int,
Cat varchar(50),
SubCat1 varchar(50),
SubCat2 varchar(50)
[Code] ....
Is there some way to concatenate these, separated by commas, but ignoring the NULL fields? For example, ItemNo 1 should show "Kitchen, Appliances"
View 3 Replies
View Related
Mar 29, 2006
New to using MS SQL server!
I have a requirements DB. Before I came on board, the DB was seeded with requirements text and a requirements ID in separate columns. I've been asked to append the ID in front of the text:
Text - The user shall be able....
ID - 1.01.01.
Combined - 1.01.01.The user shall be able....
I've created the following query that displays the columns:
CREATE VIEW dbo.ReqID
AS
SELECT RQREQUIREMENTS.ID, RQREQUIREMENTS.REQUIREMENTNAME,
RQUSERDEFINEDFIELDVALUES.FIELDVALUE
FROM RQREQUIREMENTS LEFT OUTER JOIN
RQUSERDEFINEDFIELDVALUES ON
RQREQUIREMENTS.ID =
RQUSERDEFINEDFIELDVALUES.REQUIREMENTID
WHERE (RQUSERDEFINEDFIELDVALUES.FIELDID = 171)
The Text field is REQUIREMENTNAME
The ReqId is FIELDVALUE
Should I create an UPDATE query based on this SELECT, updating the REQUIREMENTNAME column with FIELDVALUE + REQUIREMENTNAME?
Please advise - thanks!
View 8 Replies
View Related
Feb 14, 2006
Hello,I'm wondering if there is a way to concatenate two fields or a field and astring value in a single field in a view?Where in Access I might write;[field1] & " (m3)" as TotalVolumeis there a way to do this in an SQL Server View?Thanks!
View 2 Replies
View Related
Oct 11, 2005
I have a couple columns in a table that have a data type of Text. In a query I need to concatenate these two columns into one result.
For example
Code:
SELECT (Table1.TextColumn1 + ' ' + Table1.TextColumn2) AS 'Text'
FROM Table1
However when I try this I get the error
Invalid operator for data type. Operator equals add, type equals text.
View 2 Replies
View Related
Feb 22, 2008
Does anyone have code example on how to concatenate fields in a SQL Server stored procedure?
I would like to create a stored procedure to concat (off_name ' ' off_fname ' ' off_prior) to a field to bound to a combobox.
Thanks,
Ron
Table below.
ALTER PROCEDURE CreateOfficersTable
AS
CREATE TABLE [officers](
[off_int_id] [int] Identity Primary Key,
[off_badge] [int] NULL,
[off_lname] [nvarchar] (20) NOT NULL,
[off_mname] [nvarchar] (20) NOT NULL,
[off_fname] [nvarchar] (20) NOT NULL,
[off_radio_num] [int] NULL,
[off_handle] [nvarchar] (10) NULL,
[off_unit] [nvarchar] (5) NULL,
[off_prior] [int] NULL,
[off_shift] [int] null,
[dateadded] [datetime] NULL,
[userchange] [nvarchar](10) NULL,
[changedate] [datetime] NULL
)
View 5 Replies
View Related
May 29, 2008
I am trying to add a carriage return to the end of a text field through a script. This is what I'm trying:
UPDATE Table_Name SET Column_TEXT = Column_TEXT) + '
' WHERE Column_TEXT = 'Some text'
I also tried
UPDATE Table_Name SET Column_TEXT = Column_TEXT) + '<cr>' WHERE Column_TEXT = 'Some text'
But I keep getting the error
The data types text and varchar are incompatible in the equal to operator.
Help!!
Thanks in advance
Mangala
View 3 Replies
View Related
Dec 5, 2015
I have a table where I need to concatenate all values into one field separated by a comma. If the field is null display a blank value. This is my table structure and example output
Create Table #read
(
id int
,field1 varchar(100)
,field2 varchar(100)
,field3 varchar(100)
,field4 varchar(100)
[code]...
View 5 Replies
View Related
Nov 1, 2007
I currently have 5 fields in the same SQL Server table, 4 of which pull Stock Levels from other tables and the 5th is a Total Stock field which is to add the 4 previous fields.
The problem is that some of the first 4 fields contain nulls as well as figures and when the Total Stock trys to add these together the outcome is Null, which is incorrect.
E.G
COL 1 COL 2 COL 3 COL 4 TOTALSTOCK
2 NULL 0 1 NULL
I have tried to change it to a NOT NULL Column but won't let me.
Is there a way to do an update to change NULL's to 0?
Any help would be grateful
View 4 Replies
View Related
Jul 2, 2007
I have the folliwing two fields, want tp concatenate:
=Fields!sequenceno.Value & =Fields!LogType.Value
Thank you very much for the information.
View 3 Replies
View Related
Feb 8, 2007
I am writing an inventory / asset tracking application, backed by MSSQL 2005 and need to allow end users to add/remove custom fields to track. What is the best way to go about this?
I thought about modifying the table structure at run time, but quickly decided that was a terrible idea. How does say, Share Point Services track custom columns?
Thanks
Joe
View 3 Replies
View Related
Jan 2, 2007
Hi all, I am creating a search table where the keywords field is madeup of several text fields and this is causing me some problems. I canconcatentate the text ok but i can't seem to concatenate matchingrecords here is the cursor loop. I'm not a fan of cursors but alsodidn't see another way of achieving this.declare @ptr1 varbinary(16)declare @Ptr2 varbinary(16)declare @profileid intdeclare @x intset @profileid = 0while @profileid is not nullbeginselect@profileid = min([id]),@ptr1 = MIN(textptr(text1))from #holdingwhere [id] @profileiddeclare c2 cursor fast_forward forselect textptr(searchterms), datalength(searchterms)from searchwhere search.[id] = @profileidopen c2fetch c2 into @ptr2, @xwhile @@fetch_status = 0beginupdatetext search.searchterms @ptr2 null 0 #holding.text1 @ptr1fetch c2 into @ptr2, @xendclose c2deallocate c2endThe #holding table contains the fields that i want to concatenate andthe search table is the resulting table. This example would loopthrough search and find id 1 in search and then append another fieldmatching id 1 in holding then move onto the next field in turn goingthrough the whole table.i.e.search holding result after each loopid text id text1 abc 1 def abcdef2 ghi 2 jkl ghijklWhen I run this, some of the records concatenate properly but most dontwith the same text being appended to the end of searchterms. i.e loadsof results will end up with jkl tagged onto the end. I can't figure outwhen my loop is falliing over!!! Can anyone help?Dan
View 4 Replies
View Related
Aug 9, 2004
Hi,
I have fields in my table which allow nulls. Is it efficient to not insert anything (the field automatically shows up as null in this case) and leave or store some value into it. The field is a smallint field?
Thanks
View 2 Replies
View Related
Jun 13, 2006
When I include a field from my SQL Server database, which has it's Allow Nulls value checked, in the data source of any type of control with it's Enable Editing property check, I then can not edit the record! If I remove the Allow Nulls field I can then edit it! What am I missing here?
View 1 Replies
View Related
Aug 3, 2000
I have a select statement which has to convert some date fields stored
as varchar based on critera of adding numbers to the date. What I have below
will return data show below but gives me an error message when it encounters
any data with null in the field. Is there something wrong in my conversion?
Thanks
declare @tdate varchar(10)
declare @rldate datetime
select @tdate = 20000630
select @rldate = @rldate + 2
select patient_.df_admit_date, patient_.df_ppdb_date from patient_
where (convert(datetime, df_ppdb_date, 101)+ 12) >= @tdate or
(convert(datetime, df_admit_date, 101) + 2 ) >= @tdate
19980910 20000626
19981215 20000627
19590114 20000621
20000629 NULL
20000706 NULL
20000711 NULL
(6 row(s) affected)
Server: Msg 242, Level 16, State 3, Line 5
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
View 1 Replies
View Related
Nov 12, 2007
I am running an insert in a stored procedure, but need tio concatenate on of the fields. How do I do that? Below is an example:
INSERTsam.dbo.product
(productName,
ProdDesc,
TransDateBegin,
TransDateEnd
)
SELECTCode, Description + 'the product is displayed' + <br><br>'To purchase this <a href=/purchaseitem.aspx?' >here</a>
FROMweb.dbo.order_codes AS OC
WHEREproductName = code
Any ideas how I can get the select statement working?
SB
View 4 Replies
View Related
Jan 28, 2015
I have a table where I need to concatenate and sum based on conditions given below .
CREATE TABLE #DimOrder
(
[ID] [INT] IDENTITY(1,1) NOT NULL,
[OrderID] [INT] NOT NULL,
[SalesRegion] [VARCHAR](5) NULL,
[OrderType] [INT] NOT NULL,
[Code] ....
Example
SELECT 1296 AS OrderID, 2547 AS ProjectID,364 AS ProductID,6 AS FormatID,66 AS MEdiaTypeID,'Fast Track,Fast Track' AS ProductComment,'sq mi' AS UOM,'Y' AS FormatName,'LTO4_800' AS MEdia ,41.67000 AS aPPLIEDRATE
IF OrderID,PrjectID,ProductID,FormatID,MediaTypeID AND AppliedRate AND ProductComment IS different , THEN I want TO concetenate ProductComment AND sum AppliedRate
Example
SELECT 1207 AS OrderID, 2351 AS ProjectID,364 AS ProductID,6 AS FormatID,32 AS MEdiaTypeID,'no charge,final volume' AS ProductComment,'sq mi' AS UOM,'Y' AS FormatName,'3590E' AS MEdia ,55.56000 AS AppliedRate
View 2 Replies
View Related
Jul 23, 2015
I want to CONCATENATE a field if the rows have the same Id. For example the data looks like
ID Name Identifier
1 ken English
1 Jack French
2 Jamie Spanish
3 Shirley German
The data should look like this if the IDs are same. I am concatenating the Identifier column with the name if the ids are same
ID Name Identifier
1 Ken English English
1 Jack French French
2 Jamie Spanish
3 Shirley German
View 8 Replies
View Related
Jul 30, 2015
I have a column name Classname and I would like to to Concatenate value.
ID ClassName
1 Class A
2 Class B
3 Class C
4 Class D
I need a output in ssrs report with title like (Class A, Class B, Class C, Class D) .
Can I do in SSRS as well ?
I tried join function in ssrs and I am getting #error join(Field!classname,",")
View 1 Replies
View Related
Nov 19, 2007
Hi,
I have a table Called Actcodes and has 2 columns Name and Description...
And i want to insert the data from this table called PlanDBF to ActCode..
and this my insert statement...
INSERT INTO Statements..AscActCodes
(
Name,
Description
)
SELECT
ACT_CODE1,
ACT_DESC1
ACT_CODE2,
ACT_DESC2,
ACT_CODE3,
ACT_DESC3,
ACT_CODE4,
ACT_DESC4,
ACT_CODE5,
ACT_DESC5,
ACT_CODE6,
ACT_DESC6,
ACT_CODE7,
ACT_DESC7,
ACT_CODE8,
ACT_DESC8,
ACT_CODE9,
ACT_DESC9,
ACT_CODE10,
ACT_DESC10,
ACT_CODE11,
ACT_DESC11,
ACT_CODE12,
ACT_DESC12,
ACT_CODE13,
ACT_DESC13,
ACT_CODE14,
ACT_DESC14,
ACT_CODE15,
ACT_DESC15,
ACT_CODE16,
ACT_DESC16,
ACT_CODE17,
ACT_DESC17,
ACT_CODE18,
ACT_DESC18,
ACT_CODE19,
ACT_DESC19,
ACT_CODE20,
ACT_DESC20
FROM
PlanDBF
each actcode and its description is gonna be different and so i am not sure how to do this 1 - many column insert.
Any help will be appreciated.
Regards
Karen
View 7 Replies
View Related
Apr 22, 2014
I have a table named item that looks like this:
PartNumberDescriptionI.DateCreated
1A1XXX ALPHA1 1/1/2014
2A2XXX ALPHA2 1/5/2014
3A3XXX ALPHA3 1/12/2014
1B1XXX BRAVO1 1/22/2014
2B2XXX BRAVO2 1/28/2014
3B3XXX BRAVO3 2/2/2014
And a second table named orders that looks like this:
OrderNoPartNumberQtyO.DateCreated
1CHXX1 1A1XXX 2 1/8/2014
1CHXX2 2A2XXX 3 1/8/2014
1CHXX3 2A2XXX 1 1/20/2014
2CHXX1 1B1XXX 4 2/10/2014
2CHXX2 2B2XXX 2 2/10/2014
2CHXX3 2B2XXX 1 2/22/2014
I need to pull all records from the Item table and then I need to populate the most recent OrderNo and O.DateCreated. I got this far but if there is a part in the item table that does not have an order against it, I do not get a value and my goal is to see any parts that have not been ordered in the last year. Something like this:
SELECT I.PartNumber, I.Description, I.DateCreated
FROM item I
CROSS APPLY (SELECT TOP 1 O.OrderNo, O.DateCreated
FROM Orders O
WHERE O.PartNumber = I.PartNumber ORDER BY O.DateCreated DESC) O
PartNumberOrderNoO.DateCreated
1A1XXX 1CHXX1 1/8/2014
2A2XXX 1CHXX3 1/20/2014
3A3XXX NULL NULL
1B1XXX 2CHXX1 2/10/2014
2B2XXX 2CHXX3 2/22/2014
3B3XXX NULL NULL
View 4 Replies
View Related
Jul 4, 2014
I'm trying to find the previous / next values of a field when it is having null values. I'm not able to use lag / lead as they don't ignore nulls.
declare @t table (v1 int , v int, v2 datetime)
insert into @t values(4,10, '2014-01-01 10:00:00')
insert into @t values(5, 20,'2014-01-01 10:05:00')
insert into @t values(6, null, '2014-01-01 10:10:00')
insert into @t values(7, null,'2014-01-01 10:15:00')
[code]....
The next values are having nulls. i'm trying to get next value of 'v' ignoring nulls.
View 6 Replies
View Related
Mar 4, 2007
It's probably a most basic operation but I can't find how to enable this. Using SQL Server 2005
eg: how would I get this sort of step to work?
INSERT INTO company VALUES (10001,"Apps'r'Us");
where the schema is
(companyID int,
companyName varchar(50))
View 1 Replies
View Related
Oct 31, 2007
I have been battling this for 2 days! I could write a book on what does not work. I have even tried entering the data manually through the 'Open Table' SSMS function from the Object Explorer. I only have 5 records in the table. The first 4 I added a week or so ago during dev from the ASP.Net 2.0 (w VB) website that is the front end for this DB. I use a DAL between the site and DB.I added two new fields the other day and everything seemed to work fine. Now I get 'Siteid CANNOT BE NULL'. I abandoned attempts to Insert from the Website. The following was done directly through SSMS.I switched all of the columns (except PK) to allow nulls. I could add/update a record, no problem, even with directly typed data in some fields. I could also add one with only one field populated (some have defaults, some do not).Here is the table code:USE [bro122]GO/****** Object: Table [dbo].[tbsaDrem] Script Date: 10/31/2007 18:46:34 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[tbsaDrem]([DremID] [int] IDENTITY(1,1) NOT NULL,[CustID] [int] NOT NULL CONSTRAINT [DF_tbsaDrem_CustID] DEFAULT ((0)),[RealityYN] [bit] NULL CONSTRAINT [DF_tbsaDrem_ContractYN] DEFAULT ((0)),[DreamName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tbsaDrem_DreamName] DEFAULT ('New (Modify Name)'),[SiteID] [int] NOT NULL,[SitePremPric] [money] NULL CONSTRAINT [DF_tbsaDrem_SiteBasePric] DEFAULT ((0)),[NbhdBasePric] [money] NULL CONSTRAINT [DF_tbsaDrem_NbhdBasePric] DEFAULT ((0)),[PlanCtlgID] [int] NOT NULL,[PlanPric] [money] NULL CONSTRAINT [DF_tbsaDrem_PlanPric] DEFAULT ((0)),[SpecTotl] [money] NULL CONSTRAINT [DF_tbsaDrem_SpecTotl] DEFAULT ((0)),[DremTotl] AS ((([NbhdBasePric]+[SitePremPric])+[PlanPric])+[SpecTotl]) PERSISTED,[Modified] [datetime] NOT NULL CONSTRAINT [DF_tbsaDrem_Modified] DEFAULT (getdate()),[ModUser] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tbsaDrem_ModUser] DEFAULT ('None'),[Created] [datetime] NOT NULL CONSTRAINT [DF_tbsaScenario_CreatedDate] DEFAULT (getdate()),[ts] [timestamp] NOT NULL,CONSTRAINT [PK_tbsaDremID] PRIMARY KEY CLUSTERED ([DremID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This is flagged with a Trigger when a Dream becomes a Reality. ( contract is ratified.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tbsaDrem', @level2type=N'COLUMN',@level2name=N'RealityYN'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Heading Data for Each Dream (Scenario) for Customers. Each Dream can and must have only one Customer, Site, Plan. Dream Items are stored in the tbsaDreamItem table.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tbsaDrem'GOALTER TABLE [dbo].[tbsaDrem] WITH CHECK ADD CONSTRAINT [FK_tbsaDrem_tbjoSite] FOREIGN KEY([SiteID])REFERENCES [dbo].[tbjoSite] ([SiteID])GOALTER TABLE [dbo].[tbsaDrem] CHECK CONSTRAINT [FK_tbsaDrem_tbjoSite]GOALTER TABLE [dbo].[tbsaDrem] WITH CHECK ADD CONSTRAINT [FK_tbsaDrem_tbpdPlanCtlg] FOREIGN KEY([PlanCtlgID])REFERENCES [dbo].[tbsaCtlg] ([CtlgID])GOALTER TABLE [dbo].[tbsaDrem] CHECK CONSTRAINT [FK_tbsaDrem_tbpdPlanCtlg]GOALTER TABLE [dbo].[tbsaDrem] WITH CHECK ADD CONSTRAINT [FK_tbsaDrem_tbsaCust] FOREIGN KEY([CustID])REFERENCES [dbo].[tbsaCust] ([CustID])GOALTER TABLE [dbo].[tbsaDrem] CHECK CONSTRAINT [FK_tbsaDrem_tbsaCust]Any help is enormously appreciated!!!
View 4 Replies
View Related
Mar 29, 2006
I’m looking for feedback on the Best/Right way to Insert nulls into SQL dateTime field in SQL DB from a web UI textbox.
Option 1: Presently implemented:
Dim dtFollowUpDate = IIf(dtDateFollowUp.Text = "", System.Data.SqlTypes.SqlDateTime.Null, dtDateFollowUp.Text)
Although ithis does what is needed it generates the following inner exception
ParamValue {System.Data.SqlTypes.SqlDateTime} Object[System.Data.SqlTypes.SqlDateTime] {System.Data.SqlTypes.SqlDateTime} System.Data.SqlTypes.SqlDateTimeDayTicks
<error: an exception of type: {System.Data.SqlTypes.SqlNullValueException} occurred> Integer IsNull True Boolean
Option 2:
Dim dtFollowUpDate = IIf(dtDateFollowUp.Text = "", System.DBNull.Value, dtDateFollowUp.Text)
No exceptions, no problems that I have seen yet.
Option 3:
Dim dtNull As System.Data.SqlTypes.INullable
Dim dtFollowUpDate = IIf(dtDateFollowUp.Text = "", dtNull, dtDateFollowUp.Text)
No exceptions, no problems that I have seen yet.
Any advice would be greatly appreciated!
Thanks
Craig
CTARP2
View 1 Replies
View Related
Jan 4, 2010
I'm a developer working on PHP - MSSQL 2008 combined platform. While running a procedure from a webpage I'm encountering the error as below.
My Query:
DECLARE @ReturnValue INT
EXEC @ReturnValue = S_AccountsBatchActivation @FirstName='abc', @LastName='abc', @Address='abc', @City='abc', @State='abc', @Country='India', @Phone='1', @BatchId='6502'
SELECT ReturnValue=@ReturnValue
Error
Cannot insert the value NULL into column 'TariffId', table 'tempdb.dbo.#tbl
column does not allow nulls. INSERT fails.
Though the same query when run from SSMS runs fine:
Execute S_AccountsBatchActivation
@FirstName='abc',
@LastName='abc',
@Address='abc',
@City='abc',
@State='abc',
@Country='India',
@Phone='11',
@BatchId=6502
[code]...
On my end I've tried out with ANSI_NULL AND ANSI_NULLS settings being both ON and OFF but it didn't worked.
View 9 Replies
View Related
Sep 18, 2015
We have a table to 100M rows and up until now we were fine with an non clustered index a varchar(4000) because we never went above 900 bytes (yes it is a bad design).We have the need to support international character sets now so the column was updated to nvarchar(4000) and now we have data past the 900 byte limit.
The data is long, seems useless but is needed by the business and they need to be able to search "where bigcolumn like 'test%'". With an index, even with a huge amount of data, it was 'fast'. Now of course without an index it is unusable. The wildcard is always at the end of the search. I made a full text index on the column and basic queries such as: select * from ourtable where contains(bigcolumn, 'AReallyLongStringofTextHere') works fine unless there is a space in the data. We loose thousands of returned rows because of spaces in the data.
I have tried select * from ourtable where contains(bigcolumn, '"AReallyLongStringofTextHere that includes spaces"') but not all of the data is returned. I get 112 rows with the contains statement. The table scanning statement of "select * from ourtable where bigcolumn like 'AReallyLongStringofTextHere that includes spaces%' returns 1939 rows.I understand that a full text index is breaking the long string up since it contains spaces. Is there a way to retain the entire string as 1 index entry or is there a way to fix my query to return all of the rows?
View 9 Replies
View Related
