I am having issues inserting data in two tables. I created two different procedures as follows:
-Here is the first procedure
CREATE PROCEDURE [dbo].[NPAAddApplicant]
@szName varchar(100),
@szCompPostalAdd varchar(200),
@szCompStreet varchar(200),
@szCompTel1 varchar(100),
@szCompTel2 varchar(50),
@szFax varchar(50),
@szCompEmailAdd varchar(50),
@szCompWebSite varchar(100),
@szContPersonName varchar(200),
@szContactPhone1 varchar(50),
@szContactPhone2 varchar(50),
@szContactPhone3 varchar(50),
@szContEmail varchar(50),
@iUserId bigint,
@iStatus int,
@iConcurrency int,
@iCompanyId bigint,
@szType varchar(50),
@szContactDesignation varchar(150),
@szCertificateOfIncorporation varchar(50),
@iTypeOfBunkering int,
@iZoneId bigint,
@dLicenseDate date,
@szImlBpCode varchar(50),
@iTownId bigint,
@iNewApplicant bigint
AS
BEGIN
declare @szCode varchar(25)
EXEC spGenTransCode @szType, @retval = @szCode OUTPUT
INSERT INTO psApplicant
(szCode,szName,szCompPostalAdd,szCompStreet,szCompTel1,szCompTel2,szFax,szCompEmailAdd,szCompWebSite,szContPersonName,
szContactPhone1,szContactPhone2,szContactPhone3,szContEmail,iUserId,iStatus,iConcurrency,iCompanyId,szType,szContactDesignation,
szCertificateOfIncorporation,dStampDate,dCreatedate,iTypeOfBunkering,iZoneId,dLicenseDate,szImlBpCode,iTownId,iNewApplicant)
VALUES
(@szCode, @szName, @szCompPostalAdd, @szCompStreet, @szCompTel1, @szCompTel2, @szFax, @szCompEmailAdd, @szCompWebSite,
@szContPersonName, @szContactPhone1, @szContactPhone2, @szContactPhone3, @szContEmail, @iUserId, @iStatus, @iConcurrency,
@iCompanyId, @szType, @szContactDesignation, @szCertificateOfIncorporation, GETDATE(), GETDATE(), @iTypeOfBunkering,
@iZoneId, @dLicenseDate, @szImlBpCode, @iTownId,@iNewApplicant)
SELECT SCOPE_IDENTITY() AS NewApplicant;
END
-Here is the second procedure:
CREATE PROC [dbo].[NPAAddApplication]
@iApplicantId bigint,
@iStageId bigint,
@iApplicationTypeId bigint,
@iUserId bigint,
@szTransId varchar(25)
AS
BEGIN
EXEC spGenTransCode 'APP', @retval = @szTransId OUTPUT
INSERT INTO [dbo].[psApplication]
(
iApplicantId,
dAppdate,
iStageId,
dStampdate,
iApplicationTypeId,
iUserId,
szTransId
)
VALUES
(
@iApplicantId,
GETDATE(),
1,
GETDATE(),
@iApplicationTypeId,
@iUserId,
@szTransId
)
SELECT @szTransId as NewApplicationTransaction
END
Now I have a have a web api Post method that helps me load data in both tables psApplicant and psApplication at the same time but the iApplicantId of the psApplication table will automatically pick the pkId of the psApplicant table. Correct. My c# code in the api is as follows:
public long PostApplicants(mApplicant applicanto)
{
mApplicant newApplicant = new mApplicant();
newApplicant.szName = applicanto.szName;
newApplicant.szCode = applicanto.szCode;
newApplicant.szCompPostalAdd = applicanto.szCompPostalAdd;
newApplicant.szCompStreet = applicanto.szCompStreet;
newApplicant.szCompTel1 = applicanto.szCompTel1;
newApplicant.szCompTel2 = applicanto.szCompTel2;
newApplicant.szFax = applicanto.szFax;
newApplicant.szCompEmailAdd = applicanto.szCompEmailAdd;
newApplicant.szCompWebSite = applicanto.szCompWebSite;
newApplicant.szContPersonName = applicanto.szContPersonName;
newApplicant.szContactPhone1 = applicanto.szContactPhone1;
newApplicant.szContactPhone2 = applicanto.szContactPhone2;
newApplicant.szContactPhone3 = applicanto.szContactPhone3;
newApplicant.szContEmail = applicanto.szContEmail;
newApplicant.iUserId = applicanto.iUserId;
newApplicant.iStatus = applicanto.iStatus;
newApplicant.iConcurrency = applicanto.iConcurrency;
newApplicant.iCompanyId = applicanto.iCompanyId;
newApplicant.szType = applicanto.szType;
newApplicant.szContactDesignation = applicanto.szContactDesignation;
newApplicant.szCertificateOfIncorporation = applicanto.szCertificateOfIncorporation;
newApplicant.iTypeOfBunkering = applicanto.iTypeOfBunkering;
newApplicant.iZoneId = applicanto.iZoneId;
newApplicant.dLicenseDate = applicanto.dLicenseDate;
newApplicant.szImlBpCode = applicanto.szImlBpCode;
newApplicant.iTownId = applicanto.iTownId;
newApplicant.iNewApplicant = applicanto.iNewApplicant;
var ObjApplicant = applicanto.MyApplications as List<mApplications>;
try
{
NPAEntities contextApp = new NPAEntities();
var newCrit = contextApp.NPAAddApplicant(newApplicant.szName, newApplicant.szCompPostalAdd, newApplicant.szCompStreet,
newApplicant.szCompTel1, newApplicant.szCompTel2, newApplicant.szFax, newApplicant.szCompEmailAdd, newApplicant.szCompWebSite,
newApplicant.szContPersonName, newApplicant.szContactPhone1, newApplicant.szContactPhone2, newApplicant.szContactPhone3,
newApplicant.szContEmail, newApplicant.iUserId, newApplicant.iStatus, newApplicant.iConcurrency, newApplicant.iCompanyId,
newApplicant.szType, newApplicant.szContactDesignation, newApplicant.szCertificateOfIncorporation, newApplicant.iTypeOfBunkering,
newApplicant.iZoneId, newApplicant.dLicenseDate, newApplicant.szImlBpCode, newApplicant.iTownId, newApplicant.iNewApplicant).FirstOrDefault();
int retPkid = Convert.ToInt16(newCrit.NewApplicant);
int pkid = Convert.ToInt32(retPkid);
string tblName = "psApplicant";
if (pkid > 0)
{
try
{
foreach (mApplications applicats in ObjApplicant)
{
mApplications newAppls = new mApplications();
newAppls.pkId = applicats.pkId;
newAppls.iApplicantId = pkid;
newAppls.iStageId = applicats.iStageId;
newAppls.iApplicationTypeId = applicats.iApplicationTypeId;
newAppls.iUserId = applicats.iUserId;
newAppls.szTransId = applicats.szTransId;
NPAEntities context2 = new NPAEntities();
var ret = context2.NPAAddApplication(newAppls.iApplicantId, newAppls.iStageId,
newAppls.iApplicationTypeId, newAppls.iUserId, newAppls.szTransId);
}
return ObjApplicant.Count();
}
catch (Exception ex)
{
NPAEntities context = new NPAEntities();
**context.NPAGenericDeleteValue(tblName, pkid);**
return -1;
}
}
}
catch (Exception)
{
return -1;
}
return ObjApplicant.Count();
}
I am using Web API swagger. when i run it, the first table is being populated and the second table is not being populated. I then put a breakpoint on the following line of code and run the code again:
var ret = context2.NPAAddApplication(newAppls.iApplicantId, newAppls.iStageId,
newAppls.iApplicationTypeId, newAppls.iUserId, newAppls.szTransId);
Now I got an error here:
return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<NPAAddApplication>("NPAAddApplication", iApplicantIdParameter, iStageIdParameter, iApplicationTypeIdParameter, iUserIdParameter, szTransIdParameter);
saying:
{"The data reader is incompatible with the specified 'NPAModel.NPAAddApplication'. A member of the type, 'NewApplicationTransaction', does not have a corresponding column in the data reader with the same name."}
The response body is -1 and the response code is 200.
Normally the response body should be 1 and the response code is 200.
The following line of code is what is preventing data from saving in the first table if there is an error with the second insert statement
context.NPAGenericDeleteValue(tblName, pkid);
Commenting that line out will save data in both tables with response body is -1 and response code is 200
So any idea about it?
Aucun commentaire:
Enregistrer un commentaire