topic Write Back to database in QlikView

Write Back to database

Tue, 01 Sep 2009 21:55:51 GMT

I have searched the site and cant seem to find very much information on writing back to databases. There are a few posts but the attachments are not there and I cant reply. Does any one know where I can find documentation on macros for writing to databases and maybe some examples of how this can be accomplished?

Write Back to database

Tue, 01 Sep 2009 22:11:37 GMT

here's an example of a vbscript writing to sql server.

dim sServer, sConn, oConn,oRS
sServer="sqlservername"
sConn="provider=sqloledb;data source=" & sServer & ";initial catalog=logoninfo"
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn, "username", "secretpassword"
Set oRS =CreateObject("ADODB.Recordset")
sSQL="insert into logonoff(username,compname,ip,ontime) values('" & sUserName & "','" & sComputerName & "','" & sIP & "',getdate())"
ors.open sSQL, oconn

Write Back to database

Wed, 02 Sep 2009 03:34:42 GMT

Is Logonoff the table name? Also, what I want the user to be able to do is enter text into an input box and write that information to the database via ajax. Any ideas on how to make that work. The user is evaluating Agents and wants to document the actions to take as well as comments. I want that to be written out to a SQL database. Will that work with the code listed here. How can I create the input boxes. At this point, I can only have an in input box for system created variables.

I want to pass four data elements to the data base.

Agent

Comments

Actions

Year

Write Back to database

Fri, 04 Sep 2009 03:49:18 GMT

I was getting an error that I couldnt connect. everything is connecting fine and it appears to be working only it is not writing to the database. Am I missing anything here?

sub writeback ()
dim sServer, sConn, oConn,oRS
sServer="XXXXXXXXX"
sConn="provider=sqloledb;data source=" & sServer
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn, "XXXXXXXX", "XXXXXXX"
Set oRS =CreateObject("ADODB.Recordset")
sSQL="insert into TroyAgentReviews.dbo.AgentReviewNotes(AgentSub,Comments,Actions,Year) values('" & Subcode & "','" & Comments & "','" & Actions & "','2009')"
oRS.open sSQL, oConn
'Set oRS = oConn.Execute(sSQL)
end sub

Write Back to database

Fri, 04 Sep 2009 04:36:10 GMT

the line that does the actual writing is commented:

'Set oRS = oConn.Execute(sSQL)

Write Back to database

Thu, 10 Sep 2009 01:59:02 GMT

It writes with or without that particular line. I have been able to write to the database although, it only passes 2009 as the year. The other fields are blank. why isnt it passing the other data?

Write Back to database

Thu, 10 Sep 2009 03:05:23 GMT

your insert statement is hard-coded to always write 2009 in the year field

sSQL="insert into TroyAgentReviews.dbo.AgentReviewNotes(AgentSub,Comments,Actions,Year) values('" & Subcode & "','" & Comments & "','" & Actions & "','2009')" <----

Write Back to database

Thu, 10 Sep 2009 03:40:23 GMT

It is writing that as I have it hard-coded. It is not writing what is populated in the Subcode, Comments or Actions fields.

here is my sql table after I run the macro

Subcode Comments Actions Year

2009

I have one row of data in each of these fields and they are not being written to the database: Subcode, Comments, Actions. Do I need to use a set statement like

Set Subcode = ActiveDocument.Fields("Subcode").GetPossibleValues

Write Back to database

Thu, 10 Sep 2009 05:25:55 GMT


thoneycutt wrote:
It is writing that as I have it hard-coded. It is not writing what is populated in the Subcode, Comments or Actions fields.
here is my sql table after I run the macro
Subcode Comments Actions Year
 2009
I have one row of data in each of these fields and they are not being written to the database: Subcode, Comments, Actions. Do I need to use a set statement like
Set Subcode = ActiveDocument.Fields("Subcode").GetPossibleValues
?<div></div>

sorry, I misunderstood your question.

Yes, you are correct, you need to get the values from qV before you send them to SQL.

Write Back to database

sukydhak — Wed, 23 Sep 2009 22:20:21 GMT

Help

Just i'm also having the same issue.

sub writeback ()
dim sServer, sConn, oConn,oRS
sServer="localhost"
sConn="provider=sqloledb;data source=" & sServer
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn, "sa", ""
Set oRS =CreateObject("ADODB.Recordset")

set table = ActiveDocument.GetSheetObject( "TB01" )
For i = 0 to table.GetRowCount-1
set ID = table.GetCell(RowIter,0)
set Barcode = table.GetCell(RowIter,1)
set Partnercode = table.GetCell(RowIter,2)
msgbox i
msgbox Barcode
' sqlstr="insert into bis.dbo.Tbarcode_lookup (ID,Barcode,Partnercode) values('" & ID & "','" & Barcode & "','" & Partnercode & "')"
sqlstr="insert into bis.dbo.Tbarcode_lookup (ID,Barcode,Partnercode) values('1','5012345678','123')"
oRS.open sqlstr, oConn
Set oRS = oConn.Execute(sqlstr)
next

Error message Type mismatch
Msgbox i does provide me the record number but my msgbox barcode fails.

if i remove the message box for barcode, i can insert my one hardcoded value above. Any ideas would i'm doing wrong.

Write Back to database

Wed, 23 Sep 2009 22:27:17 GMT

does msgbox cstr(barcode) work?

Write Back to database

sukydhak — Wed, 23 Sep 2009 22:32:47 GMT

No i get Type mismatch: 'cstr'

Ps i don't now the ABC of VB

Write Back to database

Wed, 23 Sep 2009 22:38:27 GMT

