import Excel file to sql db format error

See more:

C#

SQL

MS-Excel

Hi I am trying to build a page to load data from excel to sql server, I am getting a error message at the line

ProductID = Convert.ToInt32(SSAdapter.InsertProductQuery(PartNumber, Description, Convert.ToDecimal(UnitCost), Convert.ToDecimal(Postage), QtyAvailable.ToInt32, MakeID, DealerID));

saying that it is formatted incorrectly can anyone help with the correct format?

the whole page is

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Security;
using System.Data.OleDb;
using System.Data.SqlClient;
using CarpartsStore.Data_Access;
using CarpartsStore.Account;
using CarpartsStore;
using CarpartsStore.DataSets;
using System.Data;

namespace CarpartsStore.Dealers
{

    partial class DealerHome : System.Web.UI.Page
    {

        protected void ButtonUpload_Click(object sender, System.EventArgs e)
        {
            PanelUpload.Visible = true;
            PanelView.Visible = false;
            PanelImport.Visible = false;
        }


        protected OleDbCommand ExcelConnection()
        {

            // Connect to the Excel Spreadsheet
            string xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/ExcelImport.xls") + ";" + "Extended Properties=Excel 8.0;";

            // create your excel connection object using the connection string
            OleDbConnection objXConn = new OleDbConnection(xConnStr);
            objXConn.Open();

            // use a SQL Select command to retrieve the data from the Excel Spreadsheet
            // the "table name" is the name of the worksheet within the spreadsheet
            // in this case, the worksheet name is "Members" and is coded as: [Members$]
            OleDbCommand objCommand = new OleDbCommand("SELECT * FROM [Products$]", objXConn);
            return objCommand;
            
            

        }

        protected void ButtonView_Click(object sender, System.EventArgs e)
        {
            PanelUpload.Visible = false;
            PanelView.Visible = true;
            PanelImport.Visible = false;

            // Create a new Adapter
            OleDbDataAdapter objDataAdapter = new OleDbDataAdapter();

            // retrieve the Select command for the Spreadsheet
            objDataAdapter.SelectCommand = ExcelConnection();

            // Create a DataSet
            DataSet objDataSet = new DataSet();
            // Populate the DataSet with the spreadsheet worksheet data
            objDataAdapter.Fill(objDataSet);

            // Bind the data to the GridView
            GridViewExcel.DataSource = objDataSet.Tables[0].DefaultView;
            GridViewExcel.DataBind();
        }


        protected void ButtonImport_Click(object sender, System.EventArgs e)
        {
            PanelUpload.Visible = false;
            PanelView.Visible = false;
            PanelImport.Visible = true;
            LabelImport.Text = "";
            // reset to blank

            // retrieve the Select Command for the worksheet data
            OleDbCommand objCommand = new OleDbCommand();
            objCommand = ExcelConnection();

            //  create a DataReader
            OleDbDataReader reader;
            reader = objCommand.ExecuteReader();


            //  create variables for the spreadsheet columns
            int ProductID = 0;
            int MakeID = 0;
            int DealerID = 0;
            string PartNumber = "";
            string Description = "";
            string UnitCost = "";
            string Postage = "";
            string QtyAvailable = "";
            string UserName = "";
            string Make = "";


            int counter = 0;
            // used for testing your import in smaller increments

            while (reader.Read())
            {
                counter = counter + 1;
                // counter to exit early for testing...

                // set default values for loop
                ProductID = 0;
                MakeID = 0;
                DealerID = 0;
                


                PartNumber = GetValueFromReader(reader,"PartNumber");
                Description = GetValueFromReader(reader,"Description");

                
                UnitCost = GetValueFromReader(reader,"UnitCost");
                Postage = GetValueFromReader(reader, "Postage"); 
                

                QtyAvailable = GetValueFromReader(reader,"QtyAvailable");
                UserName = GetValueFromReader(reader,"UserName");
                Make = GetValueFromReader(reader,"Make");


                // Insert any required validations here...

                MakeID = GetMakeID(Make);
                DealerID = GetDealerID(UserName); 
                

                //retrieve the MakeID
                ProductID = ImportIntoProducts(PartNumber, Description, UnitCost, Postage, QtyAvailable, MakeID, DealerID);

                LabelImport.Text = LabelImport.Text + ProductID + PartNumber + " " + Description + " " + UnitCost + " " + Postage + " " + QtyAvailable + " " + UserName + " Make_id: " + MakeID + " " + Make + "<br>";
                //If counter > 2 Then ' exit early for testing, comment later...
                //    Exit While
                //End If

            }
            reader.Close();

        }



        protected string GetValueFromReader(OleDbDataReader myreader, string stringValue)
        {
            object val = myreader[stringValue];
            if (val != DBNull.Value)
                return val.ToString();
            else
                return "";
        }


        protected void ButtonUploadFile_Click(object sender, System.EventArgs e)
        {

            if (FileUploadExcel.HasFile)
            {


                try
                {
                    // alter path for your project

                    FileUploadExcel.SaveAs(Server.MapPath("~/ExcelImport.xls"));
                    LabelUpload.Text = "Upload File Name: " +
                        FileUploadExcel.PostedFile.FileName + "<br>" +
                        "Type: " + FileUploadExcel.PostedFile.ContentType +
                        " File Size: " + FileUploadExcel.PostedFile.ContentLength +
                        " kb<br>";
                }
                catch (System.NullReferenceException ex)
                {
                    LabelUpload.Text = "Error: " + ex.Message;
                }
            }
            else
            {
                LabelUpload.Text = "Please select a file to upload.";
            }

        }



