topic Re: vbscript insert into qlikview from db in QlikView

vbscript insert into qlikview from db

Tue, 06 Mar 2012 12:03:26 GMT

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?

vbscript insert into qlikview from db

jagan — Tue, 06 Mar 2012 12:11:59 GMT

Hi,

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

Use

msgbox(query1);

Regards,

Jagan.

vbscript insert into qlikview from db

Tue, 06 Mar 2012 12:19:10 GMT

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

vbscript insert into qlikview from db

jagan — Tue, 06 Mar 2012 12:23:38 GMT

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.

vbscript insert into qlikview from db

Tue, 06 Mar 2012 12:30:19 GMT

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

Re: vbscript insert into qlikview from db

rajni_batra — Fri, 02 Nov 2012 08:11:56 GMT

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..:(