I am trying to use INSERT OR REPLACE but one of the key fields contains an empty space.
For example:
LastName = 'Jones'
FirstName = 'Mary'
MiddleName = ''
Using the code:
If the Row already exists, the INSERT OR REPLACE Duplicates the row.
However, if I give Mary Jones a middle name (which is included in the unique key), IT WORKS!
Is there a way to make this work?
Thanks,
Rusty
For example:
LastName = 'Jones'
FirstName = 'Mary'
MiddleName = ''
Using the code:
B4X:
CREATE TABLE [Patients] ( [OrganizationID] nvarchar(50) NOT NULL COLLATE NOCASE, [GroupID] nvarchar(50) NOT NULL COLLATE NOCASE, [FacilityID] nvarchar(50) NOT NULL COLLATE NOCASE, [MRN] nvarchar(50) NOT NULL COLLATE NOCASE, [LastName] nvarchar(50) NOT NULL COLLATE NOCASE, [FirstName] nvarchar(50) NOT NULL COLLATE NOCASE, [MiddleName] nvarchar(50) NOT NULL COLLATE NOCASE, [DOB] datetime NOT NULL COLLATE NOCASE, [Gender] smallint NOT NULL, [UniqueKey] nvarchar(20) COLLATE NOCASE, [Language] varchar(3) COLLATE NOCASE DEFAULT 'Eng', [Address1] nvarchar(50) COLLATE NOCASE, [Address2] nvarchar(50) COLLATE NOCASE, [City] nvarchar(50) COLLATE NOCASE, [State] nvarchar(20) COLLATE NOCASE, [ZipCode] nvarchar(12) COLLATE NOCASE, [OfficePhone] nvarchar(20) COLLATE NOCASE, [HomePhone] nvarchar(20) COLLATE NOCASE, [MobilePhone] nvarchar(20) COLLATE NOCASE, [EMail] nvarchar(50) COLLATE NOCASE, [SlingDate] datetime DEFAULT '1900-01-01', [Occurrences] integer NOT NULL, [Persistence] integer NOT NULL, [SurveyIDs] nvarchar COLLATE NOCASE, [Photo] blob(2147483647), [Year] varchar(4) NOT NULL COLLATE NOCASE DEFAULT '', [Make] nvarchar(50) COLLATE NOCASE DEFAULT '', [Model] nvarchar(50) COLLATE NOCASE DEFAULT '', [PurchaseDate] datetime COLLATE NOCASE DEFAULT '1900-01-01', [LastAnswers] nvarchar COLLATE NOCASE, PRIMARY KEY ([OrganizationID], [GroupID], [FacilityID], [MRN], [LastName], [FirstName], [MiddleName], [DOB], [Gender]) )
INSERT OR REPLACE INTO [Patients] VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
[OSNC, OSNC, ALL, MJ2, Jones, Mary, , 1952-08-18 00:00:00 00:00:00, 2, null, Eng, 123 Maple, , Denver, COLORADO, 80111-, (303) 351-3349, (303) 351-3856, , [email protected], 1900-01-01 00:00:00, 0, 0, [[]], null, , , , 1900-01-01 00:00:00, abc
If the Row already exists, the INSERT OR REPLACE Duplicates the row.
However, if I give Mary Jones a middle name (which is included in the unique key), IT WORKS!
Is there a way to make this work?
Thanks,
Rusty