arduk
asked on
Calling Stored Proc. with optional parameter
I have an Access 2000 ADP that is linked to an SQL server database. I am trying to execute a stored proc. that has parameters with default values specified from within VBA code.
I was under the impression that if I specify which parameters I am adding by using the ado command object, that I would not have to pass in all of the parameters, rather I could specify the name of the parameter, and its value, and it would be set. When I try to do this however, if I have 5 parameters, and set 3 of them using the createparameter, it is the first 3 that are set, not the named ones that I specify.
Do I have the wrong impression about how this works, or have I got a problem with my code??
I am using the commands as shown below...
stored proc defined as:
Create proc SP (@value1 int = 0, @value2 int = 1, @value3 int = 2)
VB CODE:
Set adoCmd = CreateObject("ADODB.Comman d")
adoCmd.CommandText = strProcName
adoCmd.CommandType = adCmdStoredProc
Set objNewParam = adoCmd.CreateParameter("va lue1", adinteger, adparaminput, , 6)
adoCmd.Parameters.Append objNewParam
Set objNewParam = adoCmd.CreateParameter("va lue3", adinteger, adparaminput, , 100)
adoCmd.Parameters.Append objNewParam
This appears to set the parameters @value1 and @value2, not @value1 and @value3 as desired...
Any help greatly appreciated...
Thanks
I was under the impression that if I specify which parameters I am adding by using the ado command object, that I would not have to pass in all of the parameters, rather I could specify the name of the parameter, and its value, and it would be set. When I try to do this however, if I have 5 parameters, and set 3 of them using the createparameter, it is the first 3 that are set, not the named ones that I specify.
Do I have the wrong impression about how this works, or have I got a problem with my code??
I am using the commands as shown below...
stored proc defined as:
Create proc SP (@value1 int = 0, @value2 int = 1, @value3 int = 2)
VB CODE:
Set adoCmd = CreateObject("ADODB.Comman
adoCmd.CommandText = strProcName
adoCmd.CommandType = adCmdStoredProc
Set objNewParam = adoCmd.CreateParameter("va
adoCmd.Parameters.Append objNewParam
Set objNewParam = adoCmd.CreateParameter("va
adoCmd.Parameters.Append objNewParam
This appears to set the parameters @value1 and @value2, not @value1 and @value3 as desired...
Any help greatly appreciated...
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can avoid this by getting rid of parameters completely. Create a string that contains your command, exactly as if you were exucuting it manually using ISQL or something similar...
dim sSSQL as string
sSQL = "exec PROC_NAME @value1=6, @value3=100"
Now just execute it using your connection object. Assuming that is called conDB....
conDB.execute sSQL
That should save you the access time, and more importantly gives you a SQL string that you can debug easily and execute directly against the database if there are problems, rather than figuring out what might (or might not) be held in the parameters.
dim sSSQL as string
sSQL = "exec PROC_NAME @value1=6, @value3=100"
Now just execute it using your connection object. Assuming that is called conDB....
conDB.execute sSQL
That should save you the access time, and more importantly gives you a SQL string that you can debug easily and execute directly against the database if there are problems, rather than figuring out what might (or might not) be held in the parameters.
ASKER
That is how I used to execute stored procs, however someone suggested that this was not the most efficient way of doing it?
Using the addparameter method also gives you access to any output parameters there may be as well...
Using the addparameter method also gives you access to any output parameters there may be as well...
There's no difference in the two execution methods, other than the ability to use output parameters (as you say).
I've been supporting and coding this type of connection for over five years in Investment banks now and the worst thing to find when something goes wrong in someones code is parameters. Even output paramaters can easily be simulated by just returning the data as a resultset instead and at 3am I'd much rather have a simple SQL string that I can run manually through ISQL than a set of parameters that I have to decode before even being able to test the sql directly.
Everyone has different opinions on this but most of the people I've spoken to on the sharp end agree with me...
I think that either DialM4Monkey's solution or mine should be enough to answer your question so can you assign the points or post what extra info you need?
Cheers,
Simon
I've been supporting and coding this type of connection for over five years in Investment banks now and the worst thing to find when something goes wrong in someones code is parameters. Even output paramaters can easily be simulated by just returning the data as a resultset instead and at 3am I'd much rather have a simple SQL string that I can run manually through ISQL than a set of parameters that I have to decode before even being able to test the sql directly.
Everyone has different opinions on this but most of the people I've spoken to on the sharp end agree with me...
I think that either DialM4Monkey's solution or mine should be enough to answer your question so can you assign the points or post what extra info you need?
Cheers,
Simon
ASKER
adocmd.parameters("@Value1
adocmd.parameters("@Value3
Using this method, the command object queries the server to find out the parameters. downside is an extra round trip to the server, so is a bit slower than using the append method I was initially using :(
Thanks for your comments anyway