Query Parameters and date type fields

Question

I have recently switched my database from ACCESS to SQL. My database is now derived using SQL Express 2023. I use parameters in my queries to the database. When I was using ACCESS and querying a table with a date field I had to be very careful when handling a date field. What would occur is that if either of the label/textbox controls holding the date for the parameters (@start and @end) were empty then the query would fail. The label that is assigned to @start always has a value. However, the textbox assigned to the @end parameter might not have a value. To get around this I coded such that if the label control assigned to @end was empty then it would be ignored in the query. I am sure that there are much better ways to do that and would appreciate any input. However, that is not what I am actually looking for. What I would like to know is now that my database is SQL, is this still necessary? I am using smalldatetime data for both of the fields in the table.

Answer 1

Hi @Glenn Walker

What I would like to know is now that my database is SQL, is this still necessary?

Not an expert of Access, but in SQL Server, you would be able to modify your convert statements for the date with an ISNULL(<value>,<default>) and provide a default value for null values.

For example, ISNULL(Convert(varchar,@end,112), '20230101'), this would default the null to be the 1st Jan 2023.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

    Private Sub UpdateRecord()
        BuildQuery = ""
#Region "Parameters"
        MasterBase.AddParam("@first", txtFirst.Text)
        MasterBase.AddParam("@middle", txtMiddle.Text)
        MasterBase.AddParam("@last", txtLast.Text)
        MasterBase.AddParam("@full", txtFirst.Text + " " + txtLast.Text)
        MasterBase.AddParam("@dept", cboDept.Text)
        MasterBase.AddParam("@title", txtTitle.Text)
        MasterBase.AddParam("@detail", txtDetail.Text)
        MasterBase.AddParam("@path", lblFilePath.Text)
        MasterBase.AddParam("@start", lblStart.Text)
        If Not String.IsNullOrWhiteSpace(txtEnd.Text) Then
            MasterBase.AddParam("@end", txtEnd.Text)
            BuildQuery = "colEnd=@end,"
        Else
            BuildQuery = ""
        End If
        MasterBase.AddParam("@active", rdoActive.Checked)
        MasterBase.AddParam("@obsolete", rdoObsolete.Checked)
        MasterBase.AddParam("@recno", lblStaffID.Text)
#End Region
#Region "Connect And execute query"
        MyError = "Update query for Employee record failed."
        MasterBase.MasterBaseQuery("UPDATE sitStaffMaster " &
                                   "Set colFirst=@first,colMiddle=@middle,colLast=@last,colFull=@full," &
                                   "colDept=@dept,colTitle=@title,colDetail=@detail,colFilePath=@path," &
                                   "colStart=@start," & BuildQuery & "colActive=@active,colObsolete=@obsolete " &
                                   "WHERE colStaffID=@recno")
#End Region
    End Sub

Answer 3

I am so sorry. I had attached the code that went with that. I am having a little trouble with your interface. Anyway, here is the code that goes with that. I hope this works this time.