VBA to call SQL Stored procedure in Outlook form

[csudougie]

Hello,

I'm having trouble getting my vbscript to execute a SQL stored procedure over an ODBC connection from an Outlook form.

The s.p. only accepts one integer and returns a string value. Can anyone help me with the syntax or point me in a direction. I've looked into pass-through queries, but they want to run in VB and the funtions I've found don't like vbscript.

Thanks.

Option Explicit
dim rc, sql, cnn

sub OpenConn()
dim dsn
set cnn = CreateObject("ADODB.Connection")
dsn = "Provider=sqloledb;Data Source=MAJ_LINKED_TABLES;"
cnn.Open dsn
SQL = "{EXECUTE dbo.GET_CUS_NAME_fn(myvalue)}"
end sub

Sub cmdTest_Click()

dim objPage, objControl
dim s
dim MyValue
Set objPage = Item.GetInspector.ModifiedFormPages("Message")
Set objControl = objPage.Controls("txt_So")
MyValue = objControl.Value
OpenConn()
rc = cnn.Execute (SQL)
msgbox rc
end sub

 

[CautionMP]

csudougie,
I think it's a syntax issue, the way I read it your passing the the string '[tt]myvalue[/tt]' to the SP.

sub OpenConn()
dim dsn
set cnn = CreateObject("ADODB.Connection")
dsn = "Provider=sqloledb;Data Source=MAJ_LINKED_TABLES;"
cnn.Open dsn       
'[s][red]SQL = "{EXECUTE dbo.GET_CUS_NAME_fn(myvalue)}"[/red][/s]
end sub

Sub cmdTest_Click()

dim objPage, objControl
dim s
dim MyValue
Set objPage = Item.GetInspector.ModifiedFormPages("Message")
Set objControl = objPage.Controls("txt_So")
MyValue = objControl.Value
OpenConn()
rc = cnn.Execute([red]"{EXECUTE dbo.GET_CUS_NAME_fn[b](" & MyValue & ")[/b]}"[/red])
msgbox rc
end sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)