|
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.
DROP TABLE IF EXISTS `expenditure`;
;
;
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;
;
LOCK TABLES `expenditure` WRITE;
;
INSERT INTO `expenditure` VALUES (1,'06-01-2014','Purchase','ttyu76',15750.00,'P');
;
UNLOCK TABLES;
DROP TABLE IF EXISTS `income`;
;
;
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;
;
LOCK TABLES `income` WRITE;
;
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');
;
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()
Try
Dim sb As New StringBuilder
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
Try
Dim sb1 As New StringBuilder
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.
|
|
|
|
|
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.
|
|
|
|
|
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 ?
|
|
|
|
|
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" ?
|
|
|
|
|
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...
<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)
<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.
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
What is COM,DCOM,COM+ component with Examples.
|
|
|
|
|
Use Google, it will find you many examples.
Veni, vidi, abiit domum
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
Thanks for the suggestion. I did that but the setup gets abandoned once I remove the EXE.
|
|
|
|
|
I think he meant add an action that removes the exe after set-up is complete..
|
|
|
|
|
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
|
|
|
|
|
Download JetBrains DotPeek[^], it has an option for decompiling back to a project. As long as the original project wasn't obfuscated it should produce workable (or close to it) code.
Edit: JetBrains produces C# code, you can use any number of C# to VB converters to convert it back to VB.
|
|
|
|
|
Thanks for the information; working on the recovery and will advise of my results.
|
|
|
|
|
How to plot sin wave in vb.net
|
|
|
|
|
Try this link[^]
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
What is the exact use of data repeater??
|
|
|
|
|
|
how to fill the values from a sqldataadapter to datagridview in vb.net
|
|
|
|
|
|
|
I have using 64 bit system. i am using crystal report but report viewer not supported in 64 bit system. (Server 2008 R2)please tell me any answer.
Pankaj tripathi
|
|
|
|
|
|