Addressing Multiple Proc Parameters

**Hans-Joerg Karpenstein** · Nov 12 '05, 10:18 PM

Re: Addressing Multiple Proc Parameters

Am Tue, 13 Apr 2004 04:46:29 -0700 schrieb CSDunn:
[color=blue]
> Hello,
>
> Private Sub FirstCombo_Afte rUpdate()
>
> Me.SecondCombo. RowSource = "EXEC dbo.ADStudentCo mbo_sp " &
> Me.FirstCombo
>
> End Sub
>
> The above code sets the RowSource of the SecondCombo to the result of
> the execution of dbo.ADStudentCo mbo_sp and the parameter sent to the
> proc from the bound column of FirstCombo.
>
> In my current situation, I just need to execute a proc and send three
> parameters to the proc during the OnClick event. I started to try the
> following:
>
> Private Sub Completed_Click ()
> If Me.Completed = 1 Then
> "Exec dbo.ADTestProce ssEvaluator_sp " & Me.Permnum & Me.TestGrade
> & Me.TestShortNam e
> End If
> End Sub
>
> The actual parameters in the proc are as follows:
> @Permnum varchar (12),
> @TestGrade smallInt,
> @TestShortName nvarchar (8)
>
> How can I properly address the proc and its parameters during the
> OnClick event of the ‘Completed' Option Group?
>
> Thank you for your help!
>
> CSDunn[/color]

Hello,

I don't have the code right now,
but to do this I would use the Command-Object.
With this you can pass parameters to the procedure and
retrieve parameters from a procedure.

HTH
Karpi
<fluctuat nec mergitur>

**MGFoster** · Nov 12 '05, 10:18 PM

Re: Addressing Multiple Proc Parameters

CSDunn wrote:
[color=blue]
> Hello,
> I have a situation in which I need to address three SQL Server 2000
> Stored Procedure parameters in the OnClick event of an Option Group.
> The Option Group lives on an Access 2000 ADP form.
>
> In another situation where I had to set the RowSource of a combo box
> based on a parameter value that was delivered to a proc from another
> combo box, I did this:
>
> Private Sub FirstCombo_Afte rUpdate()
>
> Me.SecondCombo. RowSource = "EXEC dbo.ADStudentCo mbo_sp " &
> Me.FirstCombo
>
> End Sub
>
> The above code sets the RowSource of the SecondCombo to the result of
> the execution of dbo.ADStudentCo mbo_sp and the parameter sent to the
> proc from the bound column of FirstCombo.
>
> In my current situation, I just need to execute a proc and send three
> parameters to the proc during the OnClick event. I started to try the
> following:
>
> Private Sub Completed_Click ()
> If Me.Completed = 1 Then
> "Exec dbo.ADTestProce ssEvaluator_sp " & Me.Permnum & Me.TestGrade
> & Me.TestShortNam e
> End If
> End Sub
>
> The actual parameters in the proc are as follows:
> @Permnum varchar (12),
> @TestGrade smallInt,
> @TestShortName nvarchar (8)
>
> How can I properly address the proc and its parameters during the
> OnClick event of the ‘Completed' Option Group?[/color]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need commas separating the parameters and you could use quotes
around the varchar parameters, in case they contain blanks.

If you just need to run the SP, use the Execute method of the Connection
object. E.g.:

dim cn as new adodb.connectio n
dim strSQL as string

strSQL = "Exec ADTestProcessEv aluator_sp '" & _
Me.Permnum & "'," & Me.TestGrade & _
",'" & Me.TestShortNam e & "'"

cn.open CurrentProject. Connection
cn.execute strSQL, , adCmdStoredProc

If you need to get a recordset back from the SP:

dim cn as new adodb.connectio n
dim rs as adodb.recordset
dim strSQL as string
dim lngRecords as long

strSQL = "Exec ADTestProcessEv aluator_sp '" & _
Me.Permnum & "'," & Me.TestGrade & _
",'" & Me.TestShortNam e & "'"

cn.open CurrentProject. Connection
set rs = cn.execute(strS QL, lngRecords, adCmdStoredProc )

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQHwq7oechKq OuFEgEQItuACg7s gDJghMv6nEzyQTh oW9mhnmMTwAoOjw
OFywcVNzjXWlcOG en2NTZ20H
=TSKM
-----END PGP SIGNATURE-----

**Chris Dunn** · Nov 12 '05, 10:18 PM

Re: Addressing Multiple Proc Parameters

