Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted

Macro to update database

Hey Guys - I am trying to run this... but it doesn't do anything... Any hints on what I might be doing wrong

SUB UpdateRecord


'Save as ConnectDB.vbs

Dim strSQL

Dim strUsername

Dim strPassword

Dim snpData

Dim dbMyDBConnection

Dim Dimension       

Set snpData = CreateObject("ADODB.Recordset")

Set dbMyDBConnection = CreateObject("ADODB.Connection")

strUsername = "User"

strPassword = "Pass"

dbMyDBConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=db;User ID=" & strUsername & ";Password=" & strPassword & ";"

dbMyDBConnection.Open

Dimension = "A"

strSQL = "UPDATE TABLE_NAME SET FIELD1 = 'YES' WHERE DIM = '" & Dimension & "';"

strSQLCommit = "COMMIT;"

snpData.Open strSQL, dbMyDBConnection

snpData.Open strSQLCommit, dbMyDBConnection

snpData.Close

dbMyDBConnection.Close

Set snpData = Nothing

Set dbMyDBConnection = Nothing

END SUB

marcus_sommer‌, tamilarasu

Tags (1)
1 Solution

Accepted Solutions

Re: Macro to update database

So this is what finally worked for me

'Save as ConnectDB.vbs

Dim strSQL

Dim snpData

Dim dbMyDBConnection

Dim vSelectionDim

Dim vSelectionTradeDate


SUB UpdateRecord

Set snpData = CreateObject("ADODB.Recordset")

Set dbMyDBConnection = CreateObject("ADODB.Connection")

dbMyDBConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=Db;User Id=username;Password=pass;"

dbMyDBConnection.Open

vSelectionDim = ActiveDocument.Evaluate("DIM")

vSelectionTradeDate = ActiveDocument.Evaluate("Date(Date, 'MM/DD/YYYY')")

strSQL = "UPDATE APPPNLADMIN.TABLE_NAME SET FIELD1 = 'Yes' WHERE DIM = '" & vSelectionDim & "' and DATE = TO_DATE('" & vSelectionTradeDate & "', 'MM/DD/YYYY')"

strSQLCommit = "COMMIT"

snpData.Open strSQL, dbMyDBConnection

snpData.Open strSQLCommit, dbMyDBConnection


'snpData.Close

dbMyDBConnection.Close

Set snpData = Nothing

Set dbMyDBConnection = Nothing


END SUB

View solution in original post

14 Replies
wdchristensen
Valued Contributor

Re: Macro to update database

Hi Sunny,

The code looks good but the database could be locked by another transaction. You might try to execute the update code outside of a script just to verify that it isn't something on the database side.

Re: Macro to update database

This is a dummy table I created just for testing this out. One thing I did find was that all the Dims need to come before the Sub...

Also, I think the problem is something related to the connection string itself because this is where it have issues

snpData.Open strSQL, dbMyDBConnection

wdchristensen
Valued Contributor

Re: Macro to update database

snpData.Open I believe is only for selecting data. Maybe try:

snpData.Update strSQL, dbMyDBConnection

Re: Macro to update database

Nope, that did not work

wdchristensen
Valued Contributor

Re: Macro to update database

snpData.Execute strSQL, dbMyDBConnection

???

Re: Macro to update database

That did not work as well. It there a way to know if we at least had a connection? I am not sure if we even got connected or not.

wdchristensen
Valued Contributor

Re: Macro to update database

If you change strSQL from an “update” to a “select”, maybe you can count the number of items in the recordset.

Dim cntRecords

strSQL = "SELECT * FROM TABLE_NAME WHERE DIM =’A’"

snpData.Open strSQL, dbMyDBConnection

cntRecords =snpData.RecordCount

If cntRecords is greater than zero we know that the connection is working.

Re: Macro to update database

So, this is what I got...

msgbox 5

snpData.Open strSQL, dbMyDBConnection

cntRecords = snpData.RecordCount

msgbox cntRecords

msgbox 6

I get the popup 5, but it never goes to 6 or cntRecords. So, it has to be the connection, right?

wdchristensen
Valued Contributor

Re: Macro to update database

I agree, something is up with the connection.


dbMyDBConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=db;User ID=" & strUsername & ";Password=" & strPassword & ";" 


in your example you used "db" for the data source, is that valid DSN for the connection?