OLEDB查询到SQL Server失败 [英] OLEDB query to SQL Server fails

问题描述

我有两个SQL查询:

A.

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

和

B.

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

我有以下代码:

    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)

当我将sql设置为字符串A而不是将其设置为字符串B时，似乎可以正常工作.

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.

这使我怀疑该子句有问题 在哪里userCompareStr ='GAPYLE1111'

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

我不能以这种方式使用别名userCompareStr吗?我找不到这种用法的任何示例，但是当别名用于表名时，我确实找到了类似的用法-而且我也看不到任何类似的用法.

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.

推荐答案

您有三个选择.

1)重复您在选择中所做的操作

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)使用通用表表达式

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)内联查询，请参阅Maziar Taheri的答案

3) Inline query see Maziar Taheri's answer

顺便说一句，我希望'GAPYLE1111'不会来自用户输入，否则您将面临SQL注入攻击.改用参数化查询

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

这篇关于OLEDB查询到SQL Server失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！