How to insert the Dataset value into Excel File(Exiting file) using OLEDB in C#

[vasanth chandrasekaran]

Hai All,

I try to insert the Dataset value in Exiting excel file

by using OLEDB provider.But i face error messagge in

objCmd.ExecuteN onQuery();

The error is :Syntax error in INSERT INTO statement.

Framework: 3.5
GUI: Visual studio 2008

Please Help me.Its most important one.

This is my code:

try
{
DataSet ds1;
OleDbConnection ConnSql;
OleDbConnection ConnExcel;
OleDbDataAdapte r da1;
OleDbDataAdapte r da2;
string ConnStringSql = "Provider=sqlol edb;Data Source = cst;Initial Catalog = Testido;User ID=sa;Pwd=conne ct#963;";

string OutputFilename = "D:\\Misreport. xls";

string ConnStringExcel = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" + OutputFilename + ";Extended Properties=\"Ex cel 8.0;HDR=yes;\"" ;


string sqlSelect = "select Day(joborder.Cr eatedDate)as [DateoftheMonth],CONVERT(varcha r(3),joborder.C reatedDate, 100) as [MonthName], joborder.custom erid, joborder.jobord ernumber, tasklist.employ eeid AS recruiterId, employeedetails .empname as recruitername, tasklist.Status , prescreening.pr escreeningid, customerdetails .customername, joborder.jobord ertitle,joborde rprimarymandato ryski lls.mandatorysk ills,joborder.w orklocation,par sedfi le.source,parse dfile.candidate name,prescreeni ng.to talexperienceye ars,prescreenin g.totalexperien cemon ths,parsedfile. currentorganisa tion,parsedfile .curr entctc,parsedfi le.expectedctc, prescreening.no ticep eriodmonths,pre screening.notic eperioddays,par sedfi le.presentlocat ion,parsedfile. mobilenumber,pr escre ening.landlinen umber,parsedfil e.candidateemai lid,r esult.statuscod e,CONVERT(VARCH AR(10),result.I nterv iewScheduleDate ,110) as [InterviewSchedu leDate],convert(varcha r,result.Interv iewScheduleFrom Time, 8) as [InterviewSchedu leFromTime],convert(varcha r,result.Interv iewScheduleToTi me,8) as [InterviewSchedu leToTime],CONVERT(VARCHA R(10),result.Da teOfJoining,110 ) as [DateOfJoining],result.notes,j oborder.created date from joborder inner join tasklist on joborder.jobord ernumber=taskli st.jobordernumb er inner join employeedetails on tasklist.employ eeid=employeede tails.employeei d inner join customerdetails on joborder.custom erid=customerde tails.customeri d inner join joborderprimary mandatoryskills on joborder.jobord ernumber=jobord erprimarymandat orysk ills.jobordernu mber inner join prescreeningidj obordernumberma pping on joborder.jobord ernumber=prescr eeningidjoborde rnumb ermapping.jobor dernumber and prescreeningidj obordernumberma pping.createdby = employeedetails .employeeid inner join prescreening on prescreeningidj obordernumberma pping.prescreen ingid =prescreening.p rescreeningid and prescreening.cr eatedby = employeedetails .employeeid inner join parsedfile on prescreening.pa rsedfileid=pars edfile.parsedfi leid left outer join (select r.prescreeningi d as prescreeningid, interviewstatus schedule.status code as statuscode, interviewstatus schedule.Interv iewScheduleDate ,inte rviewstatussche dule.InterviewS cheduleFromTime ,inte rviewstatussche dule.InterviewS cheduleToTime,i nterv iewstatusschedu le.DateOfJoinin g, interviewstatus schedule.notes from(select interviewstatus schedule.prescr eeningid,max(in tervi ewstatusschedul eid) as interviewstatus scheduleid from interviewstatus schedule where interviewstatus schedule.create ddate between '4/15/2010' and '7/26/2010' group by interviewstatus schedule.prescr eeningid)as r inner join interviewstatus schedule on r.interviewstat usscheduleid=in terviewstatussc hedul e.interviewstat usscheduleid) as result on prescreening.pr escreeningid=re sult.prescreeni ngid where joborder.create ddate between '4/15/2010' and '7/26/2010' and tasklist.employ eeid in ('10003','10004 ') and tasklist.status ='ACCEPTED' and joborder.custom erid in('5','6','4') ";

OleDbCommand objCmd = new OleDbCommand();
//Read the Data from database


ConnSql = new OleDbConnection (ConnStringSql) ;
ConnExcel = new OleDbConnection (ConnStringExce l);

ConnSql.Open();
ConnExcel.Open( );

da1 = new OleDbDataAdapte r();
da1.SelectComma nd = new OleDbCommand(sq lSelect);
da1.SelectComma nd.Connection = ConnSql;
ds1 = new DataSet();
da1.Fill(ds1);


for (int iRowCount = 0; iRowCount < ds1.Tables[0].Rows.Count; iRowCount++)
{
strDateoftheMon th = "";
strMonthName = "";
strcustomerid = "";
strjobordernumb er = "";
strrecruiterId = "";
strrecruiternam e = "";
strStatus = "";
strPrescreening Id = "";
strCustomerName = "";
strJobOrderTitl e = "";
strmandatoryski lls = "";
strWorkLocation = "";
strSource = "";
strCandidateNam e = "";
strTotalExperie nceYears = "";
strTotalExperie nceMonths = "";
strCurrentOrgan isation = "";
strCurrentCtc = "";
strExpectedCtc = "";
strNoticePeriod Months = "";
strNoticePeriod Days = "";
strPresentLocat ion = "";
strMobileNumber = "";
strLandLineNumb er = "";
strCandidateEma ilId = "";
strStatusCode = "";
strInterviewSch eduleDate = "";
strInterviewSch eduleFromTime = "";
strInterviewSch eduleToTime = "";
strDateOfJoinin g = "";
strNotes = "";

strDateoftheMon th = ds1.Tables[0].Rows[iRowCount]["DateoftheMonth "].ToString().Tri m();

strMonthName = ds1.Tables[0].Rows[iRowCount]["MonthName"].ToString().Tri m();

strcustomerid = ds1.Tables[0].Rows[iRowCount]["customerid "].ToString().Tri m();

strjobordernumb er = ds1.Tables[0].Rows[iRowCount]["jobordernumber "].ToString().Tri m();

strrecruiterId = ds1.Tables[0].Rows[iRowCount]["recruiterI d"].ToString().Tri m();

strrecruiternam e = ds1.Tables[0].Rows[iRowCount]["recruitern ame"].ToString().Tri m();

strStatus = ds1.Tables[0].Rows[iRowCount]["Status"].ToString().Tri m();

strPrescreening Id = ds1.Tables[0].Rows[iRowCount]["prescreeningid "].ToString().Tri m();

strCustomerName = ds1.Tables[0].Rows[iRowCount]["customerna me"].ToString().Tri m();

strJobOrderTitl e = ds1.Tables[0].Rows[iRowCount]["joborderti tle"].ToString().Tri m();

strmandatoryski lls = ds1.Tables[0].Rows[iRowCount]["mandatoryskill s"].ToString().Tri m();

strWorkLocation = ds1.Tables[0].Rows[iRowCount]["worklocati on"].ToString().Tri m();

strSource = ds1.Tables[0].Rows[iRowCount]["source"].ToString().Tri m();

strCandidateNam e = ds1.Tables[0].Rows[iRowCount]["candidaten ame"].ToString().Tri m();

strTotalExperie nceYears = ds1.Tables[0].Rows[iRowCount]["totalexperienc eyears"].ToString().Tri m();
strTotalExperie nceMonths = ds1.Tables[0].Rows[iRowCount]["totalexperienc emonths"].ToString().Tri m();
strCurrentOrgan isation = ds1.Tables[0].Rows[iRowCount]["currentorganis ation"].ToString().Tri m();
strCurrentCtc = ds1.Tables[0].Rows[iRowCount]["currentctc "].ToString().Tri m();

strExpectedCtc = ds1.Tables[0].Rows[iRowCount]["expectedct c"].ToString().Tri m();

strNoticePeriod Months = ds1.Tables[0].Rows[iRowCount]["noticeperiodmo nths"].ToString().Tri m();
strNoticePeriod Days = ds1.Tables[0].Rows[iRowCount]["noticeperiodda ys"].ToString().Tri m();

strPresentLocat ion = ds1.Tables[0].Rows[iRowCount]["presentlocatio n"].ToString().Tri m();

strMobileNumber = ds1.Tables[0].Rows[iRowCount]["mobilenumb er"].ToString().Tri m();

strLandLineNumb er = ds1.Tables[0].Rows[iRowCount]["landlinenumber "].ToString().Tri m();

if (strLandLineNum ber == "")
{
strLandLineNumb er = "NULL";
}
strCandidateEma ilId = ds1.Tables[0].Rows[iRowCount]["candidateemail id"].ToString().Tri m();

strStatusCode = ds1.Tables[0].Rows[iRowCount]["statuscode "].ToString().Tri m();

strInterviewSch eduleDate = ds1.Tables[0].Rows[iRowCount]["InterviewSched uleDate"].ToString().Tri m();
strInterviewSch eduleFromTime = ds1.Tables[0].Rows[iRowCount]["InterviewSched uleFromTime"].ToString().Tri m();

if (strInterviewSc heduleFromTime == "")
{
strInterviewSch eduleFromTime = "NULL";

}

strInterviewSch eduleToTime = ds1.Tables[0].Rows[iRowCount]["InterviewSched uleToTime"].ToString().Tri m();

if (strInterviewSc heduleToTime == "")
{
strInterviewSch eduleToTime = "NULL";
}
strDateOfJoinin g = ds1.Tables[0].Rows[iRowCount]["DateOfJoin ing"].ToString().Tri m();

strNotes = ds1.Tables[0].Rows[iRowCount]["notes"].ToString().Tri m();
if (strNotes == "")
{
strNotes = "NULL";
}

objCmd.Connecti on = ConnExcel;

objCmd.CommandT ext = "INSERT INTO Sheet1$(Dateoft heMonth, MonthName, customerid, jobordernumber, recruiterId, recruitername, Status, prescreeningid, customername, jobordertitle, mandatoryskills , worklocation, source, candidatename, totalexperience years, totalexperience months, currentorganisa tion, currentctc, expectedctc, noticeperiodmon ths, noticeperiodday s, presentlocation , mobilenumber, landlinenumber, candidateemaili d, statuscode, InterviewSchedu leDate, InterviewSchedu leFromTime, InterviewSchedu leToTime, DateOfJoining, notes) VALUES (" + strDateoftheMon th + "," + strMonthName + "," + strcustomerid + "," + strjobordernumb er + "," + strrecruiterId + "," + strrecruiternam e + "," + strStatus + "," + strPrescreening Id + "," + strCustomerName + "," + strJobOrderTitl e + "," + strmandatoryski lls + "," + strWorkLocation + "," + strSource + "," + strCandidateNam e + "," + strTotalExperie nceYears + "," + strTotalExperie nceMonths + "," + strCurrentOrgan isation + "," + strCurrentCtc + "," + strExpectedCtc + "," + strNoticePeriod Months + "," + strNoticePeriod Days + "," + strPresentLocat ion + "," + strMobileNumber + "," + strLandLineNumb er + "," + strCandidateEma ilId + "," + strStatusCode + "," + strInterviewSch eduleDate + "," + strInterviewSch eduleFromTime + "," + strInterviewSch eduleToTime + "," + strDateOfJoinin g + "," + strNotes + ")";



objCmd.ExecuteN onQuery(); -> Here i face my error:
The error is Syntax error in INSERT INTO statement

objCmd.Dispose( );


}
ConnSql.Close() ;
ConnExcel.Close ();



}

