Using a params based query

Question

I have the following UPDATE Query that is causing me fits. The fact is, I just don't see what is wrong with this query and why it will not update the record.

    Public Sub UpdateRecord()
        MasterBase.AddParam("@master", MasterID)
        MasterBase.AddParam("@name", txtName.Text)
        MasterBase.AddParam("@managerID", SiTechID)
        MasterBase.AddParam("@manager", cboManager.Text)
        MasterBase.AddParam("@type", lblChangeClass.Text)
        MasterBase.AddParam("@owner", cboOwner.Text)
        MasterBase.AddParam("@made", txtMade.Text)
        MasterBase.AddParam("@reason", txtReason.Text)
        MasterBase.AddParam("@result", txtResult.Text)
        MasterBase.AddParam("@path", lblFilePath.Text)
        CheckDate()
        MasterBase.AddParam("@active", chkActive.Checked)
        MasterBase.AddParam("@obsolete", chkObsolete.Checked)
        MasterBase.AddParam("@recno", lblChangeID.Text)

        MasterBase.MasterBaseQuery("UPDATE sitChangeMaster " &
                                     "SET MasterBaseID=@master,ChangeName=@name,ChangeManagerID=@managerid," &
                                     "ChangeManager=@manager,ChangeType=@type,ChangeOwner=@owner,ChangeMade=@made," &
                                     "ChangeReason=@reason,ChangeResult=@result,filePath=@path," & BuildQuery &
                                     "Active=@active,Obsolete=@obsolete " &
                                     "WHERE ChangeID=@recno")
    End Sub


    Private Sub CheckDate()
        BuildQuery = ""
        If Not String.IsNullOrWhiteSpace(txtOpen.Text) Then
            MasterBase.AddParam("@open", txtOpen.Text)
            BuildQuery += "DateOpen=@Open,"
        End If
        If Not String.IsNullOrWhiteSpace(txtSubmit.Text) Then
            MasterBase.AddParam("@submit", txtSubmit.Text)
            BuildQuery += ("DateSubmit=@submit,")
        End If
        If Not String.IsNullOrWhiteSpace(txtApprove.Text) Then
            MasterBase.AddParam("@approve", txtApprove.Text)
            BuildQuery += ("DateApprove=@approve,")
        End If
        If Not String.IsNullOrWhiteSpace(txtTrain.Text) Then
            MasterBase.AddParam("@train", txtTrain.Text)
            BuildQuery += ("DateTrain=@train,")
        End If
        If Not String.IsNullOrWhiteSpace(txtEffective.Text) Then
            MasterBase.AddParam("@effective", txtEffective.Text)
            BuildQuery += ("DateEffective=@effective,")
            chkActive.Checked = False
            chkObsolete.Checked = False
        End If
    End Sub

There has always been an issue, using this method with empty date fields, which I have a work around for using the CheckDate() method that creates the variable BuildQuery.

The following code, located in a module runs this query.

        Public Sub MasterBaseQuery(MyQuery As String)
            RecordCount = 0
            Exception = ""
            Try
                MasterBaseConnection.Open() 'Open connection
                ListCommand = New OleDbCommand(MyQuery, MasterBaseConnection) 'Database Command
                Params.ForEach(Sub(p) ListCommand.Parameters.Add(p)) 'Load params into command
                Params.Clear() 'Clear params list
                ListTable = New DataTable
                ListAdapter = New OleDbDataAdapter(ListCommand)
                RecordCount = ListAdapter.Fill(ListTable)

            Catch ex As Exception
                Exception = ex.Message
            End Try
            If MasterBaseConnection.State = ConnectionState.Open Then MasterBaseConnection.Close()
        End Sub

Public Sub AddParam(Name As String, Value As Object)
Dim NewParam As New OleDbParameter(Name, Value)
Params.Add(NewParam)
End Sub

So, at the end of the day, when I run the UPDATE query, I get no update. Clearly, there is an error in the query that I just am unable to ferret out.

Can anyone explain to me where I went wrong with this and why I am unable to get this right?

Answer 1

Not knowing the database but if included in the project check if Copy to Output Directory is set to Copy if newer. Otherwise test the parameter values e.g.

Imports System.Data.SqlClient
Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Operations.TestParameters("Karen", "USA", Nothing, 45)
    End Sub
End Class

Public Class Operations
    Public Shared Sub TestParameters(contactTitle As String, countryName As String, SomeDate As Date?, Id As Integer)
        Using cn = New SqlConnection With {.ConnectionString = ""}
            Using cmd = New SqlCommand With {.Connection = cn}
                cmd.CommandText = <SQL>
                    UPDATE SomeTable 
                    SET ContactName = @ContactTitle, CountryName = @CountryName,SomeDate = @SomeDate 
                    WHERE Id = @Id</SQL>.Value


                cmd.Parameters.Add("@ContactTitle", SqlDbType.DateTime)
                cmd.Parameters.Add("@CountryName", SqlDbType.DateTime)
                cmd.Parameters.Add("@SomeDate", SqlDbType.DateTime)
                cmd.Parameters.Add("@Id", SqlDbType.Int)

                cmd.Parameters("@CountryName").Value = countryName
                cmd.Parameters("@Id").Value = Id

                Dim test = cmd.Parameters.OfType(Of SqlParameter).Any(Function(param) param.Value Is Nothing)

                If test Then

                    For Each sqlParameter As SqlParameter In cmd.Parameters.OfType(Of SqlParameter)
                        Console.WriteLine($"{sqlParameter.ParameterName} -> [" &
                                          $"{If(sqlParameter.Value Is Nothing, "Null", sqlParameter.Value)}]")
                    Next

                End If
            End Using
        End Using
    End Sub

End Class

Here we get

@ContactTitle -> [Null]
@CountryName -> [USA]
@SomeDate -> [Null]
@Id -> [45]