Visual Basic

Bernhard Hiller7-Jan-14 20:49

Bernhard Hiller

7-Jan-14 20:49

I'd suggest to remove the if-elseif-series and use a Select Case statement instead (see e.g. http://msdn.microsoft.com/en-us/library/cy37t14y.aspx[^]).
Furthermore, you should consider the possibility that none of your cases was found - in the if-elseif world, that means another else clause (without an if); with select-case, it is the Case Else clause.

big_D8-Jan-14 7:43

big_D

8-Jan-14 7:43

Ok, thanks for the suggestion, I'll go ahead and do that.

GuyThiebaut7-Jan-14 21:54

GuyThiebaut

7-Jan-14 21:54

Bear in mind that string comparisons are by default case sensitive so always convert both side of the comparison to upper or lowercase(ToUpper or ToLower).
If an "m" comes through your comparison will return a false with your current code.
Clickety for how I messed up on this[^]

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

DatagridView not display any data

big_D8-Jan-14 7:44

big_D

8-Jan-14 7:44

I'm writing this for attachmate, and our interface only uses uppercase. But that is definetley a good suggestion to keep in mind.

Biplob Singha Shee6-Jan-14 7:41

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

Ron Beyer

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

Ron Beyer

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

Ron Beyer

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