Oledb Query To Sql Server Fails

I have two SQL queries: A. SELECT (upper(rtrim(ltrim(lastname))) + upper(rtrim(ltrim(firstname))) + upper(rtrim(ltrim(middlename))) + rtrim(ltrim(v)) ) AS userCompareStr FRO

Solution 1:

You have three options.

1) repeat what you did in the select in the where

SELECT (upper(rtrim(ltrim(lastname))) +upper(rtrim(ltrim(firstname))) +upper(rtrim(ltrim(middlename))) + rtrim(ltrim(v)) ) AS userCompareStr 
FROM atable  

WHERE  (upper(rtrim(ltrim(lastname))) +upper(rtrim(ltrim(firstname))) +upper(rtrim(ltrim(middlename))) + rtrim(ltrim(v)) ) ='GAPYLE1111' ;

2) Use a common table expression

with CTE AS 
(SELECT (upper(rtrim(ltrim(lastname))) +upper(rtrim(ltrim(firstname))) +upper(rtrim(ltrim(middlename))) + rtrim(ltrim(v)) ) AS userCompareStr 
FROM atable  )
SELECT userCompareStr FROM CTE where userCompareStr ='GAPYLE1111';

3) Inline query see Maziar Taheri's answer

As an aside I hope 'GAPYLE1111' doesn't come from user input, otherwise you're exposing yourself to SQL Injection attacks. Use parameterized queries instead

Solution 2:

No, you cannot use an aliased column in the WHERE clause.

See Using an Alias column in the where clause in ms-sql 2000

(the article is about SQL 2000, but it still applies today)

Solution 3:

you cannot use an alias you have set in the select clause, inside the where clause.

try this:

SELECT*FROM
(
SELECT (upper(rtrim(ltrim(lastname))) +upper(rtrim(ltrim(firstname))) +upper(rtrim(ltrim(middlename))) + rtrim(ltrim(v)) ) AS userCompareStr FROM atable)
as nested
WHERE userCompareStr='GAPYLE1111' ;

Solution 4:

I stepped away from the problem for a while, worked on something else, and came back to it. I have solved the primary problem by switching from using oledb to "something else." I'm not sure what the new (to me) method is called - except maybe "native sqlserver?"

Important points:

1. Cannot use field name alias in the WHERE clause. (as per maziar and matt)

2. Conrad's fix #1 worked on OLEDB, but I don't like that method because it's verbose (and the real command is a lot more complicated than the scaled down example I provide here) and there is a LOT of different invocations. Error-prone and hard to read (but works in a pinch).

3. To get either the WITH or the nested select work I had to switch from OLEDB to "native sqlserver" (or whatever it's called). The WITH (as suggested by Conrad) is my preferred solution - much easier to read. The nested select (suggested by Maziar) also works when I switch from OLEDB to native.

4. I need to switch to "parameterized queries" to avoid sql injection attacks as noted by Conrad.

Anyway, suggestions above work when I switched to that method. Instead of using

Provider=SQLOLEDB

I used:

providerName="System.Data.SqlClient"

I now make no reference to oledb (such as oledbadapter), but instead make references to sqlDataAdapter. I ignore the upper, ltrim, and trim functions (because it turns out they weren't the issue) and focus on the WITH which is what oledb had been choking on. Here's what I got to work:

Dim conn AsNew SqlConnection("server=localhost;database=DB;Integrated Security=SSPI;")
    Dim sql AsStringDim da As SqlDataAdapter

    Dim ds As DataSet = New DataSet()
    Dim tbl As DataTable = New DataTable

    conn = New SqlConnection()
    conn.ConnectionString =    ConfigurationManager.ConnectionStrings("DB").ConnectionString

    Sql = " WITH cte AS "
    sql = sql & "(lastname + firstname + middlename"
    Sql = Sql & "     + v) as userCompareStr FROM atable ) "
    sql = sql & "SELECT userCompareStr   FROM cte WHERE userCompareStr = '" & "GAPYLE1111" & "' ;"

    da = New SqlDataAdapter(sql, conn)
    da.Fill(ds)
    tbl = ds.Tables(0)
    TextBox2.Text = sql

    If tbl.Rows.Count < 1Then
        TextBox1.Text = "no items"Else
        TextBox1.Text = tbl.Rows.Count & " items selected"EndIf


    conn.Dispose()

Also, in web.config, I added:

I have not added the SQL Injection fix stuff yet, but I am looking into it and I'm sure now that this is something I need to do. I found some information on that here:

http://msdn.microsoft.com/en-us/library/ff648339.aspx

and here:

http://weblogs.asp.net/cibrax/archive/2006/09/28/Parameterized-Queries-_2800_Oracle_2C00_-SQLServer_2C00_-OleDb_2900_.aspx

but I need some time to experiment with it. Thanks for the help and the pointers.