Thank you. Could you please provide some sample code when you have a
chance?

CSDunn

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

**CSDunn** · Nov 12 '05, 10:19 PM

Re: Addressing Multiple Proc Parameters

MGFoster,
Thank you for your help!

CSDunn

MGFoster <me@privacy.com > wrote in message news:<RVVec.817 7$A_4.4342@news read1.news.pas. earthlink.net>. ..[color=blue]
> CSDunn wrote:
>[color=green]
> > Hello,
> > I have a situation in which I need to address three SQL Server 2000
> > Stored Procedure parameters in the OnClick event of an Option Group.
> > The Option Group lives on an Access 2000 ADP form.
> >
> > In another situation where I had to set the RowSource of a combo box
> > based on a parameter value that was delivered to a proc from another
> > combo box, I did this:
> >
> > Private Sub FirstCombo_Afte rUpdate()
> >
> > Me.SecondCombo. RowSource = "EXEC dbo.ADStudentCo mbo_sp " &
> > Me.FirstCombo
> >
> > End Sub
> >
> > The above code sets the RowSource of the SecondCombo to the result of
> > the execution of dbo.ADStudentCo mbo_sp and the parameter sent to the
> > proc from the bound column of FirstCombo.
> >
> > In my current situation, I just need to execute a proc and send three
> > parameters to the proc during the OnClick event. I started to try the
> > following:
> >
> > Private Sub Completed_Click ()
> > If Me.Completed = 1 Then
> > "Exec dbo.ADTestProce ssEvaluator_sp " & Me.Permnum & Me.TestGrade
> > & Me.TestShortNam e
> > End If
> > End Sub
> >
> > The actual parameters in the proc are as follows:
> > @Permnum varchar (12),
> > @TestGrade smallInt,
> > @TestShortName nvarchar (8)
> >
> > How can I properly address the proc and its parameters during the
> > OnClick event of the ‘Completed' Option Group?[/color]
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> You need commas separating the parameters and you could use quotes
> around the varchar parameters, in case they contain blanks.
>
> If you just need to run the SP, use the Execute method of the Connection
> object. E.g.:
>
> dim cn as new adodb.connectio n
> dim strSQL as string
>
> strSQL = "Exec ADTestProcessEv aluator_sp '" & _
> Me.Permnum & "'," & Me.TestGrade & _
> ",'" & Me.TestShortNam e & "'"
>
> cn.open CurrentProject. Connection
> cn.execute strSQL, , adCmdStoredProc
>
> If you need to get a recordset back from the SP:
>
> dim cn as new adodb.connectio n
> dim rs as adodb.recordset
> dim strSQL as string
> dim lngRecords as long
>
> strSQL = "Exec ADTestProcessEv aluator_sp '" & _
> Me.Permnum & "'," & Me.TestGrade & _
> ",'" & Me.TestShortNam e & "'"
>
> cn.open CurrentProject. Connection
> set rs = cn.execute(strS QL, lngRecords, adCmdStoredProc )
>
> --
> MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
>
> iQA/AwUBQHwq7oechKq OuFEgEQItuACg7s gDJghMv6nEzyQTh oW9mhnmMTwAoOjw
> OFywcVNzjXWlcOG en2NTZ20H
> =TSKM
> -----END PGP SIGNATURE-----[/color]

**CSDunn** · Nov 12 '05, 10:20 PM

Re: Addressing Multiple Proc Parameters

MGFoster,
I attempted to incorporate your code suggestion for executing the
proc, as follows:

Private Sub Completed_Click ()
DoCmd.RunComman d acCmdSaveRecord
Dim ctlCurrent As Control
If Me.Completed = 1 Then
'************** *************
Dim cn As New adodb.Connectio n
Dim strSQL As String

strSQL = "Exec ADTestProcessEv aluator_sp '" & _
Me.Permnum & "'," & Me.TestGrade & _
",'" & Me.TestShortNam e & "'"

cn.Open CurrentProject. Connection
cn.Execute strSQL, , adCmdStoredProc

'Lock the Combo Boxes in the Detail Section
For Each ctlCurrent In Me.Detail.Contr ols
If ctlCurrent.Cont rolType = acComboBox Then
ctlCurrent.Lock ed = True
End If
Next ctlCurrent
'************** **************
ElseIf Me.Completed = 0 Then
'Unlock the Combo Boxes in the Detail Section
For Each ctlCurrent In Me.Detail.Contr ols
If ctlCurrent.Cont rolType = acComboBox Then
ctlCurrent.Lock ed = False
End If
Next ctlCurrent

