topic dynamic update vbscript in QlikView

dynamic update vbscript

Mon, 05 Mar 2012 13:19:15 GMT

Hi everyone

I'm a newbie in Qlikview and vbscript and I have a problem..... I have a database connection to my qv with one table with columns CustomerId and TotalSales. I want to create a button to dynamic update the qv document from my database.

my code is here :

sub PullData

dim sServer, sConn, oConn,oRS

sServer="ITMC-DDI\SQLEXPRESS2008"

sConn="Provider=SQLNCLI10;Initial Catalog=DynamicUpdate;Data Source=ITMC-DDI\SQLEXPRESS2008"

Set oConn = CreateObject("ADODB.Connection")

oConn.Open sConn, "sa", "12345!a"

Set oRS =CreateObject("ADODB.Recordset")

sSQL="Select TotalSales from Customer"

oRS.Open sSQL, oconn

set possibleValues = ActiveDocument.Fields("CustomerId").GetPossibleValues

set pTotalSales=ActiveDocument.Fields("TotalSales").GetPossibleValues

NoSelected=possibleValues.Count

if (NoSelected>0) then

set curDoc = ActiveDocument.GetProperties

curDoc.EnableDynamicDataUpdate=true

ActiveDocument.SetProperties curDoc

for i=0 to NoSelected-1

query="Update Customer Set TotalSales=' " & TotalSales & " ' where CustomerId="& possibleValues.item(i).text

set sqlerror=ActiveDocument.DynamicUpdateCommand(query)

if (sqlerror = false) then

MsgBox sqlerror.ErrorMessage

exit sub

end if

ActiveDocument.Save

end sub

my problem is in sql query> I believe I do not pull TotalSales from db. So I believe the error is in SET condition of the query

Could anyone help me?

dynamic update vbscript

tanelry — Mon, 05 Mar 2012 14:09:44 GMT

The "query=" line could be a problem, there is no single quotes around the CustomerId value. Try:

query="Update Customer Set TotalSales=' " & TotalSales & " ' where CustomerId='" & possibleValues.item(i).text & "'"

dynamic update vbscript

Mon, 05 Mar 2012 14:20:12 GMT

Again the set condition returns nothing the where condition is ok

Re: dynamic update vbscript

tanelry — Mon, 05 Mar 2012 14:52:30 GMT

I think the variable TotalSales is not properly initialized at this point, thus not passing any value to the query

query="Update Customer Set TotalSales=' " & TotalSales & " ....

dynamic update vbscript

Mon, 05 Mar 2012 15:07:53 GMT

and how can I initialize TotalSales?

dynamic update vbscript

tanelry — Mon, 05 Mar 2012 16:03:07 GMT

The problem is that In your example you open the sql recordset ("Select TotalSales from Customer") but you don't pass any values from there into the "query" string.

vTotalSales = ... (initialzing variable and need to get sql result here)

query="Update Customer Set TotalSales=' " & vTotalSales & " .... (using the vTotalSales value in the string)

BTW, have you considered Partial Reload in script instead of DynamicDataUpdate? It might get you same result, being much more simple.

Re: dynamic update vbscript

Tue, 07 Jan 2014 15:51:42 GMT

Hi sisco3110

Try using error messages like msgbox(recordset.recordcount) ADODB Recordsets have lots of properties that can help you diagnose pulling the proper dataset. I had a similar problem. I changed the provider in the connection string to SQLOLEDB and solved my problem.

Frank