Comments by Member 14800672

Member 14800672 28-Sep-21 16:32pm View

Hi Richard,
The where statement was just to specify what to insert

But does this statement insert multiple records.. As you can see i need to copy 3 recrods from table1 in old db to new db

Member 14800672 10-Aug-21 6:58am View

Hi Richard I updated the question in what i have tried section

I don't want to insert only the first two. i need to insert records in the table based on the number of items inside the string split (please note that @First and @Second will always contain equal counts, i mean if there is 5 values in @First, then there will be 5 different values in @Second)

Member 14800672 10-Aug-21 4:24am View

Hi Anand,
i want to compare dateTime to dateTime and not date to DateTime
so i need the value before get< getutcdate() to be a dateTime and not a date

Member 14800672 10-May-21 9:58am View

Hi,

I've tried this
select cast((select count(*) From MyTable
where Id =@Id and Flag= 1) as float) / cast((select count(*) From MyTable
where Id =@Id ) as float) *100

but now i'm getting value like
66.6666666666667
How can i make it 66.7?

and then how to decide this based on the value retrieved
if value <= 20 return bad
else if value >20 and <=40 return poor
else if value >40 and <=60 return good
else if value >60 and <=80 return good
else if value >80 and <=100 return perfect

I want to get 66.7% and good from my stored procedure

Member 14800672 10-Oct-20 7:47am View

I did this

create PROCEDURE [dbo].[sp]
@Id1 int
,@BBId int
,@Text nvarchar(100) =''
AS
Begin
Begin transaction
declare @Id int

INSERT INTO table1
select @Text where @Text <> ''

SET @Id = SCOPE_IDENTITY()

INSERT INTO table2 (Id1, @Id)
select @Id1,@Id from table3
where table3.BBId = @BBId AND table3.Flag= 0 and
table3.Id1 = @Id1

IF(@@ROWCOUNT <= 0)
BEGIN

rollback transaction;
return
END
else
begin
UPDATE table3
SET Flag= 1
WHERE
table3.BBId = @BBID
AND table3.Id1 = @Id1
end
COMMIT TRANSACTION
END
Return 0

when i run it in SQL i get no errors, however when i call the stored procedure from .Net i get this error:
"Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.\r\nTransaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0."

Member 14800672 10-Oct-20 7:46am View

I did this

create PROCEDURE [dbo].[sp]
@Id1 int
,@BBId int
,@Text nvarchar(100) =''
AS
Begin
Begin transaction
declare @Id int

INSERT INTO table1
select @Text where @Text <> ''

SET @Id = SCOPE_IDENTITY()

INSERT INTO table2 (Id1, @Id)
select @Id1,@Id from table3
where table3.BBId = @BBId AND table3.Flag= 0 and
table3.Id1 = @Id1

IF(@@ROWCOUNT <= 0)
BEGIN

rollback transaction;
return
END
else
begin
UPDATE table3
SET Flag= 1
WHERE
table3.BBId = @BBID
AND table3.Id1 = @Id1
end
COMMIT TRANSACTION
END
Return 0

when i run it in SQL i get no errors, however when i call the stored procedure from .Net i get this error:
"Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.\r\nTransaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0."

Member 14800672 5-Oct-20 3:17am View

Hi OriginalGriff,

this error happens, when it reaches else ( i mean if @@rowCount is less than 0)
I tried adding try and catch in code below but still same error. Any idea?

create PROCEDURE [dbo].[sp]
@Id1 int
,@BBId int
,@Text nvarchar(100) =''
AS
Begin
begin try

Begin transaction
declare @Id int

INSERT INTO table1
select @Text where @Text <> ''

SET @Id = SCOPE_IDENTITY()

INSERT INTO table2 (Id1, @Id)
select @Id1,@Id from table3
where table3.BBId = @BBId AND table3.Flag= 0 and
table3.Id1 = @Id1
if(@@rowcount>0)
begin
UPDATE table3
SET Flag= 1
WHERE
table3.BBId = @BBID
AND table3.Id1 = @Id1
COMMIT TRANSACTION
end
else
rollback transaction
end try
begin catch
SELECT ERROR_MESSAGE()

ROLLBACK

end catch

END
Return 0

Member 14800672 4-Oct-20 3:56am View

insert on table2 wouldn't fail as an error, but 0 records are inserted. Maybe @Id1 is wrong... So if @Id1 is wrong, it will insert into table1, it won't insert into table2 or update table3. So no errors here my catch won't take anything. But What i want is that if i don't insert anything into table2 then i don't want the insert into table1 to happen at all

