[ryang112]

Hi there, I keep getting a FormatException in the following code block

  OrderLine  ordLine = new OrderLine();

                //Load all the 'finalized' dummyLines into a datatable (this will be used to loop through and set parameters later)

                dTab = DAL.GetAllDummyLinesForOrder(setRealOrderFromDummy);

                foreach (DataRow row in dTab.Rows)
                {
                        
                        
                       //'Get' values from the datable and use them to SET the values in the OrderLineClass
                        ordLine.OrderLineID = SqlInt32.Parse(dTab.Columns["OrderLineID"].ToString()); //Format Exception Being Thrown Here... WHY??
                        ordLine.OrderID = SqlInt32.Parse(dTab.Columns["OrderID"].ToString());
                        ordLine.ProductID = SqlInt32.Parse(dTab.Columns["ProductID"].ToString());
                        ordLine.Quantity = SqlInt32.Parse(dTab.Columns["Quantity"].ToString());
                        ordLine.LineValue = SqlMoney.Parse(dTab.Columns["LineValue"].ToString());
                        ordLine.VatID = SqlInt32.Parse(dTab.Columns["VatID"].ToString());
                        //Note IsActive is simply hardcoded
                        ordLine.IsActive = "Y";
                        //Add OrderLine With The Above Parameters
                        int x = DAL.AddOrderLine(ordLine);             
                        
                   
                }

Apologies for comments being messy, my ordLine is an instance class held in an external DLL. I have tried using:

ordLine.OrderLineID = SqlInt32.Parse(dTab.Rows[0].ToString());

as well with no success. I have checked the value of the datable dTab, and it does infact have rows and columns, so no error there right? The column names used in the foreach loop are the same as those in the SQL database, and thier datatypes match those of the class properties I am attempting to set. Even if i put in a try....catch the end result is these rows are not commited to my database.
Below is the code for the method DAL.AddOrderLine:

 //Add Order Line
        public int AddOrderLine(OrderLine orderLine)
        {
            using (SqlConnection dbConnection = new SqlConnection(DBConn))
            {
                int x;
                SqlCommand dbCmd = new SqlCommand("uspAddOrderLine", dbConnection);
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.AddWithValue("@orderLineID", orderLine.OrderLineID);
                dbCmd.Parameters.AddWithValue("@orderID", orderLine.OrderID);
                dbCmd.Parameters.AddWithValue("@productID", orderLine.ProductID);
                dbCmd.Parameters.AddWithValue("@quantity", orderLine.Quantity);
                dbCmd.Parameters.AddWithValue("@orderLineValue", orderLine.LineValue);
                dbCmd.Parameters.AddWithValue("@vatID", orderLine.VatID);
                dbCmd.Parameters.AddWithValue("@isActive", orderLine.IsActive);

                try
                {

                    if (dbConnection.State == ConnectionState.Closed)
                    {
                        dbConnection.Open();
                    }

                    x = dbCmd.ExecuteNonQuery();
                }

                catch (SqlException e)
                {
                    exc = new Exceptions();
                    exc.Exception = e;
                    logExc = new LogException();
                    logExc.LogExceptionToFile();
                    throw new ApplicationException("Error occured in the database and / or connection");
                }

                //Realease Resources
                dbConnection.Close();
                dbConnection.Dispose();
                dbCmd.Dispose();
                return x;
            }

        }

this is contained in a class in another (seperate) DLL.

Any assistance will be appreciated, I hope I have given enough information

[JackOfAllTrades]

Are you stepping through the code in the debugger? What is the value of dTab.Columns["OrderLineID"] that's failing?

[ryang112]

I have stepped through it yes, I suspect its that value but I cant find it in locals? I have attached a screen snip on my debugger and how I am attempting to find the value of it in locals.

Thanks for the speedy Reply

[ryang112]

I have stepped through it yes, I suspect its that value but I cant find it in locals? I have attached a screen snip on my debugger and how I am attempting to find the value of it in locals.

Thanks for the speedy Reply

[JackOfAllTrades]

Set a watch for the variable and step through the code. When the variable gets a value, the watch will be set with that value and you can see what it is.

Alternately, you can likely find the values under the Non-Public members. Don't have VS available, so can't be more specific than that at this point.

[ryang112]

Thanks very much I will try that.

[ryang112]

Its defintely the

SqlInt32.Parse(dTab.Columns["OrderLineID"].ToString());

part of the statement but i still cant see its value.. as its value is set to "It threw an exception etc".. so i have no idea if it reverted to null or something...

[tlhIn`toq]

This is a good example of how complex lines might seem easier to read but make debugging harder

break down the line so you can step through it.

ordLine.OrderID = SqlInt32.Parse(dTab.Columns["OrderID"].ToString());
//breaks down to
var testing = dTab.Columns["OrderID"];// At this point you can see the actual value regardless what it is
var testingString = testing.ToString();'
var testingSql = SqlInt32.Parse(testingString);
ordLine.OrderID = testingSql;

This post has been edited by tlhIn`toq: 22 August 2012 - 09:13 AM

[ryang112]

Ahaaah very very clever I will try that.. Thanks very much

[ryang112]

hey guys, saw the strangest thing, its passing the column name! Please see the picture i've included.. any ideas

[ryang112]

P.S i tried using the columns index, same result

[ryang112]

I found the solution! Below is the corrected code

   foreach (DataRow row in dTab.Rows)
                {
                       //'Get' values from the datable and use them to SET the values in the OrderLineClass
                        ordLine.OrderLineID = SqlInt32.Parse(row[0].ToString());
                        ordLine.OrderID = SqlInt32.Parse(row[1].ToString());
                        ordLine.ProductID = SqlInt32.Parse(row[2].ToString());
                        ordLine.Quantity = SqlInt32.Parse(row[3].ToString()); 
                        ordLine.LineValue = SqlMoney.Parse(row[5].ToString());
                        ordLine.VatID = SqlInt32.Parse(row[6].ToString());
                        //Note IsActive is simply hardcoded
                        ordLine.IsActive = "Y";
                        //Add OrderLine With The Above Parameters
                        int x = DAL.AddOrderLine(ordLine);             
                }

Thanks everyone for your input : ) it helped greatly