topic Re: Database write-back on Accesspoint in QlikView

Database write-back on Accesspoint

Tue, 06 May 2014 14:06:24 GMT

All - I have a nice QVW which performs database writeback using macros and ADO. I have enabled System Access and made my macro permissions loose on the Server. My QVW works on Desktop but when you get to server it does not write back to the database or update anything. I click it on AJAX and it doesn't do squat. My macro is below:

Public Sub UpdateAllocation

Application.WaitForIdle

On Error Resume Next

Err.Clear

Set vErr = ActiveDocument.Variables("vMacroErr")

dim sConn, oConn, oCmd

Set oConn = CreateObject("ADODB.Connection")

Set oCmd = CreateObject("ADODB.Command")

'Parameters

Set pId = CreateObject("ADODB.Parameter")

Set pGlName = CreateObject("ADODB.Parameter")

' Capture input variable values

Set vHidId = ActiveDocument.Variables("vHidId")

Set vInpGlName = ActiveDocument.Variables("vInpGlName")

' Connect to database

sConn = "a string I know works on the server"

oConn.Open sConn

' Run command with parameters

oCmd.ActiveConnection = oConn

oCmd.CommandText = "UPDATE SomeTable SET GLNAME=? WHERE Id=?"

oCmd.CommandType = 1

oCmd.Prepared = TRUE

Set pId = oCmd.CreateParameter("Id", 3, 1, 4, vHidId.GetContent.string)

Set pGlName = oCmd.CreateParameter("GlName", 129, 1, 50, vInpGlName.GetContent.string)

oCmd.Parameters.Append pGlName

oCmd.Parameters.Append pId

oCmd.Execute

oConn.Close

Set oCmd = Nothing

Set oConn = Nothing

If Err.Number <> 0 Then

vErr.SetContent "Error in " & Err.Source & ": " & Err.Description, true

Else

vErr.SetContent "Successfully updated allocation: " & vHidId.GetContent.string, true

End If

ActiveDocument.Fields("Id").Clear

End Sub

Re: Database write-back on Accesspoint

jerrysvensson — Tue, 06 May 2014 14:13:22 GMT

Could be lots of things.

First:

Ajax macros run serverside. Do the QVS service account has access to the database?

Be aware of that all your users will that account when adding data to the database when you get it working.

I would use variables to debug the script in Ajax. Since you can't use MsgBox.

Re: Database write-back on Accesspoint

Tue, 06 May 2014 14:19:19 GMT

Jerry - Thanks for your response...Interesting thoughts, but I am:

1) Logging into the database in my connection string using a db uid/pass
2) Catching errors and writing to variable vErr and putting it on the page

Any other way to catch responses, etc?

Re: Database write-back on Accesspoint

jerrysvensson — Tue, 06 May 2014 14:24:01 GMT

Error handling in VBscript is very limited unfortunately.

Have you checked the "Allow unsafe macro execution on server " in the security tab in the management console?

Re: Database write-back on Accesspoint

Tue, 06 May 2014 14:31:14 GMT

Yep. Unlocked all things "safety related".

Re: Database write-back on Accesspoint

advait_thakur — Tue, 06 May 2014 14:45:04 GMT

Hello Matthew,

I have achieved this in QV version 9 with the help of macro and EDX triggers, my business users were able to writeback to database from access point. Macros are enemies here and as Jerry said VB scripts are really limited.

What version of QlikView are you using ?

By the way bWiseWriteBack extension is available now with Qlik and it will allow you to writeback to Database.

I am providing you the link and I believe it will be easy for you without macro. I will recommend you to utilize this tool.

bWise Writeback extension for Qlikview

I hope this is helpful and informative.

Regards

Advait

Re: Database write-back on Accesspoint

Tue, 06 May 2014 14:50:59 GMT

Actually ... adding some additional "write out to variable" indicates that the macro doesn't run at all.. I put a set variable statement at the very top and it does not get called. I confirmed that setting a variable can be done in another macro.

Re: Database write-back on Accesspoint

Tue, 06 May 2014 15:18:00 GMT

Figured it out. Had to remove: Application.WaitForIdle

Still not sure why I added that code or even needed it!

Re: Database write-back on Accesspoint

Mon, 02 Mar 2015 11:01:05 GMT

Hi Matthew,

I am trying to implement the write back functionality in my project.

I have to update the feedback provided by user once the given task is done.

Dashboard is pulling feedback data from excel (.xlsx) file and updating into the same file.

I have used your code and updated the connection string

I am using following connection string:

xlFile="F:\Others\Comments.xlsx"

connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & xlFile & ";Extended Properties='Excel 12.0 Xml;HDR=No';"

above code is giving me error "Provider cannot be found. It may not be properly installed."

Thanks in advance.