I have a requirement to support multiple types of backend database
types, not specifically SQLServer, though, at this point, I am using
MSDE.
I've used both a reader and EXECUTENONQUERY with identical results.
Here is the stored procedure. Is is possible that my connection
transaction is somehow, "not playing correctly" with my stored
procedure transaction?
Thanks,
Trac
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.BMS2_S_FRAME_ELEMENT_ROLLUP
@brkey varchar(15),
@inspkey varchar(4),
@userkey varchar(4),
@returnCode int Output
AS
/************************************************************
* Purpose: This procedure will generate summary FRAME element
* inspection records and update the FRAME structure
* unit by first deleting the existing FRAME child
* element then summing the NON-FRAME element inspection
* records
*
* Created: 08/17/2006 tlb
*************************************************************/
/************************************************************
*
*Need to confirm how ELEMROWID is being set
* - probably want to use the same logic that's being used on Form
E for new elements.
*
*************************************************************/
DECLARE
@frame_strUnitKey numeric(4),
@insert_date datetime,
@elemenInsp_Rows int,
@strUnitType varchar(1)
set nocount on --this is for performance only to avoid sending records
affected back to client after each statement
set @insert_date = getdate()
set @strUnitType = 'F'
set @returnCode = 0
BEGIN TRANSACTION
--
-- Retrieve the key for the FRAME structure unit
--
select @frame_strUnitKey = STRUNITKEY
from STRUCTURE_UNIT s
where
s.BRKEY = @brkey
and
s.STRUNITTYPE = 'F' -- 'Frame' structure unit
--
-- Check for child ELEMINSP records
--
select @elemenInsp_Rows = count(*)
from ELEMINSP e
where
e.BRKEY = @brkey
AND e.INSPKEY = @inspkey
if @frame_strUnitKey is null
BEGIN
--
-- check for child element inspection records
-- and create one if eleminsp records exist
-- if there are no child records, don't bother
-- to make a frame record in the first place
--
IF @elemenInsp_Rows > 0
BEGIN
--
--create a new FRAME Structure Unit if there are elem insp records
but no frame
--
-- calc the next key here
select @frame_strUnitKey = ISNULL(MAX(STRUNITKEY), 0) + 1
from STRUCTURE_UNIT s2
where s2.BRKEY = @brkey;
insert into STRUCTURE_UNIT(BRKEY,
STRUNITKEY,
STRUNITTYPE,
CREATEDATETIME, CREATEUSERKEY,
MODTIME, USERKEY,
DEFAULTFLAG
)
VALUES (@brkey ,
@frame_strUnitKey,
'F',
@insert_date, @userkey,
@insert_date,@userkey,
'1' )
if (@@error > 0)
BEGIN
set @returnCode = 1
ROLLBACK TRANSACTION
goto LOCAL_EXIT
END
--
-- now load the USERSTRUCUNIT table so that the STRUNITKEY is the
same...
--
insert into USERSTRUNIT (BRKEY, STRUNITKEY)
VALUES (@brkey,
@frame_strUnitKey )
if (@@error > 0)
BEGIN
set @returnCode = 2
ROLLBACK TRANSACTION
goto LOCAL_EXIT
END
END --end if @elemenInsp_Rows > 0
END -- end if @frame_strUnitKey is null
--
-- Replace or create frame related ELEMINSP child summary rows
--
IF @elemenInsp_Rows > 0
BEGIN
--
-- Delete all preexisting "summary" element inspection records that
are
-- children of the FRAME structure unit (ELEMINSP)
--
delete from ELEMINSP
where BRKEY = @brkey
and INSPKEY = @inspkey
and STRUNITKEY = @frame_strUnitKey
if (@@error > 0)
BEGIN
set @returnCode = 3
ROLLBACK TRANSACTION
goto LOCAL_EXIT
END
--
-- create new frame related ELEMINSP child rows
--
INSERT INTO ELEMINSP
(BRKEY,
INSPKEY,
ELEMKEY,
ENVKEY,
STRUNITKEY,
ELMROWIDKEY,
ELINSPDATE,
QUANTITY,
ELEM_SCALE_FACTOR,
PCTSTATE1, QTYSTATE1,
PCTSTATE2, QTYSTATE2,
PCTSTATE3, QTYSTATE3,
PCTSTATE4, QTYSTATE4,
PCTSTATE5, QTYSTATE5,
ELCONDEST,
DESCRIPTION,
CREATEDATETIME,
CREATEUSERKEY,
MODTIME,
USERKEY )
SELECT BRKEY,
INSPKEY,
ELEMKEY,
ENVKEY,
@frame_strUnitKey AS STRUNITKEY,
(BRKEY + '-' + INSPKEY + '-'
+ REPLICATE('0', 3 - LEN(CAST(ELEMKEY AS VARCHAR(3))) ) + '-'
+ REPLICATE('0', 1 - LEN(CAST(ENVKEY AS VARCHAR(1))) ) + '-'
+ REPLICATE('0', 4 - LEN(CAST(@frame_strUnitKey AS
VARCHAR(4))) )
) AS ELMROWIDKEY,
ELINSPDATE, SUM(QUANTITY) AS QUANTITY,
AVG(ELEM_SCALE_FACTOR),
SUM(PCTSTATE1), SUM(QTYSTATE1) AS QTYSTATE1,
SUM(PCTSTATE2), SUM(QTYSTATE2) AS QTYSTATE2,
SUM(PCTSTATE3), SUM(QTYSTATE3) AS QTYSTATE3,
SUM(PCTSTATE4), SUM(QTYSTATE4) AS QTYSTATE4,
SUM(PCTSTATE5), SUM(QTYSTATE5) AS QTYSTATE5,
1 AS ELCONDEST,
'Element record added ' + CONVERT(VARCHAR, @insert_date, 111 ) AS
DESCRIPTION,
@insert_date AS CREATEDATETIME,
@userkey AS CREATEUSERKEY,
@insert_date AS MODTIME,
@userkey AS USERKEY
from ELEMINSP
where BRKEY = @brkey
and INSPKEY = @inspkey
and STRUNITKEY <> (@frame_strUnitKey)
group by
BRKEY, INSPKEY,
ELEMKEY, ENVKEY,
ELINSPDATE
if (@@error > 0)
BEGIN
set @returnCode = 4
ROLLBACK TRANSACTION
goto LOCAL_EXIT
END
--
-- Update the percentages
--
UPDATE ELEMINSP
set PCTSTATE1 = ((QTYSTATE1 / QUANTITY) * 100),
PCTSTATE2 = ((QTYSTATE2 / QUANTITY) * 100),
PCTSTATE3 = ((QTYSTATE3 / QUANTITY) * 100),
PCTSTATE4 = ((QTYSTATE4 / QUANTITY) * 100),
PCTSTATE5 = ((QTYSTATE5 / QUANTITY) * 100)
where
BRKEY = @brkey
and INSPKEY = @inspkey
and STRUNITKEY =@frame_strUnitKey
AND
QUANTITY <> 0
if (@@error > 0)
BEGIN
set @returnCode = 5
ROLLBACK TRANSACTION
goto LOCAL_EXIT
END
--
-- Generate a unique ELEMROWID for each record
--
UPDATE ELEMINSP
set ELMROWIDKEY = (BRKEY + '-' + INSPKEY + '-'
+ REPLICATE('0', 3 - LEN(CAST(ELEMKEY AS VARCHAR(3))) ) + '-'
+ REPLICATE('0', 1 - LEN(CAST(ENVKEY AS VARCHAR(1))) ) + '-'
+ REPLICATE('0', 4 - LEN(CAST(STRUNITKEY AS VARCHAR(4)))
)
)
where
BRKEY = @brkey
and INSPKEY = @inspkey
and STRUNITKEY = @frame_strUnitKey
if (@@error > 0)
BEGIN
set @returnCode = 6
ROLLBACK TRANSACTION
goto LOCAL_EXIT
END
END -- end if @elemenInsp_Rows > 0
COMMIT TRANSACTION
LOCAL_EXIT:
--set @returnCode = 15
return @returnCode
set nocount off
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Tracy,
There is one thing a reader seldom does, that is writing.
Did you already have a look at the command.executnonQuirry?
As Earl already wrote, it is better to change your OleDB for SQLClient.
http://msdn.microsoft.com/library/d...clientsqlcommandclassexecutenonquerytopic.asp
I hope this helps,
Cor
I'm hoping someone can help!
I have a stored procedure in an MSDE database. When I use Query
Analyser to run this stored procedure the correct results appear in the
database. My stored procedure returns resultCode.
The stored procedure includes a single
BEGIN TRANSACTION
[...]
COMMIT TRANSACTION
Within my application, I execute the follow code I recieve the correct
result code but the information is NOT updated in teh database. I have
tried with and without ADO transactions.
public int CreateFrame(string BRKEY, string INSPKEY, string USERKEY)
{
//System.Data.OleDb.OleDbTransaction frameTransaction;
int returnValue = 0;
_command.Parameters["@brkey"].Value = BRKEY;
_command.Parameters["@userkey"].Value = USERKEY;
_command.Parameters["@inspkey"].Value = INSPKEY;
try
{
if (_command.Connection.State == System.Data.ConnectionState.Closed)
_command.Connection.Open();
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine(ex.Message);
returnValue=-99;
}
if (returnValue !=0)
return(returnValue);
//frameTransaction = _command.Connection.BeginTransaction();
//_command.Transaction = frameTransaction;
try
{
_reader =_command.ExecuteReader();
_reader.Close();
returnValue = (int)_command.Parameters["@returnCode"].Value;
//frameTransaction.Commit();
_command.Connection.Close();
_command.Dispose();
}
catch (Exception exception)
{
//frameTransaction.Rollback();
System.Diagnostics.Trace.WriteLine(exception.Message);
returnValue=-99;
}
finally
{
if (_command != null)
_command.Dispose();
}
return(returnValue);
}
my parameters and my command and connection objects are created in the
initializer to my class
My users begin testing in 2 days and I'd like to have this
functionality working.
HELP!
