VB to SQL Stored Procedure

[Kostas]

How do I get an SQL Server 7.0 Stored Procedure to use variables from a VB form in Excel? I want the variables to be used in a query. The form opens in Excel and prompts the user for a start and end date. I designed the form in Excel's VB Editor.

Does the SQL statement go in the actual VB procedure and then passed to SQL Server somehow, or can I just pass the variables through from VB to the SQL query?

[Johnny101]

I assume you're using ADO to access the database. are you also using a Command object to run the stored proc? if so, use the parameters collection to pass in the variables. if you are using just a plain connection objects (and the execute method) to run the sproc, then build a string with the proc name followed by the variables' values and execute that.

hope this helps,

john

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org

[Kostas]

Actually thats what I'm trying to get to...whether or not I need to use an ADO, DLL, or API as a interface between my VB form and the SQL query. The only problem is that I only have the VB editor that comes with Excel and I cant set up any of those options with that.

What do you suggest and how should I go about finding out how to do this?

Got references?

[Johnny101]

Well, i'm pretty sure that you have references available in VBA (excel's VB environment), then you'd just set a reference to ADO 2.x (either 2.0, 2.1, 2.5 or 2.6, preferably anything after 2.1). then just setup a command object, populate the parameters collection, then call the execute method. the MSDN has som decent documentation on how to use this object.

here's an example of a commond ojbect's use:

dim cmd as ADODB.Command
dim rs as ADODB.Recordset

set cmd = new adodb.command

with cmd
.commandtype = adCmdStoredProc
.commandtimeout = 600 '10 minutes
.commandtext = "Your stored proc name"
.Parameters.Append .CreateParameter("@Param1", adInteger, adParamInput)
.Parameters.Append .CreateParameter("@param2", adVarChar, adParamInput, 50)
.Parameters("@Param1").Value = 5
.Parameters("@param2").Value = "johnny101"
end with

set rs = cmd.execute




if the stored proc was designed to return a recordset, the above could code would catch, if not, remove the set rs = from the cmd.execute.

hope this helps,

john

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org

[Kostas]

Thank you...that gave me some great headway into the problem. Now I just have to debug and set up a connection. I'm getting a run-time error 3709:
...reference to a closed or invalid Connection object.

Any clues?

[Johnny101]

Oops, i left off one major detail. the command object has a property called "ActiveConnection" which takes either an ADO connection object, or a valid ConnectionString value. set this before calling execute.

sorry,

john

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org

[Kostas]

I saw some code somewhere that set the ActiveConnection property to the DSN file. When I do that it does not work for me....

[coolbiz]

The ActiveConnection property should be set like this:


dim cmd as ADODB.Command
dim rs as ADODB.Recordset
dim cnn1 as ADODB.Connection ' connection object

' connect to your DB
cnn1.Open "Provider=sqloledb;Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=;"

set cmd = new adodb.command
set cmd.ActiveConnection = cnn1 ' set the command object to point to your opened connection

with cmd
.commandtype = adCmdStoredProc
.commandtimeout = 600 '10 minutes
.commandtext = "Your stored proc name"
.Parameters.Append .CreateParameter("@Param1", adInteger, adParamInput)
.Parameters.Append .CreateParameter("@param2", adVarChar, adParamInput, 50)
.Parameters("@Param1").Value = 5
.Parameters("@param2").Value = "johnny101"
end with

set rs = cmd.execute

' do the rest here
...

' close objects
rs.Close
cnn1.Close




I personally have not tested the additional code above but it should work.
-Cool Bizs

[Kostas]

Ok...thank you, what do I use in here:
Is the Data Source the Server name and the Initial Catalog the DB name?

"Provider=sqloledb;Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=;"



and here:

' do the rest here...

[coolbiz]

Yes, the connection string is for MS SQL server. Thus the Data Source points to your SERVER HOST NAME and Initial Catalog points to your DATABASE name.

-Cool Bizs

[Kostas]

New error: Run Time 91
"Object variable or With block variable not set"

I put the IP address of my server and the DB name in, along with my UID and pwd.

What could be the problem?

[coolbiz]

opps .. my bad ... right before the cnn1.Open() line, add set cnn1 = new ADODB.Connection

-Cool Bizs

[Kostas]

<laughs>
I knew it was something reasonable...

Now I'm just having a login problem that says I'm not associated with a trusted connection.

I'll ask my SA about the permissions. Maybe I can login using the sa with no pwd?!