Last post Feb 23, 2015 11:07 AM by skliz4rel
Feb 23, 2015 05:51 AM|skliz4rel|LINK
I have a stored procedure which I have written. I am trying to collecting multiple result set from it. But unfortunately I am getting an error SqlExceptionError.
I have tried all I can but don't know were the error is, I have tested my Stored procedure it works fine.
Code is below
CREATE PROCEDURE [dbo].[Collectcompanyservicewithpackages]
select * from Serviceduration where Client_CompanyID = @CompanyID;
select Serviceitem.* from Serviceduration, Serviceitem
where Client_CompanyID = @CompanyID and Serviceduration.ServicedurationID = Serviceitem.ServicedurationID;
select ServiceitemPackage.* from Serviceduration, Serviceitem, ServiceitemPackage
where Client_CompanyID = @CompanyID and Serviceduration.ServicedurationID = Serviceitem.ServicedurationID and Serviceitem.ServiceitemID = ServiceitemPackage.ServiceitemID
Database Modules below
Debug.WriteLine("App got here for starters .........................");
// If using Code First we need to make sure the model is built before we open the connection
// This isn't required for models created with the EF Designer
// Create a SQL command to execute the sproc
var cmd = context.Database.Connection.CreateCommand();
cmd.CommandText = "Collectcompanyservicewithpackages @CompanyID";
cmd.CommandType = CommandType.StoredProcedure;
DbParameter inputparameter = new SqlParameter();
inputparameter.DbType = DbType.Int64;
inputparameter.ParameterName = "CompanyID";
inputparameter.Value = CompanyID;
inputparameter.Direction = ParameterDirection.Input;
// Run the sproc
var reader = cmd.ExecuteReader();
// Read Blogs from the first result set
var durations = ((IObjectContextAdapter)context)
.Translate<Serviceduration>(reader, "Servicedurations", MergeOption.AppendOnly);
Feb 23, 2015 11:07 AM|skliz4rel|LINK
I finally solved it after 4 hours of battle. Men it not easy to be a programmer oooooo. Damn !!!!!!!!!!!!!!!.
cmd.CommandText = "Collectcompanyservicewithpackages";
You can trace above and you see difference. This is the suppose correct version. I removed @CompanyID.
and I closed the command obj with the reader obj. It works fine now. Gotten experience on multiple SP.