Simple ado question about parameters

[Guest]

Hi all,

I'm trying to connect to an access database to return a recordset. the only way i can query the database is using sql statements but i can't seem to pass dates in the sql string. I was wondering if it was possible with parameters.

My code is as follows:

Dim rec As Recordset
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command

cnn.CursorLocation = adUseClient
cnn.Open sConnection
cmd.ActiveConnection = cnn
cmd.CommandText = sSQL

sSQL = "select Format(Date_Time, 'dd-mmm-yyyy hh:mm') as When, Format(" & sUnit & "," & sFormat
sSQL = sSQL & ") as Reading from " & Range("DataTable")

-- the dates are on this line as sDate1 and 2, they are stored as strings at the moment..
sSQL = sSQL & " where (Date_Time between '" & sDate1 & "' and '" & sDate2 & "')"
sSQL = sSQL & " and (Format(" & sUnit & "," & sFormat & ") < " & "'" & Range("SpecifiedMin") & "'"
sSQL = sSQL & " or Format(" & sUnit & "," & sFormat & ") > " & "'" & Range("SpecifiedMax") & "'"
sSQL = sSQL & ") order by Date_Time"

Set rec = cmd.Execute

Thanks for any help,
Regards.
Steven

 

[Jon Skeet [C# MVP]]

I'm trying to connect to an access database to return a recordset.
the only way i can query the database is using sql statements but i
can't seem to pass dates in the sql string. I was wondering if it was
possible with parameters.

Yes, it is. See http://www.pobox.com/~skeet/csharp/faq/#db.parameters

 

[Cor Ligthert]

Yes, it is. See http://www.pobox.com/~skeet/csharp/faq/#db.parameters

Why? It's an ADO question.

I could not find anything about a recordset on that page.



Cor

 

[Cor Ligthert]

Hi Steven,

One of the normal regulars from this newsgroup has an article on the MSDN
frontpage. (He is a while not that active as normally, maybe he was to busy
with that article)

Don't be afraid I show you ADONET, because Bill is also describing the
differences from the ADO classic parameter and the ADONET parameter. And
there you have your answer in my opinion.

http://msdn.microsoft.com/data/default.aspx?pull=/library/en-us/dnadonet/html/adocnet4.asp

I hope this helps?

Cor