Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > it.comp.lang.visual-basic > #19931 > unrolled thread

Dati tabella Excel su tabella SQL Server

Started byMarco75 <marcoporzi75@gmail.com>
First post2021-11-05 04:02 -0700
Last post2021-11-05 06:12 -0700
Articles 2 — 1 participant

Back to article view | Back to it.comp.lang.visual-basic

Contents

  Dati tabella Excel su tabella SQL Server Marco75 <marcoporzi75@gmail.com> - 2021-11-05 04:02 -0700
    Re: Dati tabella Excel su tabella SQL Server Marco75 <marcoporzi75@gmail.com> - 2021-11-05 06:12 -0700

#19931 — Dati tabella Excel su tabella SQL Server

FromMarco75 <marcoporzi75@gmail.com>
Date2021-11-05 04:02 -0700
SubjectDati tabella Excel su tabella SQL Server
Message-ID<30937b09-e13b-4d50-8b2c-aa5acbbd19d0n@googlegroups.com>
Ciao,
devo popolare una tabella SQL Server a partire da Excel con VBA.
ho copiato un codice usato in un altro file che funziona benissimo ma in questo file non riesco a farlo funzionare!
Ho un errore in Update del recordset (Errore di sistema &H80040E2F (-2147217873).

questo il codice:

 Dim R1 As String
    
    R1 = MsgBox("Sto per inviare i dati al server..." & vbNewLine & vbNewLine & "Confermi?", vbQuestion + vbYesNo, "Salvataggio dati")
    
    If R1 = vbYes Then
    
    Dim conn As ADODB.Connection
    Dim ServerName As String
    Dim DbName As String
    Dim TableName As String
    Dim UserID As String
    Dim Password As String
    Dim RowCounter As Long
    Dim ColCounter As Integer
    Dim NoOfFields As Integer
    Dim StartRow As Long
    Dim EndRow As Long
    Dim r As Long
    Dim cs As String
    Dim strSQL As String
    Dim lRow As Long
    
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    NoOfFields = 16
    StartRow = 2
    EndRow = lRow
    
    Set conn = New ADODB.Connection
    cs = "DRIVER=SQL SERVER;"
    cs = cs & "DATABASE=StoricoTare;"
    cs = cs & "SERVER=AAA\SQLEXPRESS"
    
    conn.Open cs, "User", "Password"
    
    Dim rs As New ADODB.Recordset
    Set rs = New ADODB.Recordset
   
    
    strSQL = "Select * From T_StoricoTareTest;"
    rs.Open strSQL, conn, adOpenDynamic, adLockOptimistic
    'rs.Open TableName, cn, adOpenKeyset, adLockOptimistic, adCmdTable
    
    r = 2
    
    Do While Len(Range("A" & r).Formula) > 0
    
    With rs
        .AddNew
        .Fields("DataOraInserimento") = Range("Q" & r).Value
        .Fields("Anno") = Range("R" & r).Value
        .Fields("Mese") =Range("S" & R).Value
        .Fields("PianoIspezione") = Range("A" & r).Value
        .Fields("MediaSG") = Range("D" & R).Value
        .Fields("M") = Range("E" & R).Value
        .Fields("Min") = Range("F" & R).Value
        .Fields("Max") = Range("G" & R).Value
        .Fields("DevSt") = Range("H" & R).Value
        .Fields("3Sigma") = Range("I" & R).Value
        .Fields("Scost+") = Range("J" & R).Value
        .Fields("Scost-") = Range("K" & R).Value
        .Fields("ATB+") = Range("L" & R).Value
        .Fields("ATB-") = Range("M" & R).Value
        .Fields("PercentATB_Sup_Inf") = Range("N" & R).Value
        .Fields("MediaArrotondata") = Range("O" & R).Value
        .Update
    End With
    
    r = r + 1
    
    Loop
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
    
    MsgBox ("I dati sono stati caricati sul server..."), vbInformation + vbOKOnly, "Salvataggio dati"
    
    ElseIf R1 = vbNo Then
    MsgBox ("Salvataggio dati annullato..."), vbInformation + vbOKOnly, "Salvataggio dati"
    End If

[toc] | [next] | [standalone]

#19932

FromMarco75 <marcoporzi75@gmail.com>
Date2021-11-05 06:12 -0700
Message-ID<8ffc2bee-29e6-4348-8d8c-37249c61325en@googlegroups.com>
In reply to#19931
Il giorno venerdì 5 novembre 2021 alle 12:02:33 UTC+1 Marco75 ha scritto:
> Ciao, 
> devo popolare una tabella SQL Server a partire da Excel con VBA. 
> ho copiato un codice usato in un altro file che funziona benissimo ma in questo file non riesco a farlo funzionare! 
> Ho un errore in Update del recordset (Errore di sistema &H80040E2F (-2147217873). 
> 
> questo il codice: 
> 
> Dim R1 As String 
> 
> R1 = MsgBox("Sto per inviare i dati al server..." & vbNewLine & vbNewLine & "Confermi?", vbQuestion + vbYesNo, "Salvataggio dati") 
> 
> If R1 = vbYes Then 
> 
> Dim conn As ADODB.Connection 
> Dim ServerName As String 
> Dim DbName As String 
> Dim TableName As String 
> Dim UserID As String 
> Dim Password As String 
> Dim RowCounter As Long 
> Dim ColCounter As Integer 
> Dim NoOfFields As Integer 
> Dim StartRow As Long 
> Dim EndRow As Long 
> Dim r As Long 
> Dim cs As String 
> Dim strSQL As String 
> Dim lRow As Long 
> 
> lRow = Cells(Rows.Count, 1).End(xlUp).Row 
> 
> NoOfFields = 16 
> StartRow = 2 
> EndRow = lRow 
> 
> Set conn = New ADODB.Connection 
> cs = "DRIVER=SQL SERVER;" 
> cs = cs & "DATABASE=StoricoTare;" 
> cs = cs & "SERVER=AAA\SQLEXPRESS" 
> 
> conn.Open cs, "User", "Password" 
> 
> Dim rs As New ADODB.Recordset 
> Set rs = New ADODB.Recordset 
> 
> 
> strSQL = "Select * From T_StoricoTareTest;" 
> rs.Open strSQL, conn, adOpenDynamic, adLockOptimistic 
> 'rs.Open TableName, cn, adOpenKeyset, adLockOptimistic, adCmdTable 
> 
> r = 2 
> 
> Do While Len(Range("A" & r).Formula) > 0 
> 
> With rs 
> .AddNew 
> .Fields("DataOraInserimento") = Range("Q" & r).Value 
> .Fields("Anno") = Range("R" & r).Value 
> .Fields("Mese") =Range("S" & R).Value 
> .Fields("PianoIspezione") = Range("A" & r).Value 
> .Fields("MediaSG") = Range("D" & R).Value 
> .Fields("M") = Range("E" & R).Value 
> .Fields("Min") = Range("F" & R).Value 
> .Fields("Max") = Range("G" & R).Value 
> .Fields("DevSt") = Range("H" & R).Value 
> .Fields("3Sigma") = Range("I" & R).Value 
> .Fields("Scost+") = Range("J" & R).Value 
> .Fields("Scost-") = Range("K" & R).Value 
> .Fields("ATB+") = Range("L" & R).Value 
> .Fields("ATB-") = Range("M" & R).Value 
> .Fields("PercentATB_Sup_Inf") = Range("N" & R).Value 
> .Fields("MediaArrotondata") = Range("O" & R).Value 
> .Update 
> End With 
> 
> r = r + 1 
> 
> Loop 
> rs.Close 
> Set rs = Nothing 
> conn.Close 
> Set conn = Nothing 
> 
> MsgBox ("I dati sono stati caricati sul server..."), vbInformation + vbOKOnly, "Salvataggio dati" 
> 
> ElseIf R1 = vbNo Then 
> MsgBox ("Salvataggio dati annullato..."), vbInformation + vbOKOnly, "Salvataggio dati" 
> End If
Scusate, ho risolto! in SQL server non mi aveva preso un'impostazione sulla colonna ID con contatore automatico, ora funziona

[toc] | [prev] | [standalone]

Back to top | Article view | it.comp.lang.visual-basic

csiph-web