new to microsoft sql/asp and having syntax issue-HELP??

Hi everyone, I am new to using microsoft version of sql and having issues with some syntax. The
sql statment was created by someone else and it seems that ever since IE was updated (or mysql server updates), we have had a broken statement. I have tried to fix it without much success, the first error was "Incorrect syntax near the keyword UNION". It appeared that I had that issue resolved and then started getting an "error converting varchar to data type" message. Can someone take a look at the code? "sCountIN" is a variable number... I would appreciate it very much... thank you. (Syntax I found about variables say to use only double quotes if it is a number, but if I take the single quotes out, I get the syntax error near keyword UNION...


sSql = "SELECT custcount AS 'Count', CustNumber AS 'ID', 0 AS 'Amount', "

sSql = sSql & "CustomerName AS 'Name', 'cust' AS 'Type' FROM Customers "

sSql = sSql & "WHERE CustParentCustID = '" & sCountIn & "' "

sSql = sSql & "UNION SELECT d.dedcount AS 'Count', d.dedid AS 'ID', ISNULL(d.DedAmount,0) AS Amount, "

sSql = sSql & "a.Action_Type + '/' + a.Action_Reason AS Name, 'ded' AS 'Type' "

sSql = sSql & "FROM deductions d, Action_Profiles a "

sSql = sSql & "WHERE d.DedCustCount = '" & sCountIn & "' AND d.deduserstatus = 259 AND "

sSql = sSql & "a.ActionCount_key = d.dedtype "

I've never used mysql, does it support the use of stored procedures? If so you may be better off using those instead of building a string value for the sql statement. They are alot easier to trouble shoot and since they are compiled, they run faster. Did you try executing the query outside of your app, directly against the database to see if you get the same error. As far as i can tell your syntax is correct. Mabey there is a problem with the variable you are passing as sCountIn. Instead of executing the query, response.write it out to your page and see what it looks like. Copy and past it into your query analyzer and see if it works.

I think i figured it out. You said that if your sCountIn is a number. Do not use quotes at all. and make sure that you build an extra space before you start the union part of your query like this.
"WHERE CustParentCustID =" & sCountIn & " "

HI, I am back able to work on this project. I tried the suggestion there, but it did not work. Any other Ideas? I am so new to this I wouldn't know where to begin with a stored procedure. The company we got this from, is no longer in business and I am trying to trouble shoot this myself. I only have a little sql and javascript and no vbscript experience at all. Please help?? thank you. The sCountIn has been set back to just the " & sCountIn & " for both sides of the Union, cause I found several place telling me if it's a number do not put the single quotes in. So now, I have to figure out what is going on with my UNION syntax... anyone?? thank you so much...