Hi All,
I am new into this industry. I have problem with my code here. I am getting an error message "string recognised unable to convert datetime" and one more error is incorrect syntax near ')'. Could you please check the following code and help me resolve the issue.
I would be thankful to you indeed.
Kindly reply me at earliest. Its a bit urgent please.
HERE IS THE CODE:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Configuration;
using System.Data.SqlClient;
namespace MasterPage
{
public partial class ReportForWorkOrderD : Form
{
public ReportForWorkOrderD()
{
InitializeComponent();
}
SqlConnection con;
string t = "";
private void ReportForWorkOrderD_Load(object sender, EventArgs e)
{
t = ConfigurationManager.AppSettings.Get("sqlcon");
con = new SqlConnection(t);
SqlCommand cmd = new SqlCommand("select distinct(districts) from locationmaster", con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
cmbDistricts.Items.Clear();
while (dr.Read())
{
cmbDistricts.Items.Add(dr[0].ToString());
}
dr.Close();
con.Close();
SqlCommand cmd1 = new SqlCommand("select distinct(engineername) from engineermaster", con);
con.Open();
SqlDataReader dr1 = cmd1.ExecuteReader();
cmbAllotedTo.Items.Clear();
while (dr1.Read())
{
cmbAllotedTo.Items.Add(dr1[0].ToString());
}
dr1.Close();
con.Close();
}
CrystalReportForWorkOrderD crwod;
private void cmbDistricts_SelectedIndexChanged(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("(select distinct(system) from salesorder where location in (select locationname from locationmaster where districts ='" + cmbDistricts.Text + "'))union(select distinct(system) from amcorder where location in (select locationname from locationmaster where districts ='" + cmbDistricts.Text + "'))", con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
lstSystemName.Items.Clear();
while (dr.Read())
{
lstSystemName.Items.Add(dr[0].ToString());
}
dr.Close();
con.Close();
}
private void label1_Click(object sender, EventArgs e)
{
}
ArrayList ar;
string s = "''";
string a = "";
DateTime lbr;
string lbrS;
string notes = "";
SqlCommand cmd;
private void btnGo_Click(object sender, EventArgs e)
{
SqlCommand cmd3 = new SqlCommand("select companyname from companymaster", con);
con.Open();
SqlDataReader dr = cmd3.ExecuteReader();
while (dr.Read())
{
a = dr[0].ToString();
}
dr.Close();
try
{
ar = new ArrayList();
foreach (object selecteditem in lstSystemName.SelectedItems)
{
ar.Add(selecteditem.ToString());
s = s + ",'" + selecteditem.ToString() + "' ";
}
SqlCommand cmdlbr = new SqlCommand("select max(convert(datetime,lastbatteryreplacedon,105)) from systemreport where location in (select locationname from locationmaster where districts ='"+cmbDistricts.Text+"') and systemname in ("+s+")", con);
SqlDataReader drlbr = cmdlbr.ExecuteReader();
while (drlbr.Read())
{
lbr = Convert.ToDateTime (drlbr[0].ToString());
}
drlbr.Close();
System.Globalization.DateTimeFormatInfo dateInfo = new System.Globalization.DateTimeFormatInfo();
dateInfo.ShortDatePattern = "dd-mm-yyyy";
lbrS=lbr.ToString("dd-MM-yyyy");
string notesco = "";
string notescs = "";
string remarks = "";
SqlCommand cmdnotes = new SqlCommand("select remarks from complaintorder where location in (select locationname from locationmaster where districts ='" + cmbDistricts.Text + "')and service in (" + s + ")", con);
SqlDataReader drnotes = cmdnotes.ExecuteReader();
while (drnotes.Read())
{
notesco = drnotes[0].ToString();
}
drnotes.Close();
SqlCommand cmdnotes2 = new SqlCommand("select cs.remarks from complaintstatus cs ,complaintorder co where co.location in (select locationname from locationmaster where districts ='" + cmbDistricts.Text + "')and co.service in (" + s + ")and co.complaintno=cs.complaintno and cs.complaintstatus='Open')", con);
SqlDataReader drnotes2 = cmdnotes2.ExecuteReader();
while (drnotes2.Read())
{
notescs = drnotes2[0].ToString();
}
drnotes2.Close();
SqlCommand cmdnotes3 = new SqlCommand("select notes from systemreport where location in (select locationname from locationmaster where districts ='"+cmbDistricts.Text+"')and systemname in ("+s+") and convert(datetime,date,105)=(select max(convert(datetime,date,105))from systemreport where location in (select locationname from locationmaster where districts ='"+cmbDistricts.Text+"') and systemname in ("+s+"))", con);
SqlDataReader drnotes3 = cmdnotes3.ExecuteReader();
while (drnotes3.Read())
{
notes = drnotes3[0].ToString();
}
drnotes3.Close();
if (notesco.Length > 0)
{
remarks = "";
remarks = notesco;
}
if (notescs.Length > 0)
{
remarks = "";
remarks = notescs;
}
if (notes.Length > 0)
{
remarks = "";
remarks = notes;
}
cmd = new SqlCommand("(select distinct(co.customername) as CustomerName, co.complaintno as ComplaintNo,co.location as Location,lm.address as Address,lm.mobile as Mobile,co.service as System,co.systemmake as SystemMake,co.complaintdate as ComplaintDate,'' as LastBatteryReplacedOn,'',cs.complaintstatus as ComplaintStatus,defects as Defects from amc,complaintorder co,complaintstatus cs,locationmaster lm where co.complaintno=cs.complaintno and cs.complaintstatus='Open' and co.customername=lm.customername and lm.locationname=co.location and co.location in (select locationname from locationmaster where districts='" + cmbDistricts.Text + "') and co.service in (" + s + ") and convert(datetime,complaintdate,105) between convert(datetime,'" + dtpfromdate.Text + "',105) and convert(datetime,'" + dtptodate.Text + "',105)) union (select distinct(amc.customername),'',amc.location,lm.address,lm.mobile,amc.system,amc.systemmake,amc.serviceduedates,'','','','' from amc amc, locationmaster lm where amc.location=lm.locationname and amc.location in (select locationname from locationmaster where districts='" + cmbDistricts.Text + "' and convert(datetime,serviceduedates,105) between convert(datetime,'" + dtpfromdate.Text + "',105) and convert(datetime,'" + dtptodate.Text + "',105))and amc.system in (" + s + ") ) union (select distinct(ao.customername),'',ao.location,lm.address,lm.mobile,ao.system,ao.systemmake,ao.serviceduedates,'','','','' from amcorder ao,locationmaster lm where ao.location=lm.locationname and ao.location in (select locationname from locationmaster where districts='" + cmbDistricts.Text + "') and ao.system in (" + s + ") and convert(datetime,serviceduedates,105) between convert(datetime,'" + dtpfromdate.Text + "',105) and convert(datetime,'" + dtptodate.Text + "',105)) order by ComplaintNo desc ", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "workorderForE");
for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
{
SqlCommand cmdrow = new SqlCommand("select max(convert(datetime,lastbatteryreplacedon,105)) from systemreport where Customername='" + ds.Tables[0].Rows[row][0].ToString() + "' and location='" + ds.Tables[0].Rows[row][2].ToString() + "' and systemname='" + ds.Tables[0].Rows[row][5].ToString() + "' and makeofthesystem='" + ds.Tables[0].Rows[row][6].ToString() + "'", con);
SqlDataReader drrow = cmdrow.ExecuteReader();
while (drrow.Read())
{
if (drrow[0].ToString() == "1/1/1900 12:00:00 AM")
{
ds.Tables[0].Rows[row][8] = null;
}
else
{
ds.Tables[0].Rows[row][8] = drrow[0].ToString();
}
}
drrow.Close();
}
ds.WriteXmlSchema("../WorkOrderForD.xml");
crwod = new CrystalReportForWorkOrderD();
crwod.SetDataSource(ds.Tables[0]);
crystalReportViewer1.ReportSource = crwod;
s = "";
crwod.SetParameterValue("district", cmbDistricts.Text);
crwod.SetParameterValue("FromDate", dtpfromdate.Text);
crwod.SetParameterValue("ToDate", dtptodate.Text);
crwod.SetParameterValue("System", s);
crwod.SetParameterValue("AllotedTo", cmbAllotedTo.Text);
crwod.SetParameterValue("companyname", a);
s = "''";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
con.Close();
}
con.Close();
}
}
}
Thank you,