Macro SQL INSERT

[#1]

Hi guys,

Long story short here, I want to do a simple UPDATE records in a SQL database via macro.

Here is what I've found in the API guide:

sub Update

rem Set con = CreateObject("ADODB.Connection")

rem Set cmd = CreateObject("ADODB.Command")

rem con.Open "Provider=SQLOLEDB.1;Data Source=ip-sql;Initial Catalog=qlikviewDB","user","pass"

SET Result = ActiveDocument.DynamicUpdateCommand ("UPDATE qlikviewDB.dbo.qvTileSettings SET questionID = 30, itemTagID = 340 ,kpiID = 3 WHERE surveyID = 1 AND TileID = 1")

if Result = false then

MsgBox Result.ErrorMessage

end if

end sub

The API Guide doesn't talk about the SQL connection at all, I did some research (you can see the code in comment. Is it because it's using the connect command from the QVW itself? Doesn't seem to work.

In short, I'm far from a VBScript expert, and to save time, I'm sure one of you guys already has a routine handy that does exactly that 🙂

If you could lend a hand, that would be appreciated, as always.

Thanks a bunch.

[Anonymous]

Hello Nicolas,

The DynamicUpdateCommand is actually for inserting data on the fly into a QlikView document which is basically done the same way as updating a database.

Try searching the forums for writing to database and you should be able to find a few examples on how to do this. There are also plenty of VBscript examples available online for opening database connections and passing queries.

[#3]

Hmm. Make more sense... thanks.

In the meantime, I need to do exactly that too, update the logical Qlikview tables. So, I've played around with the Dynamic Update Command, and I can't get it to work. I haven't found any official documentation yet...

Here is my code sample:

SET Result = ActiveDocument.DynamicUpdateCommand ("UPDATE * SET C.QuestionID = '3450' WHERE C.TileID = 1 AND SurveyID = 1")

Where C.QuestionID is the name of the logical field inside my QVW. I tried changing the "*" with the actual logcial table name too.

Anyone see what I don't.

Appreciated.

[#4]

Hi Nicolas

I was actually trying to do something similar this weekend. What i was trying to do, was to load a spreadsheet with data and insert this into the sql-database, and the attached file is what i came up with.

It seems to work okay (I know...i need to change a few things on the delete procedure, so it is impossible to delete all rows 🙂 )

My script loops through all rows, but i guess it will lead you on the right track and pick the parts of the script that i relevant to you.

/Martin

[#5]

Cool. Thanks buddy, it's working. It. just. work. Really appreciated.

Now if I could get that darn DynamicUpdateCommand working too 🙂

[#6]

I have not tried this my selv, but I guess you could use the script attached. It seems quite similar to the script i sent you earlier with script for the SQLdb.

/Martin

[#7]

Well you know... I found that very QVW example in my desperate search for documentation on the subject a couple of days ago and had it working...

But weirdly back then, it was NOT working with my QVW...

Anyway, since it was suggested by your post... I just gave another try this morning...

And it seems like a spasm of genius hit me... since I found the darn little check-box option in the Document Properties (Server tab) that says: Enable Data Dynamic Update... heh gotta love check boxes... 😉

Btw Martin, you are one helpful fellow. Thanks man.

[#8]

Hi Nicolas i have the same problem... and at the and... i find the DARN little check Box

In Document Properties --> Server --> Enable Dinamic Data Update... a little check box in low sight side of the sheet

[#9]

Hi mkdid, 

I tested and succeed through using attached qvw file through ms explorer plugin mode but failed through chrome through external macro.

My question is that how can i update some date from qlikview to mssql dbms table  through external macro using vbscript.?

Do you have any ideas?