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.