[Skillzilog]

i want to querry inbetween date, but it isnt working the error says

Imports System.Data
Imports System.Data.OleDb
Public Class salesinventoryrpt
    Inherits System.Windows.Forms.Form
    Dim bill As New POS
    Dim mypath = Application.StartupPath & "\db1.accdb"
    Dim connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\db1.accdb;Persist Security Info=False;")
    Dim cmd As OleDbCommand
    Dim cmdGetIdentity = New OleDbCommand()
    Dim sql2 As OleDbDataAdapter = New OleDbDataAdapter("Select * from sales", connection)
    Dim db1 As DataSet = New DataSet
    Dim db2 As New DataTable

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            db1.Clear()
            db2.Clear()
            cmdGetIdentity = connection.CreateCommand
            cmdGetIdentity.CommandText = "SELECT * FROM sales where transac_date between '%" & (DateTimePicker1.Text) & "%' and '%" & (DateTimePicker2.Text) & "'"
            sql2.SelectCommand = cmdGetIdentity
            sql2.Fill(db1, "sales")
            DataGridView1.DataSource = db1
            DataGridView1.DataMember = "sales"
            DataGridView1.ReadOnly = True
            DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
            DataGridView1.MultiSelect = False
            DataGridView1.RowHeadersVisible = False
        Catch ex As Exception
            MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "Connection Error !!")
        End Try
    End Sub
End Class

Error: microsoft office access database engine : no value give for one or more required parameters

[Skillzilog]

i tried this code

cmdGetIdentity.CommandText = "SELECT * FROM sales where transac_date between " & Format(DateTimePicker1.Value, "mm/dd/yyyy") & " and '" & Format(DateTimePicker2.Value, "mm/dd/yyyy") & ""

btw the value of the date in my database is

11-14-2010


but it still aint working

[Skillzilog]

and this

"SELECT * FROM Sales WHERE transac_date between '" & Format(Me.DateTimePicker1.Value, "mm/dd/yyyy") & "' AND '" & Format(Me.DateTimePicker1.Value, "mm/dd/yyyy") & "'"

[Skillzilog]

yo it is already working i removed the

sql2.SelectCommand = cmdGetIdentity

but the problem is, even though i entered a date on the 2 datetimepicker that is not part of the transaction, it still shows me all the transaction

[Skillzilog]

im having a problem on filtering

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            db1.Clear()
            db2.Clear()
            cmdGetIdentity = connection.CreateCommand
            [b]cmdGetIdentity.CommandText = "SELECT * FROM sales WHERE transac_date BETWEEN '" & Format(DateTimePicker1.Value, "mm/dd/yyyy") & "'  transac_date  '" & Format(DateTimePicker2.Value, "mm/dd/yyyy") & "'"[/b]
            sql2.Fill(db1, "sales")
            DataGridView1.DataSource = db1
            DataGridView1.DataMember = "sales"
            DataGridView1.ReadOnly = True
            DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
            DataGridView1.MultiSelect = False
            DataGridView1.RowHeadersVisible = False
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
    End Sub

the transactiondate on the database is all NOVEMBER 4, eventhough i only select november 1 - 2 through the datetimepicker, the transaction that has been done on november 4 is still showing on the datagrid

[Skillzilog]

and this one

cmdGetIdentity.CommandText = "SELECT * FROM sales WHERE transac_date BETWEEN '#" & Format(DateTimePicker1.Value, "mm/dd/yyyy") & "#'  AND  '#" & Format(DateTimePicker2.Value, "mm/dd/yyyy") & "#"

This post has been edited by Skillzilog: 04 November 2010 - 05:04 AM

[Skillzilog]

oh damn, i tried to hardcode, but still wtf, the data where the date is november 4 is still showing

"SELECT * FROM sales where transac_date >=#11/1/2010# AND transac_date <=#11/2/2010#"

i sampled this code

[CharlieMay]

Skillz, get rid of the apostorphes around the # you should just need the #.

Try:

cmdGetIdentity.CommandText = "SELECT * FROM sales WHERE transac_date BETWEEN #" & Format(DateTimePicker1.Value, "mm/dd/yyyy") & "#  AND  #" & Format(DateTimePicker2.Value, "mm/dd/yyyy") & "#"

