By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,965 Members | 2,043 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,965 IT Pros & Developers. It's quick & easy.

Addressing Multiple Proc Parameters

P: n/a
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_AfterUpdate()

Me.SecondCombo.RowSource = "EXEC dbo.ADStudentCombo_sp " &
Me.FirstCombo

End Sub

The above code sets the RowSource of the SecondCombo to the result of
the execution of dbo.ADStudentCombo_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.ADTestProcessEvaluator_sp " & Me.Permnum & Me.TestGrade
& Me.TestShortName
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
Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Am Tue, 13 Apr 2004 04:46:29 -0700 schrieb CSDunn:
Hello,

Private Sub FirstCombo_AfterUpdate()

Me.SecondCombo.RowSource = "EXEC dbo.ADStudentCombo_sp " &
Me.FirstCombo

End Sub

The above code sets the RowSource of the SecondCombo to the result of
the execution of dbo.ADStudentCombo_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.ADTestProcessEvaluator_sp " & Me.Permnum & Me.TestGrade
& Me.TestShortName
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>

Nov 12 '05 #2

P: n/a
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_AfterUpdate()

Me.SecondCombo.RowSource = "EXEC dbo.ADStudentCombo_sp " &
Me.FirstCombo

End Sub

The above code sets the RowSource of the SecondCombo to the result of
the execution of dbo.ADStudentCombo_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.ADTestProcessEvaluator_sp " & Me.Permnum & Me.TestGrade
& Me.TestShortName
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.connection
dim strSQL as string

strSQL = "Exec ADTestProcessEvaluator_sp '" & _
Me.Permnum & "'," & Me.TestGrade & _
",'" & Me.TestShortName & "'"

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

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

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

strSQL = "Exec ADTestProcessEvaluator_sp '" & _
Me.Permnum & "'," & Me.TestGrade & _
",'" & Me.TestShortName & "'"

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

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

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

iQA/AwUBQHwq7oechKqOuFEgEQItuACg7sgDJghMv6nEzyQThoW9mh nmMTwAoOjw
OFywcVNzjXWlcOGen2NTZ20H
=TSKM
-----END PGP SIGNATURE-----

Nov 12 '05 #3

P: n/a

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!
Nov 12 '05 #4

P: n/a
MGFoster,
Thank you for your help!

CSDunn

MGFoster <me@privacy.com> wrote in message news:<RV*****************@newsread1.news.pas.earth link.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_AfterUpdate()

Me.SecondCombo.RowSource = "EXEC dbo.ADStudentCombo_sp " &
Me.FirstCombo

End Sub

The above code sets the RowSource of the SecondCombo to the result of
the execution of dbo.ADStudentCombo_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.ADTestProcessEvaluator_sp " & Me.Permnum & Me.TestGrade
& Me.TestShortName
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.connection
dim strSQL as string

strSQL = "Exec ADTestProcessEvaluator_sp '" & _
Me.Permnum & "'," & Me.TestGrade & _
",'" & Me.TestShortName & "'"

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

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

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

strSQL = "Exec ADTestProcessEvaluator_sp '" & _
Me.Permnum & "'," & Me.TestGrade & _
",'" & Me.TestShortName & "'"

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

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

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

iQA/AwUBQHwq7oechKqOuFEgEQItuACg7sgDJghMv6nEzyQThoW9mh nmMTwAoOjw
OFywcVNzjXWlcOGen2NTZ20H
=TSKM
-----END PGP SIGNATURE-----

Nov 12 '05 #5

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

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

strSQL = "Exec ADTestProcessEvaluator_sp '" & _
Me.Permnum & "'," & Me.TestGrade & _
",'" & Me.TestShortName & "'"

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

'Lock the Combo Boxes in the Detail Section
For Each ctlCurrent In Me.Detail.Controls
If ctlCurrent.ControlType = acComboBox Then
ctlCurrent.Locked = True
End If
Next ctlCurrent
'****************************
ElseIf Me.Completed = 0 Then
'Unlock the Combo Boxes in the Detail Section
For Each ctlCurrent In Me.Detail.Controls
If ctlCurrent.ControlType = acComboBox Then
ctlCurrent.Locked = 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:<RV*****************@newsread1.news.pas.earth link.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_AfterUpdate()

Me.SecondCombo.RowSource = "EXEC dbo.ADStudentCombo_sp " &
Me.FirstCombo

End Sub

