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 
FROM atable ;

and

B.

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

I have the following code:

    Dim sql As String
    Dim conn As OleDbConnection
    Dim cmd As OleDbDataAdapter
    Dim ds As DataSet
    Dim tbl As DataTable

    conn = " something here "
    cmd = New OleDbDataAdapter(sql, conn)
    ds = New DataSet
    cmd.Fill(ds)
    tbl = New DataTable
    tbl = ds.Tables(0)

Near as I can tell it seems to work when sql is set to 开发者_如何学Pythonstring A, but not when it's set to string B.

This leads me to suspect that there is something wrong with the clause WHERE userCompareStr='GAPYLE1111'

Can I not use the alias userCompareStr in this way? I can't find any examples of this kind of use, but I do find analogous use when alias is used for table name -- and I don't see anything against that kind of us.

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

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)

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' ;

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 As New SqlConnection("server=localhost;database=DB;Integrated Security=SSPI;")
    Dim sql As String
    Dim 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 < 1 Then
        TextBox1.Text = "no items"
    Else
        TextBox1.Text = tbl.Rows.Count & " items selected"
    End If


    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.