We help IT Professionals succeed at work.

asp.net / excel upload

Webboy2008
Webboy2008 used Ask the Experts™
on
The Microsoft Jet database engine could not find the object 'Sheet1$'.  Make sure the object exists and that you spell its name and the path name correctly
Error Pointing to:
OleDbDataReader odr = ocmd.ExecuteReader();

Any ideas why? I am sure that the example excel is in the same directory as the webpage.

Thanks,
protected void insertdata_Click(object sender, EventArgs e)
    {
        OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("example.xls") + "; Extended Properties=Excel 8.0");
        try
        {
            OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
            oconn.Open();
            OleDbDataReader odr = ocmd.ExecuteReader();
            string fname = "";
            string lname = "";
            string mobnum = "";
            string city = "";
            string state = "";
            string zip = "";
            while (odr.Read())
            {
                fname = valid(odr, 0);
                lname = valid(odr, 1);
                mobnum = valid(odr, 2);
                city = valid(odr, 3);
                state = valid(odr, 4);
                zip = valid(odr, 5);
                insertdataintosql(fname, lname, mobnum, city, state, zip);
            }
            oconn.Close();
        }
        catch (DataException ee)
        {
            lblmsg.Text = ee.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            lblmsg.Text = "Data Inserted Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Green;
        }
    }
    protected string valid(OleDbDataReader myreader, int stval)//if any columns are found null then they are replaced by zero
    {
        object val = myreader[stval];
        if (val != DBNull.Value)
            return val.ToString();
        else
            return Convert.ToString(0);
    }
    protected void viewdata_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection("Data Source=173.248.136.218,1533;Initial Catalog=aviation;User ID=sa;Password=&$RnY20#$%;Trusted_Connection=yes");       
        try
        {
            SqlDataAdapter sda = new SqlDataAdapter("select * from products", conn);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
        catch (DataException de)
        {
            lblmsg.Text = de.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            lblmsg.Text = "Data Shown Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Green;
        }
    }
    public void insertdataintosql(string fname, string lname, string mobnum, string city, string state, string zip)
    {
        SqlConnection conn = new SqlConnection("Data Source=173.248.136.218,1533;Initial Catalog=aviation;User ID=sa;Password=&$RnY20#$%;Trusted_Connection=yes");
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = "insert into Products(AltPartNo01,AltPartNo02,AltPartNo03,AltPartNo04,AltPartNo05) values(@fname,@lname,@mobnum,@city,@state,@zip)";
        cmd.Parameters.Add("@fname", SqlDbType.NVarChar).Value = fname;
        cmd.Parameters.Add("@lname", SqlDbType.NVarChar).Value = lname;
        cmd.Parameters.Add("@mobnum", SqlDbType.NVarChar).Value = mobnum;
        cmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = city;
        cmd.Parameters.Add("@state", SqlDbType.NVarChar).Value = state;
        cmd.Parameters.Add("@zip", SqlDbType.Int).Value = Convert.ToInt32(zip);
        cmd.CommandType = CommandType.Text;
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
    }
    protected void deletedata_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection("Data Source=173.248.136.218,1533;Initial Catalog=aviation;User ID=sa;Password=&$RnY20#$%;Trusted_Connection=yes");
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "delete from emp";
            cmd.CommandType = CommandType.Text;
            conn.Open();
            cmd.ExecuteScalar();
            conn.Close();
        }
        catch (DataException de1)
        {
            lblmsg.Text = de1.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            lblmsg.Text = "Data Deleted Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
    }

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
private void btnImportExcelToGrid_Click(object sender,
System.EventArgs e)
{
String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=C:\\Book2.xls;" +
"Extended Properties=Excel 8.0;";

DataSet ds = new DataSet();
//You must use the $ after the object
//you reference in the spreadsheet
OleDbDataAdapter da = new OleDbDataAdapter
("SELECT * FROM [Sheet1$]", strConn);

//da.TableMappings.Add("Table", "ExcelTest");

da.Fill(ds);
DataGrid2.DataSource = ds.Tables[0].DefaultView;
DataGrid2.DataBind();
}
Commented:
flip line 6 & 7 like so:

            oconn.Open();
            OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
Are you sure the sheet name in the excel file is Sheet1 or renamed to some other name?