[RESOLVED] [02/03] Select statement

Hi people,

I created a topic not soo long ago asking for help with a select statement and the people who helped me out did a great job. However after working on my application for a few days I can see that the select statement doesn't actually work.

Now so far I have the following in a search button:

Code:

---

    Dim conn As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\CNS.mdb")
            Dim adapter As New OleDbDataAdapter("Select [Company], [ChaCode], COUNT(ChaCode) As [NumberofChargeCodes] From Log where Dol BETWEEN #" & dtp.Text & "# AND #" & dtp2.Text & "# And CompID = " & cmbcomp.SelectedValue & " And NoteNumber = 1 Group By [Company], [ChaCode]", conn)
            Dim dt As New DataTable("Log")
            adapter.Fill(dt)
            dglog.DataSource() = dt
            dt.DefaultView.AllowNew = False

---

Dol in the Access database has been set to date.

Now comes the difficult part, its hard to explain because I have a range of data in my database but the answer the above Sql statement gives is not correct, for example I entered a few test data in the month of June, now when I choose a date in June in dtp1 and dtp2 (where the range of data is in between these dates) the answer the sql gives me is not the answer it should be as its empty, another example is I have data on the 30/06/2008 and data on the 01/07/2008 now if I do a search between these dates it doesnt show the correct data.

A lot of the times it shows all counts and not between the dates chosen.

I hope someone could offer me help please.

As a starter I think that it should be dtp.Value not .Text

Change both your dtp.text statements as a starter and then report back if anything has changed.

Thread moved to Database Development forum - which is where SQL questions belong

This is a good example of why you should use parameterised queries instead of building strings.

The problem is that you have made a basic error, in thinking that either a string and a date are the same (definitely not), or that the database system cares what format you use for date strings (again, definitely not).


If you build an SQL statement using a string, you need to format the date string as the database system wants it - which is not what you've done. If you pass #01/07/2008#, that is taken to mean Jan 7th. With most database systems #30/06/2008# would fail, but Access/Jet is 'nice' and automatically converts it (as the day is > 12, so can't be a month).

For an explanation of how to format them, see the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of this forum)


If you use parameterised queries, you just pass a date value rather than a string (as stimbo said), and the parameter does all of the required formatting for you - and not only does it work around the problem of the formatting for the database system you are using, but also for any system you change to later.

For information on how to use them, see the article Retrieving and Saving Data in Databases, which is also linked from the DB FAQs.

Thanks Si the Geek, I started using parameters and the problem got eliminated.

Thanks again.