adodb.command execute crash

[Keith G Hicks]

I'm using A2003 with A2k format. SP2. Also SQL 2000 SP4. This is an ADP.

I have a few vba routines that insert new rows via stored procedures in the
backend. Most of them work fine. But one in particular is completely
crashing Access (the "do you want to send a report to microsoft" window
comes up). There is no error displayed in Access itself. I've tried
decompiling, compact/repair and copying all db objects into a new ADP.
Consistently, one of the routines crashes. I can run the stored procedure
from QA and it works fine. In fact when run from my vba code it runs fine
too. The new row is added but Access crashes on the "execute" statement.

All these routines are set up the same way as follows:

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd

.CommandType = adCmdStoredProc
.CommandText = "sp_AddNewNewspaper"
.ActiveConnection = cnnCurrProj
.CommandTimeout = 0

.Parameters.Refresh
.Parameters.Item("@sNPCounty").Value = Me.cboNPCounty
.Parameters.Item("@sNPName").Value = Trim(Me.txtNPName)
.Parameters.Item("@iNewNPID").Value = 0

.Prepared = True
.Execute '<<<<<<<<<<<<<<< crashes here every time.

end with

in a startup routine I have the following to set the value of cnnCurrProj:

Set cnnCurrProj = New ADODB.Connection
Set cnnCurrProj = CurrentProject.Connection

the stored procedure is as follows:

CREATE PROCEDURE dbo.sp_AddNewNewspaper
@sNPCounty VARCHAR(30),
@sNPName VARCHAR(50),
@iNewNPID INT OUTPUT

AS

SET NOCOUNT ON

DECLARE
@sNewNpGUID VARCHAR(100),
@iError INT,
@iRowCount INT

SET @sNewNpGUID = NEWID()
SET @iError = @@ERROR IF @iError <> 0 RETURN @iError


INSERT INTO tblNewspapers (NPCounty, NPName, NewNPGuid) VALUES (@sNPCounty,
@sNPName, @sNewNpGUID)

SELECT @iError = @@ERROR, @iRowCount = @@ROWCOUNT
IF @iError <> 0 RETURN @iError
IF @iRowCount = 0 RETURN 50001

SET @iNewNPID = (SELECT NPID FROM tblNewspapers WHERE NewNPGuid =
@sNewNpGUID)
SET @iError = @@ERROR IF @iError <> 0 RETURN @iError

Again, I have similar code for another table that runs without any hicups.

I've looked all over for some clue as to what's going on here and have found
nothing. Hope someone can help.

Thanks,

Keith

 

[Sylvain Lafontaine]

Suggestions:

1- Don't use the sp_ prefix.

2- Add "Set" for setting the active connection:

Set .ActiveConnection = cnnCurrProj

3- Remove the .Prepared statement.

If none of this work, try to debug the SP (ie, find the row where there is
an error, if any) and/or remove the call to .Parameters.Refresh and set up
the parameters collection yourself (don't forget about the return value).
BTW, you should always do that because your code will save a run trip to the
server and run faster.

Any reason why you use varchar(100) instead of uniqueidentifier for the
declaration of @sNewAANpGUID?

 

[Keith G Hicks]

Thanks for the info. Adding "set" solved the problem.
Doesn't matter here if I use prepared or not. I get the same results. It
works fine. Why is that not necessary here?

Keith

 

[Sylvain Lafontaine]

The .Prepared property should have no effect because you are calling a
stored procedure.

Also, you should use the @@identity value or the scope_identity() function
to retrieve the last inserted identity value; this would be much faster than
making the query « SET @iNewNPID = (SELECT NPID FROM tblNewspapers WHERE
NewNPGuid=@sNewNpGUID) »