Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

"Syntax error converting from a character string to uniqueidentifier"

Posted on 2006-07-05
5
Medium Priority
?
1,016 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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
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…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

886 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