dynamic update vbscript

[#1]

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                    

        next

    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?

[tanelry]

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 & "'"

[#3]

Again the set condition returns nothing the where condition is ok

[tanelry]

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 & " ....

[#5]

and how can I initialize TotalSales?

[tanelry]

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.

[#7]

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