Click here to Skip to main content
12,697,311 members (28,133 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C#
i am exporting excel to the database for that i written the code has follows;

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.IO;


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

    }
    protected void insertdata_Click(object sender, EventArgs e)
    {
        
SqlConnection oconn = new SqlConnection("Data Source=.; Initial catalog=master;" + Server.MapPath("Example.xls") + ";Extended Properties=Excel 8.0");
             try
        {
           
            SqlCommand ocmd = new SqlCommand("select * from Example", oconn);
            oconn.Open();  //Here [Sheet1$] is the name of the sheet 
            SqlDataReader odr = ocmd.ExecuteReader();
            string fname = "";
            string lname = "";
            string city = "";
            
            while (odr.Read())
            {
                fname = valid(odr, 0);//Here we are calling the valid method
                lname = valid(odr, 1);
                city = valid(odr, 3);
                insertdataintosql(fname, lname, city);
            }
            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;
        }

    }
    public void insertdataintosql(string fname, string lname,string city)
    {
       
        SqlConnection oconn = new SqlConnection("Data Source=.; Initial catalog=master;" + Server.MapPath("Example.xls") + ";Extended Properties=Excel 8.0");
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = oconn;
        cmd.CommandText = "insert into Example(fname,lname,city)values(@fname,@lname,@city)";
        cmd.Parameters.Add("@fname", SqlDbType.NVarChar).Value = fname;
        cmd.Parameters.Add("@lname", SqlDbType.NVarChar).Value = lname;
        cmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = city;
        cmd.CommandType = CommandType.Text;
        oconn.Open();
        cmd.ExecuteNonQuery();
        oconn.Close();
    }
    protected string valid(SqlDataReader myreader, int stval)//if any columns are 
    {
        object val = myreader[stval];
        if (val != DBNull.Value)
            return val.ToString();
        else
            return Convert.ToString(0);
    }
}



when i run this code i got the error as follows;

Keyword not supported: 'd:\website1\example.xls;extended properties'.
Posted 30-Dec-12 5:00am
Updated 30-Dec-12 5:13am
v2
Comments
jibesh 1-Jan-13 5:37am
   
can you copy the full exception stack here for better understanding of the error?
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

I think you forgot to tell it what the parameter was!
Your string evaluates to:
Data Source=.; Initial catalog=master;d:\website1\example.xls;Extended Properties=Excel 8.0
I suspect that you wanted:
Data Source=d:\website1\example.xls; Initial catalog=master;Extended Properties=Excel 8.0
Or similar.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.170118.1 | Last Updated 30 Dec 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100