Results 1 to 2 of 2

Thread: Creating generic update statement
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Creating generic update statement

    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

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating generic update statement

    Quote Originally Posted by mmittal View Post
    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 by Steve_; Aug 1st, 2019 at 01:29 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •