C# database query error message?

Question

I am currently working on my Final for my second programming class in college and have run into a problem when doing a search of by database for a specific employee name, if the user enters a user name that is not in the database, the program crashes giving errors, instead of displaying the error message I created.

Method for Searching:

public void searchNameDbMethod()
    {
        OleDbConnection Myconnection = null;
        OleDbDataReader dbReader = null;
        string selectionText = "";
        bool errorFlag = true;

        do
        {
            string searchName = "";
            Console.Write("Search for Employee Name: ");
            searchName = Console.ReadLine();
            searchName = searchName.ToUpper();
            Console.WriteLine("\n");

            Myconnection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source= payrolldb.accdb");

            Myconnection.Open();
            selectionText = "SELECT * FROM Table1;";
            OleDbCommand cmd = Myconnection.CreateCommand();
            cmd.CommandText = selectionText;
            dbReader = cmd.ExecuteReader();

            if (dbReader.HasRows)
            {
                while (dbReader.Read())
                {
                    // since the query produces one column, the GetValue(0)           
                    //must be set                
                    // with multiple column queries, you have to know which  
                    //column holds
                    // the value that you are looking for                     
                    //field 0 = ID
                    dbName = dbReader.GetValue(1).ToString();         //1 is field 1 of the db
                    if (dbName == searchName)
                    {
                        dbName = dbReader.GetValue(1).ToString();         //1 is field 1 of the db
                        dbID = dbReader.GetValue(2).ToString();    //2 is field 2 of the db
                        dbHourlyWage = dbReader.GetValue(3).ToString();
                        dbDependents = dbReader.GetValue(4).ToString();

                        Console.Clear();
                        Console.ResetColor();
                        Console.ForegroundColor = ConsoleColor.Green;
                        Console.WriteLine("*******************************************************");
                        Console.WriteLine("**************** {0} *****************", date);
                        Console.WriteLine("*******************************************************");
                        Console.WriteLine("Employee Name: ", dbName);
                        Console.WriteLine("Employee ID: {0}", dbID);
                        Console.WriteLine("Hourly Wage: {0}", dbHourlyWage);
                        Console.WriteLine("Number of Dependents: {0}", dbDependents);
                        Console.WriteLine("*******************************************************");
                        Console.ResetColor();
                        Console.Write("\n\n");

                        errorFlag = false;
                    }//closes if             
                }// end of while
            }// end of if
            if (errorFlag == true)
            {
                Console.ResetColor();
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine("Name is not in our database!");//shows the data accumulated from above       
                Console.ResetColor();
            }//closes if
            dbReader.Close();
            Myconnection.Close();
        }//close do
        while (errorFlag == true);
    }//closes searchNameDbMethod

The Error: http://puu.sh/4cPWU.png

Please keep in mind that I am using Microsoft Access for my database for this project (not sure that it matters).

How do I make this so that if the name is not found in the DB that it will show my created error message instead of the error (crashing the program) in the image linked?

Answer 1

I was able to figure it our based on some of the code provided by @BendEg

Here is what I did:

