Not able to insert record into my sql express

Question

Post reply

Not able to insert record into my sql express

ambika.ttcpl-890289

SSC Rookie

Points: 26
More actions
September 24, 2008 at 6:53 am

#92492

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?

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

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

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

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]

ambika.ttcpl-890289 SSC Rookie Points: 26 More actions · Answer 2

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

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

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

dcorbitt Ten Centuries Points: 1006 More actions · Answer 4

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.