Writing to SQL DB / Stored Procedure from Macro

[#1]

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

[#2]

Hi.

Check this URL may help.

Qlikview - Writing back to database

[Anonymous]

Hi,

I cann't open the link!

Paul

[Anonymous]

The link cannot be opened

[jonathandienst]

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

[jerrysvensson]

Can you use With in vbscript?

Missing definition of:  adCmdStoredProc,adVarChar,adParamInput

[jonathandienst]

Hi

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

Parameters.Append .CreateParameter (...)

HTH

Jonathan