Error in import excel to sql query

Question

Hi friends
This is my query for getting dyanamic excel file to sql
create procedure sp_excelforgeneralholidays(@filname nvarchar(max))
as
declare @datavar varchar(200)
declare @sql varchar(500)
set @datavar = 'Excel 8.0;DATABASE=' + @filname

set nocount on
begin
set @sql = 'SELECT * FROM OPENROWSET(''Microsoft.jet.OLEDB.4.0'','''+@datavar +''', ''SELECT Data,Day,Reason FROM [Sheet1$]'')'
exec (@sql)
end

and This is the execution process

exec sp_excelforgeneralholidays 'D:\baskaran\\baskarbook1'

this is my excel file

Date	Day	Reason
26-Jan	Thursday	Republic Day
5-Feb	Sunday	Milad-Un-Nabi ( Id-E-Milad )
5-Feb	Sunday	(Birthday of Prophet Mohammad)
20-Feb	Monday	Maha Shivratri
8-Mar	Thursday	Holi
5-Apr	Thursday	Mahavir Jayanthi
6-Apr	Friday	Good Friday
1-May	Tuesday	May Day
6-May	Sunday	Buddha Purnima
10-Aug	Friday	Krishna Janmastami
15-Aug	Wednesday	Independence Day
20-Aug	Monday	Id-Ul-Fitr
19-Sep	Wednesday	Ganesh Chaturthi
2-Oct	Tuesday	Mahatma Gandhi Jayanthi
24-Oct	Wednesday	Vijaya Dashami

My table field is
Holidayid Date Day Reason createddatetime updateddatetime adminid

The error is
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.jet.OLEDB.4.0" for linked server "(null)".

i dont know how to solve this error.please help me

sudharsan s · Answer

check this code...: 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) { string dbConn = ConfigurationManager.ConnectionStrings[Excel].ConnectionString; SqlConnection conn = new SqlConnection(dbConn); conn.Open(); try { SqlDataAdapter sda = new SqlDataAdapter(select * from emp 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) { string dbConn = ConfigurationManager.ConnectionStrings[Excel].ConnectionString; SqlConnection conn = new SqlConnection(dbConn); conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = insert into emp(fnamelnamemobnumcitystatezip) 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.NVarChar).Value = zip; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); conn.Close(); }

Javeed M Shaikh · Answer

go ahead and read this. http://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx