This does not appear to work

hog

hog

Well-known member
Joined
Mar 17, 2003
Messages
984
Location
UK
I have this code in my app which does the following.

Prior to a user closing or deleting a contract the CanClose method of the job object is called to check if there are any jobs that have been done but are awaiting an invoice. If there are then the contract cannot be closed/deleted.

Here is the code of the CanClose method:

Code: 
  Public Function CanClose() As Boolean

         set internal error flag to false

        m_Error = False

         create a connection using global connection string

        m_objConn = New System.Data.OleDb.OleDbConnection(gconnConnection)

         set-up the SQL which will return records for selected id number with an invoice due

        m_strSQL = "SELECT tblJobs.jobid FROM tblJobs WHERE tblJobs.contractid = " & m_ContractID & " AND tblJobs.active = -1 AND tblJobs.jobdone = -1"

         create a new data adapter for the required data

        m_odaJob = New System.Data.OleDb.OleDbDataAdapter(m_strSQL, m_objConn)

         protect this section of code

        Try

             open the connection to the required database

            m_objConn.Open()

             fill the data table

            m_odaJob.Fill(m_dsJob, "tblJobs")

             if record count is greater than zero jobs exist with invoice due so return false

            If m_dsJob.Tables("tblJobs").Rows.Count >= 1 Then

                Return False

            Else

                Return True

            End If

        Catch objException As Exception

            ShowError("Location:   Class Job" & ControlChars.CrLf & ControlChars.CrLf & _
                      "Procedure:  CanClose(ByVal lngID As Long)" & ControlChars.CrLf & _
                      ControlChars.CrLf & "Error Text: " & objException.Message)

             set internal error flag to true

            m_Error = True

        Finally

            If m_objConn.State.Open Then

                 close connection to database

                m_objConn.Close()

            End If

        End Try

    End Function

The thing is this method return True whether there are invoices due or not.

For example if I run it against a contract that has 3 jobs assigned to it, but none have been done yet the method should return True as there are no jobs with that id and with active and jobdone set to True. But the method returns False and reports a total of 3 records found?

If I run this sql in Access it return no records as I would expect.

Any clues:confused:
 
Mmm, just a thought.....should I be using a Scalar query approcah instead?
 
And the answer is.......yes I should use Scalar:)

Code: 
  Public Function CanClose() As Boolean

         set internal error flag to false

        m_Error = False

         create SQL string to obtain total po cost

        m_strSQL = "SELECT COUNT(jobid) AS TotalRecords FROM tblJobs WHERE (contractid = ?) AND (active = ?) AND (jobdone = ?)"

         set properties of the oledbcommand object

        m_odcJob.CommandType = CommandType.Text
        m_odcJob.CommandText = m_strSQL

         set the parameter details

        m_odcJob.Parameters.Add("contractid", m_ContractID)
        m_odcJob.Parameters.Add("active", -1)
        m_odcJob.Parameters.Add("jobdone", -1)

         assign a connection

        m_odcJob.Connection = m_objConn

         try to obtain the sum of po cost and assign it to private member m_TotalCost

        Try

            m_odcJob.Connection.Open()

            If CType(m_odcJob.ExecuteScalar(), Integer) Then

                Return False

            Else

                Return True

            End If

        Catch objException As Exception

            ShowError("Location:   Class Job" & ControlChars.CrLf & ControlChars.CrLf & _
                      "Procedure:  CanClose(ByVal lngID As Long)" & ControlChars.CrLf & _
                      ControlChars.CrLf & "Error Text: " & objException.Message)

             set internal error flag to true

            m_Error = True

        Finally

            If m_odcJob.Connection.State.Open Then

                m_odcJob.Connection.Close()

            End If

        End Try

    End Function
 
Hog , have you found a reasonable explanation on why the

first way did not work??? I mean there doesnot seem to be

anything wrong with it.
 
I know...it was driving me nuts!!

All I know is this way works fine so Ill use it instead.

One of lifes mysteries :)
 
Back
Top