Visual Basic

Quote:

I moved only two files: my compiled .exe and a .dll required by the program that has nothing to do with the Excel problem

wClient.CancelAsync()

Dim sb As New StringBuilder
           sb.Append("SELECT ExpDate,ExpPurpose,Expenditure FROM expenditure WHERE ExpDate BETWEEN '" & DateFrom & "' AND '" & DateTo & "'")
           Dim dbcommand As New MySqlCommand
           Dim dbadapter As New MySqlDataAdapter
           Dim stdata As New DataTable
           dbcommand.Connection = conn
           dbcommand.CommandText = sb.ToString
           dbadapter.SelectCommand = dbcommand

String sb = "SELECT ExpDate,ExpPurpose,Expenditure FROM expenditure WHERE ExpDate BETWEEN @StartDate AND @EndDate")
Dim dbcommand As New MySqlCommand
Dim dbadapter As New MySqlDataAdapter
Dim stdata As New DataTable
dbcommand.Connection = conn
dbcommand.CommandText = sb;
dbcommand.Parameters.AddWithValue("@StartDate", DateFrom)
dbcommand.Parameters.AddWithValue("@EndDate", DateTo)
dbadapter.SelectCommand = dbcommand

Dim DateFrom As Date = Convert.ToDateTime(DtpFrom.Value).ToString("yyyy-MM-dd")
Dim DateTo As Date = Convert.ToDateTime(DtpTo.Value).ToString("yyyy-MM-dd")

-- Table structure for table `expenditure`
--

DROP TABLE IF EXISTS `expenditure`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `expenditure` (
  `ExpId` int(11) NOT NULL AUTO_INCREMENT,
  `ExpDate` char(10) DEFAULT NULL,
  `ExpPurpose` varchar(350) DEFAULT NULL,
  `ExpRefNo` varchar(50) DEFAULT NULL,
  `Expenditure` double(10,2) DEFAULT '0.00',
  `ExpFlag` char(10) DEFAULT NULL,
  PRIMARY KEY (`ExpId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `expenditure`
--

LOCK TABLES `expenditure` WRITE;
/*!40000 ALTER TABLE `expenditure` DISABLE KEYS */;
INSERT INTO `expenditure` VALUES (1,'06-01-2014','Purchase','ttyu76',15750.00,'P');
/*!40000 ALTER TABLE `expenditure` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `income`
--

DROP TABLE IF EXISTS `income`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `income` (
  `IncId` int(11) NOT NULL AUTO_INCREMENT,
  `IncDate` char(10) DEFAULT NULL,
  `IncPurpose` varchar(350) DEFAULT NULL,
  `IncRefNo` varchar(50) DEFAULT NULL,
  `IncIncome` double(10,2) DEFAULT '0.00',
  `IncFlag` char(2) DEFAULT NULL,
  PRIMARY KEY (`IncId`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `income`
--

LOCK TABLES `income` WRITE;
/*!40000 ALTER TABLE `income` DISABLE KEYS */;
INSERT INTO `income` VALUES (1,'06-01-2014','Sale','GE-INV/13-14/0102',1199.00,'S'),(2,'25-12-2013','Sale','GE-INV/13-14/0103',2875.00,'S'),(3,'09-01-2014','Sale','GE-INV/13-14/0104',343.00,'S'),(4,'08-01-2014','Sale','GE-INV/13-14/0105',118.00,'S');
/*!40000 ALTER TABLE `income` ENABLE KEYS */;
UNLOCK TABLES;

Private Sub GenerateReport()
    Dim DateFrom As String = Convert.ToDateTime(DtpFrom.Value).ToString("dd-MM-yyyy")
    Dim DateTo As String = Convert.ToDateTime(DtpTo.Value).ToString("dd-MM-yyyy")

    Try
        OpenConnection()


        '// Retrieve the income details
        Try
            Dim sb As New StringBuilder '<--------------------------------------------------------------------- Table Income
            sb.Append("SELECT * FROM income WHERE IncDate BETWEEN '" & DateFrom & "' AND '" & DateTo & "' ORDER BY IncDate ASC")
            Dim dbcommand As New MySqlCommand
            Dim dbadapter As New MySqlDataAdapter
            Dim stdata As New DataSet()
            dbcommand.Connection = conn
            dbcommand.CommandText = sb.ToString
            dbadapter.SelectCommand = dbcommand
            dbadapter.Fill(stdata)
            stdata.WriteXml(Application.StartupPath & "\ReportXml\Income.xml", XmlWriteMode.WriteSchema)

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try


        '// Retrieve the Expenditure details
        Try
            Dim sb1 As New StringBuilder '<--------------------------------------------------------------------- Table Expenditure
            sb1.Append("SELECT * FROM expenditure WHERE ExpDate BETWEEN '" & DateFrom & "' AND '" & DateTo & "' ORDER BY ExpDate ASC")
            Dim dcommand As New MySqlCommand
            Dim dadapter As New MySqlDataAdapter
            Dim sdata As New DataSet()
            dcommand.Connection = conn
            dcommand.CommandText = sb1.ToString
            dadapter.SelectCommand = dcommand
            dadapter.Fill(sdata)
            sdata.WriteXml(Application.StartupPath & "\ReportXml\Expenditure.xml", XmlWriteMode.WriteSchema)

            Dim objRpt1 As New InEx
            objRpt1.SetDataSource(sdata.Tables(0))
            FormReport.RptViewer.ReportSource = objRpt1
            FormReport.ShowDialog()
            FormReport.RptViewer.RefreshReport()

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        CloseConnection()
    End Try
End Sub