Creating generic update statement

mmittal

New Member
Joined
Aug 1, 2019
Messages
5
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Steve_

Board Regular
Joined
Apr 28, 2010
Messages
167
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...

Code:
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...

Code:
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...
Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,005
Messages
5,526,232
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top