By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 1,840 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

Erro: Syntax error in INSERT INTO statement

P: 4
The error is :Syntax error in INSERT INTO statement.
This is my code:
Expand|Select|Wrap|Line Numbers
  1. try
  2.             {
  3.                 DataSet ds1;
  4.                 OleDbConnection ConnSql;
  5.                 OleDbConnection ConnExcel;
  6.                 OleDbDataAdapter da1;
  7.                 OleDbDataAdapter da2;
  8.                 string ConnStringSql = "Provider=sqloledb;Data Source = cst;Initial Catalog = Testido;User ID=sa;Pwd=connect#963;";
  9.  
  10.                 string OutputFilename = "D:\\Misreport.xls";
  11.  
  12.                string ConnStringExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + OutputFilename + ";Extended Properties=\"Excel 8.0;HDR=yes;\"";
  13.  
  14.  
  15. string sqlSelect = "select Day(joborder.CreatedDate)as [DateoftheMonth],CONVERT(varchar(3),joborder.CreatedDate, 100) as [MonthName], joborder.customerid, joborder.jobordernumber, tasklist.employeeid AS recruiterId, employeedetails.empname as recruitername, tasklist.Status, prescreening.prescreeningid, customerdetails.customername, joborder.jobordertitle,joborderprimarymandatoryskills.mandatoryskills,joborder.worklocation,parsedfile.source,parsedfile.candidatename,prescreening.totalexperienceyears,prescreening.totalexperiencemonths,parsedfile.currentorganisation,parsedfile.currentctc,parsedfile.expectedctc,prescreening.noticeperiodmonths,prescreening.noticeperioddays,parsedfile.presentlocation,parsedfile.mobilenumber,prescreening.landlinenumber,parsedfile.candidateemailid,result.statuscode,CONVERT(VARCHAR(10),result.InterviewScheduleDate,110) as [InterviewScheduleDate],convert(varchar,result.InterviewScheduleFromTime, 8) as [InterviewScheduleFromTime],convert(varchar,result.InterviewScheduleToTime,8) as [InterviewScheduleToTime],CONVERT(VARCHAR(10),result.DateOfJoining,110) as [DateOfJoining],result.notes,joborder.createddate from joborder inner join tasklist on joborder.jobordernumber=tasklist.jobordernumber inner join employeedetails on tasklist.employeeid=employeedetails.employeeid inner join customerdetails on joborder.customerid=customerdetails.customerid inner join joborderprimarymandatoryskills on joborder.jobordernumber=joborderprimarymandatoryskills.jobordernumber inner join prescreeningidjobordernumbermapping on joborder.jobordernumber=prescreeningidjobordernumbermapping.jobordernumber and prescreeningidjobordernumbermapping.createdby = employeedetails.employeeid inner join prescreening on prescreeningidjobordernumbermapping.prescreeningid=prescreening.prescreeningid and prescreening.createdby = employeedetails.employeeid inner join parsedfile on prescreening.parsedfileid=parsedfile.parsedfileid  left outer join (select r.prescreeningid as prescreeningid,interviewstatusschedule.statuscode as statuscode, interviewstatusschedule.InterviewScheduleDate,interviewstatusschedule.InterviewScheduleFromTime,interviewstatusschedule.InterviewScheduleToTime,interviewstatusschedule.DateOfJoining, interviewstatusschedule.notes  from(select interviewstatusschedule.prescreeningid,max(interviewstatusscheduleid) as interviewstatusscheduleid from interviewstatusschedule  where interviewstatusschedule.createddate between '4/15/2010' and '7/26/2010' group by  interviewstatusschedule.prescreeningid)as r inner join interviewstatusschedule on r.interviewstatusscheduleid=interviewstatusschedule.interviewstatusscheduleid) as result on prescreening.prescreeningid=result.prescreeningid where joborder.createddate between '4/15/2010' and '7/26/2010' and tasklist.employeeid in ('10003','10004') and tasklist.status='ACCEPTED' and joborder.customerid in('5','6','4')";
  16.  
  17. OleDbCommand objCmd = new OleDbCommand();
  18.                 //Read the Data from database
  19.  
  20.  
  21. ConnSql = new OleDbConnection(ConnStringSql);
  22. ConnExcel = new OleDbConnection(ConnStringExcel);
  23.  
  24. ConnSql.Open();
  25. ConnExcel.Open();
  26.  
  27. da1 = new OleDbDataAdapter();
  28. da1.SelectCommand = new OleDbCommand(sqlSelect);
  29. da1.SelectCommand.Connection = ConnSql;
  30. ds1 = new DataSet();
  31. da1.Fill(ds1);
  32.  
  33.  
  34. for (int iRowCount = 0; iRowCount < ds1.Tables[0].Rows.Count; iRowCount++)
  35.                {
  36.                    strDateoftheMonth = "";
  37.                    strMonthName = "";
  38.                    strcustomerid = "";
  39.                    strjobordernumber = "";
  40.                    strrecruiterId = "";
  41.                    strrecruitername = "";
  42.                    strStatus = "";
  43.                    strPrescreeningId = "";
  44.                    strCustomerName = "";
  45.                    strJobOrderTitle = "";
  46.                    strmandatoryskills = "";
  47.                    strWorkLocation = "";
  48.                    strSource = "";
  49.                    strCandidateName = "";
  50.                    strTotalExperienceYears = "";
  51.                    strTotalExperienceMonths = "";
  52.                    strCurrentOrganisation = "";
  53.                    strCurrentCtc = "";
  54.                    strExpectedCtc = "";
  55.                    strNoticePeriodMonths = "";
  56.                    strNoticePeriodDays = "";
  57.                    strPresentLocation = "";
  58.                    strMobileNumber = "";
  59.                    strLandLineNumber = "";
  60.                    strCandidateEmailId = "";
  61.                    strStatusCode = "";
  62.                    strInterviewScheduleDate = "";
  63.                    strInterviewScheduleFromTime = "";
  64.                    strInterviewScheduleToTime = "";
  65.                    strDateOfJoining = "";
  66.                    strNotes = "";
  67.  
  68.  strDateoftheMonth = ds1.Tables[0].Rows[iRowCount]["DateoftheMonth"].ToString().Trim();
  69.  
  70.                    strMonthName = ds1.Tables[0].Rows[iRowCount]["MonthName"].ToString().Trim();
  71.  
  72.                    strcustomerid = ds1.Tables[0].Rows[iRowCount]["customerid"].ToString().Trim();
  73.  
  74.                    strjobordernumber = ds1.Tables[0].Rows[iRowCount]["jobordernumber"].ToString().Trim();
  75.  
  76.                    strrecruiterId = ds1.Tables[0].Rows[iRowCount]["recruiterId"].ToString().Trim();
  77.  
  78.                    strrecruitername = ds1.Tables[0].Rows[iRowCount]["recruitername"].ToString().Trim();
  79.  
  80.                    strStatus = ds1.Tables[0].Rows[iRowCount]["Status"].ToString().Trim();
  81.  
  82.                    strPrescreeningId = ds1.Tables[0].Rows[iRowCount]["prescreeningid"].ToString().Trim();
  83.  
  84.                    strCustomerName = ds1.Tables[0].Rows[iRowCount]["customername"].ToString().Trim();
  85.  
  86.                    strJobOrderTitle = ds1.Tables[0].Rows[iRowCount]["jobordertitle"].ToString().Trim();
  87.  
  88.                    strmandatoryskills = ds1.Tables[0].Rows[iRowCount]["mandatoryskills"].ToString().Trim();
  89.  
  90.                    strWorkLocation = ds1.Tables[0].Rows[iRowCount]["worklocation"].ToString().Trim();
  91.  
  92.                    strSource = ds1.Tables[0].Rows[iRowCount]["source"].ToString().Trim();
  93.  
  94.                    strCandidateName = ds1.Tables[0].Rows[iRowCount]["candidatename"].ToString().Trim();
  95.  
  96.                    strTotalExperienceYears = ds1.Tables[0].Rows[iRowCount]["totalexperienceyears"].ToString().Trim();
  97.                    strTotalExperienceMonths = ds1.Tables[0].Rows[iRowCount]["totalexperiencemonths"].ToString().Trim();
  98.                    strCurrentOrganisation = ds1.Tables[0].Rows[iRowCount]["currentorganisation"].ToString().Trim();
  99.                    strCurrentCtc = ds1.Tables[0].Rows[iRowCount]["currentctc"].ToString().Trim();
  100.  
  101.                    strExpectedCtc = ds1.Tables[0].Rows[iRowCount]["expectedctc"].ToString().Trim();
  102.  
  103.                    strNoticePeriodMonths = ds1.Tables[0].Rows[iRowCount]["noticeperiodmonths"].ToString().Trim();
  104.                    strNoticePeriodDays = ds1.Tables[0].Rows[iRowCount]["noticeperioddays"].ToString().Trim();
  105.  
  106.                    strPresentLocation = ds1.Tables[0].Rows[iRowCount]["presentlocation"].ToString().Trim();
  107.  
  108.                    strMobileNumber = ds1.Tables[0].Rows[iRowCount]["mobilenumber"].ToString().Trim();
  109.  
  110.                    strLandLineNumber = ds1.Tables[0].Rows[iRowCount]["landlinenumber"].ToString().Trim();
  111.  
  112.                    if (strLandLineNumber == "")
  113.                    {
  114.                        strLandLineNumber = "NULL";
  115.                    }
  116.                    strCandidateEmailId = ds1.Tables[0].Rows[iRowCount]["candidateemailid"].ToString().Trim();
  117.  
  118.                    strStatusCode = ds1.Tables[0].Rows[iRowCount]["statuscode"].ToString().Trim();
  119.  
  120.                    strInterviewScheduleDate = ds1.Tables[0].Rows[iRowCount]["InterviewScheduleDate"].ToString().Trim();
  121.                    strInterviewScheduleFromTime = ds1.Tables[0].Rows[iRowCount]["InterviewScheduleFromTime"].ToString().Trim();
  122.  
  123. if (strInterviewScheduleFromTime == "")
  124.                    {
  125.                        strInterviewScheduleFromTime = "NULL";
  126.  
  127.                    }
  128.  
  129. strInterviewScheduleToTime = ds1.Tables[0].Rows[iRowCount]["InterviewScheduleToTime"].ToString().Trim();
  130.  
  131.                    if (strInterviewScheduleToTime == "")
  132.                    {
  133.                        strInterviewScheduleToTime = "NULL";
  134.                    }
  135. strDateOfJoining = ds1.Tables[0].Rows[iRowCount]["DateOfJoining"].ToString().Trim();
  136.  
  137. strNotes = ds1.Tables[0].Rows[iRowCount]["notes"].ToString().Trim();
  138.                    if (strNotes == "")
  139.                    {
  140.                        strNotes = "NULL";
  141.                    }
  142.  
  143. objCmd.Connection = ConnExcel;
  144.  
  145. objCmd.CommandText = "INSERT INTO Sheet1$(DateoftheMonth, MonthName, customerid, jobordernumber, recruiterId, recruitername, Status, prescreeningid, customername, jobordertitle, mandatoryskills, worklocation, source, candidatename, totalexperienceyears, totalexperiencemonths, currentorganisation, currentctc, expectedctc, noticeperiodmonths, noticeperioddays, presentlocation, mobilenumber, landlinenumber, candidateemailid, statuscode, InterviewScheduleDate, InterviewScheduleFromTime, InterviewScheduleToTime, DateOfJoining, notes) VALUES (" + strDateoftheMonth + "," + strMonthName + "," + strcustomerid + "," + strjobordernumber + "," + strrecruiterId + "," + strrecruitername + "," + strStatus + "," + strPrescreeningId + "," + strCustomerName + "," + strJobOrderTitle + "," + strmandatoryskills + "," + strWorkLocation + "," + strSource + "," + strCandidateName + "," + strTotalExperienceYears + "," + strTotalExperienceMonths + "," + strCurrentOrganisation + "," + strCurrentCtc + "," + strExpectedCtc + "," + strNoticePeriodMonths + "," + strNoticePeriodDays + "," + strPresentLocation + "," + strMobileNumber + "," + strLandLineNumber + "," + strCandidateEmailId + "," + strStatusCode + "," + strInterviewScheduleDate + "," + strInterviewScheduleFromTime + "," + strInterviewScheduleToTime + "," + strDateOfJoining + "," + strNotes + ")";
  146.  
  147.  
  148.  
  149. objCmd.ExecuteNonQuery(); -> Here i face my error:
  150. The error is Syntax error in INSERT INTO statement
  151.  
  152.  
  153. objCmd.Dispose();
  154.  
  155.  
  156. }
  157. ConnSql.Close();
  158. ConnExcel.Close();
  159.  
  160.  
  161.  
  162. }
  163.  
  164.             catch (Exception ex)
  165.             {
  166.                 MessageBox.Show(ex.Message);
  167.             }
Sep 20 '10 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Where are your parameters for your command object? Are you throwing your variables directly into the INSERT statement?
Oct 1 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.