using an alias in a where statement as a parameter

[http://www.visual-basic-data-mining.net/forum]

Hello,

I have the following tables and have setup the following sql statement as part of my data adapter. The problem is that I need to do a statement that will find the records selected in my list box. I cannot figure out how to use the "name' alias in the parameter statement.

I am trying to combine first, last name, alpha number into one field for the list box and all the requests the employee made.

Here are my two tables:

EmpAlpha RequestID
EmpFirstName RequestDate
EmpLastName DivsionID

Here is my SQL statement as it is now. But it does not return the matching requests.

SELECT EmployeeCurrent.EmpLastName + ', ' + ' ' + EmployeeCurrent.EmpFirstName + ' 'EmployeeCurrent.EmpAlpha AS Name,
RequestDesired.RequestID, RequestDesired.RequestDate, RequestDesired.DivisionID, RequestDesired.CodeID
FROM EmployeeCurrent INNER JOIN
RequestDesired ON EmployeeCurrent.EmpAlpha = RequestDesired.EmployeeAlpha
WHERE ('Name' = ? + '%')
ORDER BY RequestDesired.DivisionID

How do I reference the alias for my concatenated 'Name' in the parameter so I can return the right records in the data adapter and set the datasource for my grid to include 'Name'

Debbie

--
debhemlinger - forum member
http://www.visual-basic-data-mining.net/forum

[Mohamoss]

Hi Debbie
One thing that you can do is to send that alias as an input parameter, then
add this parameter to the parameter collection of you command object then
in your command text refer to it by the parameter name ( where alias = @
alias_param )
Mohamed Mahfouz
MEA Developer Support Center
ITworx on behalf of Microsoft EMEA GTSC

[James Curran]

If I follow ths right, this line:
WHERE ('Name' = ? + '%')
should be
WHERE ([Name] = ? + '%')

As you had it, you were trying to match the literal string 'Name' against
the parameter.
--
Truth,
James Curran
Home: www.noveltheory.com Work: www.njtheater.com
Blog: www.honestillusion.com Day Job: www.partsearch.com
(note new day job!)
"http://www.visual-basic-data-mining.net/forum" <si******@gmail.com> wrote
in message news:%2****************@tk2msftngp13.phx.gbl...
Hello,

I have the following tables and have setup the following sql statement as
part of my data adapter. The problem is that I need to do a statement that
will find the records selected in my list box. I cannot figure out how to
use the "name' alias in the parameter statement.

I am trying to combine first, last name, alpha number into one field for the
list box and all the requests the employee made.

Here are my two tables:

EmpAlpha RequestID
EmpFirstName RequestDate
EmpLastName DivsionID

Here is my SQL statement as it is now. But it does not return the matching
requests.

SELECT EmployeeCurrent.EmpLastName + ', ' + ' ' +
EmployeeCurrent.EmpFirstName + ' 'EmployeeCurrent.EmpAlpha AS Name,
RequestDesired.RequestID, RequestDesired.RequestDate,
RequestDesired.DivisionID, RequestDesired.CodeID
FROM EmployeeCurrent INNER JOIN
RequestDesired ON EmployeeCurrent.EmpAlpha =
RequestDesired.EmployeeAlpha
WHERE ('Name' = ? + '%')
ORDER BY RequestDesired.DivisionID

How do I reference the alias for my concatenated 'Name' in the parameter so
I can return the right records in the data adapter and set the datasource
for my grid to include 'Name'

Debbie

--
debhemlinger - forum member
http://www.visual-basic-data-mining.net/forum