467,886 Members | 1,786 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,886 developers. It's quick & easy.

Getting Syntax error converting from a character string to uniqueidentifier

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
  • viewed: 9022
Share:
2 Replies
* 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
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

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Gizmo | last post: by
1 post views Thread by amitbadgi | last post: by
2 posts views Thread by brian_harris | last post: by
1 post views Thread by SealedClassSingleton | last post: by
6 posts views Thread by ewpatton | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.