MS SQL Update

[moone009]

Basically whatever column is to the right of my input column I would like to use that value as the value to update in my database.

Before I can even test it to see if it works I keep getting "End if without Block if error" any ideas?

Sub SNVlookUP_Server()
    Dim cn As ADODB.Connection
    Dim strSQL As String, i As Integer, ii As Integer
    Dim rst As ADODB.Recordset
    Dim a, b, bb, sA As String, sCol As String, Rng As Range, Rng1 As Range, InputBox As String
    
'**********************************************************************************************'
'sCol is where the user will enter the column of the look up value
'**********************************************************************************************'
    ''InputBox = Application.InputBox("Insert DB Connections:")
    sCol = Application.InputBox("Please input the column of the look up value:")
    If VBA.Len(sCol) = 0 Then
        MsgBox "No column is selected!"
    Else
        Set Rng = Range(Cells(1, sCol), Cells(65536, sCol).End(xlUp))
        Set Rng1 = Range(Cells(2, 2), Cells(65536, 2).End(xlUp))
        a = Application.WorksheetFunction.Transpose(Rng.Value)
        bb = Application.WorksheetFunction.Transpose(Rng1.Value)
        b = a
        Set cn = New ADODB.Connection
        cn.Open msConnStringDB
        For i = LBound(a) To UBound(a)
        For ii = LBound(bb) To UBound(bb)
'**********************************************************************************************'
'To change the look up value change it.viItemName

'To change the value that the look up value is referencing change "max(th.TransDate) as MaxDate"
'**********************************************************************************************'

                    strSQL = "Update lalocation " & _
                    "Set propertydatamap = '" & VBA.Trim$(bb(ii)) & "'" & _
                    "WHERE ServiceNumber ='" & VBA.Trim$(a(i)) & "'"
                    Set rst = cn.Execute(strSQL)
                
Next
        Rng.Offset(0, 1).Value = Application.WorksheetFunction.Transpose(b)
        cn.Close
        Set cn = Nothing
End If

End Sub

 

[Ruddles]

You have two Fors but only one Next. Is that single Next intended to terminate both For loops?

If so, change it to Next ii, i.

 

[Norie]

You need to add a next, and you should probably include the loop control variables not just Next on it's own - it get's lonely and doesn't know who to talk to.

        For i = LBound(a) To UBound(a)
            For ii = LBound(bb) To UBound(bb)
                '**********************************************************************************************'
                'To change the look up value change it.viItemName
                'To change the value that the look up value is referencing change "max(th.TransDate) as MaxDate"
                '**********************************************************************************************'
                strSQL = "Update lalocation " & _
                         "Set propertydatamap = '" & VBA.Trim$(bb(ii)) & "'" & _
                         "WHERE ServiceNumber ='" & VBA.Trim$(a(i)) & "'"
                Set rst = cn.Execute(strSQL)
            Next ii
        Next i