Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

"Syntax error converting from a character string to uniqueidentifier"

Posted on 2006-07-05
5
Medium Priority
?
1,015 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

670 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