Member 14800672 4-Oct-20 1:38am View

It depends on table1... I take @Id from table1 and use it in table2... So if 0 records are inserted in table2 i dont want the insert to happen in table1

Member 14800672 12-Aug-20 4:40am View

how can i use the while with the table variable i created?

Member 14800672 9-Aug-20 6:25am View

Here's my stored procedure
(
select field1 *from table2 where Id =@Id -- result 1
select count(field1) from table2 where Id=@Id and Id2= @Id1 -- result 2
)

So now i want to say in a new stored procedure
Insert into table1 select from table2 where Id=@Id and Id2= @Id1 and (result 1 - result 2=0)

Member 14800672 6-Aug-20 12:53pm View

Thanks aloot

Member 14800672 6-Aug-20 10:34am View

Thanks alot Chill60, it was very helpful, would you mind looking at this?
it's some how similar
https://www.codeproject.com/Questions/5275815/How-to-insert-more-than-one-record-in-a-new-table

Member 14800672 6-Aug-20 5:29am View

Hi Chill,

what my stored procedure does
is inserting into two tables using the same Id and delete from 2 other tables using the same Id

create procedure doLogic
@Id int

Insert into table1(Id) values @Id
Insert into table2(Id) values @Id

delete from table3 where Id = @Id
delete from table4 where Id = @Id

Return 0

that's why i was thinking i should loop and insert each Id, what do u think is the best thing to do i my case?

and can u explain the function example you mentioned more? how to replace it with the select that returns all records? that i need to pass to the stored procedure?

Member 14800672 9-Jun-20 17:41pm View

Thanks.. It is working now.. But can i do it based on the query string of the url. I mean if i have id parameter in the url.. Then when i click addY for id=1 i want to invalidate the cache for getY with parameter id=1 not other ids

Member 14800672 16-May-20 14:04pm View

Can you help in getting the desired output?

Member 14800672 16-May-20 12:01pm View

using the the first code, it's not working

var data = new[]
{
new{ID=1, Day="sun"},
new{ID=3, Day="tue"},
new{ID=5, Day="thu"},
new{ID=7, Day="Sat"}
};
if the data is like this i only get tue and sat..while i should get Sun,Tue,thu,Sat ( i want them separated by comment not new line)

also if the data is
var data = new[]
{
new{ID=1, Day="sun"},
new{ID=2, Day="mon"},
new{ID=3, Day="tue"},
new{ID=7, Day="Sat"}
};

i should get Sun-Tue,Sat

Member 14800672 12-May-20 11:03am View

I added the fields types in the question
String Day; example: Sunday
Int id; example: 1
String time; example: 9:00 to 10:00

Member 14800672 2-May-20 16:41pm View

Thank you so much, i tried left join instead of full join and it worked as well. Which is better and did you use full join for a reason?

Member 14800672 26-Apr-20 7:59am View

i mean i don't want to call it using https/url/api i only want to call it directly like this /api/..
this is not doable?

Member 14800672 22-Apr-20 14:51pm View

thank you so much
but now in controller class how i'm going to get each one, right now the two lists are retrieved but what to write in controller to deal with each one of them?

Member 14800672 22-Apr-20 8:01am View

what is the return type?

Member 14800672 22-Apr-20 7:55am View

Deleted

what is mergeOptions.. i'm getting an error there plus i'm getting an error in context.connection.open()

and what is the return type?

Member 14800672 20-Apr-20 16:26pm View

I will try ur query now(what does select 1 mean, 1 refers to what?) I do have a table called PlaceLoc how can i use it in my query to get the correct result without distinct?

Member 14800672 20-Apr-20 14:59pm View

I do have a table that has both placeId and loc called placeLoc where exactly should i add this in our code?

Member 14800672 20-Apr-20 13:39pm View

Is there a way to do it without distinct and still not getting duplicated records?

Member 14800672 20-Apr-20 13:31pm View

GroupFirst does the comma seperation in days and it groups by another field
So it was first like
Sunday, Monday
Sunday, Monday
Wednesday, thursday
And the groupLast now groups by day to not get repeated values so
Sunday, Monday
Wed, Thursday
So day here is a field that comes from DB, that's why i made an enum to compare with it so will u be able to help now?

Member 14800672 20-Apr-20 11:47am View

