Solved

cannot get this

Posted on 2004-09-23
22
197 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 250 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 250 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now