Using Parameters in Queries

[The_Grudge]

I wrote an app about ten years ago using VB 6.0. It connects to an Oracle back-end and it's been humming along pretty well for that time with periodic updates to functionality. I primarily work as a Data Analyst so I don't code every day and while I've written some VB.NET apps, you could say my skills aren't exactly polished any more. Were they ever?

Anyway, the program does a lot of data retrieval/manipulation using queries. Select, Insert, Update, Delete -- all of it. I had a problem for the longest time where the regional settings on the user's computer had to be set a certain way or the app wouldn't work. I rewrote the SQL to include To_Date functions on the dates in question and that seemed to fix it.

I'm now tasked with converting the SQL to work with SQL Server as our company is trying to get off Oracle. A few searches of this forum led me to something I had never seen before; using parameters in the SQL. Wow, makes sense!

I got a few queries working with parameters and I can see the advantages for sure. But I have a couple of questions...

1) When I say "Where TABLE.DATE >= ? and TABLE.DATE <=?" in my query, it returns dates from well outside the range I ask for. Using Between instead seems to fix the issue. That leads me to question 2...

2) Is there anything I need to do in the SQL itself to work with the date? I identify it's a date in my parameter (below), but I wonder how it knows what format my date is in. Do I need to add a CAST statement (or something like that) to the SQL statement so it knows what format my date is in?

.Parameters.Append .CreateParameter("StartDate", adDate, adParamInput, , dtStartDate)

[techgnome]

1) Well, for SQL Server, you should be using Named Parameters and a SQL Server driver/provider, not a generic one. That might be why your query doesn't quite work. But that's a guess.
2) Format is for display only. Period. If the date being passed to the parameter is a Date object (and not something that simply looks like a date) then it doesn't matter what the format (which is a display mechanism) is. That said, it looks like you're relying on the default property of a datetime picker... which may or may not be a datetime object - it could be a string... and that may also be part of the issue. You should be EXPLICITLY stating what property to use, and making sure that it is returning a DateTime object.

-tg

[The_Grudge]

Thanks. I tried using parameters like @StartDate and @EndDate, but it wouldn't work. A post on here suggested trying ? instead and that seemed to work.

I do format the date in the code (Dim stStartDate as Date) and use it that way, assuming I understand correctly. Here's my code, with some of the SQL names simplified

Public Function PrintSpecials(ByVal dtStartDate As Date, ByVal strInstitution As String)
 Dim strSQL As String
 Dim intCount As Integer
 Dim intLineCount As Integer
 Dim strLastLocation As String
 Dim dtEndDate As Date
 Dim intDaysDiff As Integer
 Dim rsSpecials As ADODB.Recordset
 Set rsSpecials = New ADODB.Recordset
 
 Dim strEndDate As String
 dtEndDate = DateAdd("d", 4, dtStartDate)
 
 strSQL = "SELECT MYTABLE.CLINIC_DATE, MYTABLE.SPECIAL_DATE, PROD.CSU_LOCATION.LOCATION, " & _
          "PROD.CSU_NURSE_CLINIC.NURSE_CLINIC_NAME, PROD.LOCAL_DOC.DOC_NAME, MYTABLE.TIME_OF_DAY " & _
          "from MYTABLE left outer join PROD.CSU_LOCATION on " & _
          "MYTABLE.LOCATION_NUM = PROD.CSU_LOCATION.LOCATION_NUM " & _
          "left outer join PROD.CSU_NURSE_CLINIC on MYTABLE.CLINIC_NUM = PROD.CSU_NURSE_CLINIC.CLINIC_NUM " & _
          "left outer join PROD.LOCAL_DOC on MYTABLE.SID_DOC_NUM = PROD.LOCAL_DOC.SID_DOC_NUM " & _
          "where MYTABLE.CLINIC_DATE BETWEEN ? AND ? " & _
          "and MYTABLE.INSTITUTION = ? " & _
          "and MYTABLE.SPECIAL = ? " & _
          "and MYTABLE.CANCELLED = ? order by 3, 6, 1 ;"
          
Dim objCommand As ADODB.Command
Set objCommand = New ADODB.Command
  
