topic Inserting data into a database in QlikView

Inserting data into a database

Thu, 13 May 2010 13:28:56 GMT

Hy!

I would like to know, if there is a possibility to insert a new record (data) into existing database through some kind of a form in Qlikview Sheet (I am connected to the Access DB)?

So, I would like to create a form in Qlikview Sheet (with data fields in which I could enter desired values) and then when I would click on button insert, this record would be inserted into my database that I'm connected to through Qlickview...

Please help me, thanks!

Inserting data into a database

tseebach — Thu, 13 May 2010 13:39:25 GMT

In principle yes, but its not an recommendable solution.

Here are the basic steps:

Read out your data from the source, mark all the fields you want a user to be able to edit as inputfields. Add some empty lines by making an inline table. You need to figure out a way to do get the right ID's for the database.
Enter input fields values, and save these into a csv file.
Reload and read in the csv file, but only the added lines. Then count the records and loop them all while you execute SQL INSERTS against your source.
Reread the source, reset all input fields, add some new ones line in step 1.

I'd very much recommend, you to write a simple popup html widget that can be opened from qlikview and that has the input form you look for. From an architectural view I'd recommend that solution.

QlikView also has no good functions for validating input.

Inserting data into a database

sparur — Thu, 13 May 2010 13:40:26 GMT

Hello.

Yes it's possible. But it's not a usual task for QlikView. Qlikview it's a tool for analyzing information.

But if you want it, you can create a VBScript macro, in which create a connection to your database and insert new rows. How do it? You can to use ADO connection, for example. You find more details in VBScript documentation.

Inserting data into a database

Thu, 13 May 2010 13:44:12 GMT

I'm totally lost... How do I read out my data from data source, mark all the fields I want,... Where can I do that?

Inserting data into a database

prabhu0505 — Thu, 13 May 2010 13:44:39 GMT

Hi,

Combination of Input Box (for getting input), Button with Macro enabled (for executing query) and Reload statement(reloading for reflection)

I hope this will do for you.

Inserting data into a database

Thu, 13 May 2010 13:51:37 GMT


Saravana Prabhu wrote:
Hi,
 Combination of Input Box (for getting input), Button with Macro enabled (for executing query) and Reload statement(reloading for reflection)
 I hope this will do for you.
 <div></div>

This is the most likable solution. Do You have any small example maybe, like tutorial/guide... please?

Inserting data into a database

prabhu0505 — Fri, 14 May 2010 05:22:30 GMT

Sample Code:











set
 


 
set
 


 status1 = ActiveDocument.variables("Status") mySelections1 = doc.fields("Project_id").GetSelectedValues

sql_query =
"update Projects set Project_status='"&status1.getContent. String&"' where Project_id="&mySelections1.Item(0).Text

This code is for updating, cook it for insert.

Inserting data into a database

prabhu0505 — Fri, 14 May 2010 05:26:09 GMT

Sample Code:


set mySelections = doc.fields("[Project Name]").GetSelectedValues
set mySelections1 = doc.fields("Project_id").GetSelectedValues
sql_query = "update Projects set Project_status='"&status1.getContent.String&"' where Project_id="&mySelections1.Item(0).Text

This code is for update, cook it for insert.

Inserting data into a database

Fri, 14 May 2010 06:05:00 GMT

Where do I write this code? Thanks!

Inserting data into a database

Fri, 14 May 2010 06:13:17 GMT

What do I have to change in Your code? Project Name and Project:_ID? Everything else stay the same? mySelections and mySelections1 is right? What does Project_ID and Projects stands for?

Inserting data into a database

Fri, 14 May 2010 06:28:37 GMT

So, I have a project named 'Music'. I want to add new musician ('mname') into a table 'Musician' through the input box... Can You please help me? Do You need any other data? Thanks!!

Re: Inserting data into a database

baratiram — Wed, 19 Jun 2019 13:30:09 GMT

hi,

I am trying to insert a record into database on click of a button.

I tried to do using the macro inside the Button- actions with vb script. But I couldn't connect to the database. If you have any sample files please share.

sub writeback ()
dim sServer, sConn, oConn,oRS
sServer="servername"
Set oConn = CreateObject("ADODB.Connection")
oConn.open("provider=SQLOLEDB.1; Data Source='xxxxxx'; Initial catalog=xxxxxx; User ID='xxxxxxx'; password='xxxxxx'")
'Set oConn = CreateObject("ADODB.Connection")
'oConn.Open sConn, "xxxxxxxxxxx", "xxxx"
'Set oRS =CreateObject("ADODB.Recordset")
'sSQL="SELECT * FROM [xxxxx].[dbo].[table]"
'oRS.open sSQL, oConn
'Set oRS = oConn.Execute(sSQL)
end sub

but getting access denied or Login failed for user.

If you any other way to do so please let me know