?
Solved

cannot get this

Posted on 2004-09-23
22
Medium Priority
?
234 Views
Last Modified: 2010-05-02
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
Comment
Question by:aot2002
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 5
22 Comments
 
LVL 4

Expert Comment

by:Prestaul
ID: 12135854
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
 
LVL 1

Author Comment

by:aot2002
ID: 12135874
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
 
LVL 1

Author Comment

by:aot2002
ID: 12135902
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:EDDYKT
ID: 12135906
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
 
LVL 4

Expert Comment

by:Prestaul
ID: 12135922
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
 
LVL 4

Expert Comment

by:Prestaul
ID: 12135935
EDDYKT, he has already checked it.  This means that it is redundant BUT also that it will in no way effect the code.
0
 
LVL 1

Author Comment

by:aot2002
ID: 12135965
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
 
LVL 1

Author Comment

by:aot2002
ID: 12136026
does this make sense Prestaul ?
0
 
LVL 4

Expert Comment

by:Prestaul
ID: 12136033
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
 
LVL 26

Expert Comment

by:EDDYKT
ID: 12136053
>>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
 
LVL 4

Expert Comment

by:Prestaul
ID: 12136055
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
 
LVL 1

Author Comment

by:aot2002
ID: 12136068
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
 
LVL 26

Expert Comment

by:EDDYKT
ID: 12136070
by the way what is your writetolog routinme?
0
 
LVL 1

Author Comment

by:aot2002
ID: 12136092
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
 
LVL 4

Expert Comment

by:Prestaul
ID: 12136101
>>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
 
LVL 26

Assisted Solution

by:EDDYKT
EDDYKT earned 1000 total points
ID: 12136124
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
 
LVL 1

Author Comment

by:aot2002
ID: 12136132
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
 
LVL 1

Author Comment

by:aot2002
ID: 12136143
Comment from EDDYKT  feedback
Date: 09/23/2004 02:15PM EDT
 Comment  


May be just replace with this
 



THANKS
0
 
LVL 4

Accepted Solution

by:
Prestaul earned 1000 total points
ID: 12136151
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
 
LVL 4

Expert Comment

by:Prestaul
ID: 12136169
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
 
LVL 26

Expert Comment

by:EDDYKT
ID: 12136173
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
 
LVL 4

Expert Comment

by:Prestaul
ID: 12136245
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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month15 days, 7 hours left to enroll

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question