db connection

[brentmtc]

Trying to make a db connection (SQL Server 2000) and then populate a table (by executing a stored procedure) with data. I keep receiving an assortment of errors. Any suggestions to fix my code would be appreciated.

// Create db connection, insert values into generic table, and close db connection
VBSTART
Dim connstr
Dim MyDB
Dim rs

connstr= "Driver={OBDC MS SQL Server}; Data Source=***-***-***-*;Initial Catalog=db**;Persist Security Info=True;User ID=***;Password=****;"


Set MyDB = CreateObject("ADODB.Connection")
MyDB.Open connstr


rs = MyDB.Execute("uspScreenScrape_Test;")


VBEND

Thank you.

-brentmtc

[edauthier]

Is your code missing> VBRUN>?

I use DBCONNECT> when using Macro Scheduler:
Like so:
Let>connstr=Driver={SQL Native Client};Server=servername\sqlinstance; Database=SQL; Uid=user; Pwd=password;

DBConnect>connstr,dbH

[Marcus Tettmar]

"assortment of errors"? Please try being a little more specific.

[brentmtc]

I am now getting a connection to the db and able to insert some hard coded values(as in the commented out Let>) but I am not able to get values from the 'screen scrape' to be inserted using a stored procedure. Here is the code I have now.

Let>str=Driver={SQL SERVER};Server=***;Port=****;Database=***;User=***;Password=****;
DBConnect>str,dbH

//Let>SQL=Insert into tbGeneric_Patient_Info(LastName, FirstName) Select 'Harris', 'Sean'
Let>SQL=("uspScreenScrape_Test";)
DBExec>dbH,SQL,result

DBClose>dbH

Some of my 'screen scrape' values are %MRN%, %Suffix%,
%LastName%, etc.

Have any suggestions?

Thank you

-brentmtc

[edauthier]

Brent
I currently do not use MS for sprocs, but maybe I can assist you. Did you have any luck with this?

Ed

[brentmtc]

I have not got my macro to execute my stored procedures yet. Any suggestions?










Thanks,

-brentmtc

[edauthier]

adCmdStoredProc=4
cmd.CommandText = "uspScreenScrape_Test"
cmd.commandtype=adCmdStoredProc