.net SQL Select Count with date criteria

[EDN Admin]

Iam wanting to do a SQL SELECT COUNT in VB .NET to an Access DB. I can get the code to work using a text fields and text criteria but if I use a date field and Date criteria it returns 0 when there are some 47 records that it should count.

<div style="color:Black;background-color:White; <pre>
<span style="color:Blue; Dim Results <span style="color:Blue; As <span style="color:Blue; String
<span style="color:Blue; Dim InvDT <span style="color:Blue; As <span style="color:Blue; String = <span style="color:Blue; Date.Today
InvDT = <span style="color:#A31515; "#" & InvDT & <span style="color:#A31515; "#"
<span style="color:Blue; Dim strCnn <span style="color:Blue; As <span style="color:Blue; String = <span style="color:#A31515; "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=S:MainData.mdb" <span style="color:Green; connection string to your DB
<span style="color:Blue; Dim strSQL <span style="color:Blue; As <span style="color:Blue; String = <span style="color:#A31515; "SELECT COUNT(InvoiceID) FROM InvHdr WHERE InvoiceDate = " & InvDT <span style="color:Green; Query to the DB
<span style="color:Blue; Dim cnn <span style="color:Blue; As <span style="color:Blue; New OleDbConnection(strCnn) <span style="color:Green; Create new connection
<span style="color:Blue; Dim CMD <span style="color:Blue; As <span style="color:Blue; New OleDbCommand(strSQL, cnn) <span style="color:Green; Create new command
cnn.Open()
<span style="color:Blue; Dim DR <span style="color:Blue; As OleDbDataReader
DR = CMD.ExecuteReader() <span style="color:Green; Populate the reader
<span style="color:Blue; While (DR.Read())
<span style="color:Green; Loop all the records in your case it will be only one
<span style="color:Green; MessageBox.Show(DR(0).ToString()) Show the result of the query
Results = DR(0).ToString()
<span style="color:Blue; End <span style="color:Blue; While
cnn.Close()
[/code]
<br/>
thanks in advance as this has realy stumped me.<br/>


View the full article