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;
                }

            }
        }

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

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!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.