Keyword not supported: 'd:\website1\example.xls;extended pr
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'.