Suppress Stored Procedure message in ADP

[Guest]

I have 2 forms in an ADP project - one for a user to input a number of
values, the second accepts those values as input parameters for a SQL server
stored procedure. The stored procedure has 3 insert SQL statements. All is
working well, and the stored procedure is running correctly.

Where I am having a problem is that, when the stored procedure runs, the
user gets a message saying "The stored procedure executed, but returned no
results". The user hits okay, and then gets the message again. Is there
anyway I can suppress this message?

 

[Sylvain Lafontaine]

Hum, maybe you have set the option NoCount to ON in your stored procedure;
no line count is returned to the ADO command object, hence the error
message.

How are you calling your stored procedure? By opening a recordset? An
example of your code could be helpfull here. With ADO, you can specify that
no result set will be returned by using the command object instead of the
recordset object.

S. L.

 

[Guest]

Thanks for the reply Sylvain.

The SP is bound to a blank form that accepts Input Parameters from another
form. I don't think what I am getting back is an error message - the Stored
Procedure does return no results since it contains 3 insert queries and no
select queries - hence no results.

Here's the SP....there is no VBA code - I've been reying on the built-in
functionality of Access...

ALTER PROCEDURE dbo.sp_add_new_person

-- Get variables from form for inserting into person table
@userid varchar(50),
@institution int,
@program int = NULL,
@role int,
@type int = 11,
@fname varchar(50),
@lname varchar(50),
@street varchar(100)= NULL,
@city varchar(50)= NULL,
@pcode varchar(10)= NULL,
@prov varchar(50)= NULL,
@country varchar(50)= NULL,
@phone varchar(50)= NULL,
@email varchar(255)= NULL,
@password varchar(50)= NULL,
@birth datetime= NULL,
@webct varchar(20)= NULL,

-- Get variables from form for which group this record should be associated
@group int = Null,

-- Set Group Status 41 confirmed, 42 Pending, 43 Waiting, 44 Denied
@groupstatus int


AS

BEGIN

DECLARE @LastValue int, @mainGroup int -- Variable to hold last inserted
person ID

SET @mainGroup = 3 -- set Main Group ID to Educators group

-- Insert new record

INSERT dbo.V_PERSONS
(UserID, InstitutionID, ProgramID, RoleID, TypeID, FirstName, LastName,
StreetAddress, City, PostalCode, Province, Country, Phone, Email, Password,
BirthDate, WebCTID)
VALUES
(@userid, @institution, @program, @role, @type, @fname, @lname, @street,
@city, @pcode, @prov, @country, @phone, @email, @password, @birth, @webct)

-- Get the value of the personID just inserted above

SET @LastValue =
(SELECT SCOPE_IDENTITY())

-- Associate the new person with the correct group

INSERT INTO Person_Dir.dbo.Persons_Groups
(PersonID, GroupID, StatusID)
VALUES
(@LastValue, @mainGroup, @groupstatus)

-- Associate the new person with the correct sub-group

INSERT INTO Person_Dir.dbo.Persons_Groups
(PersonID, GroupID, StatusID)
VALUES
(@LastValue, @group, @groupstatus)

END

 

[Sylvain Lafontaine]

No wonder that you may receive strange error message for relying on a bound
form to perform an execute only query.

You should use the ADO objects to perform this kind of things. In the
microsoft.public.access.adp.sqlserver newsgroup, you will probably find many
articles about the use of ADO from ADP.



S. L.