topic Re: Writing to SQL DB / Stored Procedure from Macro in QlikView

Writing to SQL DB / Stored Procedure from Macro

Fri, 22 Nov 2013 11:57:01 GMT

HI,

Im trying to insert a text value into a SQL DB from by calling a macro.

My VBScript looks like :

sub insert_comments

dim sServer, sConn, oConn,oRS

sServer="myserver"

sConn="provider=sqloledb;data source=" & sServer & ";initial catalog=mydb"

Set oConn = CreateObject("ADODB.Connection")

Set cmd = CreateObject("ADODB.Command")

oConn.Open sConn, "username", "password"

msgbox ("here")

With cmd

.CommandType = adCmdStoredProc

.ActiveConnection = oConn

.CommandText = "insert_comments"

.Parameters.Append CreateParameter ("comment",adVarChar,adParamInput,1000,"stored procedure comment")

.Execute

End With

Set cmd = Nothing

end sub

My SQL Stored Procedure is :

[insert_comments]

declare

@comment nvarchar(1000)

Insert into qlikview_comments (comment)

Values (@comment)

The Msgbox pops up - so I know I'm calling the macro correctly. The macro fails on the .Parameters.Append line.

Can anyone help?

Tom

Re: Writing to SQL DB / Stored Procedure from Macro

Mon, 27 Jan 2014 18:52:06 GMT

Hi.

Check this URL may help.

Qlikview - Writing back to database

Re: Writing to SQL DB / Stored Procedure from Macro

Anonymous — Tue, 28 Jan 2014 11:05:14 GMT

Hi,

I cann't open the link!

Paul

Re: Writing to SQL DB / Stored Procedure from Macro

Anonymous — Tue, 28 Jan 2014 11:58:50 GMT

The link cannot be opened

Re: Writing to SQL DB / Stored Procedure from Macro

jonathandienst — Tue, 28 Jan 2014 12:16:18 GMT

Is CreateParameter() a function in your VBScript? Is suspect that it is a member of an ADO COM object. I don't have an ADO reference handy (and I am to rusty to remember - perhaps the command object or a ADO parameter object?

HTH

Jonathan

Re: Writing to SQL DB / Stored Procedure from Macro

jerrysvensson — Tue, 28 Jan 2014 12:17:55 GMT

Can you use With in vbscript?

Missing definition of: adCmdStoredProc,adVarChar,adParamInput

Re: Writing to SQL DB / Stored Procedure from Macro

jonathandienst — Tue, 28 Jan 2014 12:21:22 GMT

Quick google: its an ADO command method - you're just missing the period before CreateParameter

Parameters.Append .CreateParameter (...)

HTH

Jonathan