CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Posts
    1

    null error handling with mysql database....

    hello all. I am working on a simple program for some medical clients and I'm having trouble with one part. There are two parts to this, one is the web app to view the mysql DB, and the other a windows form to upload data into DB. I am having trouble with the data entry side. I've tried to build in as much internal error handling, since my clients are not very computer literate (simple, simple, simple).
    When I am uploading info i have two cases: either patient exists already or does not, based on searching the DB for existing last names (I'm going to have to change search parameter as well because i will have some patients with same last name but diff first name, but i can address that later). If patient exists then i only need to upload the test report to the 'test' Table, no patient info to 'patient' Table is ness. The part for uploading in case: patient does exists works fine. However if the code checks the DB and does not find a name it returns a null value which throws an error. Null Reference Exception gets returned. This has been frustrating me for hours now.... Anyone help?


    Code:
    private void button2_Click(object sender, EventArgs e)
            {
                int DoctorID;
                int PatientID=0;
                string connString = "server=localhost;User Id=root;password=Commando;database=flex";
                MySqlConnection conn = new MySqlConnection(connString);
                MySqlCommand selectDocCommand = conn.CreateCommand();
                MySqlCommand selectPatientCommand = conn.CreateCommand();
                MySqlCommand patientTableCommand = conn.CreateCommand();
                MySqlCommand testCommand = conn.CreateCommand();
                MySqlCommand LNameCommand = conn.CreateCommand();
                selectDocCommand.CommandText = "Select DoctorID from (doctors) Where doctors.DoctorUserID = '" + DoctorName.Text + "'";
                selectPatientCommand.CommandText = "Select PatientID From (patients) Where LName = '" + LastName.Text + "'";
    
                conn.Open();
                DoctorID = (int)selectDocCommand.ExecuteScalar();
               // PatientID = (int)selectPatientCommand.ExecuteScalar();
                MySqlCommand selectlnameCommand = conn.CreateCommand();
                selectlnameCommand.CommandText = "Select LName From (Patients) Where Lname = '" + LastName.Text + "'";
                string blah = (string)selectlnameCommand.ExecuteScalar();
    
                if (blah == LastName.Text)  //have to change this! What if two diff patients with same last name...
                {
                    testCommand.CommandText = "Insert into testreports (PDF, TestDate, patients_PatientID, TestType) Values ('fred.pdf', '" + TestDate.Text + "', '3', '" + testType + "')";
                    testCommand.ExecuteNonQuery();
                    conn.Close();
                    MessageBox.Show("Test report has been uploaded",
    "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                }
                else
                {
                    PatientID = (int)selectPatientCommand.ExecuteScalar();
                    //if (PatientID == null)
                    //{
                    //    PatientID = 99;
                    //}
                    //else
                    //{
                    //    int newID = PatientID;
                    //}
                    patientTableCommand.CommandText = "Insert patients (LName, FName, doctors_DoctorID) values ('" + LastName.Text + "', '" + FirstName.Text + "', '" + DoctorID + "')";
                    patientTableCommand.ExecuteNonQuery();
                    testCommand.CommandText = "Insert testreports (PDF, TestDate, patients_PatientID, TestType) Values ('test4.pdf', '" + TestDate.Text + "', '" + PatientID + "' '" + testType + "')";
                    testCommand.ExecuteNonQuery();
                    conn.Close();
                    MessageBox.Show("A new patient has been added to the database.",
    "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                }
            }
        }



    Error details:
    System.NullReferenceException was unhandled
    Message=Object reference not set to an instance of an object.
    Source=ISIDE
    StackTrace:
    at ISIDE.Form1.button2_Click(Object sender, EventArgs e) in C:\Users\Owner\Desktop\OCR\ISIDE\ISIDE\Form1.cs:line 399
    at System.Windows.Forms.Control.OnClick(EventArgs e)
    at System.Windows.Forms.Button.OnClick(EventArgs e)
    at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
    at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
    at System.Windows.Forms.Control.WndProc(Message& m)
    at System.Windows.Forms.ButtonBase.WndProc(Message& m)
    at System.Windows.Forms.Button.WndProc(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
    at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
    at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
    at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
    at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
    at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
    at System.Windows.Forms.Application.Run(Form mainForm)
    at ISIDE.Program.Main() in C:\Users\Owner\Desktop\OCR\ISIDE\ISIDE\Program.cs:line 18
    at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
    at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
    at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
    at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
    at System.Threading.ThreadHelper.ThreadStart()
    InnerException:
    Last edited by Cimperiali; February 10th, 2012 at 09:25 AM. Reason: Added [Code][/Code] and [/quote][/quote] tags

  2. #2
    Join Date
    Jul 2000
    Location
    Milano, Italy
    Posts
    7,726

    Re: null error handling with mysql database....

    Code:
    private void button2_Click(object sender, EventArgs e)
            {
                //first check input data;
                //use a variable to hold textbox.tex, so you trim it once (but preserve textBox.text as entered, avoiding
                //GUI updates till you're ready for that.
     
                string patientName= LastName.Text.Trim();
                if (string.IsNullOrEmpty(patientName))
                {
                        MessageBox.Show("No patient name entered");
                        return;  
                }
                if (string.IsNullOrEmpty( DoctorName.Text.Trim()))
                {
                        MessageBox.Show("No Doctor name entered");
                        return;  
                }
    
                int DoctorID;
                int PatientID=0;
                string connString = "server=localhost;User Id=root;password=Commando;database=flex";
                
                MySqlConnection conn = new MySqlConnection(connString);
    
                MySqlCommand selectDocCommand = conn.CreateCommand();
                MySqlCommand selectPatientCommand = conn.CreateCommand();
                MySqlCommand patientTableCommand = conn.CreateCommand();
                MySqlCommand testCommand = conn.CreateCommand();
                MySqlCommand LNameCommand = conn.CreateCommand();
    
                //add a @ to literal strings to have them escaped:
                //selectDocCommand.CommandText = "Select DoctorID from (doctors) Where doctors.DoctorUserID = '" + DoctorName.Text + "'";
                //selectPatientCommand.CommandText = "Select PatientID From (patients) Where LName = '" + LastName.Text + "'";
                
                selectDocCommand.CommandText = @"Select DoctorID from (doctors) Where doctors.DoctorUserID = '" + DoctorName.Text + "'";
                selectPatientCommand.CommandText = @"Select PatientID From (patients) Where LName = '" + patientName + "'";
                
               
                //even better than the @ : you should look at how to make inlines queries with parameters
    
                //trap errors : try{}catch{}finally{} - have a search with google for these keywords with c#
                try
                {
    
                    conn.Open();
                    DoctorID = (int)selectDocCommand.ExecuteScalar();
                   // PatientID = (int)selectPatientCommand.ExecuteScalar();
                    MySqlCommand selectlnameCommand = conn.CreateCommand();
                    selectlnameCommand.CommandText = "Select LName From (Patients) Where Lname = '" + patientName + "'";
                
                
                
                    //string blah = (string)selectlnameCommand.ExecuteScalar();
    
                    //from your call to db you can obtain a value or a DBNULL. 
                    //you should wrap code like the following in a NoNull() function or extension method
                    //as you might have to do this check and replace more than once
                    object result = selectlnameCommand.ExecuteScalar();
                    if (result ==DBNull.Value)
                    {
                        result =string.Empty; 
                    }
                    string blah = result.ToString();
                
                    //now see if values make sense: you do not have patient in db if you find no match
                    //or, better to say:  if the result from db is an empty string (probably it was the dbNull value that made
                    //your program crash). But in case you do not have the name, you do not have an ID for that patient.
                    //or it is allowed in your db to have patients with no name?
                    
                    if (string.IsNullOrEmpty( blah)|| blah.ToLower()  != patientName.ToLower())
                    {
                        //insert the patient: you do not have it in your db!
    
                        //by the way: I did not check all others data you're trying to insert.
                        //you shure they are all fine for your db?
                         MySqlCommand  selectNewPatientIdCommand =conn.CreateCommand();
                        selectNewPatientIdCommand.CommandText = @"Select max(PatientID) From (patients) ";
    
                        //did I already told you you should wrap this kind of stuff in a function?
                        //in any case, beware:
                        //this is NOT a good way to get a newId. You should read it after writing a new record in a table with
                        //a single column of tipe numeric (or longInt, or int) and identity = true. Then you should delete that 
                        //new record . This is called a "Sequence" table
                        object pId= selectNewPatientIdCommand.ExecuteScalar();
                        if (pId ==DBNull.Value)
                        {
                            pId=0; 
                        }
                        
                        PatientID ==(int)pId +1 ;
                        //****EDITED****
                       /*
                             I see now you're not writing PatientID  in patients tables, this I think it is an identity there.
                             You should read it after inserting the new line:
                        */
                        patientTableCommand.CommandText = @"Insert patients (LName, FName, doctors_DoctorID) values ('" + patientName + "', '" + FirstName.Text + "', '" + DoctorID + "')";
                        patientTableCommand.ExecuteNonQuery();
    /*
    read real patient id here:
    */
                       PatientID = (int)selectPatientCommand.ExecuteScalar();
    
                        testCommand.CommandText = @"Insert testreports (PDF, TestDate, patients_PatientID, TestType) Values ('test4.pdf', '" + TestDate.Text + "', '" + PatientID + "' '" + testType + "')";
                        testCommand.ExecuteNonQuery();
                        conn.Close();
                        MessageBox.Show("A new patient has been added to the database.", "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
    
                    }
                    else //if Blah is not empty - thus it is== to LastName.Text
                    {
                        //by the way: I did not check all others data you're trying to insert.
                        //you shure they are all fine for your db?
                        testCommand.CommandText = "Insert into testreports (PDF, TestDate, patients_PatientID, TestType) Values ('fred.pdf', '" + TestDate.Text + "', '3', '" + testType + "')";
                        testCommand.ExecuteNonQuery();
                        conn.Close();
                        MessageBox.Show("Test report has been uploaded", "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                    }
                    
                }catch (Exception ex)
                {
                    //here you could take actions based on the error.
                    //In this sammple I simply get any kind of error and show the error message;
                    //your application will survive, but this is not enough in a  real program.
                    MessageBox.Show(this, "An error has occurred: " +ex.Message);
                }
                finally
                {
                    if(conn!= null)
                    {
                        //be sure you close connections
                        //an alternative to this is to use the
                        // using keyword when declaring conn 
                        conn.Close();
                    }
                }
            }
    Last edited by Cimperiali; February 10th, 2012 at 09:31 AM.
    ...at present time, using mainly Net 4.0, Vs 2010



    Special thanks to Lothar "the Great" Haensler, Chris Eastwood , dr_Michael, ClearCode, Iouri and
    all the other wonderful people who made and make Codeguru a great place.
    Come back soon, you Gurus.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured