How to open stored procedure w/param from control

  • Thread starter Thread starter Charlie
  • Start date Start date
C

Charlie

I have a form with buttons on it that I would like to open a stored procedure
with, easy enough, however I want to pass a parameter to the stored
procedure. The parameter is a value from a text box on the form. How/can
this be done?
 
Build an EXEC string to call your SP with the parameters; something like:

sqlString "EXEC MySP 1234, 5678, 'MyName', ...

or use the Command object and add your parameters to the Parameters
collection. There have been numerous examples on how to use the Command
object: search for "Parameters" with Google on this newsgroup. You can
either add the parameters explicitely or use the Refresh command:

Dim cmd as ADODB.Command
Set cmd = new ADODB.Command

Set cmd.Connection = CurrentProject.Connection

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "NameOfYourStoredProcedure"

Dim p As ADODB.Parameter
cmd.Parameters.Refresh

For Each p In cmd.Parameters
Debug.Print "name = " & p.name
Debug.Print "Direction = " & p.Direction
Debug.Print "Type = " & p.Type
Debug.Print "Size = " & p.Size
Debug.Print "Precision = " & p.Precision
Debug.Print "NumericScale = " & p.NumericScale
Debug.Print
Next

The file adovbs.inc (there is also a version for javascript) that you will
find on your machine contains the values for the Type but here's a
transcript:
http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=122

You can also use a typelib instead:
http://www.asp101.com/articles/john/typelibs/default.asp

Don't forget that the name of the parameters is not important but that their
*order* is. Also, if you have a return value in your SP then you must add a
parameter of
type adParamReturnValue and it must be the first of the list (*important*).

Finally, you can take a look at the m.p.data.ado newsgroup.
 
Ok, so what is wrong with this?

Dim strsql as string

strsql = "Exec [up_UniverNums]' " & (e-mail address removed)" ' "
 
Nothing wrong excerpt the location of the blank spaces.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Charlie said:
Ok, so what is wrong with this?

Dim strsql as string

strsql = "Exec [up_UniverNums]' " & (e-mail address removed)" ' "



Sylvain Lafontaine said:
Build an EXEC string to call your SP with the parameters; something like:

sqlString "EXEC MySP 1234, 5678, 'MyName', ...

or use the Command object and add your parameters to the Parameters
collection. There have been numerous examples on how to use the Command
object: search for "Parameters" with Google on this newsgroup. You can
either add the parameters explicitely or use the Refresh command:

Dim cmd as ADODB.Command
Set cmd = new ADODB.Command

Set cmd.Connection = CurrentProject.Connection

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "NameOfYourStoredProcedure"

Dim p As ADODB.Parameter
cmd.Parameters.Refresh

For Each p In cmd.Parameters
Debug.Print "name = " & p.name
Debug.Print "Direction = " & p.Direction
Debug.Print "Type = " & p.Type
Debug.Print "Size = " & p.Size
Debug.Print "Precision = " & p.Precision
Debug.Print "NumericScale = " & p.NumericScale
Debug.Print
Next

The file adovbs.inc (there is also a version for javascript) that you
will
find on your machine contains the values for the Type but here's a
transcript:
http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=122

You can also use a typelib instead:
http://www.asp101.com/articles/john/typelibs/default.asp

Don't forget that the name of the parameters is not important but that
their
*order* is. Also, if you have a return value in your SP then you must
add a
parameter of
type adParamReturnValue and it must be the first of the list
(*important*).

Finally, you can take a look at the m.p.data.ado newsgroup.
Click to expand...
Click to expand...
 
I added the spaces here to make it easier to read is all, they don't exist in
the code. However, I am getting an error: "Expected End of Statement"

Sylvain Lafontaine said:
Nothing wrong excerpt the location of the blank spaces.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Charlie said:
Ok, so what is wrong with this?

Dim strsql as string

strsql = "Exec [up_UniverNums]' " & (e-mail address removed)" ' "



Sylvain Lafontaine said:
Build an EXEC string to call your SP with the parameters; something like:

sqlString "EXEC MySP 1234, 5678, 'MyName', ...

or use the Command object and add your parameters to the Parameters
collection. There have been numerous examples on how to use the Command
object: search for "Parameters" with Google on this newsgroup. You can
either add the parameters explicitely or use the Refresh command:

Dim cmd as ADODB.Command
Set cmd = new ADODB.Command

Set cmd.Connection = CurrentProject.Connection

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "NameOfYourStoredProcedure"

Dim p As ADODB.Parameter
cmd.Parameters.Refresh

