# in variable used in SQL?

# in variable used in SQL? (Full Version)

All Forums >> [Web Development] >> ASP and Database

Message

sgreen0 -> # in variable used in SQL? (9/30/2005 15:03:30)
I need to query a table for a field with a # sign in it. As a example, one of the fields contains: Lyric Lab #3 - Rhyme Assignment My SQL includes: WHERE AssignmentInfo.AssignmentName = " & vAssignmentName VAssignmentName would contain "Lyric Lab #3 - Rhyme Assignment" the Query generates an error and the WHERE clause looks like this: 'AssignmentInfo.AssignmentName = Lyric Lab' That implies to me that it is cutting off everything after the # sign. Is there something I can do about this? Stephen

Spooky -> RE: # in variable used in SQL? (9/30/2005 15:51:48)
What is your actual SQL string?

sgreen0 -> RE: # in variable used in SQL? (9/30/2005 16:18:07)

Spooky!

The query looks like this:

SELECT OnLineContacts.ID, OnLineRegistration.CourseID, OnLineRegistration.EvalId, OnLineEvaluators.LastName, OnLineEvaluators.FirstName, OnLineEvaluators.Email, AssignmentInfo.AssignmentID, AssignmentInfo.AssignmentName, AssignmentInfo.TableName, AssignmentResultsSummary.DateTaken, AssignmentResultsSummary.DateGraded, AssignmentResultsSummary.Grade, AssignmentResultsSummary.Notes, AssignmentResultsSummary.Retake, AssignmentResultsSummary.Pass FROM OnLineEvaluators INNER JOIN ((OnLineContacts INNER JOIN OnLineRegistration ON OnLineContacts.ID = OnLineRegistration.ID) INNER JOIN (AssignmentInfo INNER JOIN AssignmentResultsSummary ON AssignmentInfo.AssignmentID = AssignmentResultsSummary.AssignmenttID) ON OnLineContacts.ID = AssignmentResultsSummary.ID) ON OnLineEvaluators.EvalID = AssignmentResultsSummary.EvalID WHERE AssignmentInfo.AssignmentName = Lyric Lab

The last part *should* read "Lyric Lab #3 - Rhyme Assignment"

I should mention that the Assignment Name comes from a querystring passed in the link from the previous page. When I hover over that link I can see that it reads "View_Assignment.asp?AssignmentName=Lyric Lab #3 - Rhyme Assignment"

Thanks for you help.

Stephen

Spooky -> RE: # in variable used in SQL? (9/30/2005 16:21:08)
If its text - where are the quotes?

sgreen0 -> RE: # in variable used in SQL? (10/1/2005 2:44:52)

Good Point!

When I add single quotes around the variable thus:

fp_sQry="SELECT OnLineContacts.ID, OnLineRegistration.CourseID, OnLineRegistration.EvalId, OnLineEvaluators.LastName, OnLineEvaluators.FirstName, OnLineEvaluators.Email, AssignmentInfo.AssignmentID, AssignmentInfo.AssignmentName, AssignmentInfo.TableName, AssignmentResultsSummary.DateTaken, AssignmentResultsSummary.DateGraded, AssignmentResultsSummary.Grade, AssignmentResultsSummary.Notes, AssignmentResultsSummary.Retake, AssignmentResultsSummary.Pass FROM OnLineEvaluators INNER JOIN ((OnLineContacts INNER JOIN OnLineRegistration ON OnLineContacts.ID = OnLineRegistration.ID) INNER JOIN (AssignmentInfo INNER JOIN AssignmentResultsSummary ON AssignmentInfo.AssignmentID = AssignmentResultsSummary.AssignmenttID) ON OnLineContacts.ID = AssignmentResultsSummary.ID) ON OnLineEvaluators.EvalID = AssignmentResultsSummary.EvalID WHERE AssignmentInfo.AssignmentName = '" & vAssignmentName & "'"

I get this:

SELECT OnLineContacts.ID, OnLineRegistration.CourseID, OnLineRegistration.EvalId, OnLineEvaluators.LastName, OnLineEvaluators.FirstName, OnLineEvaluators.Email, AssignmentInfo.AssignmentID, AssignmentInfo.AssignmentName, AssignmentInfo.TableName, AssignmentResultsSummary.DateTaken, AssignmentResultsSummary.DateGraded, AssignmentResultsSummary.Grade, AssignmentResultsSummary.Notes, AssignmentResultsSummary.Retake, AssignmentResultsSummary.Pass FROM OnLineEvaluators INNER JOIN ((OnLineContacts INNER JOIN OnLineRegistration ON OnLineContacts.ID = OnLineRegistration.ID) INNER JOIN (AssignmentInfo INNER JOIN AssignmentResultsSummary ON AssignmentInfo.AssignmentID = AssignmentResultsSummary.AssignmenttID) ON OnLineContacts.ID = AssignmentResultsSummary.ID) ON OnLineEvaluators.EvalID = AssignmentResultsSummary.EvalID WHERE AssignmentInfo.AssignmentName = 'Lyric Lab'

Still missing everything beyond the #...

Stephen

Spooky -> RE: # in variable used in SQL? (10/1/2005 8:03:27)
Sorry - should have read your reply more. If its coming from a querystring then you should URLencode the string being passed. '#' has a special meaning as a bookmark in a hyperlink. A check you can do for this sort of thing is to response.write the value passed from the previous page and see that it matches what you expect to get

sgreen0 -> RE: # in variable used in SQL? (10/1/2005 14:14:45)
Thanks. That did the trick. Stephen

Page: [1]

0.0625