Not able to insert record into my sql express

  • I am using an sp to insert a record in sql express. When i insert it with query window it gets properly inserted but if i insert it using the code no error is returned and it even returns the inserted record's primary key.If i stop the solution and rerun it i find the record missing.Can any one help me on this?

  • Any chance of the schema, some sample data, and the query you are running?

    Without these noone can really help you.



    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • sorry about it

    ALTER procedure [dbo].[pInsVesselInformationReport]

    (

    @VoyageNumber nvarchar(200)

    ,@VesselCode nvarchar(200)

    ,@MasterCode nvarchar(200)

    ,@Period nvarchar(500)

    ,@ReportDate datetime

    ,@SMTHH int

    ,@SMTMM int

    )

    AS

    DECLARE @VesselInfoReportId INT;

    BEGIN

    SET NOCOUNT ON

    SET @FMSRefNo = ISNULL(@FMSRefNo,'');

    IF (NOT EXISTS(SELECT * FROM tVesselInformation WHERE FMSRefNo = @FMSRefNo))

    BEGIN

    INSERT INTO tVesselInformation

    (

    VoyageNumber

    ,VesselCode

    ,MasterCode

    ,Period

    ,ReportDate

    ,SMTHH

    ,SMTMM

    )

    VALUES

    (

    @VoyageNumber

    ,@VesselCode

    ,@MasterCode

    ,@Period

    ,@ReportDate

    ,@SMTHH

    ,@SMTMM

    )

    SELECT @VesselInfoReportId = SCOPE_IDENTITY();

    END

    ELSE

    BEGIN

    SELECT @VesselInfoReportId=VesselInfoReportId FROM tVesselInformation

    WHERE FMSRefNo = @FMSRefNo;

    EXECUTE pUpdateVesselInformationReport

    @VesselInfoReportId=@VesselInfoReportId

    ,@VoyageNumber=@VoyageNumber

    ,@VesselCode=@VesselCode

    ,@MasterCode=@MasterCode

    ,@Period=@Period

    ,@ReportDate=@ReportDate

    ,@SMTHH=@SMTHH

    ,@SMTMM=@SMTMM

    END

    SELECT @VesselInfoReportId;

    END

    this is my sp

    and from my code i insert it like

    int

    vesselInfoReportID = (int)ServiceDB.ExecuteScalar("pInsVesselInformationReport",

    objVesselInfoReport.VoyageNumber,

    objVesselInfoReport.VesselCode,

    objVesselInfoReport.MasterCode,

    objVesselInfoReport.Period,

    objVesselInfoReport.ReportDate,

    objVesselInfoReport.SMTHH,

    objVesselInfoReport.SMTMM,

    );

    where my ServiceDB = DatabaseFactory.CreateDatabase();

    and i use Microsoft.Practices.EnterpriseLibrary.Common.dll

    I have reduced the parameters as there are around 100 fields

  • When you meet the IF condition you are never returning @VesselInfoReportId. You need to either add:

    Select @VesselInfoReportId

    in the IF after the

    Select @VesselInfoReportId = Scope_Identity()

    Or move

    Select @VesselInfoReportId

    Outside your IF..ELSE block

  • Have you looked up the record based on the returned primary key?

    (As Jack points out based on your snippet, if it is returning a primary key, then it is performing an update, not an insert, as the insert snippet doesn't return anything.)

    So, check the record with the returned primary key to see if it was updated with the values you passed in.

    Are you passing in a good @FMSRefNo? That appears to be key.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply