vbscript insert into qlikview from db

[#1]

I have a problem with my vbscript.

I want to create a button to make dynamic update. my code is this

sub PullData

dim sServer, sConn, oConn,oRS,counter

sServer="Ixxxxxxx"

sConn="Provider=SQLNCLI10;Initial Catalog=DynamicUpdate;Data Source=Ixxxxxxxxx"

Set oConn = CreateObject("ADODB.Connection")

oConn.Open sConn, "xxxxx", "xxxxxxx"

Set oRS =CreateObject("ADODB.Recordset")

sSQL = "select TotalSales from Customer"

set oRS = oConn.Execute(sSQL)

while not oRS.EOF

counter=counter+1

value = oRS("TotalSales")

'msgbox(value)

'msgbox(counter)

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

                    NoSelected=possibleValues.Count

                                           msgbox(counter)

                                           msgbox(NoSelected)

                                 if counter<=NoSelected then

            query="UPDATE Customer SET TotalSales='"&value & "'  WHERE CustomerId='"&counter & "'"  

            msgbox(query)

            set sqlerror=ActiveDocument.DynamicUpdateCommand(query)

             if (sqlerror = false) then                     

                   MsgBox sqlerror.ErrorMessage

                   exit sub

            end if                    

        else

        set curDoc = ActiveDocument.GetProperties

        curDoc.EnableDynamicDataUpdate=true  

        ActiveDocument.SetProperties curDoc

        msgbox("Insert")

        query1="INSERT INTO Customer VALUES ('"&counter & "','"&value & "')"

   end if

     oRS.movenext

  wend

Set oRS = Nothing

Set oConn = nothing

'oRS.Close

'oConn.Close

ActiveDocument.Save

end sub

My update query works fine but the insert query does nothing. I check the else  statement with msgbox("Insert") and it walk through of it but the insert query makes nothing.

Has anyone any idea?

[jagan]

Hi,

Get the Insert query generated and try manually in database to check whether it is working. 

Use

msgbox(query1);

Regards,

Jagan.

[#3]

Thanks for the response but I don't understand what you mean.Please could you give me an example?

[jagan · Accepted solution]

Hi,

Try this script, hope you missed the code in Bold below

sub PullData

dim sServer, sConn, oConn,oRS,counter

sServer="Ixxxxxxx"

sConn="Provider=SQLNCLI10;Initial Catalog=DynamicUpdate;Data Source=Ixxxxxxxxx"

Set oConn = CreateObject("ADODB.Connection")

oConn.Open sConn, "xxxxx", "xxxxxxx"

Set oRS =CreateObject("ADODB.Recordset")

sSQL = "select TotalSales from Customer"

set oRS = oConn.Execute(sSQL)

while not oRS.EOF

counter=counter+1

value = oRS("TotalSales")

'msgbox(value)

'msgbox(counter)

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

                    NoSelected=possibleValues.Count

                                           msgbox(counter)

                                           msgbox(NoSelected)

                                 if counter<=NoSelected then

            query="UPDATE Customer SET TotalSales='"&value & "'  WHERE CustomerId='"&counter & "'"  

            msgbox(query)

            set sqlerror=ActiveDocument.DynamicUpdateCommand(query)

             if (sqlerror = false) then                     

                   MsgBox sqlerror.ErrorMessage

                   exit sub

            end if                    

        else

        set curDoc = ActiveDocument.GetProperties

        curDoc.EnableDynamicDataUpdate=true  

        ActiveDocument.SetProperties curDoc

        msgbox("Insert")

        query1="INSERT INTO Customer VALUES ('"&counter & "','"&value & "')"

          msgbox(query1)

     set sqlerror=ActiveDocument.DynamicUpdateCommand(query1)

             if (sqlerror = false) then                     

                   MsgBox sqlerror.ErrorMessage

                   exit sub

            end if       

   end if

     oRS.movenext

  wend

Set oRS = Nothing

Set oConn = nothing

'oRS.Close

'oConn.Close

ActiveDocument.Save

end sub

Regards,

Jagan.

[#5]

Thnx Jagan that works perfect......thnx a lot

[rajni_batra]

Hello jagan,

I have a query, need to update data from QV to SQl... i dont know anything about this.. how should i start this..

Please help..:(