I'm writing an application in Flex 3 Developer Studio. All of my SQL queries are passed to a VB file as a WebService, so they queries are all written in my .asmx file and I only pass the parts of the query that change (in this particular case, the WHERE and ORDER BY clauses). I can trace() the string that I'm passing to the .asmx file, and that's what I've been doing to figure out that my error only occurs when the operator in the WHERE clause includes the '<' character.

I don't think so. I have other queries that include the '<' character that run fine. The only difference between those queries and this one is that I'm passing the WHERE and ORDER BY clauses as strings to the function in my .asmx file that actually queries the server. Maybe I should use the bitwise operator for the '<' char? Anyone know that offhand?

You can use alternate syntax and/or characteristic functions to work around the problem with the <, but I have no idea (other than the one offered) why this character is a problem to you. Your statement:

WHERE MSM.MatureDate <> '01/02/1900';

Characteristic functions are normally coded as CASE statements these days, but here is an alternative. The absolute value of the sign of the date difference will be 1 if the dates are not the same and 0 if the dates are the same. (If you use this, document it.)

WHERE ABS(SIGN(DATEDIFF (DAY,MSM.MatureDate,'01/02/1900'))) = 1

Using a CASE statement and avoiding a < character.

WHERE (CASE WHEN MSM.MatureDate = '01/02/1900' THEN 0 ELSE 1 END) = 1

Of course, that assumes that your Date column is totally pure and is not a DATETIME column with any time other than 00:00:00.000. If you have to deal with time, then:

1-The contents of the parameters WhereClause and OrderByClause. Could you print these out or write them to a file, so that you can examine what got passed.

If your WhereClause = "WHERE MBI.DurationToWorst" then the problem is before the string gets to your function. That would pop the problem from this function to higher up the stack.

2. If the WhereClause inside the function is fine, please try to preconcatenate the strings into a variable then call the New SqlDataAdapter with that variable. (Also print this variable out or write it to a file.) E.g.

1-The contents of the parameters WhereClause and OrderByClause. Could you print these out or write them to a file, so that you can examine what got passed.

The WhereClause and OrderByClause groups that I included in my last post were copied from an external debugger which intercepted two trace() functions which passed the variables that held the strings you see. What you read there is exactly what gets read by the VB function down to the extra space at the end of the WHERE clauses.

2. If the WhereClause inside the function is fine, please try to preconcatenate the strings into a variable then call the New SqlDataAdapter with that variable. (Also print this variable out or write it to a file.) E.g.

The method that I'm using for this function is used in nearly every other function in my VB file. Why would this one break if the rest work just fine? I'm still going to try this though as the rest of the query is static and it shouldn't be too difficult to slap together.

*** Edit *** - I just tried passing the entire query as one string and I get the exact same problem.

3. Incorrect syntax near the keyword 'AS'. Possible Cause: ORDER BY SpFinalCash AS Possible Cause: SELECT Grp.GrpName AS , Port.PortID AS SPPortID,

Actually, this second possible cause of #3 would give you two errors: Incorrect syntax near ','. Incorrect syntax near the keyword 'AS'.

Now, since I can see your code, I know that you are not writing these errors. But something is inducing them. I suspect string truncation could be the cause. You should review the length of all of your string variable to make sure that nothing is being lost.

Also, since your trace did not reveal any distortion of the query, I believe that you should run SQL Server Profiler and catch the actual statement as received by the SQL Server. (If you do not have the rights to do this, then get your DBA or Administrator to do it for you.)

Once you can see what SQL Server sees, you will probably have the answer. (I see that this latest was a SoapError, but I don't know if the Soap protocol adds any problems.)

And this makes more sense because right now I'm passing the entire string to the .asmx file, so every time I do it sends the '<' in these two lines as '%lt;' and it bounces an error. Before when I was just passing the WHERE and ORDER BY clauses it only bounced an error when the WHERE clause included a '<'.

This looks to me like the Flex Builder compiler is to blame; damn you Adobe!

So now the only questions remaining are "Why the ____?" and "How do I fix it?"

So, basically your code is being treated as XML, thus the conversion of those characters into the XML replacement codes. (I only thought of that possibility once I saw the SOAP error, but I did not know enough to give a suggestion beyond "use Profiler".)

Flex Builder is outside my knowledge, but is it able to produce a non-XML stream? If so, it might be some setting that you can control.