Getting Syntax error converting from a character string to uniqueidentifier

Roy Rodsson via .NET 247

Hi all!

I am using a stored procedure in SQL2000 for retrieving fileinformations from a db.
the table as an uniqueidentifier for the file information.

The stored procedure has a variable called fileIds that is oftype varchar.
I am putting in a comma separated string with ids (such as:'9B176B0C-CA03-49C9-A2E7-063038E7CF20','9B176B0C-CA03-49C9-A2E7-063038E7CF22','9B176B0C-CA03-49C9-A2E7-063038E7CF23')

However, when I execute the sp via my dataadapter.fill I get thementioned "Syntax error converting from a character string touniqueidentifier" back.
To me it seems like the IN part doesn't like the comma separatedstring with unique ids :-(

select
*
FROM
Files
WHERE
Files.FileID IN(@FileIds);
The strange stuff is that the sp works correctly when I executeit from the query analyzer !

By the way - I am using a typed dataset in the fill command, butI have verified the results several of times and it seems to becorrect (for instance, I have let the dataadapter create a newdataset and compared it with my typed dataset - they areidentical) I have also tried to select everything without thewhere part and it works fine - so I guess the problem is in the"IN (@FileIds)" - part ?
Please, help me to shed a light on this problem.

/Roy.
--------------------------------
From: Roy Rodsson

-----------------------
Posted by a user from .NET 247 (http://www.dotnet247.com/)

<Id>bbiYAgQ3OU6ew03zELzN/A==</Id>

Nov 20 '05 #1

Herfried K. Wagner [MVP]

* Roy Rodsson via .NET 247 <an*******@dotnet247.com> scripsit:

select
*
FROM
Files
WHERE
Files.FileID IN(@FileIds);

You will more likely get an answer here:

<URL:news://news.microsoft.com/microsoft.public.dotnet.framework.adonet>

Web interface:

<URL:http://msdn.microsoft.com/newsgroups/?dg=microsoft.public.dotnet.framework.adonet>

--
Herfried K. Wagner [MVP]
<URL:http://dotnet.mvps.org/>

Nov 20 '05 #2

EricJ

the problem is that when your sp gets the string is adds its own ' '
you would get
select
*
FROM
Files
WHERE
Files.FileID
IN(''9B176B0C-CA03-49C9-A2E7-063038E7CF20','9B176B0C-CA03-49C9-A2E7-063038E7
CF22'');
1 whole string instaid of comma separated

I don't know a sollution to do this in an sp but i would execute it as
sqltext directly from vb. You don't put user input directly in the statement
and precompiled sp is useless since the condition always changes)
yust a thought

eric

"Roy Rodsson via .NET 247" <an*******@dotnet247.com> wrote in message
news:Om**************@TK2MSFTNGP10.phx.gbl...
Hi all!

I am using a stored procedure in SQL2000 for retrieving file informations
from a db.
the table as an uniqueidentifier for the file information.

The stored procedure has a variable called fileIds that is of type varchar.
I am putting in a comma separated string with ids (such as:
'9B176B0C-CA03-49C9-A2E7-063038E7CF20','9B176B0C-CA03-49C9-A2E7-063038E7CF22
','9B176B0C-CA03-49C9-A2E7-063038E7CF23')

However, when I execute the sp via my dataadapter.fill I get the mentioned
"Syntax error converting from a character string to uniqueidentifier" back.
To me it seems like the IN part doesn't like the comma separated string with
unique ids :-(

select
*
FROM
Files
WHERE
Files.FileID IN(@FileIds);
The strange stuff is that the sp works correctly when I execute it from the
query analyzer !

By the way - I am using a typed dataset in the fill command, but I have
verified the results several of times and it seems to be correct (for
instance, I have let the dataadapter create a new dataset and compared it
with my typed dataset - they are identical) I have also tried to select
everything without the where part and it works fine - so I guess the problem
is in the "IN (@FileIds)" - part ?
Please, help me to shed a light on this problem.

/Roy.
--------------------------------
From: Roy Rodsson

-----------------------
Posted by a user from .NET 247 (http://www.dotnet247.com/)

<Id>bbiYAgQ3OU6ew03zELzN/A==</Id>

Nov 20 '05 #3