catch (Exception ex)
{
MessageBox.Show (ex.Message);
}

Pls help me.

[vasanth chandrasekaran]

please anybody help me to solve the error.

[vasanth chandrasekaran]

Hai everybody,

Finally i identify my error.I was wrongly declare the Insert querry.
Wrong Insert querry:
objCmd.CommandT ext = "INSERT INTO Sheet1$(Dateoft heMonth, MonthName, customerid, jobordernumber, recruiterId, recruitername, Status, prescreeningid, customername, jobordertitle, mandatoryskills , worklocation, source, candidatename, totalexperience years, totalexperience months, currentorganisa tion, currentctc, expectedctc, noticeperiodmon ths, noticeperiodday s, presentlocation , mobilenumber, landlinenumber, candidateemaili d, statuscode, InterviewSchedu leDate, InterviewSchedu leFromTime, InterviewSchedu leToTime, DateOfJoining, notes) VALUES (" + strDateoftheMon th + "," + strMonthName + "," + strcustomerid + "," + strjobordernumb er + "," + strrecruiterId + "," + strrecruiternam e + "," + strStatus + "," + strPrescreening Id + "," + strCustomerName + "," + strJobOrderTitl e + "," + strmandatoryski lls + "," + strWorkLocation + "," + strSource + "," + strCandidateNam e + "," + strTotalExperie nceYears + "," + strTotalExperie nceMonths + "," + strCurrentOrgan isation + "," + strCurrentCtc + "," + strExpectedCtc + "," + strNoticePeriod Months + "," + strNoticePeriod Days + "," + strPresentLocat ion + "," + strMobileNumber + "," + strLandLineNumb er + "," + strCandidateEma ilId + "," + strStatusCode + "," + strInterviewSch eduleDate + "," + strInterviewSch eduleFromTime + "," + strInterviewSch eduleToTime + "," + strDateOfJoinin g + "," + strNotes + ")";

