none
how to search a records from MS ACCESS WHERE "from a date to a date" <==which ASSIGNED BY ME RRS feed

  • Question

  • all readers, thanks for ready my posted problem.

    hello to ALL respective seniors of this help-forum, i'm moderate vb.net student whose currently doing programming for my education degree's project.

    program = vb.net2003
    database = ms access 2003
    application = windowapplication form

    expected result= i was expecting to show all records from a database where from "date" to "date" as assigned by me and display it in datagrid.

    the problem/issue= i have a problem i can't solve about the "OleDbDataAdapter" which highlighted in bold and blue in color. what seems to be the problem of my coding command? ;

    Error message shows => No value given for one or more required parameters. (i have no idea what's it means)
    image to preview= http://i109.photobucket.com/albums/n75/vaultpower/JUSTYOUANDME-1.jpg

    Below is my sample code:

    Dim oldest_date As Date
        Dim latest_date As Date

    Dim mon1 As Integer

        Dim mon2 As Integer

    Dim old_year As Integer
            Dim latest_year As Integer

    mon1 = Format(Int32.Parse(txtm1.Text))
            mon2 = Format(Int32.Parse(txm2.Text))

            old_year = Format(Int32.Parse(txty1.Text))
            latest_year = Format(Int32.Parse(txty2.Text))

            oldest_date = Module1.Number2Date(old_year, mon1, Format(Int32.Parse(txtd1.Text)))
            latest_date = Module1.Number2Date(latest_year, mon2, Format(Int32.Parse(txtd2.Text)))


     Dim sConnStr As String
            Dim oConn As OleDbConnection
            Dim oDA As OleDbDataAdapter
            Dim oDT As New DataSet

            sConnStr = "Provider=Microsoft.Jet.Oledb.4.0;Data source=MEDALINK_DTB.mdb"
            oConn = New OleDbConnection(sConnStr)
            oDA = New OleDbDataAdapter("SELECT * FROM CLIENT_REGISTRATION WHERE CLI_DATE >= oldest_date AND CLI_DATE <= latest_date", oConn)

            oConn.Open()
            oDA.Fill(oDT, "CLIENT_REGISTRATION")
            DataGrid1.DataSource = oDT.Tables("CLIENT_REGISTRATION")

            oDA.Dispose() : oDA = Nothing
            oConn.Close() : oConn = Nothing

            Dim ROWINDEX As Integer = DataGrid1.CurrentRowIndex

            Try
    TGT_NAME = DataGrid1.Item(ROWINDEX, 0)
            Catch ex As Exception
                MessageBox.Show("Note: Client's information not found!", "Note: ", MessageBoxButtons.OK, MessageBoxIcon.Warning)

            End Try




    if you all seniors can help, your help will be appreciated! thanks


    Saturday, November 1, 2008 4:27 AM

All replies

  • In Access you should use # as the date delimiter. but the better practice is to write a parameterized query in access.
    Sunday, November 2, 2008 4:08 AM
  • You are using variable names inside of SQL string and provider does not recognize them, since they are outside of query scope. What you need to do is to build your query string dynamically or pass date values as parameters. First way would require more checking from your code to avoid potential SQL injection and you would also need to add # around date values, something like

     

    oDA = New OleDbDataAdapter("SELECT * FROM CLIENT_REGISTRATION WHERE CLI_DATE >= #" & oldest_date.ToString() & "# AND CLI_DATE <= #" & latest_date.ToString() & "#", oConn)

     

    Check next KB article about how to use parameters to pass values to queries

     

    http://support.microsoft.com/kb/308049/en-us

     

    Monday, November 3, 2008 11:24 AM
    Moderator