snif ! je comprends plus rien ....

Question

j ai fait une fonction qui regarde si deux champs string sont identiques.
Si cest le cas alors j incremente une variable a.

a la fin je regarde si cette valeur a est restee a 0, ou sil elle a ete incrementee

si elle reste a 0 je lance une requete sql.

Mais je comprend pas ca marche pas !!
j ai fait des tests affichant la valuer de a.

Dans la boucle a sincrement bien mais dans le derniere if, meme si nest pas egal a 0 il rentre dans la boucle et devient egal a 0.

Apperement le double = ne fonctionne pas en vb !!

Que dois je faire alors ??


'##### delete hotfixs which are not available #####
    'initialization of the recordset    sql1 "select * from HOTFIX where ID_SERVER" & stock
    Set rs = dbs.OpenRecordset(sql1, dbOpenDynaset)
    'for every record of the recordset we test
    While Not rs.EOF
        a = 0
        str1 = rs.Fields("HOTFIX")
        For j = 1 To retour
            str2 = Séparer(iHotfixs, ";").PosAlr(j)
            If StrComp(str1, str2, vbTextCompare) = 0 Then
                a = a + 1
                
            End If
        Next
        'if the hotfix values for this server in the DB doesnZt exist in the  field of the excel file, we donZt increment a => a = 0
        If a = 0 Then
            MsgBox ("efface")            sql1 " delete * from hotfixs where ID_SERVER " & stock & " and HOTFIX '" & rs.Fields("HOTFIX") & "'"
        End If
        rs.MoveNext
    Wend


merci

cs_lhomme · Answer

euh en fait cest ce code la desolé


    '##### insert the news ones #####
    'If there is already values in the DB
    If testPresenceHotfix(dbs, stock) = True Then
    
        'initialization of the recordset before updating        sql1 "select * from HOTFIX where ID_SERVER" & stock
        Set rs = dbs.OpenRecordset(sql1, dbOpenDynaset)
        
        'for each hotfix value in the excel field, we verify its presence in the db
        For j = 1 To retour
            a = 0
            
            While Not rs.EOF
                str2 = Séparer(iHotfixs, ";").PosAlr(j)
                str1 = rs.Fields("HOTFIX")
                If StrComp(str1, str2, vbTextCompare) = 0 Then
                    a = a + 1
                End If
                
                rs.MoveNext
            Wend
           
            'if the hotfix value for the server is already in the database, a will be > 0 so if a=0 weZve got to introduct the value in the DB
            If (a = 0) Then
                MsgBox ("hot manquant => insertion" & a)
                sql1 = "INSERT INTO HOTFIX (ID_SERVER,HOTFIX) values  (" & stock & ",'" & Séparer(iHotfixs, ";").PosAlr(j) & "');"
                dbs.Execute sql1
            End If
        Next
        rs.Close
    End If

cs_lhomme · Answer

il faut bien que kinitialise a à 0 non ?

au debut je le met a 0, ensuite si il varie je ne lance pas de requete sql et si il ne varie pas je lance ma requete !

Je suis bien oblige de le mettre a 0 non ? :question)

cs_lhomme · Answer

ce que je comprend pas cest que au debut mon a je le meta 0.

ensuite  quant je test mon a dans am boucle while mon a ne vaut plus 0.

mais il rentre quant meme dans le if final et quand je laffiche a linterieur il vaut 0 mais pkoi donc ??

cs_CanisLupus · Answer

C koi ta variable stock ? c du string ?
d'autre part, tu pourrais expliquer ta fonction séparer. Moi, j'utilise split.

Cordialement

CanisLupus

cs_CanisLupus · Answer

Et puis au vu de ton code, si tu faisais un rs.movefirst avant While Not rs.EOF ???
Sinon, la soluce de Renfeld me semble plus correcte.
Ou alors, tu mets le code en entier, on teste et on te dis où ça peche.

Cordialement

CanisLupus

cs_lhomme · Answer

