DUBT on dictionary

[sal21]

pseudo code

this is a partt of for next loop

...

dim DICT as Object
Set DICT = CreateObject("Scripting.Dictionary")

for next loop

INDICE = LAT & "-" & LNG

If Not DICT.Exists(INDICE) Then

SQL = "INSERT INTO " & TABELLA & " (ISTAT, REG, STRADA, LAT, LNG) VALUES " & _
"('" & ISTAT & "','" & REG & "','" & Replace(STRADA, "'", "''") & "','" & LAT & "','" & LNG & "')"
CON.Execute (SQL)

NR = NR + 1

DICT.Add NR, INDICE

Else

Stop ' IF EXITS

End If

next

is this correct if INDICE not exists in dictionay?
in effect i dont insert a ner recorset if in dictionaty already exists a pair of LAT and LNG

[HansV]

How do you change LAT and LNG in the loop?

[SpeakEasy]

Surely you can directly query TABELLA for records with existing LAT LNG

[sal21]

How do you change LAT and LNG in the loop?

complete code:

Code: Select all

Public Sub LEGGI_STRADE()

    Dim strLine As String
    Dim FSO As Object
    Dim TSO As Object

    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set TSO = FSO.OpenTextFile("C:\Lavori_Vb6\MAPPA_ITALIA\FILES\REGIONI\" & NOMEFILE)

    Set DICT = CreateObject("Scripting.Dictionary")
    DICT.RemoveAll

    NR = 0

    Me.LAZIONI.Caption = REG & "-" & REGIONE
    DoEvents

    CON.BeginTrans
    Do While Not TSO.AtEndOfStream

        strLine = TSO.ReadLine

        If Right(strLine, 4) = ";;;;" Then
            STRINGA = Split(strLine, ";")(0)
            COMUNE = UCase(STRINGA)
            If Split(strLine, ";")(1) > "" Then
                ISTAT = Split(strLine, ";")(1)
                Me.LAZIONI2.Caption = ISTAT & "-" & COMUNE
                DoEvents
            Else
                ISTAT = ""
            End If
        End If

        If Left(strLine, 2) = ";;" And ISTAT > "" Then
            STRINGA = strLine
            STRINGA = Replace(STRINGA, ";;", "")
            STRADA = UCase(Split(STRINGA, ";")(0))
            STRADA = Replace(STRADA, Chr(34), "")
            STRADA = Replace(STRADA, Chr(63), "")

            LAT = Split(STRINGA, ";")(2)
            LNG = Split(STRINGA, ";")(3)

            INDICE = LAT & "-" & LNG

            If Not DICT.Exists(INDICE) Then

                SQL = "INSERT INTO " & TABELLA & " (ISTAT, REG, STRADA, LAT, LNG) VALUES " & _
                      "('" & ISTAT & "','" & REG & "','" & Replace(STRADA, "'", "''") & "','" & LAT & "','" & LNG & "')"
                CON.Execute (SQL)

                NR = NR + 1

                DICT.Add NR, INDICE

            Else

                Stop

            End If

        End If

    Loop
    CON.CommitTrans

    SQL = "UPDATE " & TABELLA & " INNER JOIN COMUNI_ISTAT ON " & TABELLA & ".ISTAT=COMUNI_ISTAT.ISTATEXT SET " & TABELLA & ".REGIONE=[COMUNI_ISTAT].[REGIONE], " & TABELLA & ".PR=[COMUNI_ISTAT].[PR], " & TABELLA & ".CF=[COMUNI_ISTAT].[CF], " & TABELLA & ".PROVINCIA=[COMUNI_ISTAT].[PROVINCIA], " & TABELLA & ".CAP=[COMUNI_ISTAT].[CAP], " & TABELLA & ".COMUNE=[COMUNI_ISTAT].[COMUNE], " & TABELLA & ".PRFX=[COMUNI_ISTAT].[PRFX], " & TABELLA & ".Z=[COMUNI_ISTAT].[Z], " & TABELLA & ".ZONA=[COMUNI_ISTAT].[ZONA], " & TABELLA & ".AGG='" & Date & "' WHERE " & TABELLA & ".REG='" & REG & "'"
    CON.Execute SQL, , adCmdText + adExecuteNoRecords

    SQL = "UPDATE REGIONI SET DIMENS='" & NR & "', AGG='" & Date & "' WHERE REG1='" & REG & "'"
    CON.Execute (SQL)

    TSO.Close
    Set TSO = Nothing
    Set FSO = Nothing

End Sub

[HansV]

I don't think you should have Stop in the Else part...

[SpeakEasy]

Still think you should simply leverage TABELLA

I'd set up a mutifield index set to No Duplicates across LAT and LNG. At which point you won't be able to Insert duplicate LAT, LNG records into the table. You'll need to wrap the Con.Excute call with On Error Resume Next ... On Error Goto 0

[sal21]

I don't think you should have Stop in the Else part...

only for test...
if item exists in dictionary object