The datagrid is show wrong output in vb6

Question

0.00/5 (No votes)

See more:

when i choose same date from month 10(say dtpckr1 = 02-10-2019 and dtpckr2 = 02-10-2019 ) ..data datagrid does not print anything and shows the msgbox not record found which i code for convinence...but when i choose start date from last moth and end date in this month(say dtpckr1 = 30-09-2019 and dtpckr2 = 02-10-2019 ) it shows all the data from month 09 and nothing from month 10 ... and the strange this is when choose date which is from moth 09 even if it is same(say dtpckr1 = 13-09-2019 and dtpckr2 = 13-09-2019 or 22-09-2019) it works perfectly i tried to attach screen shots but ..it showing server error...so please try to help me out by refering the following code .. I also checked the dataformat of database and my input,they are same...in databse the date datatype is "date/time" and format is "short date"....if have any other solution then please tell me... i will try... my purpose it to show datewise food orders in datagridview and then calculate the total sale... thanks

What I have tried:

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub cmdSearch_Click()

Dim date1 As Date
Dim date2 As Date

If IsNull(DTPicker1.Value And DTPicker2.Value) Then

    MsgBox "You must select date", vbCritical, "Warning"
    Exit Sub
End If
DTPicker1.Value = Format(DTPicker1.Value, "dd-mm-yyyy")
DTPicker2.Value = Format(DTPicker2.Value, "dd-mm-yyyy")
date1 = DTPicker1.Value
date2 = DTPicker2.Value
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\OrderMania\ordermania.mdb;Persist Security Info=False"
rs.CursorLocation = adUseClient

If DTPicker2.Value < DTPicker1.Value Then
MsgBox "End Date Cannot Be Lesser Then Start Date", vbCritical, "Wrong Input"
Exit Sub
Else
Adodc1.RecordSource = "select * from order1 where (date between #" & date1 & "# and #" & DTPicker2.Value & "#)"
Adodc1.Refresh
If Adodc1.Recordset.EOF Then
MsgBox "Please Enter Another Date", vbCritical, "No Record Found"
Else
Adodc1.Caption = Adodc1.RecordSource
End If
End If

con.Close

Call sale

End Sub


Public Sub sale()
Dim i As Integer
Dim Tot, gst, gtot As Double

For i = 0 To Adodc1.Recordset.RecordCount - 1
    Tot = Tot + CDbl(DataGrid1.Columns(5).Text)
    Adodc1.Recordset.MoveNext

Next i

Text1.Text = Tot
gst = Tot * 0.05
Text2.Text = gst
gtot = Tot + gst
Text3.Text = gtot
End Sub

Posted 2-Oct-19 22:00pm

Member 14611170

Updated 2-Oct-19 22:38pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2019-10-02T22:39:00

Well, there's few mistakes in your code...

1.

The most important is that your code is sql injection[^] vulnerable!

VB

Adodc1.RecordSource = "select * from order1 where (CDate(date) between #" & date1 & "# and #" & DTPicker2.Value & "#)"

Do NOT use concatenating string! Rather use parameterized queries.

VB

Dim cmd As ADODB.Command
Dim sQry As String

'connection initialization here

sQry = "select * from order1 where dateField between ? and ?;"
Set cmd = New ADODB.Command
cmd.Activeconnection= con  
cmd.CommandText=sQry
cmd.parameters.Append cmd.CreateParameter(, adDate, adParamInput, , date1)
cmd.parameters.Append cmd.CreateParameter(, adDate, adParamInput, , date2)

'execute command here!

2.

There's a list of reserved words[^] for MS Access database. Never use them as the field names!
For example:

SQL

SELECT Date, Month
FROM order1

Note that Date and Month are reserved words. Database engine will NOT return field values!
Even if you can workaround this by using [] brackets around reserved words:

SQL

SELECT [Date], [Month]
FROM order1

i do NOT recommend that! Use custom name instead:

date -> orderdate
month -> ordermonth

etc.

3.

Use ISO date format: "MM/dd/yyyy" when you build query manually:

SQL

SELECT <Field_list>
FROM YourTable
WHERE DateField BETWEEN #10/02/2019# AND #10/03/2019#;

4.

Take a look at underlined part of your query:

VB

"select * from order1 where (CDate(date) between #" & date1 & "# and #" & DTPicker2.Value & "#)"

You're trying to convert date field to datetime data type. Is there a reason to store date as a string? Use proper data types!

For further details, please see:
ADODB: Parameters Collection[^]
VB6 - find database table/field names that are Reserved Words-VBForums[^]