The above code sets the RowSource of the SecondCombo to the result of
the execution of dbo.ADStudentCombo_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.ADTestProcessEvaluator_sp " & Me.Permnum & Me.TestGrade
& Me.TestShortName
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.connection
dim strSQL as string

strSQL = "Exec ADTestProcessEvaluator_sp '" & _
Me.Permnum & "'," & Me.TestGrade & _
",'" & Me.TestShortName & "'"

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

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

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

strSQL = "Exec ADTestProcessEvaluator_sp '" & _
Me.Permnum & "'," & Me.TestGrade & _
",'" & Me.TestShortName & "'"

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

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

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

iQA/AwUBQHwq7oechKqOuFEgEQItuACg7sgDJghMv6nEzyQThoW9mh nmMTwAoOjw
OFywcVNzjXWlcOGen2NTZ20H
=TSKM
-----END PGP SIGNATURE-----

Nov 12 '05 #6

P: n/a
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.ActiveConnection = "Provider=SQLOLEDB;Data Source=yourSqlServer;" _
& "Initial Catalog=yourSqlDB;UID=SA;PWD=tiger;"
cmd.CommandTimeout = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "yourSqlstoredProc"
cmd.Parameters("@Permnum").Value = var1
cmd.Parameters("@TestGrade").Value = var2
cmd.Parameters("@TestShortName").Value = var3
Set RSado = cmd.Execute
Set RSdao = CurrentDb.OpenRecordset("tblcboRowsource")
DoEvents
Do While Not RSado.EOF
RSdao.AddNew
For i=0 To RSdao.Fields.Count-1: RSdao(i)=RSado(i): Next
RSdao.Update
RSado.MoveNext
Loop
RSado.Close
RSdao.Close
cmd.ActiveConnection.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!
Nov 12 '05 #7

P: n/a
CSDunn wrote:
MGFoster,
I attempted to incorporate your code suggestion for executing the
proc, as follows:

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

strSQL = "Exec ADTestProcessEvaluator_sp '" & _
Me.Permnum & "'," & Me.TestGrade & _
",'" & Me.TestShortName & "'"

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

'Lock the Combo Boxes in the Detail Section
For Each ctlCurrent In Me.Detail.Controls
If ctlCurrent.ControlType = acComboBox Then
ctlCurrent.Locked = True
End If
Next ctlCurrent
'****************************
ElseIf Me.Completed = 0 Then
'Unlock the Combo Boxes in the Detail Section
For Each ctlCurrent In Me.Detail.Controls
If ctlCurrent.ControlType = acComboBox Then
ctlCurrent.Locked = 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 ADTestProcessEvaluator_sp ..." etc.
is not considered a stored procedure's name, but a SQL script.

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

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

iQA/AwUBQH2TT4echKqOuFEgEQJuhwCg5sEnZDEzLdD1g+5LBKSw1g Xxcc4AoKSt
g3S1dMG/9gnDflF5ZRmBfdzF
=bkLH
-----END PGP SIGNATURE-----

Nov 12 '05 #8

P: n/a
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 'adCmdStoredProc' to 'adCmdText'.

Imagine that! An answer from MSDN!

Big thanks again for your help!

CSDunn

MGFoster <me@privacy.com> wrote in message news:<9r*****************@newsread2.news.pas.earth link.net>...
CSDunn wrote:
MGFoster,
I attempted to incorporate your code suggestion for executing the
proc, as follows:

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

strSQL = "Exec ADTestProcessEvaluator_sp '" & _
Me.Permnum & "'," & Me.TestGrade & _
",'" & Me.TestShortName & "'"

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

'Lock the Combo Boxes in the Detail Section
For Each ctlCurrent In Me.Detail.Controls
If ctlCurrent.ControlType = acComboBox Then
ctlCurrent.Locked = True
End If
Next ctlCurrent
'****************************
ElseIf Me.Completed = 0 Then
'Unlock the Combo Boxes in the Detail Section
For Each ctlCurrent In Me.Detail.Controls
If ctlCurrent.ControlType = acComboBox Then
ctlCurrent.Locked = 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 ADTestProcessEvaluator_sp ..." etc.
is not considered a stored procedure's name, but a SQL script.

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

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

iQA/AwUBQH2TT4echKqOuFEgEQJuhwCg5sEnZDEzLdD1g+5LBKSw1g Xxcc4AoKSt
g3S1dMG/9gnDflF5ZRmBfdzF
=bkLH
-----END PGP SIGNATURE-----

Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.