467,136 Members | 1,291 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,136 developers. It's quick & easy.

using an alias in a where statement as a parameter

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
Nov 16 '05 #1
  • viewed: 1571
Share:
2 Replies
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

Nov 16 '05 #2
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
Nov 16 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Jessard | last post: by
1 post views Thread by Daveyk0 | last post: by
9 posts views Thread by Ron | last post: by
15 posts views Thread by arnuld | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.