ODBC Datareader and query not returning expected results

[eljainc]

Hello,

I'm having a problem with the ODBCDataReader Class in C# (Visual Studio 2005). I am able to open the database and query the tables in the code below. The problem is that the .read() loop returns over 7,000 records when in fact there is only one record matching the criteria below (when looking for the last name). When the criteria selected is for the date, then all of the records in the database are returned.

Can someone tell me what is wrong in the query/reading of the database, or an explanation as to why I'm getting incorrect results.

private void queryButton_Click(object sender, EventArgs e)
        {
            string connectionString;

            connectionString = "DSN=ECLIPSE;";
            cnn = new OdbcConnection(connectionString);
            try
            {
                cnn.Open();




                string selectQueryString;
                int myCount = 0;

                //doesn't work
                string CriteriaPredicate = 
                    "APPOINTMENTS.Date = '"+ConvertToSQLFormat("2012/10/08")+"'";

                //try this one
                CriteriaPredicate = "PATIENTS.LastName="
                +"'"+
                "MATHESON"+
                "'";



                selectQueryString = "SELECT "+
                    "PATIENTS.PatientID, "+
                    "PATIENTS.LastName, " +
                    "PATIENTS.FirstName, "+
                    "PATIENTS.Birthdate, "+
                    "PATIENTS.Gender, "+
                    "PATIENTS.HomePhone" +

                    " FROM APPOINTMENTS, PATIENTS" +
                    " WHERE "+CriteriaPredicate;


                System.Data.Odbc.OdbcDataReader drOffices;
                System.Data.Odbc.OdbcCommand cmdOffices;


                cmdOffices = new System.Data.Odbc.OdbcCommand(selectQueryString, cnn);
                drOffices = cmdOffices.ExecuteReader();
                string myString;
                while (drOffices.Read())


                {


                    myString = (drOffices["LastName"]).ToString().Trim() + ", " + 
                         drOffices["FirstName"].ToString().Trim() ;


                    //DataGrid.Row
                    dataGridView1.Rows.Add(drOffices["LastName"].ToString().Trim(),
                            drOffices["FirstName"].ToString().Trim(),
                            drOffices["Birthdate"].ToString().Trim());



                    myCount++;
                }    
                MessageBox.Show("Done. Count = "+myCount.ToString());
            }
            catch (OdbcException ex)
            {
                string err = "ODBC err: " + ex.Message;
                MessageBox.Show(err);
            }

            finally
            {
                if (cnn != null)
                {
                    cnn.Close(); 
                    cnn = null;
                }

            }
        }

[Momerath]

Remove 'APPOINTMENTS' from your table list. You are getting a record for every row in the appointments table that matches the criteria. If you are going to use two tables like that, you need to somehow tell it which rows in the appointments table belong to which rows in the Patient table, for example:
WHERE PATIENT.PatientID = APPOINTMENTS.PatientID

[eljainc]

Momerath, you were right on with this suggestion. By making that modification to the query, it pulls the right fields/data from the tables.

Thanks so much!