I wrote a lot of vb apps using the Merant odbc driver.
A lot of security things were included into the latest update but what???
A lot of our customers can't use their apps anymore...
Please, I hope someone can give me some advise.
Latest XP Pro Update...
Started by
Ruben@Adafi
, Aug 11 2005 07:13 AM
#1
Posted 11 August 2005 - 07:13 AM
Ruben@Adafi
-
- Member
-
- 17 posts
Member
I wrote a lot of vb apps using the Merant odbc driver.
A lot of security things were included into the latest update but what???
A lot of our customers can't use their apps anymore...
Please, I hope someone can give me some advise.
#2
Posted 11 August 2005 - 07:43 AM
dsenette
-
- Community Leader
-
- 26,047 posts
Je suis Napoléon!
are you wanting programming help to get your programs to work with the new update?
#3
Posted 11 August 2005 - 08:25 AM
Ruben@Adafi
- Topic Starter
-
- Member
-
- 17 posts
Member
That'd be great but I guess this isn't the right board for that.
However, here is a bit more indepth explanation of what is happening.
The programs are all created to interact with a bookkeeping application called Multivers (only runnable in Belgium and the Netherlands).
To connect to that bookkeeping database, I connect with the Merant driver using odbc.
The program I created looks for invoices and exports them to a text file.
The next time it reads a log file to continue from the last exported invoice.
This is very important as the textfiles need to be handed over to a third-party for processing into a check-up program (governemental project).
Anyways, it all worked fine till all of a sudden I was updating the program on my developing environment and it jammed...
I thought what the heck and then custmers stare calling saying that their program (the original state) didn't work anymore.
I checked it and it always happened after applying that Windows XP pro SP2 update. (BTW, this program is only installed on Win XP Pro SP2 machines)
Below is the code when searching for invoices, I will put into color the important things.
I just notice that colors don't work in code so look for [COLOR=RED] to pinpoint the important things.
Thx a lot already.
However, here is a bit more indepth explanation of what is happening.
The programs are all created to interact with a bookkeeping application called Multivers (only runnable in Belgium and the Netherlands).
To connect to that bookkeeping database, I connect with the Merant driver using odbc.
The program I created looks for invoices and exports them to a text file.
The next time it reads a log file to continue from the last exported invoice.
This is very important as the textfiles need to be handed over to a third-party for processing into a check-up program (governemental project).
Anyways, it all worked fine till all of a sudden I was updating the program on my developing environment and it jammed...
I thought what the heck and then custmers stare calling saying that their program (the original state) didn't work anymore.
I checked it and it always happened after applying that Windows XP pro SP2 update. (BTW, this program is only installed on Win XP Pro SP2 machines)
Below is the code when searching for invoices, I will put into color the important things.
I just notice that colors don't work in code so look for [COLOR=RED] to pinpoint the important things.
Thx a lot already.
Private Sub SearchFacturen()
MsgBox "Initialization"
' Show status to user
frmNmbHellerExport.lblDebiteurStatus.Caption = "Please wait while the results are being fetched..."
frmNmbHellerExport.lblDebiteurStatus.Refresh
frmNmbHellerExport.cmdSearchU4Records.Item(0).Enabled = False 'Debit
frmNmbHellerExport.cmdSearchU4Records.Item(1).Enabled = False 'Invoice
frmNmbHellerExport.cmdExportU4Records.Item(0).Enabled = False 'Debit
frmNmbHellerExport.cmdExportU4Records.Item(1).Enabled = False 'Invoice
frmNmbHellerExport.cmdShowExclusion.Visible = False
frmNmbHellerExport.cmdShowExclusion.Enabled = False
MsgBox "Fetch log file dates if there are any"
' *************
' Fetch the log file dates if there are any
' *************
Dim strFactNr As String ' Will contain log file's factnr if there is one
If Not justCreated Then
strLogStr = FileHandler.fileOpen(strLogPath)
Else
strLogStr = FileHandler.fileOpen(strFileDir + "\" + strLogPath)
End If
[COLOR=red]Checks whether all invoice need to be searched starting from user input (if there hasn't been an export before or whether to continue with the last exported invoice[/COLOR]
' If (Len(strLogStr) = 5) Means the log file only contains the text empty
' if (Len(strlogstrThen) > 5) And (Len(strlogstrThen) < 11) Means there is a date for debit but not for fact
If (Len(strLogStr) = 5) Then 'All invoices need to be searched
searchAll = True
Else
strFactNr = Right(strLogStr, 8)
strFactOriNr = strFactNr
searchAll = False
End If
MsgBox "Search all: " & searchAll
' *************
' Build search query
' *************
[COLOR=red]Here is the query[/COLOR]
Dim strSearchFacturen As String
If searchAll Then
' Search all invoices
Dim strSearchFrom As String
strSearchFrom = InputBox("Please fill in the invoice number from" + Chr(10) + "where to start the export." + Chr(10) + Chr(10) + "Clicking cancel will cancel the search.")
If strSearchFrom = "" Then ' Cancel button clicked
frmNmbHellerExport.lblDebiteurStatus.Caption = "Please specify your search..."
frmNmbHellerExport.lblDebiteurStatus.Refresh
Exit Sub
End If
strFactOriNr = strSearchFrom
strSearchFacturen = "SELECT d.CDDEBITEUR, d.FACTNUMMER, d.FACTDATUM, d.VERVALDATUM1, d.VERVALDATUM2, d.FACTBEDRAG, b.BETKORTPERC, b.KORTINGDAGEN, d.BEDRAGBTW, d.CDBETCOND " & _
"FROM DEBFACTUUR d, BETALINGSCONDITIE b " & _
"WHERE d.CDBETCOND = b.CDBETCOND (+)" & _
"AND d.CDDAGBOEK = 'VF' " & _
"AND d.FACTNUMMER > '" & strSearchFrom & "' " & _
"AND (d.CDDEBITEUR <> 'CREDI' AND d.CDDEBITEUR <> 'DUBDE' AND d.CDDEBITEUR <> 'ECOCH' AND d.CDDEBITEUR <> 'KOERS' AND d.CDDEBITEUR <> 'LECOH' AND d.CDDEBITEUR <> 'RAF' AND d.CDDEBITEUR <> 'RSW') " & _
"ORDER BY d.FACTNUMMER"
Else
' Search invoices that are altered or created after dtDate
strSearchFacturen = "SELECT d.CDDEBITEUR, d.FACTNUMMER, d.FACTDATUM, d.VERVALDATUM1, d.VERVALDATUM2, d.FACTBEDRAG, b.BETKORTPERC, b.KORTINGDAGEN, d.BEDRAGBTW, d.CDBETCOND " & _
"FROM DEBFACTUUR d, BETALINGSCONDITIE b " & _
"WHERE d.CDBETCOND = b.CDBETCOND (+)" & _
"AND d.CDDAGBOEK = 'VF' " & _
"AND d.FACTNUMMER > '" & strFactNr & "' " & _
"AND (d.CDDEBITEUR <> 'CREDI' AND d.CDDEBITEUR <> 'DUBDE' AND d.CDDEBITEUR <> 'ECOCH' AND d.CDDEBITEUR <> 'KOERS' AND d.CDDEBITEUR <> 'LECOH' AND d.CDDEBITEUR <> 'RAF' AND d.CDDEBITEUR <> 'RSW') " & _
"ORDER BY d.FACTNUMMER"
End If
MsgBox "Query built: " & Chr(13) & Chr(13) & strSearchFacturen
' *************
' Fill datagrid if there's data
' *************
[COLOR=red]Fill recordset[/COLOR]
Dim testje As Integer
frmNmbHellerExport.dcU4Records.RecordSource = strSearchFacturen
[COLOR=red]The line below: also tried with aduseserver but ten times slower and often gets stuck as if it looses the connection[/COLOR]
frmNmbHellerExport.dcU4Records.CursorLocation = adUseClient
frmNmbHellerExport.dcU4Records.LockType = adLockOptimistic
frmNmbHellerExport.dcU4Records.Refresh
MsgBox "Recordset filled"
'testje = frmNmbHellerExport.dcU4Records.Recordset.RecordCount
If Not frmNmbHellerExport.dcU4Records.Recordset.EOF = False Then
frmNmbHellerExport.lblDebiteurStatus.Caption = "No invoices found for export..."
frmNmbHellerExport.lblDebiteurStatus.Refresh
frmNmbHellerExport.mshfU4Records.Rows = 1
frmNmbHellerExport.mshfU4Records.Visible = False
MsgBox "No invoices found for export..."
Exit Sub
End If
MsgBox "The query found invoices"
' Set column headers
frmNmbHellerExport.mshfU4Records.Visible = False 'to make it more faster
MsgBox "Recordset field count"
frmNmbHellerExport.mshfU4Records.Cols = frmNmbHellerExport.dcU4Records.Recordset.Fields.Count
' Set the maximum rows of flexgrid immediately to make it more faster
' +1 because first row contains column headers
MsgBox "Recordset record count"
[COLOR=red]it jams a the line below: it sais that an E_Fail status got returned
Run-time error '-2147467259 (80004005)':
Data provider or other service returned an E_FAIL status.
[/COLOR]
frmNmbHellerExport.mshfU4Records.Rows = frmNmbHellerExport.dcU4Records.Recordset.RecordCount + 1
MsgBox "Put column headers"
frmNmbHellerExport.mshfU4Records.TextMatrix(0, 0) = "Debiteur"
frmNmbHellerExport.mshfU4Records.TextMatrix(0, 1) = "FactNr"
frmNmbHellerExport.mshfU4Records.TextMatrix(0, 2) = "FactDatum"
frmNmbHellerExport.mshfU4Records.TextMatrix(0, 3) = "Valutadatum"
frmNmbHellerExport.mshfU4Records.TextMatrix(0, 4) = "Vervaldatum"
frmNmbHellerExport.mshfU4Records.TextMatrix(0, 5) = "FactBedrag"
frmNmbHellerExport.mshfU4Records.TextMatrix(0, 6) = "Korting %"
frmNmbHellerExport.mshfU4Records.TextMatrix(0, 7) = "Korting Dagen"
frmNmbHellerExport.mshfU4Records.TextMatrix(0, 8) = "BedragBTW"
' Set column widths
With frmNmbHellerExport.mshfU4Records
.ColWidth(0) = 650
.ColWidth(1) = 800
.ColWidth(2) = 1000
.ColWidth(3) = 1000
.ColWidth(4) = 1000
.ColWidth(5) = 1000
.ColWidth(6) = 900
.ColWidth(7) = 1150
.ColWidth(8) = 1080
.ColWidth(9) = 0
End With
' *************
' Adjust Null values
' *************
MsgBox "Adjust null values"
frmNmbHellerExport.pbExport.Min = "0"
frmNmbHellerExport.pbExport.Max = frmNmbHellerExport.dcU4Records.Recordset.RecordCount
frmNmbHellerExport.pbExport.Value = "0"
frmNmbHellerExport.pbExport.Visible = True
Dim i As Long, j As Long ' Loop variables
Dim intDebit As Long ' Variable to hold the index for the strDebitArray
intDebit = 0
Dim loopDebit As Long ' Loop variable for looping the strDebitArray
Dim blnDebitExists As Boolean ' Boolean; true = strDebitArray already contains that debitNr
Dim strSearchBetLevCond As String ' Variable to check on betalings- en leverconditie
strExcluded = vbNullString
Dim lngExcluded As Long ' Variable to hold the number of excluded records to adjust the number of rows
lngExcluded = 0
Do While Not frmNmbHellerExport.dcU4Records.Recordset.EOF
' Fetch betalings- en leverconditie
strSearchBetLevCond = "SELECT CDBETCOND, CDLEVCOND " & _
"FROM DEBITEUR " & _
"WHERE CDDEBITEUR = '" & frmNmbHellerExport.dcU4Records.Recordset.Fields(0) & "'"
frmNmbHellerExport.dcLevCond.RecordSource = strSearchBetLevCond
frmNmbHellerExport.dcLevCond.CursorLocation = adUseClient
frmNmbHellerExport.dcLevCond.LockType = adLockOptimistic
frmNmbHellerExport.dcLevCond.Refresh
If Not frmNmbHellerExport.dcLevCond.Recordset.EOF = False Then
frmNmbHellerExport.dcLevCond.Enabled = False
frmNmbHellerExport.lblDebiteurStatus.Caption = "Invalid customer reference: " & frmNmbHellerExport.dcU4Records.Recordset.Fields(0) & " -- Search aborted!"
frmNmbHellerExport.lblDebiteurStatus.Refresh
Exit Sub
End If
If frmNmbHellerExport.dcLevCond.Recordset.Fields(0) = "C" And Not frmNmbHellerExport.dcLevCond.Recordset.Fields(1) = "REM" Then
' Build strExcluded for summary on excluded invoices
strExcluded = strExcluded & "InvoiceNr: " & frmNmbHellerExport.dcU4Records.Recordset.Fields(1) & _
vbCrLf & "DebitNr: " & frmNmbHellerExport.dcU4Records.Recordset.Fields(0) & _
vbCrLf & "Reason: " & _
vbCrLf & " * Betalingsconditie: " & frmNmbHellerExport.dcLevCond.Recordset.Fields(0) & " - Leveringsconditie: " & frmNmbHellerExport.dcLevCond.Recordset.Fields(1) & _
vbCrLf & "-------------------------------------------------------------------------------" & vbCrLf & vbCrLf
lngExcluded = 1
Else
If Not frmNmbHellerExport.dcU4Records.Recordset.Fields(0) = "01879" And Not frmNmbHellerExport.dcU4Records.Recordset.Fields(0) = "01880" And Not frmNmbHellerExport.dcU4Records.Recordset.Fields(0) = "00452" Then ' InterCompany
If Not frmNmbHellerExport.dcU4Records.Recordset.Fields(0) = "CREDI" And Not frmNmbHellerExport.dcU4Records.Recordset.Fields(0) = "DUBDE" And Not frmNmbHellerExport.dcU4Records.Recordset.Fields(0) = "ECOCH" And Not frmNmbHellerExport.dcU4Records.Recordset.Fields(0) = "KOERS" And Not frmNmbHellerExport.dcU4Records.Recordset.Fields(0) = "RAF" And Not frmNmbHellerExport.dcU4Records.Recordset.Fields(0) = "RSW" Then
i = i + 1 'ignore fixed row containing column headers
frmNmbHellerExport.pbExport.Value = frmNmbHellerExport.pbExport.Value + "1"
Dim recCount As Integer
recCount = frmNmbHellerExport.dcU4Records.Recordset.RecordCount
' ****
' Percentage completion count
' ****
If Not recCount = 1 Then
frmNmbHellerExport.lblDebiteurStatus.Caption = "Please wait while the results are being fetched... " & Round(Int(((i - 1) / (frmNmbHellerExport.dcU4Records.Recordset.RecordCount - 1)) * 100), 0) & "% Completed."
frmNmbHellerExport.lblDebiteurStatus.Refresh
Else
frmNmbHellerExport.lblDebiteurStatus.Caption = "Please wait while the results are being fetched... " & Round(Int((i / (frmNmbHellerExport.dcU4Records.Recordset.RecordCount)) * 100), 0) & "% Completed."
frmNmbHellerExport.lblDebiteurStatus.Refresh
End If
blnDebitExists = False
For j = 0 To (frmNmbHellerExport.dcU4Records.Recordset.Fields.Count - 1) 'ignore fixed column
frmNmbHellerExport.mshfU4Records.TextMatrix(i, j) = NullToDash(frmNmbHellerExport.dcU4Records.Recordset.Fields(j))
If j = 0 Then ' Store debitnumber
If Not intDebit = 0 Then
For loopDebit = 0 To intDebit - 1
If frmNmbHellerExport.mshfU4Records.TextMatrix(i, j) = strDebitArray(loopDebit) Then
blnDebitExists = True
End If
Next
If Not blnDebitExists Then
strDebitArray(intDebit) = frmNmbHellerExport.mshfU4Records.TextMatrix(i, j)
intDebit = intDebit + 1
End If
Else
strDebitArray(0) = frmNmbHellerExport.mshfU4Records.TextMatrix(i, j)
intDebit = intDebit + 1
End If
lngDebitAmount = intDebit
End If
If j = 9 Then ' Store betalingsconditie
strArray(i) = frmNmbHellerExport.mshfU4Records.TextMatrix(i, j)
End If
Next
Else
' Build strExcluded for summary on excluded invoices
strExcluded = strExcluded & "InvoiceNr: " & frmNmbHellerExport.dcU4Records.Recordset.Fields(1) & _
vbCrLf & "DebitNr: " & frmNmbHellerExport.dcU4Records.Recordset.Fields(0) & _
vbCrLf & "Reason: " & _
vbCrLf & " * DebitNr is not a numeric value: " & frmNmbHellerExport.dcU4Records.Recordset.Fields(0) & _
vbCrLf & "-------------------------------------------------------------------------------" & vbCrLf & vbCrLf
lngExcluded = lngExcluded + 1
End If
Else
' Build strExcluded for summary on excluded invoices
strExcluded = strExcluded & "InvoiceNr: " & frmNmbHellerExport.dcU4Records.Recordset.Fields(1) & _
vbCrLf & "DebitNr: " & frmNmbHellerExport.dcU4Records.Recordset.Fields(0) & _
vbCrLf & "Reason: " & _
vbCrLf & " * InterCompany: " & _
vbCrLf & "-------------------------------------------------------------------------------" & vbCrLf & vbCrLf
lngExcluded = lngExcluded + 1
End If
End If
'you can also increment the row by one, but this makes the loading a bit slower
'msflexgrid1.Rows = msflexgrid1.Rows + 1
frmNmbHellerExport.dcU4Records.Recordset.MoveNext
Loop
frmNmbHellerExport.mshfU4Records.Visible = True
frmNmbHellerExport.lblDebiteurStatus.Caption = "Marking to be exported invoices..."
frmNmbHellerExport.lblDebiteurStatus.Refresh
' *************
' Mark records that are to be exported
' *************
frmNmbHellerExport.pbExport.Min = "0"
frmNmbHellerExport.pbExport.Max = frmNmbHellerExport.dcU4Records.Recordset.RecordCount
frmNmbHellerExport.pbExport.Value = "0"
Dim k As Integer
On Error Resume Next
With frmNmbHellerExport.mshfU4Records
Dim strFetchedFactNr As String
For k = 1 To frmNmbHellerExport.dcU4Records.Recordset.RecordCount
frmNmbHellerExport.pbExport.Value = frmNmbHellerExport.pbExport.Value + "1"
If Not frmNmbHellerExport.dcU4Records.Recordset.RecordCount = 1 Then
frmNmbHellerExport.lblDebiteurStatus.Caption = "Marking to be exported invoices... " & Round(Int(((k - 1) / (frmNmbHellerExport.dcU4Records.Recordset.RecordCount - 1)) * 100), 0) & "% Completed."
frmNmbHellerExport.lblDebiteurStatus.Refresh
Else
frmNmbHellerExport.lblDebiteurStatus.Caption = "Marking to be exported invoices... " & Round(Int((k / (frmNmbHellerExport.dcU4Records.Recordset.RecordCount)) * 100), 0) & "% Completed."
frmNmbHellerExport.lblDebiteurStatus.Refresh
End If
.Row = k 'current row
With frmNmbHellerExport.mshfU4Records
.Col = 1 ' the column which contains the FactNr
strFetchedFactNr = .Text
End With
' if record or modifications not yet exported
If Not searchAll Then
If strFactNr < strFetchedFactNr Then
.FillStyle = flexFillRepeat 'repeat for entire range
.RowSel = k 'ending row
.Col = 0 'starting col
.ColSel = .Cols - 1 'ending col
.CellBackColor = vbCyan
Else
.FillStyle = flexFillRepeat 'repeat for entire range
.RowSel = k 'ending row
.Col = 0 'starting col
.ColSel = .Cols - 1 'ending col
.CellBackColor = vbWhite
End If
Else
If strFactNr <= strFetchedFactNr Then
.FillStyle = flexFillRepeat 'repeat for entire range
.RowSel = k 'ending row
.Col = 0 'starting col
.ColSel = .Cols - 1 'ending col
.CellBackColor = vbCyan
Else
.FillStyle = flexFillRepeat 'repeat for entire range
.RowSel = k 'ending row
.Col = 0 'starting col
.ColSel = .Cols - 1 'ending col
.CellBackColor = vbWhite
End If
End If
Next k
End With
' intLastRow = 0
If Not LenB(strExcluded) = 0 Then ' There are excluded invoices
frmNmbHellerExport.cmdShowExclusion.Enabled = True
frmNmbHellerExport.cmdShowExclusion.Visible = True
frmNmbHellerExport.mshfU4Records.Rows = frmNmbHellerExport.mshfU4Records.Rows - lngExcluded
End If
frmNmbHellerExport.lblDebiteurStatus.Caption = "You can now browse through the invoice list..."
frmNmbHellerExport.lblDebiteurStatus.Refresh
frmNmbHellerExport.pbExport.Visible = False
With frmNmbHellerExport.mshfU4Records
.Row = 1
End With
frmNmbHellerExport.cmdSearchU4Records.Item(0).Enabled = False 'Debit
frmNmbHellerExport.cmdSearchU4Records.Item(1).Enabled = True 'Invoice
frmNmbHellerExport.cmdExportU4Records.Item(0).Enabled = False 'Debit
frmNmbHellerExport.cmdExportU4Records.Item(1).Enabled = True 'Invoice
End Sub
#4
Posted 11 August 2005 - 08:35 AM
dsenette
-
- Community Leader
-
- 26,047 posts
Je suis Napoléon!
nice code (vb?) have you tried rebuilding the odbc instance after the update?
#5
Posted 11 August 2005 - 08:47 AM
Ruben@Adafi
- Topic Starter
-
- Member
-
- 17 posts
Member
Indeed, VB 6.
I did rebuild the odbc instance.
Hence I re-installed the complete bookkeeping software as well... but no result.
I am in contact with the creators of this bookkeeping software (we are a business partner) but they don't find it.
they're testing this on a new version of the bookeeping application, but the realease is planned in 4 months or so.........
I cannot wait that long or we'll loose customers.
Thx for your reply and time.
cheers mate.
I did rebuild the odbc instance.
Hence I re-installed the complete bookkeeping software as well... but no result.
I am in contact with the creators of this bookkeeping software (we are a business partner) but they don't find it.
they're testing this on a new version of the bookeeping application, but the realease is planned in 4 months or so.........
I cannot wait that long or we'll loose customers.
Thx for your reply and time.
cheers mate.
#6
Posted 11 August 2005 - 08:50 AM
dsenette
-
- Community Leader
-
- 26,047 posts
Je suis Napoléon!
sorry that i can't be of much real help but...it's been a while sinve ive used vb (fun stuff) and i never really got to farr into odbc
#7
Posted 11 August 2005 - 09:13 AM
Ruben@Adafi
- Topic Starter
-
- Member
-
- 17 posts
Member
No problem 
Shall I repost this in the programming thread?
Shall I repost this in the programming thread?
#8
Posted 11 August 2005 - 09:39 AM
dsenette
-
- Community Leader
-
- 26,047 posts
Je suis Napoléon!
i'll get a mod to just move this one so you don't have to type it over
#10
Posted 11 August 2005 - 09:43 AM
dsenette
-
- Community Leader
-
- 26,047 posts
Je suis Napoléon!
the move has been requested
Similar Topics
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users
As Featured On:
Sign In
Create Account
