SQL query debugging required

Question

i am working in winforms with C#. heres my code

query = "SELECT max(Appointment_Time) FROM Appointments WHERE (Appointment_Status = 'D')";
dset = db.GetRecords(query,"Appointments");
ctime_Label.Text = dset.Tables["Appointments"].Rows[0]["Appointment_Time"].ToString();

db.GETRecords is a function of the class that provides me sql-services, simply returns me a dataset. i keep getting the error "the column 'Appointment_Time' doesnot belong to the table Appointments" which is stupid cuz

• when i am inserting the values its working fine.
• when i am NOT using max(Appointment_Time) function it is working fine

wha is the problem here.. i think there is something to do with the max() function. Any suggestion or alternative for this

Answer 1

When you apply a function in the SELECT clause the server has to choose a name for the resulting column, this usually reflects what the function is doing - something like max(Appointment_Time). The fields used as arguments to the function appear to be 'hidden'. This is why the field Appointment_Time is not visible to the calling code in your case.

You should specify what you want the aggregated field to be called in the SQL using AS, e.g.:

SELECT max(Appointment_Time) AS Max_Appointment_Time
FROM Appointments WHERE (Appointment_Status = 'D')

Then refer to the field as Max_Appointment_Time in the calling code. You can use the same name as the source field if you like.

Answer 2

Run the query in SQL Server and see what you get.

Now try:

 SELECT max(Appointment_Time) as A_Time FROM Appointments WHERE (Appointment_Status = 'D')";

and then

 ctime_Label.Text = dset.Tables["Appointments"].Rows[0]["A_Time"].ToString();