the procedure executed successfully but did not return records

  • Thread starter Thread starter Barry George
  • Start date Start date
B

Barry George

I am new to sqlServer and am doing an application front end using Access
2003 and the backend sqlServerExpress 2005.

When I run a very simple Stored Procedure on a table called 'MiscCharges'
such as;

SET NOCOUNT ON
UPDATE MiscCharges SET Description='New Description'
WHERE Joborder='1000'

I get the following message: "the procedure executed successfully but did
not return records"

This happens regardless of how I run the SP. Either in the SP window or from
VBA code.

I don't get this message with a simple SELECT sql statement.

What am I doing wrong?
 
Try this Barry...

Dim oCmd As Command, param As Parameter
Dim cn As New ADODB.Connection, sqlString As String

sqlString = "YourStoredProc"
Set oCmd = New ADODB.Command
Set cn = CurrentProject.Connection
Set oCmd.ActiveConnection = cn
oCmd.CommandText = sqlString
oCmd.CommandType = adCmdStoredProc
oCmd.CommandTimeout = 15

oCmd.Execute , , adExecuteNoRecords
cn.close
set cn = nothing

hth.. bob
 
Thank you Bob. I will give that a try.

BG


Bob said:
Try this Barry...

Dim oCmd As Command, param As Parameter
Dim cn As New ADODB.Connection, sqlString As String

sqlString = "YourStoredProc"
Set oCmd = New ADODB.Command
Set cn = CurrentProject.Connection
Set oCmd.ActiveConnection = cn
oCmd.CommandText = sqlString
oCmd.CommandType = adCmdStoredProc
oCmd.CommandTimeout = 15

oCmd.Execute , , adExecuteNoRecords
cn.close
set cn = nothing

hth.. bob
Click to expand...
 
Just to clarify, you're not doing anything wrong. Bob's post is a good way around the behaviour, but what you're experiencing is
exactly the expected behaviour.

When you run a SELECT query, it returns records, so there's no need to inform you of its success. When you run something like an
INSERT, UPDATE or DELETE query (also called Action Queries in MDBs), Access is nice and informs you of its success. The message
you're getting isn't an error, it's simply an acknowledgement that everything went as expected, but that you weren't running a
SELECT query, so it has nothing to show you.



Rob
 
Thanks for the Info Rob.

Barry


Robert Morley said:
Just to clarify, you're not doing anything wrong. Bob's post is a good
Click to expand...
way around the behaviour, but what you're experiencing is
exactly the expected behaviour.

When you run a SELECT query, it returns records, so there's no need to
Click to expand...
inform you of its success. When you run something like an
INSERT, UPDATE or DELETE query (also called Action Queries in MDBs),
Click to expand...
Access is nice and informs you of its success. The message
you're getting isn't an error, it's simply an acknowledgement that
Click to expand...
everything went as expected, but that you weren't running a
 
Back
Top