Solved

"Syntax error converting from a character string to uniqueidentifier"

Posted on 2006-07-05
5
1,014 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 250 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
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…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

617 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