End If
End Sub

This code is implemented in the OnClick event of an Option Group. The
option group is build on a field called 'Completed'. When one of two
options in the group is clicked, the record is saved, and the OnClick
event evaluates the field 'Completed' for zero or one. If 'Completed'
= 1, then the proc is executed with parameters, and the combo boxes in
the detail section are locked. If 'Completed' = 0, then the combo
boxes are unlocked.

When I tested the OnClick event of the option group, I received an
error that expressed a 'Syntax error or access violation' Run-time
error at the following line:

cn.Execute strSQL, , adCmdStoredProc

I checked my permissions to the proc in SQL Server, and everything
looks okay. What else could be causing the error?

Thanks again!

CSDunn

MGFoster <me@privacy.com > wrote in message news:<RVVec.817 7$A_4.4342@news read1.news.pas. earthlink.net>. ..[color=blue]
> CSDunn wrote:
>[color=green]
> > Hello,
> > I have a situation in which I need to address three SQL Server 2000
> > Stored Procedure parameters in the OnClick event of an Option Group.
> > The Option Group lives on an Access 2000 ADP form.
> >
> > In another situation where I had to set the RowSource of a combo box
> > based on a parameter value that was delivered to a proc from another
> > combo box, I did this:
> >
> > Private Sub FirstCombo_Afte rUpdate()
> >
> > Me.SecondCombo. RowSource = "EXEC dbo.ADStudentCo mbo_sp " &
> > Me.FirstCombo
> >
> > End Sub
> >
> > The above code sets the RowSource of the SecondCombo to the result of
> > the execution of dbo.ADStudentCo mbo_sp and the parameter sent to the
> > proc from the bound column of FirstCombo.
> >
> > In my current situation, I just need to execute a proc and send three
> > parameters to the proc during the OnClick event. I started to try the
> > following:
> >
> > Private Sub Completed_Click ()
> > If Me.Completed = 1 Then
> > "Exec dbo.ADTestProce ssEvaluator_sp " & Me.Permnum & Me.TestGrade
> > & Me.TestShortNam e
> > End If
> > End Sub
> >
> > The actual parameters in the proc are as follows:
> > @Permnum varchar (12),
> > @TestGrade smallInt,
> > @TestShortName nvarchar (8)
> >
> > How can I properly address the proc and its parameters during the
> > OnClick event of the ‘Completed' Option Group?[/color]
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> You need commas separating the parameters and you could use quotes
> around the varchar parameters, in case they contain blanks.
>
> If you just need to run the SP, use the Execute method of the Connection
> object. E.g.:
>
> dim cn as new adodb.connectio n
> dim strSQL as string
>
> strSQL = "Exec ADTestProcessEv aluator_sp '" & _
> Me.Permnum & "'," & Me.TestGrade & _
> ",'" & Me.TestShortNam e & "'"
>
> cn.open CurrentProject. Connection
> cn.execute strSQL, , adCmdStoredProc
>
> If you need to get a recordset back from the SP:
>
> dim cn as new adodb.connectio n
> dim rs as adodb.recordset
> dim strSQL as string
> dim lngRecords as long
>
> strSQL = "Exec ADTestProcessEv aluator_sp '" & _
> Me.Permnum & "'," & Me.TestGrade & _
> ",'" & Me.TestShortNam e & "'"
>
> cn.open CurrentProject. Connection
> set rs = cn.execute(strS QL, lngRecords, adCmdStoredProc )
>
> --
> MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
>
> iQA/AwUBQHwq7oechKq OuFEgEQItuACg7s gDJghMv6nEzyQTh oW9mhnmMTwAoOjw
> OFywcVNzjXWlcOG en2NTZ20H
> =TSKM
> -----END PGP SIGNATURE-----[/color]

**Rich P** · Nov 12 '05, 10:20 PM

Re: Addressing Multiple Proc Parameters

I'm not exactly following what you are trying to accomplish here, but it
sound like you want to set the rowsource of a combobox based on some
parameters sent to an sp in sql server. If this is the case then I
would retrieve the dataset from the sqlserver sp and write it to a table
in Access that the combobox rowsource is based on. You can have a
standalone sub in Access that you call from your option group. I
presume the option group is where the params are set. Here is code for
the sub:

Call this sub from your option group where you set the param values for
var1, var2, var3:

