how to suppress sp execution results when a form loads? 1

[embryo]

I have a command button on a form with an event procedure that fires off a stored proc that updates any records whose value is "Promised" to "Broken Promise" if they do not pay by the PromiseDate value.. it then opens a form which allows them to buiild the criteria for reports.

The problem is that if there are no records to update, a pop-up with the following text occurs and I'd like to learn how to suppress it.

"The stored procedure executed successfully but did not return records."

I've tried setting NOCOUNT to ON in the proc, but it makes no difference-

Anyone??

Thanks,


Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If

 

[SonOfEmidec1100]

This is probably not SQLServer issue, anyway can you post the code that fires off the SP.

 

[embryo]

Agreed...I posted it on the ADP forum a couple of weeks ago and got no responses til today, at which time a guy suggested I post it on this forum....

Here's the event procedure on my command button:

---------------------------------------------------
Sub Command10_Click()
On Error GoTo Err_Command10_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FrmBuildListMenu"
Application.SetOption "Confirm Action Queries", False
Application.SetOption "Confirm Record Changes", False
DoCmd.OpenStoredProcedure "dbo.qryBrokenPromise"
Application.SetOption "Confirm Action Queries", False
Application.SetOption "Confirm Record Changes", False

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub

---------------------------------------------------

Here's the stored procedure:


ALTER Procedure dbo.qryBrokenPromise
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
As
SET NOCOUNT ON;

UPDATE dbo.TblDebtors SET dbo.TblDebtors.StatusID = 'Brkn Proms'
WHERE (((dbo.TblDebtors.PromiseDate) >= GETDATE()));

----------------------------------------------------

Thanks,

Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If

 

[SonOfEmidec1100]

Only observation from the sql side is that the stored procedure has paramameters that don't seem to be used, the OUTPUT one would be worth investigating further.

 

[donutman]

Add this line after the Update in the SP.

[Blue]SELECT[/Blue] [Fuchsia]COUNT[/Fuchsia][Gray]([/Gray][Gray]*[/Gray][Gray])[/Gray] [Blue]FROM[/Blue] dbo.TblDebtors

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[embryo]

True SonOfEmidec1100...i had yet to whittle those extraneous params away..

donutman...that did the trick, thanks a gazillion!



Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If

 

[donutman]

Oops. Try it before the Update instead.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[embryo]

OK, but it worked fine running the select after the update....??

Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If

 

[donutman]

Hey, if it works at the end, kewl. We cross posted so I didn't realize that you got it working when I posted. I had solved a similar problem by putting it at the beginning.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

It just occured to me that you could probably get it to work without a query on the table.

Select 1

That should do it without a performance hit.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[embryo]

This is turning out to be a real hair-puller...

I also was concerned about performance since the tblDebtors table will be growing quite a bit, so took your advice and changed the procedure to the following:
-------------------------------------------------
ALTER Procedure dbo.qryBrokenPromise
As
SET NOCOUNT ON;

UPDATE dbo.TblDebtors SET dbo.TblDebtors.StatusID = 'Brkn Proms'
WHERE (((dbo.TblDebtors.PromiseDate) >= GETDATE()));

SELECT 1
--------------------------------------------------

Instead of the pop-up Access message informing me that no records were returned, a new window opens up showing the resultset..(1)

....so, I switched it back to the count(*), but now even that causes the new window to pop up showing it's resultset...which is even more annoying than the alert I was getting before!

I THOUGHT that executing the SET NOCOUNT ON statement first was supposed to eliminate the return of a resultset????

Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If

 

[donutman]

Did you try it before the Update. The NoCount refers to the counting of the number of rows affected not the resultset itself.
BTW, if you have a window that is set to display the returning resultset then I think that is always going to give you a problem. You need to turn that off or run the Update query from a different command object. I'm not familiar with the programming language that you are using.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

did you try SET ANSI_WARNINGS OFF?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[donutman]

Hmm...the St. Bernard's back. I'm lazy, so I've learned to grab the first kludge that comes to me.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[embryo]

------------------------------------------------
ALTER Procedure dbo.qryBrokenPromise
As
SET NOCOUNT ON;
SET ANSI_WARNINGS OFF

UPDATE dbo.TblDebtors SET dbo.TblDebtors.StatusID = 'Brkn Proms'
WHERE (((dbo.TblDebtors.PromiseDate) >= GETDATE()));
--------------------------------------------------

just did try it....to no avail...
is this the right way??

Thanks,

Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If

 

[donutman]

Ok, then did you try putting the Select 1 before the Update? If that doesn't work, then I would try to find a way to execute the SP from a command object that doesn't create a window with the resultset. If you don't know how to do that, I would suggest posting in the appropriate forum.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[embryo]

Yes, I tried moving the select and have same result...
In the event procedure that the command button kicks off, it is only opening the stored procedure and simultaneously loading a form. The command object code to open the stored procedure isn't supposed to open up a window..it just does..

The really weird thing is that when iI initially tried your suggestion about adding a select after the update, it worked as desired..just opened up the form without first telling me it had nothing to update..

Oh well..
I appreciate your help-

Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If

 

[donutman]

Then there's this kludge:

[Blue]ALTER[/Blue] [Blue]PROCEDURE[/Blue] dbo.qryBrokenPromise[green]
--   (@parameter1 datatype = default value,
[/green][green]--    @parameter2 datatype OUTPUT)
[/green][Blue]AS[/Blue]
[Blue]IF[/Blue] 0[Gray]<[/Gray][Gray]([/Gray][Blue]SELECT[/Blue] [Fuchsia]COUNT[/Fuchsia][Gray]([/Gray][Gray]*[/Gray][Gray])[/Gray] 
         [Blue]FROM[/Blue] dbo.TblDebtors
         [Blue]WHERE[/Blue] dbo.TblDebtors.PromiseDate [Gray]>[/Gray][Gray]=[/Gray] [Fuchsia]GetDate[/Fuchsia][Gray]([/Gray][Gray])[/Gray][Gray])[/Gray]
   [Blue]UPDATE[/Blue] dbo.TblDebtors 
      [Blue]SET[/Blue] dbo.TblDebtors.StatusID [Gray]=[/Gray] [red]'Brkn Proms'[/red]
      [Blue]WHERE[/Blue] dbo.TblDebtors.PromiseDate [Gray]>[/Gray][Gray]=[/Gray] [Fuchsia]GetDate[/Fuchsia][Gray]([/Gray][Gray])[/Gray][Gray])[/Gray]
[Blue]GO[/Blue]

If this still give you a message, then the next level of kludges is to force a meaningless Update of a row.
-Karl



[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]