Click here to Skip to main content
15,944,390 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have write the below code to Import Excel Sheet data into SQL Server using ASP.Net...An error occur at this query 0bject count = cmd.ExecuteNonQuery(); and display this message
Could not find stored procedure 'spx_Import From Excel 2007'

C#
public partial class Admin_Impoert_Excell_File : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void upload_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
            string FilePath = Server.MapPath(FolderPath + FileName);
            FileUpload1.SaveAs(FilePath);
            GetExcelSheets(FilePath, Extension, "Yes");
        }
    }

        private void GetExcelSheets(string FilePath, string Extension, string isHDR)
    {
        string conStr="";
        switch (Extension)
        {
           // case ".xls": //Excel 1997-2003
               // conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
               // break;
            case ".xlsx": //Excel 2007
                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
        }
        //Get the Sheets in Excel WorkBook
        conStr = String.Format(conStr, FilePath, isHDR);
        OleDbConnection connExcel = new OleDbConnection(conStr);
        OleDbCommand cmdExcel = new OleDbCommand();
        OleDbDataAdapter oda = new OleDbDataAdapter();
        cmdExcel.Connection = connExcel;
        connExcel.Open();

        //Bind the Sheets to DropDownList
        ddlSheets.Items.Clear();
        ddlSheets.Items.Add(new ListItem("--Select Sheet--", ""));
        ddlSheets.DataSource = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        ddlSheets.DataTextField = "TABLE_NAME";
        ddlSheets.DataValueField = "TABLE_NAME";
        ddlSheets.DataBind();
        connExcel.Close();
        txtTable.Text = "";
       displayname.Text = Path.GetFileName(FilePath);
        upload.Visible = false;
        FileUpload1.Visible = false;
        Panel1.Visible = true; 

    }

        protected void btnSave_Click(object sender, EventArgs e)
        {
            string FileName = displayname.Text;
            string Extension = Path.GetExtension(FileName);
            string FolderPath = Server.MapPath(ConfigurationManager.AppSettings["FolderPath"]);
            string CommandText = "";
            switch (Extension)
            {
                //case ".xls": //Excel 97-03
                 //   CommandText = "spx_ImportFromExcel03";
                  //  break;
                case ".xlsx": //Excel 2007
                    CommandText = "spx_Import From Excel 2007";
                    break;
            }
            //Read Excel Sheet using Stored Procedure
            //And import the data into Database Table
            String strConnString = ConfigurationManager.AppSettings["OTAConnectionString"];
            SqlConnection con = new SqlConnection(strConnString);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = CommandText;
            cmd.Parameters.Add("@SheetName", SqlDbType.VarChar).Value = ddlSheets.SelectedItem.Text;
            cmd.Parameters.Add("@FilePath", SqlDbType.VarChar).Value = FolderPath + FileName;
            cmd.Parameters.Add("@HDR", SqlDbType.VarChar).Value = yes.Text;
            cmd.Parameters.Add("@TableName", SqlDbType.VarChar).Value = txtTable.Text;
            cmd.Connection = con;
            try
            {
                con.Open();
                object count = cmd.ExecuteNonQuery();
                lblmessage.ForeColor = System.Drawing.Color.Green;
                lblmessage.Text = count.ToString() + " records inserted.";
            }
                       
        }

        
}
Posted
Updated 9-Sep-14 8:22am
v2

1 solution

The error message is quite clear: your database doesn't contain a stored procedure called spx_Import From Excel 2007.

Are you sure those spaces are supposed to be there? Looking at your 2003 branch, I'd expect the procedure to be called spx_ImportFromExcel2007 instead.
 
Share this answer
 
Comments
mudi lala 9-Sep-14 14:53pm    
I have a store procedure spx_Import From Excell 2007 ....but i dont know where to add the(spx_Import From Excel 2007.) in database...
someone explain me with steps??

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900