ok alors voila mon code ! surtout on ne se moque pas , jai pas vraiment utlise les fonctionnalite de vba je pense. j ai fait ca a ma sauce ! Private Sub Cmd_Importation_Click() ' all the var. we need ( string, integer, recordset, database ) Dim PathFic As String Dim NomFic As String Dim NomFicXLS As String Dim iDomain As String Dim iServer_Name As String Dim iHotfixs As String Dim iOS_Version As String Dim i As Integer Dim j As Integer Dim k As Integer Dim sql1 As String Dim dbs As database Dim retour As String Dim stock As Integer Dim rs As Recordset Dim a As Integer Dim str1 As String Dim str2 As String 'initialization of the objects we use Set dbs = CurrentDb Set ClasseurXLS = CreateObject("Excel.application") 'Initialization : ask the name of the file If (Text1.value <> "") Then NomFic = Text1 NomFic = "" & NomFic Else MsgBox ("The fileZs name is missing") Exit Sub End If 'Initialization : ask the way to obtain the file If (Text2.value <> "") Then PathFic = Text2 Else MsgBox ("The folderZs name is missing") Exit Sub End If 'Open the Worbook to import the informations ClasseurXLS.Workbooks.Open PathFic & NomFic ' ##################### For every server of the excel file ################################# '!!!!!!!!!!!!!! To use the recordsets the Microsoft DAO 2.5/3.5 Compatibility Library is needed !!!!!!!!!!!!! 'We donZt start at the first line because thatZs the columnsZdescriptions => i = 2 i = 2 Do While ClasseurXLS.cells(i, 1) <> "" 'Save the excelZinformations into different var ( string, integer ) iDomain = ClasseurXLS.cells(i, 1) iServer_Name = ClasseurXLS.cells(i, 3) iHotfixs = ClasseurXLS.cells(i, 7) 'HOTFIXS : how many hotfixs values are in the Hotfixs field of the excel file retour = Séparer(iHotfixs, ";").Count '###################### Basic Data and Server recognition ######################## '##### Is the server in the access DB ? ##### 'If yes Update If testPresenceServer(dbs, iServer_Name) = True Then 'initialization of the recordset before updating sql1 "select * from MAINTABLE where NAME_SERVER'" & iServer_Name & "'" Set rs = dbs.OpenRecordset(sql1, dbOpenDynaset) 'save the ID_SERVER for the HotfixsZupdate stock = rs.Fields("ID_SERVER") rs.Edit 'Update rs.Fields("DOMAIN") = iDomain 'End of the Update rs.Update rs.Close End If 'If no, we have to create a new line in the access DB If testPresenceServer(dbs, iServer_Name) = False Then 'insert the new server thanks to a sql request sql1 = "INSERT INTO MAINTABLE (NAME_SERVER,DOMAIN) values ('" & iServer_Name & "','" & iDomain & "');" dbs.Execute sql1 'save the ID_SERVER for the HotfixsZupdate sql1 "select * from MAINTABLE where NAME_SERVER'" & iServer_Name & "'" Set rs = dbs.OpenRecordset(sql1, dbOpenDynaset) stock = rs.Fields("ID_SERVER") rs.Close End If '############################################################################################ '###################### HOTFIXSZUPDATE ######################## '##### delete hotfixs which are not available ##### 'initialization of the recordset sql1 "select * from HOTFIX where ID_SERVER" & stock Set rs = dbs.OpenRecordset(sql1, dbOpenDynaset) 'for every record of the recordset we test While Not rs.EOF a = 0 str1 = rs.Fields("HOTFIX") For j = 1 To retour str2 = Séparer(iHotfixs, ";").PosAlr(j) If StrComp(str1, str2, vbTextCompare) = 0 Then a = a + 1 End If Next 'if the hotfix values for this server in the DB doesnZt exist in the field of the excel file, we donZt increment a => a = 0 If a = 0 Then MsgBox ("efface") sql1 " delete * from hotfixs where ID_SERVER " & stock & " and HOTFIX '" & rs.Fields("HOTFIX") & "'" End If rs.MoveNext Wend '##### insert the news ones ##### 'If there is already values in the DB If testPresenceHotfix(dbs, stock) = True Then 'initialization of the recordset before updating sql1 "select * from HOTFIX where ID_SERVER" & stock Set rs = dbs.OpenRecordset(sql1, dbOpenDynaset) 'for each hotfix value in the excel field, we verify its presence in the db For j = 1 To retour a = 1 While Not rs.EOF If StrComp(str1, str2) = 0 Then a = 0 End If rs.MoveNext Wend 'if the hotfix value for the server is already in the database, a will be > 0 so if a=0 weZve got to introduct the value in the DB If a = 0 Then MsgBox ("hot manquant => insertion" & a) sql1 = "INSERT INTO HOTFIX (ID_SERVER,HOTFIX) values (" & stock & ",'" & Séparer(iHotfixs, ";").PosAlr(j) & "');" dbs.Execute sql1 End If Next rs.Close End If 'if the DB is empty If testPresenceHotfix(dbs, stock) = False Then For j = 1 To retour MsgBox ("table vide insertion primaire") sql1 = "INSERT INTO HOTFIX (ID_SERVER,HOTFIX) values (" & stock & ",'" & Séparer(iHotfixs, ";").PosAlr(j) & "');" dbs.Execute sql1 Next End If '############################################################################################### 'incrementation of i => next line in the excel file i = i + 1 Loop ' ############################################## End of the Update ################################################ 'closing the Workbook ClasseurXLS.Workbooks.Close MsgBox ("Update OK") End Sub et sinon la fonction separer je lai chopee sur le site : ' we define a special type for the function Séparer Public Type SEPRR PosRev(100) As String PosAlr(100) As String Count As String End Type ' With this function we will have three to use use it : ' 1 - Séparer( ........, symbol).Count : return the number of occurence in the input string ' 2 - Séparer( ........, symbol).PosRev(X) : return the X th string from the right side ' 3 - Séparer( ........, symbol).PosAlr(X) : return the X th string from the left side Public Function Séparer(Chaine As String, Séparateur As String) As SEPRR Dim IndX, IndX2, Lpos Séparer.PosRev(0) = Chaine Lpos = 1 Séparer.Count = 0 IndX = 0 For IndX2 = Lpos To Len(Chaine) - Lpos If Lpos < Len(Chaine) Then If Mid(Chaine, IndX2, Len(Séparateur)) = Séparateur Then IndX = IndX + 1 Séparer.PosAlr(IndX) = Mid(Chaine, Lpos, IndX2 - Lpos) Lpos = IndX2 + Len(Séparateur) Séparer.Count = Séparer.Count + 1 End If Else End If Next Séparer.PosAlr(IndX + 1) = Right(Chaine, Len(Chaine) - Lpos + 1) Séparer.Count = Séparer.Count + 1 For IndX = 1 To Séparer.Count Séparer.PosRev(Séparer.Count - IndX + 1) = Séparer.PosAlr(IndX) Next End Function merci , merci !!

cs_lhomme · Answer

bon deja dans le code aue je vous ai mis il manque juste deux lignes : dasn le while

            str1 = rs.Fields("HOTFIX")
            str2 = Séparer(iHotfixs, ";").PosAlr(j)

Et puis en fait mon erreur cetait le rs.movefirst qui manquait avant de rentrer dans la boucle while !!

sinon mon rs restait tt le temps a eof apres la premiere iteration du if !!

Merci les gars !! :big)

Snif ! je comprends plus rien ....

7 réponses

Discussions similaires