        protected int GetMakeID(string MakeName)
        {

            int makeID = 0;
            try
            {
                CarpartsStore.DataSets.SSSProductsDataSetTableAdapters.MakesTableAdapter SSAdapter = new CarpartsStore.DataSets.SSSProductsDataSetTableAdapters.MakesTableAdapter();
                SSSProductsDataSet.MakesDataTable SSDataTable = null;
                SSDataTable = SSAdapter.GetMakeByName(MakeName);
                // see if the category already exists in the table, if not insert it
                if (SSDataTable != null)
                {
                    if (SSDataTable.Rows.Count > 0)
                    {
                        if (SSDataTable[0].MakeID > 0)
                        {
                            makeID = SSDataTable[0].MakeID;
                        }
                    }
                }
                if (makeID == 0)
                {
                    // if it is still 0, then insert it into the table
                    // retrieve the identity key category_id from the insert
                    makeID = (int)SSAdapter.InsertMakeQuery(MakeName);
                    // if this fails to return the proper category_id, make sure to 
                    // set the InsertCategoryQuery ExecuteMode Property to Scalar
                }
                return makeID;
            }
            catch (System.NullReferenceException ex)
            {
                LabelImport.Text = LabelImport.Text + ex.Message;
                return 0;
            }

        }

        protected int GetDealerID(string UserName)
        {

            int DealerID = 0;
            try
            {
                CarpartsStore.DataSets.SSSProductsDataSetTableAdapters.DealersTableAdapter SSAdapter = new CarpartsStore.DataSets.SSSProductsDataSetTableAdapters.DealersTableAdapter();
                SSSProductsDataSet.DealersDataTable SSDataTable = null;
                SSDataTable = SSAdapter.GetDealersByUserName(UserName);
                // see if the User already exists in the table, if not insert it
                if (SSDataTable != null)
                {
                    if (SSDataTable.Rows.Count > 0)
                    {
                        if (SSDataTable[0].DealerID > 0)
                        {
                            DealerID = SSDataTable[0].DealerID;
                        }
                    }
                }
                if (DealerID == 0)
                {
                    // if it is still 0, then insert it into the table
                    // retrieve the identity key category_id from the insert
                    DealerID = 0;
                    // if this fails to return the proper category_id, make sure to 
                    // set the InsertCategoryQuery ExecuteMode Property to Scalar
                }
                return DealerID;
            }
            catch (System.NullReferenceException ex)
            {
                LabelImport.Text = LabelImport.Text + ex.Message;
                return 0;
            }

        }


        protected int ImportIntoProducts(string PartNumber, string Description, string UnitCost, string Postage, string QtyAvailable, int MakeID, int DealerID)
        {


            // make sure values don't exceed column limits
            PartNumber = Left(PartNumber, 50);
            Description = Left(Description, 300);
           
            
            


            int ProductID = 0;
            try
            {
                CarpartsStore.DataSets.SSSProductsDataSetTableAdapters.ProductsTableAdapter SSAdapter = new CarpartsStore.DataSets.SSSProductsDataSetTableAdapters.ProductsTableAdapter();
                SSSProductsDataSet.ProductsDataTable SSDataTable = null;
                SSDataTable = SSAdapter.GetProductsByPartNumberDealer(PartNumber, DealerID);
                // see if the category already exists in the table, if not insert it
                if (SSDataTable != null)
                {
                    if (SSDataTable.Rows.Count > 0)
                    {
                        if (SSDataTable[0].ProductID > 0)
                        {
                            ProductID = SSDataTable[0].ProductID;
                            LabelImport.Text = LabelImport.Text + "<font color=blue>PartNumber Found, Not Imported: " + " ID: " + ProductID + " " + PartNumber + " " + Description + "" + UnitCost + "" + Postage + ".</font><br>";
                        }
                    }
                }
                if (ProductID == 0)
                {
                    // if it is still 0, then insert it into the table
                    // retrieve the identity key ProductID from the insert
                    ProductID = Convert.ToInt32(SSAdapter.InsertProductQuery(PartNumber, Description,Convert.ToDecimal(UnitCost),Convert.ToDecimal(Postage),Convert.ToInt32(QtyAvailable), MakeID, DealerID));
                    LabelImport.Text = LabelImport.Text + "<font color=white>Part Number Imported: " + " ID: " + ProductID + " " + PartNumber + " " + Description + " Cost: " + UnitCost + ".</font><br>";
                }
                return ProductID;
            }
            catch (System.NullReferenceException ex)
            {
                LabelImport.Text = LabelImport.Text + "<font color=red>" + ex.Message + "</font><br>";
                return 0;
            }

        }



        public static string Left(string text, int length)
        {
            if (length < 0)
                throw new ArgumentOutOfRangeException("length", length, "length must be > 0");
            else if (length == 0 || text.Length == 0)
                return "";
            else if (text.Length <= length)
                return text;
            else
                return text.Substring(0, length);

        }

    }
}

Would be greatful for any help thanks