I had to populate the fields before I could write them. this is What I ended up with.

sub writeback ()
dim sServer, sConn, oConn,oRS
dim sSubcodeSelected, sActionSelected, sCommentSelected
dim sSubcode, sComments, sActions
dim sSubcodemsg, sCommentsmsg, sActionsmsg

sServer="xxxxxxxxxx"
sConn="provider=sqloledb;data source=" & sServer
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn, "xxxxxxxxxx", "xxxxxxxx"
Set oRS =CreateObject("ADODB.Recordset")

' Get selected Value
set sSubcodeValues = ActiveDocument.Fields("Subcode").GetPossibleValues
Set sCommentsValues = ActiveDocument.Variables("Comments2")
Set sActionsValues = ActiveDocument.Variables("Actions2")

sSubcodemsg ="([Subcode]='"& sSubcodeValues.item(0).text & "')"
sSubcode = sSubcodeValues.item(0).text
sCommentsmsg = sCommentsValues.GetContent.String
sComments = sCommentsValues.GetContent.String
sActionsmsg = sActionsValues.GetContent.String
sActions = sActionsValues.GetContent.String

'msgbox sSubcodemsg
'msgbox sCommentsmsg
'msgbox sActionsmsg

sSQL="insert into TroyAgentReviews.dbo.AgentReviewNotes(AgentSub,Comments,Actions,Year) " _
& "values('" & sSubcode & "','" & sComments & "','" & sActions & "','2009')"
oRS.open(sSQL), oConn
oConn.close
Set oConn = nothing
end sub

Write Back to database

Wed, 23 Sep 2009 22:43:46 GMT

The attached adds comments or deletes comment to an excel file. This may or may not assist you.

Open the zip file and place the files in a folder named "C:\QlikViewExcel".

hth,

Stephen

Write Back to database

sukydhak — Wed, 23 Sep 2009 22:52:18 GMT

One step forward and i found another issue.

sub writeback ()
dim sServer, sConn, oConn,oRS
sServer="localhost"
sConn="provider=sqloledb;data source=" & sServer
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn, "sa", "q1w2zaxs"
Set oRS =CreateObject("ADODB.Recordset")

set table = ActiveDocument.GetSheetObject("TB01")
For RowIter = 0 to table.GetRowCount-1
'msgbox i & ": " & ID(i)
set ID = table.GetCell(RowIter,0)
'ActiveDocument.Fields("Barcode").GetPossibleValues
set Barcode = table.GetCell(RowIter,1)
set Partnercode = table.GetCell(RowIter,2)
' msgbox RowIter
' msgbox Barcode.Text
' out sqlstr
' wscript.echo ID
' sqlstr="insert into bis.dbo.Tbarcode_lookup (ID,Barcode,Partnercode) values('" & ID(i) & "','" & Barcode & "','" & Partnercode & "')"
sqlstr="insert into bis.dbo.Tbarcode_lookup (ID,Barcode,Partnercode) values('" & RowIter & "','" & Barcode.Text & "','" & Partnercode.Text & "')"
oRS.open sqlstr, oConn
Set oRS = oConn.Execute(sqlstr)
next

end sub

works now but it's writing 3 times the number of records.

Write Back to database

sukydhak — Wed, 23 Sep 2009 22:59:54 GMT

Guys

A quick thanks to all

The working script is below.

sub writeback ()
dim sServer, sConn, oConn,oRS
sServer="localhost"
sConn="provider=sqloledb;data source=" & sServer
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn, "sa", "q1w2zaxs"
Set oRS =CreateObject("ADODB.Recordset")

set table = ActiveDocument.GetSheetObject("TB01")
For RowIter = 0 to table.GetRowCount-1
set ID = table.GetCell(RowIter,0)
set Barcode = table.GetCell(RowIter,1)
set Partnercode = table.GetCell(RowIter,2)
' msgbox RowIter
' msgbox Barcode.Text
sqlstr="insert into bis.dbo.Tbarcode_lookup (ID,Barcode,Partnercode) values('" & RowIter & "','" & Barcode.Text & "','" & Partnercode.Text & "')"
oRS.open sqlstr, oConn
'Set oRS = oConn.Execute(sqlstr)
next

end sub

Note that my barcode and partnercode feilds are varchar and ID is a number.

Thanks once again.

Write Back to database

Mon, 12 Mar 2012 19:25:34 GMT

I have this script :

but I'm using SQL Server locally, using Windows authentication-with no username or password.

How should I alter my 'oConn.Open sConn, "sa", "khgkhgghg"', as mine should probably empty.

My script is :

sub writeback ()

dim sServer, sConn, oConn,oRS

sServer="localhost"

sConn="provider=sqloledb;data source=" & sServer

Set oConn = CreateObject("ADODB.Connection")

oConn.Open sConn, "sa", "khgkhgghg"

Set oRS =CreateObject("ADODB.Recordset")

set table = ActiveDocument.GetSheetObject("TB04")

For RowIter = 0 to table.GetRowCount-1

set RowID = table.GetCell(RowIter,0)

set Name = table.GetCell(RowIter,1)

set Grade = table.GetCell(RowIter,2)

'set Working = table.GetCell(RowIter,3)

'set [Date] = table.GetCell(RowIter,4)

'set Code = table.GetCell(RowIter,5)

' msgbox RowIter

' msgbox Barcode.Text

sqlstr="insert into Test.dbo.working (RowID,Name,Grade) values('" & RowID & "','" & Name.Text & "','" & Grade.Text & "')" ',Working,Date,Code

oRS.open sqlstr, oConn

'Set oRS = oConn.Execute(sqlstr)

end sub

Any ideas?