Visual Basic

6-Jan-14 7:41

Hi all,

My MySQL table structure is ...
(I am giving you from SQL Query)

SQL

DROP TABLE IF EXISTS `expenditure`;
CREATE TABLE IF NOT EXISTS `expenditure` (
  `ExpId` int(11) NOT NULL AUTO_INCREMENT,
  `ExpDate` date 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;

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

/*!40000 ALTER TABLE `expenditure` DISABLE KEYS */;
INSERT INTO `expenditure` (`ExpId`,`ExpDate`,`ExpPurpose`,`ExpRefNo`,`Expenditure`,`ExpFlag`) VALUES
(1,'2014-01-06','Purchase','ttyu76',15750,'P');
/*!40000 ALTER TABLE `expenditure` ENABLE KEYS */;

My Code is...

Private Sub Generate()
        Dim DateFrom As Date = Convert.ToDateTime(DtpFrom.Value).ToString("yyyy-MM-dd")
        Dim DateTo As Date = Convert.ToDateTime(DtpTo.Value).ToString("yyyy-MM-dd")
        Try
            OpenConnection()
            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
            dbadapter.Fill(stdata)
            DgvExp.DataSource = stdata  'DgvExp is the name of DataGridView

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

After the execution of the code, my Datagridview is not displaying any Data.

What is wrong ?

Dave Kreskowiak6-Jan-14 7:55

Dave Kreskowiak

6-Jan-14 7:55

Your query didn't return anything, so the datatable is empty and, therefore, your grid.

Do NOT user string concatenation to build your query, especially with dates. Google for, and use, "vb.net MySQL parameterized queries".

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

Ron Beyer6-Jan-14 7:58

6-Jan-14 7:58

Try changing this:

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

To this:

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

The problem is that the date strings are not properly formatted to return the right data in your SQL, since you are using the default ToString method instead of an actual format like yyyy-MM-dd. Using parameters will force the underlying data adapter to insert the correct strings for the values without you having to worry about formatting them. Additionally, this is preferred to avoid SQL injection attacks and cleaner code.

Biplob Singha Shee6-Jan-14 8:37

6-Jan-14 8:37

Hi Ron,
Thank you for your answer. I changed my code exactly how you said but no result. Should I Change this ?

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

If yes, then in which format ?

Actually I am trying to create an Income & Expenditure Statement. I have another table namely "income". All income data stored here. I have created a crystal report with two sub report inside. I got the result. But last two days it failed to retrieve any records. I searched and found it retrieve any record that is in previous year i.e. 2013 but failed to retrieve any record which date is after 31/12/2013. I don't know why it is doing so. But if I searched the date from 01/01/2013 to 31/12/2013, then it displays 2/1/2014 date result. I am totally confused.

What should I do ?
Please help me...

modified 6-Jan-14 14:54pm.

Ron Beyer6-Jan-14 8:58

6-Jan-14 8:58

Did you debug and see if your select statement is actually returning data?

Biplob Singha Shee6-Jan-14 9:24

6-Jan-14 9:24

Hi Ron,

I think something is wrong. My Income and Expenditure table both have 1 record each but while executing the code, Crystal report shows 10 records in income subreport(December 2013 records) but no records in expenditure subreport while I am searching the records between 06/01/2014 to 07/01/2014.

Let me re-check my code and generated XML files for CrystalReport Datasource and then I can tell you.

Thank you for helping me, I will get back to you after checking the code.

Thanks again

Biplob

Biplob Singha Shee9-Jan-14 4:56

9-Jan-14 4:56

Hi Ron,

I have tried parameterized queries as stated but facing some problems. Sometimes it displays data and sometimes not (May be it is wrong in my code). However, I've changed a field in my MySQL Database. I changed the Date field from Date datatype to Char(10) in both Income and Expenditure Table. Below I am giving the structure(SQL) of both the tables with data.

SQL

-- 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;

Now I am giving the VB.NET Code for report generation...

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

As you can see the Income table has 4 data and Expenditure table has just 1 data.
Amazingly, while I am generating the report from 01/12/2013 to 10/01/2014 [^], the report shows only 3 data (January 2014) from income table and 1 data from Expenditure table (It has only one data). But if I generate the report from 01/12/2013 to 31/12/2014 [^], the report shows all the data from the tables.

I am Completely Confused and unable to find the solution. I debug and found my select statements are returning data that are showing in the report.

Please help me.

Ron Beyer9-Jan-14 5:13

9-Jan-14 5:13

BETWEEN is a mathematical comparison, when used on strings it simply selects, alphabetically, if the row is between the two values. Because you changed your date column to a char column, its no longer a date and between doesn't mean between the two dates, it means between the two strings alphabetically.

You really need to have that be a date column to use the BETWEEN correctly.

Biplob Singha Shee9-Jan-14 6:28

9-Jan-14 6:28

Hi Ron,
Thank you for your reply. I understood the BETWEEN comparison. I would like to say that, if I make the datatype of the field "date", it never displays any data. I don't know why. Is there any fault in my code ? Another thing is, if I convert the string in to date then is it possible to calculate ?

Biplob Singha Shee9-Jan-14 6:32

9-Jan-14 6:32

Sorry, it is not possible I think because how can I retrieve the database datatype string in to datetime. So what should I do, make the field datatype "Date" or "DateTime" ?

Biplob Singha Shee9-Jan-14 6:52

9-Jan-14 6:52

Ok, now I changed the DataType in to Date and changed the dates in to "yyyy-MM-dd" format in the database and changed my code for MySQL Date format...

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

Now it retrieving the data correctly in between two dates but a new problem arises. The report date field not showing any date.

While viewing the datasource of crystal report (XML), the schema shows...

XML

<xs:element name="IncDate" msdata:DataType="MySql.Data.Types.MySqlDateTime, MySql.Data, Version=6.6.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" type="xs:anyType" minOccurs="0" />

and the data shows (in XML)

XML

<IncDate xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
     <TimezoneOffset>0</TimezoneOffset>
     <Year>2013</Year>
     <Month>12</Month>
     <Day>25</Day>
     <Hour>0</Hour>
     <Minute>0</Minute>
     <Second>0</Second>
     <Millisecond>0</Millisecond>
   </IncDate>

What should I do ?

Please note that in my ConnectionString the Allow Zero Datetime is set to "True". Is this the problem ?

modified 9-Jan-14 13:19pm.

Dave Kreskowiak6-Jan-14 9:21

Dave Kreskowiak

6-Jan-14 9:21

Biplob Singha Shee wrote:
If yes, then in which format ?

That's the entire point behind using a PARAMETERIZED QUERY. You don't have to worry about the format AT ALL!! It's automatically handled by the Parameter object.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

Biplob Singha Shee6-Jan-14 9:27

Incorporating setup with other EXEs

6-Jan-14 9:27

Hi Dave,
Thank you for your answer. I am trying to solve this problem with parameterized queries however there is something wrong in my code I think. Let me check this first.

Thanks again,

Regards,

Biplob

COM component

dharmendra113-Jan-14 20:01

dharmendra11

3-Jan-14 20:01

What is COM,DCOM,COM+ component with Examples.

Re: COM component

Richard MacCutchan3-Jan-14 22:38

Richard MacCutchan

3-Jan-14 22:38

Use Google, it will find you many examples.

Veni, vidi, abiit domum

SPSandy2-Jan-14 20:27

SPSandy

2-Jan-14 20:27

Hi,

I have checked quite a few sites and articles but am not able to figure out how to solve my problem. The problem is:

I am creating a setup for a project. In that I want to include 2 more EXEs which will perform while the setup is run. 1. Wirting Serial number into the registry and 2. Installation of MySql.

I have tried with Custom Action but it copies the Serial Number Registration EXE in the folder where the installation is taking place. This I don't want. It should run the file and then remove it.

If anybody can help me in this I will be obliged.

Re: Incorporating setup with other EXEs

Bernhard Hiller2-Jan-14 21:50

Bernhard Hiller

2-Jan-14 21:50

SPSandy wrote:
it copies the Serial Number Registration EXE in the folder where the installation is taking place

That's OK - add another custom action which runs that executable, and one more which removes it.
As for the MySQL installation, you could try a precondition for your setup.

Re: Incorporating setup with other EXEs

SPSandy2-Jan-14 23:21

SPSandy

2-Jan-14 23:21

Thanks for the suggestion. I did that but the setup gets abandoned once I remove the EXE.

Re: Incorporating setup with other EXEs

Heriberto Lugo5-Jan-14 20:14

Heriberto Lugo

5-Jan-14 20:14

I think he meant add an action that removes the exe after set-up is complete..

Recovering VB.NET code

Tim Carmichael2-Jan-14 7:37

Tim Carmichael

2-Jan-14 7:37

I just found out a VB.NET DLL application was not, as I thought, put into SourceSafe. I do have the complied code (DLL, PDB, XML). Is there anyway I can recover the bulk of the original .NET code?

The folders were supposed to be backed up, but obviously weren't...

Thanks in advance,

Tim

Re: Recovering VB.NET code

Ron Beyer2-Jan-14 7:52