topic Re: Call a Stored Proc behind a button in QlikView

Call a Stored Proc behind a button

Thu, 17 Oct 2013 00:22:08 GMT

Hi Guys,

I am working on a new QlikView requirement where I am working to make QlikView Report/Dashboard more intuitive by making some transactional control on it.

I have a requirement where I want to call a stored procedure by a Button on QlikView.

So when I click the button, I want to run the following command EXEC sp_xx_yy

Please advice

Regards

Naveen

Re: Call a Stored Proc behind a button

vikasmahajan — Thu, 17 Oct 2013 05:27:10 GMT

pfa

Vikas

Re: Call a Stored Proc behind a button

Thu, 17 Oct 2013 18:21:15 GMT

Thanks for Sharing this Vikas. However, the QVW attached doesn't have a button.

I want the Stoerd Proc to be called when the button is clicked.

Regards

Naveen

Re: Call a Stored Proc behind a button

Thu, 17 Oct 2013 19:19:32 GMT

In a macro use this :

For a query that won't return results:

DoCmd.RunSQL

For one that will:

Dim dBase As Database dBase.OpenRecordset

Syntax

expression.OpenRecordset(Name, Type, Options, LockEdit) , only use the "Name"

Name

Required

String

The source of the records for the new Recordset. The source can be a table name, a query name, or an SQL statement that returns records. For table-typeRecordset objects in Microsoft Access database engine databases, the source can only be a table name.

Hope this helps

Regards!

Re: Call a Stored Proc behind a button

Clever_Anjos — Thu, 17 Oct 2013 19:32:34 GMT

What do you want to do with proc results? Refresh your data?

Re: Call a Stored Proc behind a button

Thu, 17 Oct 2013 19:35:31 GMT

Hi Clever,

No - I will just set a value on the database with this stored procedure. I don't want to refresh any data or bring this data to the front.

Cheers

Naveen

Re: Call a Stored Proc behind a button

Thu, 17 Oct 2013 19:36:00 GMT

Rau`l,

Thanks - I'll check this and get back to you.

Regards

Naveen

Re: Call a Stored Proc behind a button

Thu, 17 Oct 2013 19:37:22 GMT

Update

I am thinking of the following approach :

1) Create a Button in Qlikview

2) Add a action on the button (External > Macro)

3) Write a VB program on on that Macro and call the SQL Stored Proc

I am not sure on what impact this could have on the performance etc, but I'll try this and keep you posted

Regards

Naveen

Re: Call a Stored Proc behind a button

Thu, 17 Oct 2013 19:40:55 GMT

Yep, thats the point off my recently post, maybe you need to use the "System Acces Mode" in the Macro , and let "Alloe System Acces". Good luck!

Re: Re: Call a Stored Proc behind a button

Clever_Anjos — Thu, 17 Oct 2013 19:43:04 GMT

Naveen Vyas, set the direction, you can execute using macros (vbScript)

you macro code should be like this

sp = "YOUR STORED PROCEDURE"

Set cmd = CreateObject("ADODB.Command")
With cmd
 .ActiveConnection = "YOUR CONNECTION STRING"
 .CommandType = 4
 .CommandText = sp
 .Execute
End With
Set cmd = Nothing

Re: Call a Stored Proc behind a button

Thu, 17 Oct 2013 19:49:50 GMT

Clever Anjos - Thanks for this.

But, where am I calling the Stoerd Procedure here ?

I mean the "EXEC sp" command ?

I also need to pass a few variables with this Stored Procedure, But I'll handle the variables separately.

Re: Re: Call a Stored Proc behind a button

Clever_Anjos — Thu, 17 Oct 2013 19:58:23 GMT

It executes on line 08 .Execute

If you need to suply parameters use as this

sp = "YOUR STORED PROCEDURE" 

Set cmd = CreateObject("ADODB.Command") 
With cmd 
  .ActiveConnection = "YOUR CONNECTION STRING" 
  .CommandType = 4 
  .Parameters.Append .CreateParameter("@param1", adVarWChar, adParamInput, 50, "a value")
  .Parameters.Append .CreateParameter("@param2", adVarWChar, adParamInput, 50, "another value")
  .CommandText = sp 
  .Execute ' this will execute your proc
End With 
Set cmd = Nothing

Re: Re: Call a Stored Proc behind a button

Clever_Anjos — Thu, 17 Oct 2013 19:58:52 GMT

Good remembering

Re: Call a Stored Proc behind a button

chetanpatel — Wed, 19 Oct 2022 04:23:25 GMT

what i am doing wrong here

SUB insert

Set cmd = CreateObject("ADODB.Command")
With cmd
.ActiveConnection = "Provider=sqloledb;Data Source=servername;Initial Catalog=DB"
.CommandType = 4
.CommandText = "INSERT INTO [DB].[Utility].[QlikviewLoadInfo] ([QlikAppNM],[RunDT]) VALUES ('Test', '2022-10-19')"
.Execute
End With
Set cmd = Nothing
End Sub