SQL - Simple query

[DaveRook]

Hi

I am going 'grey' with this problem! I have the most simple of queries which works fine in SQL server, but won't work on the web page! I'm 99% certain the error is due to the quote symbol (after the number 0.050). Sadly, I need to use a quote symbol as some of my measurements are in inches (others in millimeters)!

When my code runs in SQL server, it works fine and I use (this is a very stripped down version):

SELECT * FROM View_BoardToBoard WHERE Pitch LIKE '0.050"'

The code in my CS webpage is:

 
string strPitch = "0.050"";
string strSQL = "SELECT * FROM View_BoardToBoard WHERE Pitch LIKE '"+strPitch+"'";

This displays no results! As mentioned above, it must be because of the " (quote) symbol! I obviously can't do:

string strPitch = "0.050"";

as I'll get a Newline in constant error message!!

Any ideas?

[balame2004]

I think use shoud use string strPitch = "0.050""; . Does not it work?

[DaveRook]

Hi

No, that won't work. I can't use the " as an inch symbol because it closes the string!

Any other ideas?

[MikeTheBike]

Hi

No, that won't work. I can't use the " as an inch symbol because it closes the string!

Any other ideas?

Hi
Have you tried

strPitch = "0.050"""

??

MTB

[DaveRook]

Hi Mike,

Sadly, still no luck. When I do that, I get :

CS1002: ; expected

This is a real sod! Normally I would just add the " in the web page, but as the dimensions from the site are either inches or mm, I can't do this!

Any other ideas?

Thanks

[DaveRook]

Hi

Solved - I added the wildcard as it couldn't work it with the quote "

 string strSQL = "SELECT * FROM View_BoardToBoard WHERE Pitch LIKE '0.050%''

Thanks

Dave

[tlhintoq]

"your text\" "
In other places where we need to specify a quote within quotes we use the \ symbol, just like other special string characters.
\n\l New line
\t tab

[r035198x]

Better use Parameters for the SQL command. It will take care of all the quoting and provides the first layer of defense against SQL injection.

[HankWalters]

You could also set a variable to the character code for the quote sign and concatenate it to your string. Something like this:

ChrQuote = Chr(34)
strPitch = "0.050" & ChrQuote
string strSQL = "SELECT * FROM View_BoardToBoard WHERE Pitch LIKE '" & strPitch & "'";

[Frinavale]

I was going to recommend the exact same thing as r0.

This article about how to use a database in your program gives an example of how to use parameters.

See this article for more information about Sql Injection Attack.