For Each p In cmd.Parameters
Debug.Print "name = " & p.name
Debug.Print "Direction = " & p.Direction
Debug.Print "Type = " & p.Type
Debug.Print "Size = " & p.Size
Debug.Print "Precision = " & p.Precision
Debug.Print "NumericScale = " & p.NumericScale
Debug.Print
Next

The file adovbs.inc (there is also a version for javascript) that you
will
find on your machine contains the values for the Type but here's a
transcript:
http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=122

You can also use a typelib instead:
http://www.asp101.com/articles/john/typelibs/default.asp

Don't forget that the name of the parameters is not important but that
their
*order* is. Also, if you have a return value in your SP then you must
add a
parameter of
type adParamReturnValue and it must be the first of the list
(*important*).

Finally, you can take a look at the m.p.data.ado newsgroup.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


I have a form with buttons on it that I would like to open a stored
procedure
with, easy enough, however I want to pass a parameter to the stored
procedure. The parameter is a value from a text box on the form.
How/can
this be done?
Click to expand...
Click to expand...
Click to expand...
 
Well, then display here the exact string that you get and how you are
executing it. Also, did you check the SP?

Also, the @ instead of a ! in the (e-mail address removed)" looks
suspicious.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Charlie said:
I added the spaces here to make it easier to read is all, they don't exist
in
the code. However, I am getting an error: "Expected End of Statement"

Sylvain Lafontaine said:
Nothing wrong excerpt the location of the blank spaces.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Charlie said:
Ok, so what is wrong with this?

Dim strsql as string

strsql = "Exec [up_UniverNums]' " & (e-mail address removed)" '
"



:

Build an EXEC string to call your SP with the parameters; something
like:

sqlString "EXEC MySP 1234, 5678, 'MyName', ...

or use the Command object and add your parameters to the Parameters
collection. There have been numerous examples on how to use the
Command
object: search for "Parameters" with Google on this newsgroup. You
can
either add the parameters explicitely or use the Refresh command:

Dim cmd as ADODB.Command
Set cmd = new ADODB.Command

Set cmd.Connection = CurrentProject.Connection

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "NameOfYourStoredProcedure"

Dim p As ADODB.Parameter
cmd.Parameters.Refresh

For Each p In cmd.Parameters
Debug.Print "name = " & p.name
Debug.Print "Direction = " & p.Direction
Debug.Print "Type = " & p.Type
Debug.Print "Size = " & p.Size
Debug.Print "Precision = " & p.Precision
Debug.Print "NumericScale = " & p.NumericScale
Debug.Print
Next

The file adovbs.inc (there is also a version for javascript) that you
will
find on your machine contains the values for the Type but here's a
transcript:
http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=122

You can also use a typelib instead:
http://www.asp101.com/articles/john/typelibs/default.asp

Don't forget that the name of the parameters is not important but that
their
*order* is. Also, if you have a return value in your SP then you must
add a
parameter of
type adParamReturnValue and it must be the first of the list
(*important*).

Finally, you can take a look at the m.p.data.ado newsgroup.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


I have a form with buttons on it that I would like to open a stored
procedure
with, easy enough, however I want to pass a parameter to the stored
procedure. The parameter is a value from a text box on the form.
How/can
this be done?
Click to expand...
Click to expand...
Click to expand...
 
Charlie, try this. You forgot the second '&', and I suspect that is why you
were getting the error.

strsql = "Exec [up_UniverNums] ' " & Forms!frmUniverNums.txtBeginDate & "' "
DoCmd.RunSql strsql

Also, if you want to use the text box to filter, you could put it on an
unbound main form, with a subform control containing a form whose record
source is your procedure. In the subform's properties, on the data tab,
enter @YourParameterName = forms!YourMainFormName!txtBeginDate. Then do a
requery of the sub after your DoCmd.RunSql. If you do not want a subform the
method would be simple for opening up a separate form. Once you set the
target form's input parameters setting, simply have the command button open
the form once you populate txtBeginDate. Look up
http://support.microsoft.com/kb/235359/EN-US/. I don't remember exactly, but
I think the article talks about using the autoform method to create a form.
You don't need to do that, just make the target form, and set is record
source to your procedure. I hope this helps you. I struggled with the same
thing around a year or so ago, and this article was the one that finally
worked for me.

Greg
Ok, so what is wrong with this?

Dim strsql as string

strsql = "Exec [up_UniverNums]' " & (e-mail address removed)" ' "
Build an EXEC string to call your SP with the parameters; something like:
Click to expand...
[quoted text clipped - 46 lines]
Click to expand...
 
Back
Top