OLEDB query to SQL Server fails

Question

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 string 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.

Marymarya

Answer 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

Xerophagy

This is all news to me. Thanks for the additional pointer. I'll look into it. – Marymarya

@TheFallibleFiend no problem here's the canonical comic on the subject xkcd.com/327 – Xerophagy

It looks to me like OLEDB does not like the "with." I've simplified the sql, expecting it to return no results, but at least to execute: WITH cte AS (SELECT (lastname + firstname + middlename + ssn4 ) as userCompareStr FROM atable ) SELECT userCompareStr FROM cte WHERE userCompareStr='GAPYLE1111'; It nevertheless fails. I'm not sure, but I think oledb does not recognize all sql. I'll try some of the other examples. – Marymarya

@TheFallibleFiend I forgot to mention that this works on SQL 2005 and later, are you using SQL 2000? – Xerophagy

Conrad, It's Microsoft SQL Server 2008. – Marymarya

@TheFallibleFiend what did you use in the provider. It worked fine for me when I used Provider=SQLNCLI;Server=..., – Xerophagy

I worked on another problem for a few days and came back to this. This time I switched from using oledb. At that time I was using: Provider=SQLOLEDB I don't know what I'm using exactly, but maybe "native" SQLSERVER? My provider is set to: providerName="System.Data.SqlClient" – Marymarya

@TheFallible The provider should be Provider=SQLNCLI10; see connectionstrings.com – Xerophagy

Also, I switched the provider to SQLNCLI10 as you suggested and it did work for me. tx,tff – Marymarya

Answer 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)

Pyrone

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

Oculus

Answer 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 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.

Marymarya