CODEWITHSUNDEEP
c#forms
Selase
Selase

Reputation: 175

Retrieving data from OleDb query not displaying in MessageBox

I get no exception errors with the below code which could mean there is no problem with my sql statement. However upon trying to verify if it returns any value, i pass the returned items to a string value and try to display it in a message box.

the problem am facing is the message box only displays the name of the column and not the data ive requested from the table.

what could possibly be the problem? and please advice if theres a better way to work around this... 

    public void DisplayPurchase(OleDbConnection mDB)
    {
        openDB();
        string sqlQuery;
        OleDbCommand cmd;
        OleDbDataReader rdr;



        sqlQuery = "SELECT CustomerTable.[Youth ID], CustomerTable.Firstname, " +
            "CustomerTable.Lastname, Youth.Purchaseid, Youth.NumbersOfSport, " + 
            "Youth.Price, Youth.TotalCostOfTraining, Youth.PercentageDiscount, " +
            "Youth.AmountDue, Youth.DatePurchased" +
            " FROM CustomerTable, Youth WHERE Youth.YouthID = CustomerTable.[Youth ID]" +
            " AND CustomerTable.[Youth ID] = 7";

        try
        {
            cmd = new OleDbCommand(sqlQuery, mDB);

            rdr = cmd.ExecuteReader();
            if (rdr.HasRows)
            {
                qtyInt1 = (int)rdr["Youth.NumbersOfSport"];
                youthInt1 = (int)rdr["CustomerTable.[Youth ID]"];
                firstStr1 = (string)rdr["CustomerTable.Firstname"];
                purStr1 = (string)rdr["Youth.Purchaseid"];
                lastStr1 = (string)rdr["CustomerTable.Lastname"];
                priceStr1 = (string)rdr["Youth.Price"];
                totalCstStr1 = (string)rdr["Youth.TotalCostOfTraining"];
                discountStr1 = (string)rdr["Youth.PercentageDiscount"];
                amtDueStr1 = (string)rdr["Youth.AmountDue"];
                //purDate1 = (DateTime)rdr["Youth.DatePurchased"];

                MessageBox.Show(firstStr1.ToString());

                closeDB();

            }
            else
            {
                MessageBox.Show("Reader has no rows");
                closeDB();
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }


    }

Thank you

Upvotes: 0

Views: 547

Answers (5)

Selase
Selase

Reputation: 175

Its amazing how this stuff works... Id firstly like to thank everybody that contributed to solving this problem.Am really grateful for every alphabet posted.

sqlQuery = "SELECT Youth.YouthID, Firstname, Lastname, NumbersOfSport, Price, TotalCostOFTraining, PercentageDiscount, Purchaseid, AmountDue, DatePurchased FROM CustomerTable, Youth WHERE CustomerTable.YouthID = Youth.YouthID AND Youth.YouthID = "+ toSql(youthInt);

        try
        {
            cmd = new OleDbCommand(sqlQuery, mDB);

            rdr = cmd.ExecuteReader();
            if (rdr.HasRows)
            {
                rdr.Read();
                qtyInt1 = (int)rdr["NumbersOfSport"];
                youthInt1 = (int)rdr["YouthID"];
                firstStr1 = (string)rdr["Firstname"];
                purInt1 = (int)rdr["Purchaseid"];
                lastStr1 = (string)rdr["Lastname"];
                priceStr1 = (string)rdr["Price"];
                totalCstStr1 = (string)rdr["TotalCostOfTraining"];
                discountStr1 = (string)rdr["PercentageDiscount"];
                amtDueStr1 = (string)rdr["AmountDue"];
                purDate1 = (DateTime)rdr["DatePurchased"];



                closeDB();

            }
            else
            {
                MessageBox.Show("Reader has no rows");
                closeDB();
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }


    }

I had to call for the rdr.read(); functions as well as taking of the table referencing on the database columns and each help came from a different sources..... thats awesome... Thanks everybody...

Upvotes: 0

Forgotten Semicolon
Forgotten Semicolon

Reputation: 14100

Remove the table names when you are retrieving the data:

qtyInt1 = (int)rdr["NumbersOfSport"];
youthInt1 = (int)rdr["Youth ID"];  // You may need to rename this one in the query
firstStr1 = (string)rdr["Firstname"];
purStr1 = (string)rdr["Purchaseid"];
lastStr1 = (string)rdr["Lastname"];
priceStr1 = (string)rdr["Price"];
totalCstStr1 = (string)rdr["TotalCostOfTraining"];
discountStr1 = (string)rdr["PercentageDiscount"];
amtDueStr1 = (string)rdr["AmountDue"];

Upvotes: 0

Andomar
Andomar

Reputation: 238048

Have you tried:

firstStr1 = (string)rdr["Firstname"];

The fieldname in a datareader typically does not include the tablename prefix.

Upvotes: 0

halfdan
halfdan

Reputation: 34204

You need to call Read() on the reader to read the first row.

if(rdr.HasRows) {
    rdr.Read();
    ...

Best wishes,
Fabian

Upvotes: 1

Josh Yeager
Josh Yeager

Reputation: 3793

I'm pretty sure that you have to call rdr.Read() before you can access any data from it. So, add that as the first line after if(rdr.HasRows())

Upvotes: 3

Related Questions