Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Writing to SQL DB / Stored Procedure from Macro

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

6 Replies
Not applicable
Author

Hi.

Check this URL may help.

Qlikview - Writing back to database

Anonymous
Not applicable
Author

Hi,

I cann't open the link!

Paul

Anonymous
Not applicable
Author

The link cannot be opened

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jerrysvensson
Partner - Specialist II
Partner - Specialist II

Can you use With in vbscript?

Missing definition of:  adCmdStoredProc,adVarChar,adParamInput

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

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

Parameters.Append .CreateParameter (...)

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein