Problems with a Dash in a Variable 1

[DonP]

I am trying to convert a series of VBScripts designed to search an Access database over to searching an SQL2000 database but I am having issues in converting the sql statements. On one, there is a variable being used as part of the query and if I hardcode the value it works but as a variable, it does not. It appears to be a problem with the fact that there is a dash in the value even though it works when hardcoded!

this works after a fashion:
[tt]WHERE (agency.County = N'" & COUNTY & "') AND (AgencyCategory.CATEGORY = N'LR-620')[/tt]

while this does not:
[tt]WHERE (agency.County = N'" & COUNTY & "') AND (AgencyCategory.CATEGORY = N'" & QueryString & "')[/tt]

The error message in the browser is:
Incorrect syntax near 'LR' which seems to indicate that it is having issues with the dash.

I am not sure what the "N" is for - SQL Server Enterprise Manager put it there, but the value "QueryString" is where I am having a problem. The value contains things like "LR-620" for example. Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[fluteplr]

Have tried tracing the statement on the server side to see what is actually be received by the server. I find that this frequently results in discovering an obvious error.

The N converts the field to a nchar field which would be appropriate if the field on the server is an nchar or nvarchar if not you can leave it out.

 

[DonP]

Using SQL Server Enterprise Manager, I get the correct results when I hardcode the variable to a known field value. It was the Enterprise Manager that created the SQL statement for me as I've never done this with SQLserver before.

I am also just now discovering that my other variable "COUNTY" often has a space in it which is also causing a problem. It should actually be "COUNTIES" and as soon as I corrected it, it stops at the space:
[tt]Incorrect syntax near 'Santa'.[/tt], which should be "Santa Clara." Again, in Enterprise Manager this works as it should. Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[DonP]

Although it is still not working, I've made some progress It appears that it was the double quotes that were causing this particular problem and without them, it moves past that error with an entirely new one. The "N" was apparently not necessary either.

So instead of:
[tt]WHERE (agency.County = N'" & COUNTY & "') AND (AgencyCategory.CATEGORY = N'" & QueryString & "')
[/tt]

I am using:
[tt]WHERE (agency.County = ' & COUNTY & ') AND (AgencyCategory.CATEGORY = ' & QueryString & ')
[/tt]

This must be one of the differences between Access and SQL Server query strings. There are lots of "MID" and "LEFT$" etc. in the existing VBscripts that do not seem to work with SQL Server query strings either. Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[tlbroadbent]

If the the double quotes were passed to SQL Server, SQL would have interpreted them as column delimiters. You could SET QUOTED_IDENTIFIES OFF before running a query to see if the problem is eliminated. The 'N' converts the character string to UNICODE and is usually not required, as you have determined.

You can replace LEFT$ with LEFT and MID$ with SUBSTRING. Instead of '&' for concatenation, SQL uses '+' so you want to make sure you are sending '+' in your queries. Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[DonP]

Thanks, Terry! I'll make the changes tomorrow as I am home now. Is there a site or FAQ with these little differences side by side in an easy-to-compare manner? If so, it would sure help! Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[DonP]

Replacing MID with SUBSTRING gives an error that SUBSTRING function requires three arguments. What three arguments? Is there a replacement that is more direct?

Don Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[fluteplr]

SUBSTRING
Returns part of a character, binary, text, or image expression. For more information about the valid Microsoft® SQL Server™ data types that can be used with this function, see Data Types.

Syntax
SUBSTRING ( expression , start , length )

Arguments
expression

Is a character string, binary string, text, image, a column, or an expression that includes a column. Do not use expressions that include aggregate functions.

start

Is an integer that specifies where the substring begins.

length

Is an integer that specifies the length of the substring (the number of characters or bytes to return).

This came from SQL server books on line. Which you should have a copy of if you are running sql server. It is not that we mind helping you, but could you at least make an effort to look in the manual!!

 

[DonP]

I did look in the manual but I either got too many results or nothing useful, mainly because I was not sure what to use in a search. At any rate, my existing code had something like: value,1 so as a simple guess based on the error message, I added a ,1 to make it value,1,1 - and it worked. This was the first good result that I've had, but it's good because I started over with a fresh copy of the script after doing a lot of playing around of the old one - and reading the documentation. With the reading I did, and the help I got here, I got results right away on my first try this time.

What is making this extra difficult is that the query strings are in pieces throughout the code rather than being in one place. The script concatenates them back togther depending upon the search type. Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[fluteplr]

when looking for the syntax for substring
do not do a search, use then index tab and enter substring. This will work for ALL sql server key words.

 

[DonP]

Problem was, I didn't know to search on "substring" until you told me the term and by then, I had answered my own question. Sorry to have wasted your time.

But now I am having a problem with InStr, I find all sorts of references to it in on Microsoft's Web site and found function comparison charts both there and in the SQL Server documentation, but nothing relating to this one. Is there one? Str does not seem it be it, but it's the closest that I found. Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[tlbroadbent]

There is no INSTR function in SQL Server. The replacement is CHARINDEX. It returns the starting position of the string to be found. The arguments are entered in different order than INSTR.

Syntax: CHARINDEX ( expression1 , expression2 [ , start_location ] )

NOTE: INSTR equivalent would be INSTR(colA,"ghi",)

Example:
CHARINDEX('ghi',colA) - Find the string 'ghi' in column colA
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.