With objCommand
   .ActiveConnection = SQLCon
   .CommandType = adCmdText
   .CommandText = strSQL
   .Prepared = True
   

   .Parameters.Append .CreateParameter("StartDate", adDate, adParamInput, , dtStartDate)
   .Parameters.Append .CreateParameter("EndDate", adDate, adParamInput, , dtEndDate)
   .Parameters.Append .CreateParameter("Institution", adChar, adParamInput, 4, strInstitution)
   .Parameters.Append .CreateParameter("SpecialFlag", adChar, adParamInput, 1, "Y")
   .Parameters.Append .CreateParameter("CancelledFlag", adChar, adParamInput, 1, "N")
  
  rsSpecials.CursorType = adOpenDynamic
  Set rsSpecials = objCommand.Execute
  Set objCommand = Nothing
  
End With

[dilettante]

One problem is that these "DBMSs From Planet X" (including SQL Server which is a re-branded and then heavily modified Sybase) are foreign to Windows.

You have to keep in mind that Windows is far more of a Visual Basic operating system than people are willing to give it credit for. That's VB as in classic VB, i.e. VB6 - not The Great Pretender a.k.a. VFred.

VB (VB6), ADO, Windows in general have an impedance mismatch with such a foreign DBMS. There are lots of hacks and workarounds to make them usable, but again these are not fully compatible with Windows or VB6.


Foreign date formats can be particularly problematic. It doesn't help that a lot of the Morts who use VB can barely handle the native VB/OLE Date type without becoming completely tangled up in their own underwear. They usually go very far wrong trying to stuff Date values into String variables and getting confused when they rely on implicit type coercion that tries to undo the mess they've made.

But foreign date/time values might be anything. For example the "SQL Server Clueless" often end up choosing a data type like the SQL Server "Timestamp" type when they create a table column. For most of the current SQL Server ODBC Drivers and OLEDB Providers this type gets mapped to ADO's adDBTimeStamp "crutch" data type which represents such values as the Variant subtype Decimal. This is such an old issue that there are plenty of MS KB articles dating back to the turn of the Century on coping with it.

These can be problematic, and so it is possible this is where you have gone wrong.


Consider this example, which makes use of a persisted Recordset to save the need to install SQL Server or create and populate a database instance just to show some of the gyrations involved:

Option Explicit

'Based on:
'
'   HOWTO: Get Fractions of a Second from ADO adDBTimeStamp Field (Q193869).

Private Sub Form_Load()
    Dim ConversionFactor As Variant 'Pseudo-Const, We don't have Decimal Consts or literals.
    Dim RS As ADODB.Recordset
    Dim Dec As Variant
    Dim FullFidelity As String

    ConversionFactor = CDec(86400000) 'Milliseconds per day.

    Set RS = New ADODB.Recordset
    With RS
        .CursorLocation = adUseClient
        .Open "persisted.xml", , adOpenStatic, adLockReadOnly
        Dec = CDec(.Fields("Timestamp").Value)
        FullFidelity = Format$(.Fields("Timestamp").Value, "YYYY-MM-DD HH:NN:SS") _
                     & "." _
                     & Format$((Abs(Dec) - Fix(Dec)) * ConversionFactor Mod 1000, "000")

        'Margins:
        ScaleLeft = -90
        CurrentX = 0
        ScaleTop = -90
        CurrentY = 0
        Font.Bold = True
        ForeColor = &HFF8000
        Print "As a "; TypeName(.Fields("Timestamp").Value); Tab(22); _
              "As a "; TypeName(Dec); Tab(40); _
              "With Full Fidelity ";
        ForeColor = &HF0&
        Print "(ms)"
        
        Font.Bold = False
        ForeColor = vbBlack
        Print Format$(.Fields("Timestamp").Value, "YYYY-MM-DD HH:NN:SS"); Tab(22); _
              CStr(Dec); Tab(40); _
              Left$(FullFidelity, Len(FullFidelity) - 4);
        ForeColor = &HF0&
        Print Right$(FullFidelity, 4)
        .Close
    End With
End Sub

Since we haven't seen your database schema we can't say whether this is a part of your problem.