Msg After Procedure Runs 1

[lars7]

Hi Guys,
I just converted my first (.mdb) file to a (.adp) but all my stored procedures show a message after running:

"The store procedure executed properly but did not return any records."

this happens when I click to open the procedure, but I get an run-time error message ('8008') when I call the procedure from a form .

Thanks in advance for any help on this.

 

[PWise]

how do you call it from a form?

 

[lars7]

Hi Pwise,
Thanks for replying.


When it was a .mdb file I had this simple delete statement as the form opened:

Private Sub Form_Open(Cancel As Integer)

DELETE *
FROM DuplicatePayNumber


but when I converted it to a .adp file this didn't work so I created this Stored Procedure and called it like so:



Private Sub Form_Open(Cancel As Integer)
Dim stDocName1 As String


stDocName1 = "StoredProcedure1"
DoCmd.OpenStoredProcedure stDocName1, acNormal


with this Sql.


ALTER PROCEDURE dbo.StoredProcedure1
AS
DELETE FROM dbo.DuplicatePayNumber


whether I call it from the form or open it by double clicking I get the same message but if I execute it from Management Studio Express it runs fine.

 

[payback]

Have a look at the NOCOUNT option in the SQL documentation.

 

[lars7]

Hi payback,

Thanks for replying,

I tried this but still got the message:


ALTER PROCEDURE dbo.StoredProcedure1
AS

SET NOCOUNT ON

DELETE FROM dbo.DuplicatePayNumber

SET NOCOUNT OFF

 

[danvlas]

CurrentProject.Connection.Execute "StoredProcedure1"

or (better)

dim cnn as New adodb.connection
Dim cmd as new adodb.command

set cnn=CurrentProject.Connection

with cmd
.activeConnection=cnn
.CommandText="StoredProcedure1"
.CommandType = adstoredProc
'.Parameters.Refresh 'this is not needed if no parameters exist
.Execute
end with

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[lars7]

Hi Danvlas,

Thanks for these,

The first one worked great but in the second I got an error message,

"Arguments are of the wrong type, are out of the acceptable range, or are in conflict with one another"

until I commented out:

'.CommandType = adstoredProc


But I will use the first Cheers.

 

[danvlas]

Sorry, my mistake.

.CommandType=adCmdStoredProc


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html