In between query problem

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 1448 Views - Last Post: 04 November 2010 - 04:37 PM Rate Topic: -----

#1 Skillzilog  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 75
  • Joined: 25-September 10

In between query problem

Posted 04 November 2010 - 01:38 AM

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

Is This A Good Question/Topic? 0
  • +

Replies To: In between query problem

#2 Skillzilog  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 75
  • Joined: 25-September 10

Re: In between query problem

Posted 04 November 2010 - 02:08 AM

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
Was This Post Helpful? 0
  • +
  • -

#3 Skillzilog  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 75
  • Joined: 25-September 10

Re: In between query problem

Posted 04 November 2010 - 02:30 AM

and this

"SELECT * FROM Sales WHERE transac_date between '" & Format(Me.DateTimePicker1.Value, "mm/dd/yyyy") & "' AND '" & Format(Me.DateTimePicker1.Value, "mm/dd/yyyy") & "'"
Was This Post Helpful? 0
  • +
  • -

#4 Skillzilog  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 75
  • Joined: 25-September 10

Re: In between query problem

Posted 04 November 2010 - 02:44 AM

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
Was This Post Helpful? 0
  • +
  • -

#5 Skillzilog  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 75
  • Joined: 25-September 10

Re: In between query problem

Posted 04 November 2010 - 04:38 AM

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
Was This Post Helpful? 0
  • +
  • -

#6 Skillzilog  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 75
  • Joined: 25-September 10

Re: In between query problem

Posted 04 November 2010 - 05:02 AM

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

Was This Post Helpful? 0
  • +
  • -

#7 Skillzilog  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 75
  • Joined: 25-September 10

Re: In between query problem

Posted 04 November 2010 - 05:15 AM

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
Was This Post Helpful? 0
  • +
  • -

#8 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: In between query problem

Posted 04 November 2010 - 05:15 AM

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

Was This Post Helpful? 0
  • +
  • -

#9 Skillzilog  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 75
  • Joined: 25-September 10

Re: In between query problem

Posted 04 November 2010 - 05:26 AM

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
Was This Post Helpful? 0
  • +
  • -

#10 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: In between query problem

Posted 04 November 2010 - 05:56 AM

Try changing this line:
Dim cmdGetIdentity = New OleDbCommand()

to
Dim cmdGetIdentity as New OledbCommand()

Was This Post Helpful? 1
  • +
  • -

#11 Skillzilog  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 75
  • Joined: 25-September 10

Re: In between query problem

Posted 04 November 2010 - 06:11 AM

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
Was This Post Helpful? 0
  • +
  • -

#12 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: In between query problem

Posted 04 November 2010 - 06:31 AM

What line is throwing that error?
Was This Post Helpful? 0
  • +
  • -

#13 Skillzilog  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 75
  • Joined: 25-September 10

Re: In between query problem

Posted 04 November 2010 - 02:55 PM

sir i dont know what line causes me that, Posted Image 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

Was This Post Helpful? 0
  • +
  • -

#14 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: In between query problem

Posted 04 November 2010 - 04:03 PM

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

Was This Post Helpful? 1
  • +
  • -

#15 Skillzilog  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 75
  • Joined: 25-September 10

Re: In between query problem

Posted 04 November 2010 - 04:21 PM

oh sir thank you, im thinking too much that i didnt see it, ill be more careful next time
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2