Sub RunSP(var1 As String, var2 As Integer, var3 As String)
Dim cmd As New ADODB.Command
Dim RSado As New ADODB.Recordset
Dim RSdao As DAO.Recordset

cmd.ActiveConne ction = "Provider=SQLOL EDB;Data Source=yourSqlS erver;" _
& "Initial Catalog=yourSql DB;UID=SA;PWD=t iger;"
cmd.CommandTime out = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "yourSqlstoredP roc"
cmd.Parameters( "@Permnum").Val ue = var1
cmd.Parameters( "@TestGrade").V alue = var2
cmd.Parameters( "@TestShortName ").Value = var3
Set RSado = cmd.Execute
Set RSdao = CurrentDb.OpenR ecordset("tblcb oRowsource")
DoEvents
Do While Not RSado.EOF
RSdao.AddNew
For i=0 To RSdao.Fields.Co unt-1: RSdao(i)=RSado( i): Next
RSdao.Update
RSado.MoveNext
Loop
RSado.Close
RSdao.Close
cmd.ActiveConne ction.Close
End Sub

Then you can requery your combobox refresh the form and you have your
rowsource. Also, for Acc2K the 2nd recordset object could also be an
ADODB recordset set to the current project. I think you also need a
reference to Microsoft ActiveX data Object 2.6 or higher for this to be
able to communicate with Sql Server2k (don't think it happens by default
in Acc2k - and definitely not in acc97)

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

**MGFoster** · Nov 12 '05, 10:20 PM

Re: Addressing Multiple Proc Parameters

CSDunn wrote:
[color=blue]
> MGFoster,
> I attempted to incorporate your code suggestion for executing the
> proc, as follows:
>
> Private Sub Completed_Click ()
> DoCmd.RunComman d acCmdSaveRecord
> Dim ctlCurrent As Control
> If Me.Completed = 1 Then
> '************** *************
> Dim cn As New adodb.Connectio n
> Dim strSQL As String
>
> strSQL = "Exec ADTestProcessEv aluator_sp '" & _
> Me.Permnum & "'," & Me.TestGrade & _
> ",'" & Me.TestShortNam e & "'"
>
> cn.Open CurrentProject. Connection
> cn.Execute strSQL, , adCmdStoredProc
>
> 'Lock the Combo Boxes in the Detail Section
> For Each ctlCurrent In Me.Detail.Contr ols
> If ctlCurrent.Cont rolType = acComboBox Then
> ctlCurrent.Lock ed = True
> End If
> Next ctlCurrent
> '************** **************
> ElseIf Me.Completed = 0 Then
> 'Unlock the Combo Boxes in the Detail Section
> For Each ctlCurrent In Me.Detail.Contr ols
> If ctlCurrent.Cont rolType = acComboBox Then
> ctlCurrent.Lock ed = False
> End If
> Next ctlCurrent
>
> End If
> End Sub
>
>
> This code is implemented in the OnClick event of an Option Group. The
> option group is build on a field called 'Completed'. When one of two
> options in the group is clicked, the record is saved, and the OnClick
> event evaluates the field 'Completed' for zero or one. If 'Completed'
> = 1, then the proc is executed with parameters, and the combo boxes in
> the detail section are locked. If 'Completed' = 0, then the combo
> boxes are unlocked.
>
> When I tested the OnClick event of the option group, I received an
> error that expressed a 'Syntax error or access violation' Run-time
> error at the following line:
>
> cn.Execute strSQL, , adCmdStoredProc
>
> I checked my permissions to the proc in SQL Server, and everything
> looks okay. What else could be causing the error?
>[/color]
< SNIP previous posts >

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It's probably a syntax error in the strSQL string. Place a breakpoint
on the line:

cn.Execute strSQL, , adCmdStoredProc

and run the code. When the code breaks check the value of the strSQL.
Make sure that all the parameters are in place (not nulls). If you
want, you may try running the strSQL string value in the MS SQL Query
Analyzer. If that works, then we know the strSQL string command is OK.
If it fails in the Analyzer, post the strSQL string & I'll look at it to
determine why it is syntatically incorrect.

