insert/update NULL instead of ''

Hi,
due to special reasons I have to ensure, that in insert and update
statements for varchar-columns (which allow NULL-values) the value ''
automatically becomes replaced by NULL before the records have been
inserted/updated. (This because I have to convert a large application from
another database -which automatically substituted '' by NULL- to SqlServer
2000 SP4).
My first thought was to find a setting in SqlServer server. But I didn't
found one. Is there any?
My second thought was to find a setting in the OLEDB-provider. But I didn't
found one. Is there any?
(I'm using OLEDB, but not ADO)
My third thought has been to define triggers for that case (my very first
ones). I did it as listed below. Is this the correct way? Or is there a
better way? Or perhaps a way with better performance?
create table test (primkey integer not null, testcol1 varchar(10), testcol2
varchar(10))
create trigger test_trigger_u on test instead of update
as
update test set primkey = i.primkey,
testcol1 = nullif(i.testcol1, ''),
testcol2 = nullif(i.testcol2, '')
from test t inner join inserted i
on t.primkey = i.primkey
create trigger test_trigger_i on test instead of insert
as
insert into test (primkey,
testcol1,
testcol)
select primkey,
nullif(testcol1, ''),
nullif(testcol2, '')
from inserted
Regards,
RainerRainer,
Why not just evaluate the incoming value (i.e., stored procedure parameter)
if the value IS NULL replace it with ''. You could optionally use a trigger
as well but the former would probably give better performance and should
probably be used unless you can't control the input method/application.
HTH
Jerry
"Rainer Ebert" <rainer_ebert_at_arcor.de> wrote in message
news:%23127HJC1FHA.2884@.TK2MSFTNGP09.phx.gbl...
> Hi,
> due to special reasons I have to ensure, that in insert and update
> statements for varchar-columns (which allow NULL-values) the value ''
> automatically becomes replaced by NULL before the records have been
> inserted/updated. (This because I have to convert a large application from
> another database -which automatically substituted '' by NULL- to SqlServer
> 2000 SP4).
> My first thought was to find a setting in SqlServer server. But I didn't
> found one. Is there any?
> My second thought was to find a setting in the OLEDB-provider. But I
> didn't found one. Is there any?
> (I'm using OLEDB, but not ADO)
> My third thought has been to define triggers for that case (my very first
> ones). I did it as listed below. Is this the correct way? Or is there a
> better way? Or perhaps a way with better performance?
> create table test (primkey integer not null, testcol1 varchar(10),
> testcol2 varchar(10))
> create trigger test_trigger_u on test instead of update
> as
> update test set primkey = i.primkey,
> testcol1 = nullif(i.testcol1, ''),
> testcol2 = nullif(i.testcol2, '')
> from test t inner join inserted i
> on t.primkey = i.primkey
> create trigger test_trigger_i on test instead of insert
> as
> insert into test (primkey,
> testcol1,
> testcol)
> select primkey,
> nullif(testcol1, ''),
> nullif(testcol2, '')
> from inserted
> Regards,
> Rainer
>|||Jerry,
our application does not use stored procedures. It uses sql-statements to
select, insert, update and delete data. The application should run against
the previous database (Gupta SQLBase) and against MS SqlServer (depending on
the customer). This goal should be reached with as few source code
modifications as possible. I know, that I can change each affected
insert/update statement in the sourcecode and replace '' by NULL. But I'm
looking for a way to avoid doing this.
Do you think, the triggers are o.k.?
Do you know a better way?
regards,
Rainer
P.S.: By the way, I want to replaye '' by NULL, not NULL by ''
"Jerry Spivey" <jspivey@.vestas-awt.com> schrieb im Newsbeitrag
news:ObsNBRC1FHA.3124@.TK2MSFTNGP12.phx.gbl...
> Rainer,
> Why not just evaluate the incoming value (i.e., stored procedure
> parameter) if the value IS NULL replace it with ''. You could optionally
> use a trigger as well but the former would probably give better
> performance and should probably be used unless you can't control the input
> method/application.
> HTH
> Jerry
> "Rainer Ebert" <rainer_ebert_at_arcor.de> wrote in message
> news:%23127HJC1FHA.2884@.TK2MSFTNGP09.phx.gbl...
>|||I believe Rainer is actually looking for a way to *insert* null values
instead of the supplied empty strings rather than trying to prevent null
values from being inserted.
NULLIF is the way to go. Yet, I'd suggest handling that in the insert
procedures, rather than in the triggers, and use the triggers if changing th
e
procedures cannot be done (i.e. if there aren't any).
Or did I miss something?
ML|||Functionality wise...a trigger should work fine.
HTH
Jerry
"Rainer Ebert" <rainer_ebert_at_arcor.de> wrote in message
news:uzfHiaC1FHA.904@.tk2msftngp13.phx.gbl...
> Jerry,
> our application does not use stored procedures. It uses sql-statements to
> select, insert, update and delete data. The application should run against
> the previous database (Gupta SQLBase) and against MS SqlServer (depending
> on the customer). This goal should be reached with as few source code
> modifications as possible. I know, that I can change each affected
> insert/update statement in the sourcecode and replace '' by NULL. But I'm
> looking for a way to avoid doing this.
> Do you think, the triggers are o.k.?
> Do you know a better way?
> regards,
> Rainer
> P.S.: By the way, I want to replaye '' by NULL, not NULL by ''
> "Jerry Spivey" <jspivey@.vestas-awt.com> schrieb im Newsbeitrag
> news:ObsNBRC1FHA.3124@.TK2MSFTNGP12.phx.gbl...
>|||I believe Rainer is actually looking for a way to *insert* null values
instead of the supplied empty strings rather than trying to prevent null
values from being inserted.
NULLIF is the way to go. Yet, I'd suggest handling that in the insert
procedures, rather than in the triggers, and only use triggers if changing
the procedures is not an option (i.e. if - for some insane reason - there
aren't any).
Or am I missing something?
ML|||Ahh...ML...ok.
Something like:
CREATE TABLE #TEST
(ID INT NOT NULL,
VAL VARCHAR(10))
DECLARE @.VAL VARCHAR(10)
SET @.VAL = ''
INSERT #TEST(ID,VAL)
VALUES(1,NULLIF(@.VAL,''))
SELECT * FROM #TEST
--DROP TABLE #TEST
then?
HTH
Jerry
"ML" <ML@.discussions.microsoft.com> wrote in message
news:D577E511-2347-4A2B-9F26-FA578CFEF71B@.microsoft.com...
>I believe Rainer is actually looking for a way to *insert* null values
> instead of the supplied empty strings rather than trying to prevent null
> values from being inserted.
> NULLIF is the way to go. Yet, I'd suggest handling that in the insert
> procedures, rather than in the triggers, and only use triggers if changing
> the procedures is not an option (i.e. if - for some insane reason - there
> aren't any).
> Or am I missing something?
>
> ML|||As the modern German would say: Wonderbra!
ML|||Or as Cosmo would say: Wonderbro! ;-)
Jerry
"ML" <ML@.discussions.microsoft.com> wrote in message
news:1D8DEB5B-C763-4B1F-A18E-2E87411B674A@.microsoft.com...
> As the modern German would say: Wonderbra!
>
> ML|||Hmm... which one? :)
http://en.wikipedia.org/wiki/Cosmo
MLsql

INSERT special characters into SQL Database (Protect my Tic)

Greetings!

I'm using classic ASP and an insert statement to save bulletin messages to a database that can be updated or viewed later. At first I thought it worked perfectly so I took some sample messages and one of them kept erroring out.

I've pretty much determined that it hates the ' character.. and I'm sure it'd hate other special characters as well so here's my question. How do I insert special characters into a database? My co-worker suggested HTMLencode and decode but really that's not going to work.Was it a quote that didn't go through?|||

Quote:

Originally Posted by Arielle

Greetings!

I'm using classic ASP and an insert statement to save bulletin messages to a database that can be updated or viewed later. At first I thought it worked perfectly so I took some sample messages and one of them kept erroring out.

I've pretty much determined that it hates the ' character.. and I'm sure it'd hate other special characters as well so here's my question. How do I insert special characters into a database? My co-worker suggested HTMLencode and decode but really that's not going to work.

hi Arielle,

if you are using sql server2000 for backend activities, it accepts all the special characters while inserting a record,except for ' single quotes. You would have noticed that it throws errors, when we try to insert something like this:

eg:
insert into <tablename> values(val1,val2,'the student's of csc dept have scored the highest')

unclosed quotation mark before the character string...
incorrect syntax near...

if you want to insert your message that contains apostophe character from your asp page to sql server table, then you will have to use the Replace() function in your asp page.

Replace(string,find,replacewith)
where,
string: the string to be searched
find: the part of the string that will be replaced
replacewith: the replacement substring

eg: dim txt,crreason
txt=request("txtCrReason")
crreason=Replace(txt,"'","''")
...and send the value stored in the variable 'crreason' to your insert query.

try this and let me know if it has solved your problem. Gud Luck!

cheers,
jai

insert special characters in database

I am writing a web application and everytime I attempt to insert some special characters in the database I get errors. Is there a way around this?Depends what you mean. If you mean unicode and you are attempting to insert into CHar or VarChar fields then you need to use NVarChar or NChar. BoL has all the gory details. Otherwise you'll need to porovide more details.

HTH|||it seems everytime I include an apostrophe the system gives me an error..not sure why this is happening

my second question is-- when someone types this:

I cant wait
to leave
this place

--It come out like this
I cant wait to leave this place

How can I make the system print all the tab keys and return carriage?

thank you for any responses|||You need to escape apostrophes.

"I can't wait to leave this place"
must be submitted as
"I can''t wait to leave this place"

HTH|||The latter problem is probably a presentation issue - your FE needs to interpret the carriage returns & line feeds.|||hi everybody,

This is Rajen, hope it will be nice sharing ideas with u all. Feeling nice to join u all .:beer: