GlennWalker-1236 avatar image
0 Votes"
GlennWalker-1236 asked karenpayneoregon answered

Using a params based query

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)
         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 = ""
                 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)
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?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Did you get an exception inside the Catch block?

To perform the command, you can execute RecordCount = ListCommand.ExecuteNonQuery. The ListTable and ListAdapter are not needed in this case.

0 Votes 0 ·

1 Answer

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

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)}]")
                 End If
             End Using
         End Using
     End Sub
 End Class

Here we get

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.