473,320 Members | 1,863 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQL: IF EXISTS... using TEXT field

I'm trying to use a IF EXISTS (SELECT...) UPDATE... ELSE INSERT... sql
query.

This works until I try to use it with a text field type. When I do that, I
get an error saying ntext can't be used in this type of aggregate query.

Ugh.

So, is there solution to this via SQL? Or do I manually need to write three
separate queries and connections: the first to see if it exists, thena
separate UPDATE or INSERT function?

-Darrel
May 27 '06 #1
4 2293
dmm
not sure this is the correct group, but you should use 'if select 1 from xxx
where xxxx' as in the code below:
--won't work
if exists
(
select
cast ('fred' as ntext)
where 1 = 1
)
begin
print 'exists'
end
else
print 'doesnt exist'

--so use select 1 with the same where conditions
if exists
(
select
1
where 1 = 1

)
begin
print 'exists'
end
else
print 'doesnt exist'
HTHs

"Darrel" <no*****@nospam.com> wrote in message
news:%2***************@TK2MSFTNGP03.phx.gbl...
I'm trying to use a IF EXISTS (SELECT...) UPDATE... ELSE INSERT... sql
query.

This works until I try to use it with a text field type. When I do that, I
get an error saying ntext can't be used in this type of aggregate query.

Ugh.

So, is there solution to this via SQL? Or do I manually need to write
three separate queries and connections: the first to see if it exists,
thena separate UPDATE or INSERT function?

-Darrel

May 27 '06 #2
> not sure this is the correct group, but you should use 'if select 1 from
xxx where xxxx' as in the code below:


I'm not quite following that. I'm not using a SP, btw. Here's my actual
query I'd like to use:

IF EXISTS (SELECT varType, longVarValue FROM table WHERE varType =
'siteMenu') UPDATE table SET longVarValue = ? WHERE varType = 'siteMenu'
ELSE INSERT INTO table (varType, longVarValue) VALUES ('siteMenu', ?)

With longVarValue being of type 'text' in the DB. Do I need to case that as
'ntext'?

-Darrel
May 27 '06 #3
dmm
Sorry, poor communication skillz ;)

I was trying to point out that actually it is the EXISTS keyword that
doesn't like being given NText. Rather than selecting out the actual value,
if you keep your origonal WHERE predicates you may simply select 1 (logical
true). The rest of your query may remain the same.

try the following:

IF EXISTS
(
SELECT
1
FROM
table
WHERE
varType = 'siteMenu'
)
UPDATE table SET longVarValue = ? WHERE varType = 'siteMenu'
ELSE
INSERT INTO table (varType, longVarValue) VALUES ('siteMenu', ?)

"Darrel" <no*****@nospam.com> wrote in message
news:u8**************@TK2MSFTNGP02.phx.gbl...
not sure this is the correct group, but you should use 'if select 1 from
xxx where xxxx' as in the code below:


I'm not quite following that. I'm not using a SP, btw. Here's my actual
query I'd like to use:

IF EXISTS (SELECT varType, longVarValue FROM table WHERE varType =
'siteMenu') UPDATE table SET longVarValue = ? WHERE varType = 'siteMenu'
ELSE INSERT INTO table (varType, longVarValue) VALUES ('siteMenu', ?)

With longVarValue being of type 'text' in the DB. Do I need to case that
as 'ntext'?

-Darrel

May 27 '06 #4
> I was trying to point out that actually it is the EXISTS keyword that
doesn't like being given NText.


Argh! USER ERROR! ;o)

I completely missed that. Thanks for pointing it out!

-Darrel
May 27 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: ST | last post by:
Hi, I'm sort of in a rush here...I'm sort of new to vb.net and I'm trying to write the syntax to check a sql table to see if the record already exists based on firstname and lastname text fields...
3
by: Peter Bailey | last post by:
Could someone please tell me how to pass criteria as if it were a parameter. I have a routine now that creates the sql string (well almost). at present the parameter is so I can pass one item ie...
20
by: Bryan | last post by:
hello all... im trying to add a record to an sql db on ms sql server 2000, using vb.net. seems to be working.. except for one thing, one of the columns in the database is a bit datatype, and...
8
by: Yitzak | last post by:
Hi is there a way to run a SQL Script to Update an Access Schema. Without using VBA code. E.g. in mssql server I can run script like if not exists (select * from syscolumns where name =...
6
by: BrianDP | last post by:
I have a table that has always been in my back-end of this application. The table is getting quite large, and, on top of that, we lose records out of this table. They just dissappear! No rhyme or...
2
by: Billy | last post by:
This string is supposed to provide all records from an MDB database that match the courier and date specified in the query. I Response.Write the query and I get a date as 1/27/2007. The date...
4
by: Neil | last post by:
I was reading on the Microsoft web site that to use the Rich Text property in Access 2007, that you needed to set the table field's TextFormat property to RichText, in addition to setting the text...
0
by: Medhatithi | last post by:
Hi, I have been in several ways benefiited from this site. I would like to share some sql tuning techniques(simple, but effective) with you all. SQL Tuning Tips Oracle Tips Session #6 ...
0
MMcCarthy
by: MMcCarthy | last post by:
Rather than using the Access design view change the view to SQL. I am going to attempt to outline the general syntax used for SQL queries in Access. Angle brackets <> are used in place of some...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.