Correct Insert Querry:objCmd.CommandT ext = "INSERT INTO [Sheet1$] ([Date of the month], [Month name], [Customer id], [Job order number], [Recruiter Id], [Recruiter name], [Status], [Prescreening id], [Customer name], [Job order title], [Mandatory skills], [Work location], [Source], [Candidate name], [Total experience years], [Total experience months], [Current organisation], [Current ctc], [Expected ctc], [Notice period months], [Notice period days], [Present location], [Mobile number], [Landline number], [Candidate email id], [Status code], [Interview schedule date], [Interview schedule from time], [Interview schedule to time], [Date of joining], [Notes]) VALUES ('" + strDateoftheMon th + "','" + strMonthName + "','" + strcustomerid + "','" + strjobordernumb er + "','" + strrecruiterId + "','" + strrecruiternam e + "','" + strStatus + "','" + strPrescreening Id + "','" + strCustomerName + "','" + strJobOrderTitl e + "','" + strmandatoryski lls + "','" + strWorkLocation + "','" + strSource + "','" + strCandidateNam e + "','" + strTotalExperie nceYears + "','" + strTotalExperie nceMonths + "','" + strCurrentOrgan isation + "','" + strCurrentCtc + "','" + strExpectedCtc + "','" + strNoticePeriod Months + "','" + strNoticePeriod Days + "','" + strPresentLocat ion + "','" + strMobileNumber + "','" + strLandLineNumb er + "','" + strCandidateEma ilId + "','" + strStatusCode + "','" + strInterviewSch eduleDate + "','" + strInterviewSch eduleFromTime + "','" + strInterviewSch eduleToTime + "','" + strDateOfJoinin g + "','" + strNotes + "')";

Thanks