?
Solved

"Syntax error converting from a character string to uniqueidentifier"

Posted on 2006-07-05
5
Medium Priority
?
1,025 Views
Last Modified: 2012-05-05
I have a table in my SQL Server 2000 database with a uniqueidentifier column called "token" assigned to each record. I want to be able to look up a particular record using this token.

In Query Analyzer, both of the following SQL statements execute perfectly:
SELECT * FROM mytable WHERE token='165844ae-3610-4ef0-a93b-993be0b0c88d'
SELECT * FROM mytable WHERE token=CONVERT(uniqueidentifier,'165844ae-3610-4ef0-a93b-993be0b0c88d')

However, running either of those from my C# ASP.NET web application causes a server error that reads: "Syntax error converting from a character string to uniqueidentifier." I am running the following from my app using a very basic data manager I have written which works fine oodles of other places:

string strToken = Request.QueryString["id"];
[...]
DataSet ds = Data.DataManager.Select("SELECT * FROM mytable WHERE token=CONVERT(uniqueidentifier,'" + strToken + "')");

As an aside, I am aware that feeding something directly from the query string into a SQL statement like that can open you up to SQL injection attacks... I am using regular expressions to check for and prevent that, however.

Thanks for your assistance!
0
Comment
Question by:okwiater
  • 2
  • 2
5 Comments
 
LVL 4

Expert Comment

by:gregg1ep00
ID: 17042462
Is your data manager able to accept parameters as part of the query?  If so, you could do something like this:

Guid token = new Guid( strToken );
Data.DataManager.SelectParameters.Add( "@Token", token );
DataSet ds = Data.DataManager.Select( "Select * FROM mytable WHERE token=@token" );

This would most likely require some modifications to your DataManager class.

Something else you could try is enclosing the unique identifier string in braces:

DataSet ds = Data.DataManager.Select("SELECT * FROM mytable WHERE token=CONVERT(uniqueidentifier,'{" + strToken + "}')");


Hope that helps!  :)

Greg
0
 
LVL 8

Accepted Solution

by:
kaliyugkaarjun earned 1000 total points
ID: 17042476
Hi

whats the value of strToken after retrieving it from Querystring .
If u have not checked pls check it in debug.

0
 
LVL 1

Author Comment

by:okwiater
ID: 17042593
Thanks for the help... kaliyugkaarjun was right, the problem was in the QueryString. Once that was fixed, the SQL worked fine.
0
 
LVL 8

Expert Comment

by:kaliyugkaarjun
ID: 17043479
Hi

Always try 1st with debug .Bcos there we get most of the things right.

Cheers!!!
0
 
LVL 1

Author Comment

by:okwiater
ID: 17043680
It was tricky because the token handoff was from one web app to another web app. So a change I made in one affected the other. Made no sense at the time, but in hindsight, of course, it's 20/20. Thanks again.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Did you know PowerShell can save you time with SaaS platforms? Simply leverage RESTfulAPIs to build your own PowerShell modules. These will kill repetitive tickets and tabs, using the command Invoke-RestMethod. Tune into this webinar to learn how…
Watch the software video of Kernel Import PST to Office 365 tools which can easily import PST and OST files to Office 365 for bulk mailboxes. The process of migration is simple and user can map source and destination mailboxes and easily import data…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question