public void searchNameDbMethod()
    {
        OleDbConnection Myconnection = null;
        OleDbDataReader dbReader = null;
        string selectionText = "";
        bool errorFlag = true;

        do
        {
            string searchName = "";
            Console.Write("Search for Employee Name: ");
            searchName = Console.ReadLine();
            searchName = searchName.ToUpper();
            Console.WriteLine("\n");

            Myconnection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source= payrolldb.accdb");

            Myconnection.Open();
            selectionText = "SELECT * FROM Table1 WHERE employee_name='" + searchName + "'";
            OleDbCommand cmd = Myconnection.CreateCommand();
            cmd.CommandText = selectionText;
            dbReader = cmd.ExecuteReader();

            if (dbReader.HasRows)
            {
                while (dbReader.Read())
                {
                    // since the query produces one column, the GetValue(0)           
                    //must be set                
                    // with multiple column queries, you have to know which  
                    //column holds
                    // the value that you are looking for                     
                    //field 0 = ID
                    dbName = dbReader.GetValue(1).ToString();         //1 is field 1 of the db
                    if (dbName == searchName)
                    {
                        dbName = dbReader.GetValue(1).ToString();         //1 is field 1 of the db
                        dbID = dbReader.GetValue(2).ToString();    //2 is field 2 of the db
                        dbHourlyWage = dbReader.GetValue(3).ToString();
                        dbDependents = dbReader.GetValue(4).ToString();

                        Console.Clear();
                        Console.ResetColor();
                        Console.ForegroundColor = ConsoleColor.Green;
                        Console.WriteLine("*******************************************************");
                        Console.WriteLine("**************** {0} *****************", date);
                        Console.WriteLine("*******************************************************");
                        Console.WriteLine("Employee Name: ", dbName);
                        Console.WriteLine("Employee ID: {0}", dbID);
                        Console.WriteLine("Hourly Wage: {0}", dbHourlyWage);
                        Console.WriteLine("Number of Dependents: {0}", dbDependents);
                        Console.WriteLine("*******************************************************");
                        Console.ResetColor();
                        Console.Write("\n\n");

                        errorFlag = false;
                    }//closes if             
                }// end of while
            }// end of if
            else if (!dbReader.HasRows)
            {
                Console.ResetColor();
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine("Name is not in our database!");//shows the data accumulated from above       
                Console.ResetColor();
            }//closes if
            dbReader.Close();
            Myconnection.Close();
        }//close do
        while (errorFlag == true);
    }//closes searchNameDbMethod

As you can see I changed my query like he suggested (but without the extra complicated stuff), and changed my if (errorFlag == true) statement to else if (!dbReader.HasRows) and it seems to work like a charm!

Answer 2

i think you should search your employee in a SQL-Where-Clause.

selectionText = "SELECT * FROM Table1 WHERE <EmployeeName> like @Name;";

And add a parameter to your SQL-Query.

General i would write my code like this:

void searchNameDbMethod()
{
    OleDbConnection Myconnection = null;
    OleDbDataReader dbReader = null;
    string selectionText = "";


    string searchName = "";
    Console.Write("Search for Employee Name: ");
    searchName = Console.ReadLine();
    searchName = searchName.ToUpper();
    Console.WriteLine("\n");

    Myconnection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source= payrolldb.accdb");

    Myconnection.Open();
    selectionText = "SELECT * FROM Table1 WHERE Employee like @Name;";
    OleDbCommand cmd = Myconnection.CreateCommand();
    cmd.CommandText = selectionText;

    cmd.Parameters.Add(new OleDbParameter() { ParameterName = "@Name", Value = searchName, DbType = System.Data.DbType.String });

    try
    {
        dbReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

        while (dbReader.Read())
        {
            // since the query produces one column, the GetValue(0)           
            //must be set                
            // with multiple column queries, you have to know which  
            //column holds
            // the value that you are looking for                     
            //field 0 = ID
            string dbName = dbReader.GetValue(1).ToString();         //1 is field 1 of the db
            if (dbName == searchName)
            {
                dbName = dbReader.GetValue(1).ToString();         //1 is field 1 of the db
                string dbID = dbReader.GetValue(2).ToString();    //2 is field 2 of the db
                string dbHourlyWage = dbReader.GetValue(3).ToString();
                string dbDependents = dbReader.GetValue(4).ToString();

                Console.Clear();
                Console.ResetColor();
                Console.ForegroundColor = ConsoleColor.Green;
                Console.WriteLine("*******************************************************");
                Console.WriteLine("**************** {0} *****************", date);
                Console.WriteLine("*******************************************************");
                Console.WriteLine("Employee Name: ", dbName);
                Console.WriteLine("Employee ID: {0}", dbID);
                Console.WriteLine("Hourly Wage: {0}", dbHourlyWage);
                Console.WriteLine("Number of Dependents: {0}", dbDependents);
                Console.WriteLine("*******************************************************");
                Console.ResetColor();
                Console.Write("\n\n");
            }
        }
    }
    catch
    {
        if (dbReader != null)
        {
            dbReader.Close();
        }
    }
    finally
    {
        if (Myconnection != null)
        {
            Myconnection.Close();
        }
    }
}

Only an example, to improve your solution.