user3487243 user3487243 - 5 months ago 7
SQL Question

SQL JOIN multi-part identifier error

I have two tables,

Customers[CustomerID,Email]
and

History[CustomerID,BikeID, Checkout, StationIDout, Checkin, StationIDin ]


Given a customer's e-mail, I want to query the
History
table and obtain all rows containing that email's corresponding
CustomerID
where the bike has been returned (
WHERE Checkin IS NOT NULL
), ordering by most recent
Checkin
date.


I tried the following query but am getting the error:


"The multi-part identifier "test_email@test.org" could not be bound."


test_email@test.org exists in the database. I saw other posts explaining how it's either a typo or some column is being wrongly selected, but I don't see my query doing any of these. Is there something else I'm missing?

email = "test_email@test.org";

sql = string.Format(@"
SELECT BikeID, Checkout, StationIDout, Checkin, StationIDin
FROM History INNER JOIN
Customers ON Customers.CustomerID = History.CustomerID
AND Customers.Email = {0}
WHERE Checkin IS NOT NULL
ORDER BY Checkin DESC", email);

Answer

Missing string quotes:

 sql = string.Format(@"
    SELECT BikeID, Checkout, StationIDout, Checkin, StationIDin 
    FROM History INNER JOIN 
    Customers ON Customers.CustomerID = History.CustomerID
    AND Customers.Email = '{0}' 
                          ^   ^
    WHERE Checkin IS NOT NULL
    ORDER BY Checkin DESC", email);

But rather than using concat or format, use parameters