Addressing Multiple Proc Parameters

[CSDunn]

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?

Thank you for your help!

CSDunn

[Hans-Joerg Karpenstein]

Am Tue, 13 Apr 2004 04:46:29 -0700 schrieb CSDunn:

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

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]

CSDunn wrote:

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?

-----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]

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]

MGFoster,
Thank you for your help!

CSDunn

MGFoster <[e-mail] > wrote in message news:<RV******* **********@news read1.news.pas. earthlink.net>. ..

CSDunn wrote:

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?

-----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-----

[CSDunn]

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 <[e-mail] > wrote in message news:<RV******* **********@news read1.news.pas. earthlink.net>. ..

CSDunn wrote:

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?

-----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-----

[Rich P]

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]

CSDunn wrote:

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?

< 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]

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 <[e-mail] > wrote in message news:<9r******* **********@news read2.news.pas. earthlink.net>. ..

CSDunn wrote:

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?

< 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-----