Also, this won't work if your fields in the database are not set to Date/Time. You shouldn't need to format it. The database should match 11/04/10 with 11/04/2010.

This post has been edited by CharlieMay: 04 November 2010 - 05:17 AM

[Skillzilog]

sir i tried ur code but it still isnt working, i think it has something to do with this

Dim sql2 As OleDbDataAdapter = New OleDbDataAdapter("Select * from sales", connection)

and i change it to

Dim sql2 As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM sales WHERE transac_date BETWEEN #" & Format(DateTimePicker1.Value, "mm/dd/yyyy") & "#  AND  #" & Format(DateTimePicker2.Value, "mm/dd/yyyy") & "#", connection)

but im receiving some errors like

An error occurred creating the form. See Exception.InnerException for details. The error is: Object reference not set to an instance of an object.

Dim sql as New Oledbdataadapter(CmdGetidentity,connection)

An error occurred creating the form. See Exception.InnerException for details. The error is: Conversion from type 'OleDbCommand' to type 'String' is not valid.

sir yes sir, my field format si date/time , hereby is the example of data inside the field 11/04/2010

[CharlieMay]

Try changing this line:

Dim cmdGetIdentity = New OleDbCommand()

to

Dim cmdGetIdentity as New OledbCommand()

[Skillzilog]

sir this is my updated code

Dim cmdGetIdentity As New OleDbCommand()
Dim db1 As DataSet = New DataSet
    Dim db2 As New DataTable

Try
            cmdGetIdentity = connection.CreateCommand
mdGetIdentity.CommandText = "SELECT * FROM sales where transac_date >=#11/1/2010# AND transac_date <=#11/2/2010#"
            Dim sql3 As OleDbDataAdapter = New OleDbDataAdapter(cmdGetIdentity)
            db1.Clear()
            sql3.Fill(db1, "sales")

still no luck, and i get this new error

NO VALUE IS GIVEN FOR ONE OR MORE REQUIRED PARAMETERS

[CharlieMay]

What line is throwing that error?

[Skillzilog]

sir i dont know what line causes me that, that is the screenshot, goddamn this problem, i think im already debugging it for 12 hours since yesterday still i get no luck for figuring out this out, here is the whole code sir, i already tried different code suggested by different people still i cant solve it , sir this is my software and the database in it, please kindly see it for me

http://www.4shared.c...xK/SOFTENG.html

thnx

Imports System.Data
Imports System.Data.OleDb
Public Class salesinventoryrpt
    Inherits System.Windows.Forms.Form
    Dim mypath = Application.StartupPath & "\db1.accdb"
    Dim connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\db1.accdb;Persist Security Info=False;")
    Dim cmd As OleDbCommand
    Dim cmdGetIdentity As New OleDbCommand()
    Dim db1 As DataSet = New DataSet
    Dim db2 As New DataTable
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            cmdGetIdentity = connection.CreateCommand
            cmdGetIdentity.CommandText = "SELECT * FROM sales where transac_date >=#11/1/2010# AND transac_date <=#11/2/2010#"
            Dim sql3 As OleDbDataAdapter = New OleDbDataAdapter(cmdGetIdentity)
            db1.Clear()
            sql3.Fill(db1, "sales")
            DataGridView1.DataSource = db1
            DataGridView1.DataMember = "sales"
            DataGridView1.ReadOnly = True
            DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
            DataGridView1.MultiSelect = False
            DataGridView1.RowHeadersVisible = False
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
    End Sub
End Class

This post has been edited by Skillzilog: 04 November 2010 - 02:57 PM

[CharlieMay]

Your sales table has the field transact_date. You have it spelled transac_date (notice the T is missing) in your select statement.

This should give you the results you want.

cmdGetIdentity.CommandText = "SELECT * FROM sales WHERE transact_date BETWEEN #" & DateTimePicker1.Value & "#  AND  #" & DateTimePicker2.Value & "#"

This post has been edited by CharlieMay: 04 November 2010 - 04:06 PM

[Skillzilog]

oh sir thank you, im thinking too much that i didnt see it, ill be more careful next time