trying to read sql statement c#

Question

string queryStr = "select max(patient_history_date_bio) " +
    "as med_date, medication_name from biological where " +
    "(patient_id = " + patientID.patient_id + ") " +
    "group by medication_name;";



using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand(queryStr, conn))
{
    conn.Open();

    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        int count = 0;

        while (rdr.Read())
        {
            MessageBox.Show("test");
            med.medication_date[count] = new DateTime();

            med.medication_date[count] = DateTime.Parse(rdr["med_date"].
                ToString());

            MessageBox.Show("test2");

            med.medication_name[count] = rdr["medication_name"].ToString();

            count++;
        }
    }

    conn.Close();
}

so i'm trying to read this sql statement. "test" message box displays, but not "test2". I tried running the sql statement in VS by itself (in the server explorer), and the sql statement works. it gives me what i want. but somehow, the code doesn't work... does anyone see why?

Answer 1

Assuming patient_id is some sort of integer (or is it a Guid), my assumption is that the current culture of your program is causing the ToString method call on int to be formatted in a way that is returning something that the parser can't parse (e.g. "1,234,567").

Generally, the way you are executing this statement is not a best-practice. While you might not be susceptible to injection attacks if the id is indeed an int (you are most definitely open to them if it's a string), you generally want to parameterize the queries.

The reason for this is not only to protect against injection attacks, but because it will properly format the parameter in the query string according to the type.

Another thing to point out about your code is how you are retrieving the values from the reader. You are effectively calling ToString on the DateTime instance, then calling Parse on the string to get a DateTime back.

This effectively burns cycles. All you need to do is cast (unbox in the case of value types) the value back.

So where you have:

med.medication_date[count] = DateTime.Parse(rdr["med_date"].
    ToString()); 

You should have:

med.medication_date[count] = (DateTime) rdr["med_date"];

All that being said, as to why the second message box is not showing, my first guess is that you are executing this in an event handler in a Windows Forms application, and that an exception is being thrown.

I think that what you will find is that if medication_date is an array, then it hasn't been initialized and you are getting a NullReferenceException or something about the array index being out of bounds.

Answer 2

What is med.medication_date?

If it is an array, maybe it hasn't been initialized yet. If it is a list, you should assign to it using med.medication_date.Add(value);

Alternatively, as everyone else is saying, the date time conversion may be at fault. Try replacing

MessageBox.Show("test");

With

MessageBox.Show(rdr["med_date"].ToString());

Answer 3

Without more info, it looks like the line

med.medication_date[count] = DateTime.Parse(rdr["med_date"].ToString()); 

throws an exception due to an unrecognised date, and the exception is being swallowed by a handler higher up.

Answer 4

you should direct your debug stuff to the output window...it's muche easier to follow the flow.

          system.diagnostics.debug.writeline(rdr["med_date"].ToString());