Printable View
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?
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
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?
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
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?
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
I saw some code somewhere that set the ActiveConnection property to the DSN file. When I do that it does not work for me....
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
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...
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
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?
opps .. my bad ... right before the cnn1.Open() line, add set cnn1 = new ADODB.Connection
-Cool Bizs
<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?!