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.

 

[Herfried K. Wagner [MVP]]

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

You will more likely get an answer here:

<URL:
Web interface:

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

 

[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



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.