Creating generic update statement

[mmittal]

Hi All,

I'm trying to load data from excel to sql server. For this I created an insert statement which is very generic means this statement can be used for any table. I just have to change the name of the sheet and it will automatically pick up the names of the columns and their values from the rows.

Eg: strSQL = "insert into AeroAdm." & ActiveSheet.Name & "(" & CustoString & ") values( " & myString1 & ")"

Now, I'm trying to get a generic update statement.

The problem here is I'm not able to create this statement since this statement has to read one column heading and then the value of the column based on where condition and then the second column heading and the value and so on.

Eg: 'Update AeroAdm.tablename Set col1 = 'val', col 2 = 'val' and so on till the last column where c.Offset(1, 0).value = '';

Can anybody please guide me how this can be achieved?

Thank you

 

[Steve_]

Hi All,

I'm trying to load data from excel to sql server. For this I created an insert statement which is very generic means this statement can be used for any table. I just have to change the name of the sheet and it will automatically pick up the names of the columns and their values from the rows.

Eg: strSQL = "insert into AeroAdm." & ActiveSheet.Name & "(" & CustoString & ") values( " & myString1 & ")"

Now, I'm trying to get a generic update statement.

The problem here is I'm not able to create this statement since this statement has to read one column heading and then the value of the column based on where condition and then the second column heading and the value and so on.

Eg: 'Update AeroAdm.tablename Set col1 = 'val', col 2 = 'val' and so on till the last column where c.Offset(1, 0).value = '';

Can anybody please guide me how this can be achieved?

Thank you

My Insert code requires it be fed an array. I have included one of my array constructors so that you can see the dimensions/whatnot for the array you will need to to construct...

Function cmdPays_Commit_Clicked()
    Dim pArray(0, 4)
    x = Pull_Table(Server_Name, Database_Name, Invo_Table, "SELECT PK", " Where IID like '" & Userform1.cmbPays_Invoices & "'")
    pArray(0, 0) = "P" & Format(Now, "YYYYMMDDHHMMSS")
    pArray(0, 1) = CDbl(Userform1.txtPays_Amount)
    pArray(0, 2) = x(0, 0)
    pArray(0, 3) = Userform1.txtPays_Comment
    pArray(0, 4) = Format(Date, "YYYY-MM-DD") & " " & Format(Time, "HH:MM:SS")
    Upload_Table Server_Name, Database_Name, Pays_Table, Build_Query(pArray, Pays_Table) ' This will fire my Build_Query function first
End Function                                                                              ' And feed the query to my uploader

Here is how I build my query...

Function Build_Query(qArray, curTable)
    Build_Query = "INSERT INTO " & curTable & " VALUES("
    For i = LBound(qArray, 2) To UBound(qArray, 2)
        If Not IsNull(qArray(0, i)) Then
            Build_Query = Build_Query & "'" & qArray(0, i) & "',"
        Else
            Build_Query = Build_Query & "NULL,"
        End If
    Next i
    Build_Query = Mid(Build_Query, 1, Len(Build_Query) - 1) & ")"
End Function

And then I upload the query here...

Function Upload_Table(cServer, cDatabase, cTable, cQuery)
    Dim CN As ADODB.Connection, RS As ADODB.Recordset, cmd As ADODB.Command
    Set cmd = New ADODB.Command
    Set RS = New ADODB.Recordset
    Set CN = New ADODB.Connection
    CN.Open "Driver={SQL Server};Server=" & cServer & ";Database=" & cDatabase & ";"
    With cmd
        .ActiveConnection = CN
        .CommandTimeout = 0
        .CommandText = cQuery
    End With
    cmd.Execute
    CN.Close: Set CN = Nothing: Set RS = Nothing: Set cmd = Nothing
End Function