If the strSQL string looks OK, continue the run. If you get another
error, change the adCmdStoredProc to adCmdText. I believe this may be
the cause of the error: adCmdStoredProc means the strSQL is the name of
a stored procedure. Perhaps "Exec ADTestProcessEv aluator_sp ..." etc.
is not considered a stored procedure's name, but a SQL script.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQH2TT4echKq OuFEgEQJuhwCg5s EnZDEzLdD1g+5LB KSw1gXxcc4AoKSt
g3S1dMG/9gnDflF5ZRmBfdz F
=bkLH
-----END PGP SIGNATURE-----

**CSDunn** · Nov 12 '05, 10:21 PM

Re: Addressing Multiple Proc Parameters

MGFoster,
I checked the SQL syntax in QA, and examined the values in the code.
The results led me to look into couple of items in MSDN on the
'EXECUTE' method.

After this research, I found I was able to solve the problem by
changing 'adCmdStoredPro c' to 'adCmdText'.

Imagine that! An answer from MSDN!

Big thanks again for your help!

CSDunn

MGFoster <me@privacy.com > wrote in message news:<9rgfc.950 8$k05.8188@news read2.news.pas. earthlink.net>. ..[color=blue]
> CSDunn wrote:
>[color=green]
> > MGFoster,
> > I attempted to incorporate your code suggestion for executing the
> > proc, as follows:
> >
> > Private Sub Completed_Click ()
> > DoCmd.RunComman d acCmdSaveRecord
> > Dim ctlCurrent As Control
> > If Me.Completed = 1 Then
> > '************** *************
> > Dim cn As New adodb.Connectio n
> > Dim strSQL As String
> >
> > strSQL = "Exec ADTestProcessEv aluator_sp '" & _
> > Me.Permnum & "'," & Me.TestGrade & _
> > ",'" & Me.TestShortNam e & "'"
> >
> > cn.Open CurrentProject. Connection
> > cn.Execute strSQL, , adCmdStoredProc
> >
> > 'Lock the Combo Boxes in the Detail Section
> > For Each ctlCurrent In Me.Detail.Contr ols
> > If ctlCurrent.Cont rolType = acComboBox Then
> > ctlCurrent.Lock ed = True
> > End If
> > Next ctlCurrent
> > '************** **************
> > ElseIf Me.Completed = 0 Then
> > 'Unlock the Combo Boxes in the Detail Section
> > For Each ctlCurrent In Me.Detail.Contr ols
> > If ctlCurrent.Cont rolType = acComboBox Then
> > ctlCurrent.Lock ed = False
> > End If
> > Next ctlCurrent
> >
> > End If
> > End Sub
> >
> >
> > This code is implemented in the OnClick event of an Option Group. The
> > option group is build on a field called 'Completed'. When one of two
> > options in the group is clicked, the record is saved, and the OnClick
> > event evaluates the field 'Completed' for zero or one. If 'Completed'
> > = 1, then the proc is executed with parameters, and the combo boxes in
> > the detail section are locked. If 'Completed' = 0, then the combo
> > boxes are unlocked.
> >
> > When I tested the OnClick event of the option group, I received an
> > error that expressed a 'Syntax error or access violation' Run-time
> > error at the following line:
> >
> > cn.Execute strSQL, , adCmdStoredProc
> >
> > I checked my permissions to the proc in SQL Server, and everything
> > looks okay. What else could be causing the error?
> >[/color]
> < SNIP previous posts >
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> It's probably a syntax error in the strSQL string. Place a breakpoint
> on the line:
>
> cn.Execute strSQL, , adCmdStoredProc
>
> and run the code. When the code breaks check the value of the strSQL.
> Make sure that all the parameters are in place (not nulls). If you
> want, you may try running the strSQL string value in the MS SQL Query
> Analyzer. If that works, then we know the strSQL string command is OK.
> If it fails in the Analyzer, post the strSQL string & I'll look at it to
> determine why it is syntatically incorrect.
>
> If the strSQL string looks OK, continue the run. If you get another
> error, change the adCmdStoredProc to adCmdText. I believe this may be
> the cause of the error: adCmdStoredProc means the strSQL is the name of
> a stored procedure. Perhaps "Exec ADTestProcessEv aluator_sp ..." etc.
> is not considered a stored procedure's name, but a SQL script.
>
> --
> MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
>
> iQA/AwUBQH2TT4echKq OuFEgEQJuhwCg5s EnZDEzLdD1g+5LB KSw1gXxcc4AoKSt
> g3S1dMG/9gnDflF5ZRmBfdz F
> =bkLH
> -----END PGP SIGNATURE-----[/color]

Addressing Multiple Proc Parameters

Addressing Multiple Proc Parameters

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment