• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

cannot get this

i cannot get it to log any sql querys which didnt match in the database ?
i put a debug on the wrtetolog line and it doesnt stop and i know it should cause i put a fake fax number in the system.





Private Sub SaveFailure(dbip As String, dbname As String, dbTable As String, fldResult As String, fldFaxNum As String, strResult As String, strFaxNum As String)

Dim sqlFindContact As String
Dim rstx As New ADODB.Recordset
Dim strContactId As String, sqlUpdateContact As String, sqlUpdateFax As String
On Error GoTo errhand
sqlFindContact = "SELECT CONTACTID FROM CONTACT WHERE FAX ='" & strFaxNum & "'"
sqlUpdateContact = "UPDATE C_CONTACT_ADD_ON SET CORRESPONDENCE_PREFERENCE = 'BAD_FAX' WHERE CONTACTID='"
'sqlUpdateFax = "UPDATE " & dbTable & _
'       " SET " & fldResult & "='" & strResult & "'" & _
'        " WHERE " & fldFaxNum & "='" & strFaxNum & "'"

'MsgBox fldFaxNum & " " & strContactId
If CheckConnect(dbip, dbname) Then
    'look for contactid in contact table
    rstx.Open sqlFindContact, cxsql, adOpenStatic, adLockReadOnly
    If Not rstx.EOF Then
        strContactId = rstx!ContactId
        'update the C_CONTACT_ADD_ON table with bad fax flag
        Dim strbadfaxstring As String
        strbadfaxstring = sqlUpdateContact & strContactId & "'"
        'MsgBox strbadfaxstring
        Call progressbar("SQL updating")
        cxsql.Execute strbadfaxstring
        If Not rstx.EOF Then
        Call writetolog(App.Path & "\log.txt", "Fax Record Not Found " & strFaxNum)
        End If
               
        'update the dbtable with result info
        'cxsql.Execute sqlUpdateFax
    End If
Else
    MsgBox "can not connect to database"
End If
exitfn:
If Not cxsql Is Nothing Then
    If cxsql.State = 1 Then cxsql.Close
End If
If Not rstx Is Nothing Then
    If rstx.State = 1 Then rstx.Close
End If
Exit Sub
errhand:
MsgBox err.Description
Resume exitfn
End Sub
0
aot2002
Asked:
aot2002
  • 9
  • 8
  • 5
2 Solutions
 
PrestaulCommented:
You may have to explain this further.  You are looking up a contact using a fax number that you already know is bad so that you can flag it and then log it?  What does CheckConnect do?  What is the total behavior of SaveFailure supposed to be?
0
 
aot2002Author Commented:
sorry
the query works fine and the system has been working great, the problem is im not when the system cannot find a fax number or not so i thought id log when a query returns no results of finding a fax number in the contact table.

0
 
aot2002Author Commented:
the problem is im not when the system cannot find a fax number


i mean the problem is when it cannot find a fax in the contact table im not aware of this so i wanna log it
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
EDDYKTCommented:
May be just remove


' If Not rstx.EOF Then
Call writetolog(App.Path & "\log.txt", "Fax Record Not Found " & trFaxNum)
 '       End If

because you already test the case at the beginning
0
 
PrestaulCommented:
Are you saying that if it cannot find a fax number it enters this routine and logs it?  Or that this routine is supposed to check for a fax number and log it if it can't find it?
0
 
PrestaulCommented:
EDDYKT, he has already checked it.  This means that it is redundant BUT also that it will in no way effect the code.
0
 
aot2002Author Commented:
Are you saying that if it cannot find a fax number it enters this routine and logs it?  Or that this routine is supposed to check for a fax number and log it if it can't find it?

HUH ?
arent these the same questions ?

if a fax number is found in contact table then it shouldnt log to the file !

or IF NO RESULTS FOUND WHEN SEARCHING FOR A FAXNUM IN CONTACT THEN LOG THAT FAXNUM YOUR TRYING TO FIND
0
 
aot2002Author Commented:
does this make sense Prestaul ?
0
 
PrestaulCommented:
Your statements don't make sense and you don't understand my questions...  Try this (keeping in mind that now I'm guessing what behavior you want):

    If Not rstx.EOF Then

This line says, "If there IS a matching fax number then do something".  I think that you want:

    If rstx.EOF Then

This line says, "If there IS NOT a matching fax number then do something".  HOWEVER, this means that you don't have a ContactID!  You cannot update a record based on a ContactID that you retrieve by looking for a fax number that does not exist in the table!

Does this make sense?
0
 
EDDYKTCommented:
>>EDDYKT, he has already checked it.  This means that it is redundant BUT also that it will in no way effect the code.

Doesn't he open recordset with clientcursor or servercursor?
0
 
PrestaulCommented:
Try this and see if it logs in the text file correctly.  
Replace:

    If Not rstx.EOF Then
        strContactId = rstx!ContactId
        'update the C_CONTACT_ADD_ON table with bad fax flag
        Dim strbadfaxstring As String
        strbadfaxstring = sqlUpdateContact & strContactId & "'"
        'MsgBox strbadfaxstring
        Call progressbar("SQL updating")
        cxsql.Execute strbadfaxstring
        If Not rstx.EOF Then
        Call writetolog(App.Path & "\log.txt", "Fax Record Not Found " & strFaxNum)
        End If
               
        'update the dbtable with result info
        'cxsql.Execute sqlUpdateFax
    End If


With:

    If rstx.EOF Then
        Call writetolog(App.Path & "\log.txt", "Fax Record Not Found " & strFaxNum)
    End If
0
 
aot2002Author Commented:
This line says, "If there IS NOT a matching fax number then do something".

yes let me try this this is what i want !
if no fax was found i wanna log that faxnumber !
0
 
EDDYKTCommented:
by the way what is your writetolog routinme?
0
 
aot2002Author Commented:
Public Function writetolog(tmp, line)

fle = FreeFile

If fileexists(tmp) = True Then
Open tmp For Append As fle
Print #fle, line
Close fle

Else 'ok no file has been created yet !

Open tmp For Output As fle
Print #fle, line
Close fle

End If


End Function


Function fileexists(strFile)
 
  X = Dir(strFile)
  If X = "" Then
  fileexists = False
  Else
  fileexists = True
  End If
 
End Function

0
 
PrestaulCommented:
>>by the way what is your writetolog routinme?

Good question!

>>Doesn't he open recordset with clientcursor or servercursor?

Why would it matter what type of cursor he is using?
0
 
EDDYKTCommented:
May be just replace with this


Public Function writetolog(tmp, line)
    Dim FileNo As Integer
   
    On Error Resume Next
    FileNo = FreeFile
    Open tmp For Append Shared As #FileNo
    Print #FileNo, line
    Close #FileNo
End Sub
0
 
aot2002Author Commented:
ohh wait i see what you mean ok im sorry for the misscommuication

ok heres the english terms im doing !

the system is pulling a text file which looks for failed fax numbers then it searchs the contact table for the fax number it finds
when it finds one it updates the contactid table with badfax.

the problem with that is i cannot seem to get it to tell me when its searching the table for a faxnumber that for some reason it did not find that faxnum in the contact table ?
if i write it to a log i can check it later.

hope this helps
0
 
aot2002Author Commented:
Comment from EDDYKT  feedback
Date: 09/23/2004 02:15PM EDT
 Comment  


May be just replace with this
 



THANKS
0
 
PrestaulCommented:
OKAY!!!  Now try this...

Replace:

    If Not rstx.EOF Then
        strContactId = rstx!ContactId
        'update the C_CONTACT_ADD_ON table with bad fax flag
        Dim strbadfaxstring As String
        strbadfaxstring = sqlUpdateContact & strContactId & "'"
        'MsgBox strbadfaxstring
        Call progressbar("SQL updating")
        cxsql.Execute strbadfaxstring
        If Not rstx.EOF Then
        Call writetolog(App.Path & "\log.txt", "Fax Record Not Found " & strFaxNum)
        End If
               
        'update the dbtable with result info
        'cxsql.Execute sqlUpdateFax
    End If


With:

    If Not rstx.EOF Then
        strContactId = rstx!ContactId
        'update the C_CONTACT_ADD_ON table with bad fax flag
        Dim strbadfaxstring As String
        strbadfaxstring = sqlUpdateContact & strContactId & "'"
        'MsgBox strbadfaxstring
        Call progressbar("SQL updating")
        cxsql.Execute strbadfaxstring
               
        'update the dbtable with result info
        'cxsql.Execute sqlUpdateFax
    Else
        Call writetolog(App.Path & "\log.txt", "Fax Record Not Found " & strFaxNum)
    End If
0
 
PrestaulCommented:
That says, "If you find the fax number (that is bad) then update the record to show that it is bad, otherwise write to the log so that I know you couldn't find the bad fax number."

Is that what you wanted?
0
 
EDDYKTCommented:
If he opens the client side cursor, then it is no problem, however if he opens with server side cursor, then read this


http://beta.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/VB_Databases/Q_20707547.html?query=adOpenKeyset&clearTAFilter=true
0
 
PrestaulCommented:
EDDYKT, I don't think that his problems are related to the cursor type in this case.  I see why you asked the question though and cursor type may be an issue in the future if he has many users.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 9
  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now