Using Two Dates To Return Data

bopo

Member
Joined
Dec 10, 2006
Messages
13
Programming Experience
Beginner
Hi

Well basically, a user enters a date into a textbox, and another date into another, I want a lable to display how many records (or rows) of information there are, between those dates.

Below is a start, but I am quite new and this coding doesnt work really, but I have given it my best shot :eek:.
VB.NET:
        Dim cn As OleDbConnection
        Dim str As String
        Dim i As Long
        Dim cmd As OleDbCommand
        cn = New OleDbConnection
        With cn
            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; data source = K:\Global\IT Call Log\PerDB\PermenantDB.mdb;"
        End With
        'cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source = K:\Global\IT Call Log\PerDB\PermenantDB.mdb;")
        cn.Open()
        'Dim str2 As String
        'str2 = "SELECT id FROM Table1 WHERE ID between '" & txtfrom.Text & "' AND '" & txtto.Text & "'"

        str = "SELECT count(SubmittedAt) FROM Table1 WHERE SubmittedAt BETWEEN '" & txtfrom.Text & "' AND '" & txtto.Text & "'"
        cmd = New OleDbCommand(str, cn)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        If dr.HasRows() Then
            dr.Read()
            i = dr.GetInt32(0)
            lblqueryresults.Text = dr.GetInt32(0).ToString
        Else
            i = -1
            lblqueryresults.Text = "Nothing Found"
        End If
        dr.Close()
        cn.Close()
        cn.Dispose()
Help appreciated
 
I also had the same problem with MS Access and .NET DateTime, but then I used OleDbParameters instead of passing variables in the SQL string. Works fine.
 
Read the DW2 link in my signature, first the section on creating a simple data app, then the section on creating a form to search data

You are using .NET 2. You should NOT be writing connection strings or SQL statements in your code.. Read the DW2 link to find out more
 
I think when you put a date in the select format you must use the DateValue before it, so the select command should be something like
str = "SELECT count(SubmittedAt) FROM Table1 WHERE SubmittedAt BETWEEN DateValue('"& txtfrom.Text &"') AND DateValue('" & txtto.Text & "')"

Hope this will solve the problems ................
 
Hi.. aside form this post being 2 months old, please read the PQ link in my signature, and from then on dont advise newbies to make SQLs with string concatenation like that. It's old, insecure, slow, inefficient and there are NO good reasons for EVER writing an SQL like. As noted, read the PQ link and you will learn interesting things :)

Welcome to the forums
 
Back
Top