Can you explain more, what i didn't get the bits and enum, can you show me how to do it?

the comma separated comes from this
var groupLast = groupFirst
.GroupBy(x => new { x.Day })
.Select(g => new
{
Day = g.Key.Day,
groupedBy = g
});
This group returns two objects:
Day = Sun,Mon
Day = wed,thur

i want to return Tuesday,Friday and Saturday

Member 14800672 20-Apr-20 8:43am View

Thanks alot.
If i have variable (var)that has 2 objects
where the day equal concatenated value
[0] Sunday,Monday
[1] Wednesday,Thursday

I want to apply your solution on it
string[] allDays = Enum.GetNames(typeof(WeekDays));
var missing = allDays.Except(days);

so i want put in allDays.Except() all the days in the variable i mentioned above, how to do so?

Member 14800672 20-Apr-20 6:23am View

Perfect.. Thank you :D

Member 14800672 20-Apr-20 4:56am View

yes i mean what is not used for PlaceId and Loc Combined. (i updated the question). Can you please help?

Member 14800672 17-Apr-20 8:35am View

Hi,

yes i read it but it doesn't specify anything

Here's the URL i tried: https://localhost:****/api/Customer?Id=1&num1=1&num2=1.2/
but i'm getting this error
No HTTP resource was found that matches the request URI

However it works if i use slashes: https://localhost:****/api/Customer/Id/1/num1/1.1/num2/3.2/

Here's the route for my method:
[HttpGet]
[Route("api/Customer/{Id}/{num1}/{num2}/", Name = "GetCustomers")]
public IHttpActionResult GetCustomers(int Id, double num1, double num2)
{
}

And here's my register method:
public static void Register(HttpConfiguration config)
{
// Web API configuration and services

// Web API routes
config.MapHttpAttributeRoutes();

config.Routes.MapHttpRoute(
name: "DefaultApi",
routeTemplate: "api/{controller}/{Id}/{num1}/{num2}",
defaults: new {
Id = RouteParameter.Optional,
num1= RouteParameter.Optional,
num2= RouteParameter.Optional
}
);

}

Member 14800672 17-Apr-20 0:10am View

i think i need to do
Number number = new Number(group.groupedBy.First().num1,group.groupedBy.First().num2);

btw what is the benefit of .Select i made in the query? i still can access Id1 and Id2 without it.. in that case i will put group.First().num1 or any other field so when should i put .Select in query?

Member 14800672 16-Apr-20 23:56pm View

Table has Id1,Id1,Num1,Num2 fields
var query = Table
.GroupBy(x => new { x.Id1, x.Id2 })
.Select(g => new
{
Id1 = g.Key.Id1,
Id2 = g.Key.Id2,
groupedBy = g

})
foreach (var group in query)
{
// number class takes two double values
Number number = new Number(group.num1, group.num2); // i can't get to num1 and num2
}

i tried this but still i have error
group.groupedBy.Select(x=> x.num1)
the error : can't convert from Ienumrable<double> to double

Member 14800672 16-Apr-20 16:33pm View

As i said query has object[0] which has two fields Id=1,name =name1and it also has object[1] which has Id=2,name=name2
Now when i write
Foreach(var x in query)
{
// i want to store the names
// x. What?
String value += x.
}

Member 14800672 16-Apr-20 5:11am View

what if there was two other records
IdFirst=3, IdSecond=3, Name=name31
IdFirst=1, IdSecond=2, Name=name1221

this should be there result:
IdFirst=1, IdSecond=1, Name=name11,name12
IdFirst=1, IdSecond=2, Name=name1221
IdFirst=2, IdSecond=2, Name=name21,name22
IdFirst=3, IdSecond=3, Name= name31

how i'm not going to string join these two? i just want to join related ones

Member 14800672 16-Apr-20 4:20am View

can u help me in applying this to my code

Member 14800672 15-Apr-20 13:37pm View

Just tried it it's not working

Member 14800672 15-Apr-20 13:06pm View

I edited the typos and the response that i am currently getting ( i think i was mistaken when i first wrote it),
If you can see the edited one, i'm getting a new record with id=1 four times Hence (hour is repeated 4 times) instead of 1 hour and inside it are the different timings. So i'm not sure if this will affect the solution you provided.

I think i need to group by Id. so please help.

I will look at ur reply now and see if it will work

Comments by Member 14800672 (Top 39 by date)