Database

26-Jun-06 10:53

[Message Deleted]

Colin Angus Mackay27-Jun-06 4:33

27-Jun-06 4:33

Since you have not mentioned which technology you are using it might be difficult for people to answer you.

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

PyroManiak27-Jun-06 6:11

27-Jun-06 6:11

What do you mean technology? I'm writing this in C# using the data adapters and all that built into the IDE.

Colin Angus Mackay27-Jun-06 12:27

27-Jun-06 12:27

PyroManiak wrote:
What do you mean technology? I'm writing this in C# using the data adapters and all that built into the IDE.

That's helpful - You never mentioned any of this in your original post.

Colin Angus Mackay27-Jun-06 12:47

27-Jun-06 12:47

I know that isn't really much help to you - but your query shows your skill level and the answer pitched at that skill level is too long for a forum response.

My general advice is to not rely on wizards and binding until you understand how database interaction works. Then you can decide if wizards and binding are for you (I rejected them because they push me in to really bad designs - they are great for quick demos are conferences to woo the managers, but as a serious tool there is a long way to go yet.)

I'd say that the best advice for you is to get a book on ADO.NET and find out how database interaction actually works rather than rely on wizards and binding.

As a high level answer:

PyroManiak wrote:
new form loads (should be on current record, not record 1 which requires the user to navigate back and forth to find the right item.)

From your list box you will know what row you picked. You can use that information when populating the new form in order to get the correct row from the database. I don't know where these back and forward buttons come from. I'd guess it is some crazy wizard (hateful things so they are) that did that for you because it would require a fair bit of skill to write that sort of functionality - based on your question I'm guessing that is currently beyond your ken at the moment.

PyroManiak wrote:
I can't figure out how to do this without editing the SQL query to accept a parameter.

I'm assuming you mean parameter on the query - You will always need that if you want only one specific row. The parameter (or parameters) will define the value of the primary key for the row that you want. There is no way of getting around that unless you plan to pull across the entire table (and that is a waste of memory and network bandwidth)

PyroManiak27-Jun-06 12:58

27-Jun-06 12:58

What I meant by using a parameter is if I search for "part A" when the new form opens, it only populates the datatable with part a being record 1/1. This doesn't help me when I'm trying to add a new record or delete the current record because the actual table row is n/###.

I'd appreciate an answer reguardless of how complicated it is. I understand the basics of ADO.NET, but that doesn't help when a function I'm looking for doesn't exist by any means I'd think to look for it. Thats no better then asking someone to make a Caesar Salad dressing, and having them screw it up because they didn't know to include anchovy paste, its not their fault for not knowing what isn't there, but that also doesn't mean that they are clueless in cooking.

PyroManiak27-Jun-06 13:08

27-Jun-06 13:08

I found what I was looking for... there is a .position for the bindingsource and I was able to pass my variable to it and everything works now.

Was that really going to be such a hard answer?

Colin Angus Mackay27-Jun-06 13:27

SQL Server Function Call [modified]

27-Jun-06 13:27

PyroManiak wrote:
Was that really going to be such a hard answer?

From your description so far I would never have guessed.

tadhg8826-Jun-06 3:16

26-Jun-06 3:16

Hi i am having trouble tryin to call a function 4 times in the same statement but with different parameters this is the function

CREATE FUNCTION [dbo].[pr_bll_GetGoalkeeperMarks]
(@PlayerID int,
@Ability nvarchar(120)) returns int
--WITH ENCRYPTION
BEGIN
DECLARE @result int
select @result = case @Ability
WHEN 'Athletic' THEN ScoutingGoalkeeperAthleticAbility.Agility+ScoutingGoalkeeperAthleticAbility.Reactions+ScoutingGoalkeeperAthleticAbility.Bodystrength --Athletic
WHEN 'Character' THEN ScoutingGoalkeeperCharacter.Confidence+ScoutingGoalkeeperCharacter.Bravery+ScoutingGoalkeeperCharacter.Leadership+ScoutingGoalkeeperCharacter.Energetic+ScoutingGoalkeeperCharacter.RiskTaker+ScoutingGoalkeeperCharacter.Talkative+ScoutingGoalkeeperCharacter.Competitive+ScoutingGoalkeeperCharacter.Presence --Character
WHEN 'Tactical' THEN ScoutingGoalkeeperTacticalAbility.Vision+ScoutingGoalkeeperTacticalAbility.Awareness+ScoutingGoalkeeperTacticalAbility.Anticipation+ScoutingGoalkeeperTacticalAbility.StartingPosition --Tactical
WHEN 'Technical' THEN ScoutingGoalkeeperTechnicalAbility.Handling+ScoutingGoalkeeperTechnicalAbility.Crosses+ScoutingGoalkeeperTechnicalAbility.ShotStopping+ScoutingGoalkeeperTechnicalAbility.KickingDeadBall+ScoutingGoalkeeperTechnicalAbility.KickingHands+ScoutingGoalkeeperTechnicalAbility.Distribution+ScoutingGoalkeeperTechnicalAbility.BackPasses+ScoutingGoalkeeperTechnicalAbility.Positioning+ScoutingGoalkeeperTechnicalAbility.CommandOfArea+ScoutingGoalkeeperTechnicalAbility.Sweeping+ScoutingGoalkeeperTechnicalAbility.ComingOffLine --Technical
end
from
ScoutingReport sr
Join Person p on p.Id = sr.ScoutID
left join ScoutingDefenderTechnicalAbility on ScoutingDefenderTechnicalAbility.ScoutingReportID = sr.Id
left join ScoutingForwardTechnicalAbility on ScoutingForwardTechnicalAbility.ScoutingReportID = sr.Id
left join ScoutingGoalkeeperAthleticAbility on ScoutingGoalkeeperAthleticAbility.ScoutingReportID = sr.Id
left join ScoutingGoalkeeperCharacter on ScoutingGoalkeeperCharacter.ScoutingReportID = sr.Id
left join ScoutingGoalkeeperTacticalAbility on ScoutingGoalkeeperTacticalAbility.ScoutingReportID = sr.Id
left join ScoutingGoalkeeperTechnicalAbility on ScoutingGoalkeeperTechnicalAbility.ScoutingReportID = sr.Id
left join ScoutingMidfielderTechnicalAbility on ScoutingMidfielderTechnicalAbility.ScoutingReportID = sr.Id
left join ScoutingOutfieldAthleticAbility on ScoutingOutfieldAthleticAbility.ScoutingReportID = sr.Id
left join ScoutingOutfieldCharacter on ScoutingOutfieldCharacter.ScoutingReportID = sr.Id
left join ScoutingOutfieldTacticalAbility on ScoutingOutfieldTacticalAbility.ScoutingReportID = sr.Id
where
sr.ScoutedPersonId = @PlayerId and
sr.type=0
return @result
END

playerid and ability parameters are passed in and a count of that ability for that player is passed back it works fine when the statement doesnt try to get the average of each ability but when this is included the results are the same for each of the four calls im just wondering if anyone has any idea why this is happenin thanks in advance

select avg(dbo.pr_bll_GetGoalkeeperMarks(scoutedPersonID, 'Athletic')) as 'Athletic',
avg(dbo.pr_bll_GetGoalkeeperMarks(scoutedPersonID, 'Character')) as 'Character',
avg(dbo.pr_bll_GetGoalkeeperMarks(scoutedPersonID, 'Tactical')) as 'Tactical',
avg(dbo.pr_bll_GetGoalkeeperMarks(scoutedPersonID, 'Technical')) as 'Technical',
avg(dbo.pr_bll_GetPlayerTotalMarks(scoutedPersonID))
from scoutingreport sr
Where sr.type = 0
And ScoutId = 11619
And sr.Position in (Select ID From ScoutingPosition Where PositionGroup = 0)

-- modified at 6:45 Wednesday 28th June, 2006

Colin Angus Mackay27-Jun-06 4:37

27-Jun-06 4:37

I notice you have WITH ENCRYPTION on your definition (presently commented out). In general this is a very bad idea from a maintenance point of view. Unless this is part of a commercial application that will be sold to many people I don't recomment using WITH ENCRYPTION because it serves as definitive answer to the question: What the hell is my function/stored procedure doing?

tadhg8827-Jun-06 5:06

27-Jun-06 5:06

yes but it is commented out so i presume its havin no effect

Colin Angus Mackay27-Jun-06 5:12

27-Jun-06 5:12

tadhg88 wrote:
but it is commented out so i presume its havin no effect

That is correct. It will have no effect. However, you comment code out because you intend to put it back later - otherwise you should just delete the superflous stuff.

tadhg8827-Jun-06 6:00

27-Jun-06 6:00

ya thanks for the advice but that doesnt really help me with my problem does it

Eric Dahlvang27-Jun-06 9:19

27-Jun-06 9:19

tadhg88 wrote:
ya thanks for the advice but that doesnt really help me with my problem does it

If you're looking for help, politeness goes a long way - rudeness gets nowhere.

Colin Angus Mackay knows as much about SQL as anyone I've seen on The Code Project. I'd be nice to him if I were you.

----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Colin Angus Mackay27-Jun-06 12:25

27-Jun-06 12:25

tadhg88 wrote:
ya thanks for the advice but that doesnt really help me with my problem does it

That's why I marked the subject as "OT" (Off Topic) because so that others would know that I didn't answer the question.

Sorry, but I've been quite busy the last couple of days and not had time to concentrate enough on your code to read it through and determine the problem for you.

If Eric's solution doesn't work out and I have more time to look more in depth at your problem then I can maybe help - but I'm too busy right now to help on the complicated questions. (Sorry that doesn't help you either)

tadhg8828-Jun-06 0:01

28-Jun-06 0:01

my apologies i read my post again today and it was a little rude so thanks for takin the time to even try to help IT IS appreciated

Eric Dahlvang27-Jun-06 9:13

27-Jun-06 9:13

Have you tried to Group By scoutedPersonID?

Also, you have 'Tactical' as the second parameter for both the Athletic and Tactical columns. I assume that is not your intention.

----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

tadhg8828-Jun-06 0:44

28-Jun-06 0:44

if i group by scoutedpersonid it will give me averages for each player but i want all players that were scouted by a specific scout and get the average of these

Eric Dahlvang28-Jun-06 4:02

28-Jun-06 4:02

I'm not sure I understand the need for a function in this case. Couldn't you just make a view called vewScoutTotals:

select  sr.ScoutedPersonId, sr.ScoutID, ScoutingGoalkeeperAthleticAbility.Agility +ScoutingGoalkeeperAthleticAbility.Reactions <br />
+ScoutingGoalkeeperAthleticAbility.Bodystrength as Athletic,<br />
ScoutingGoalkeeperCharacter.Confidence +ScoutingGoalkeeperCharacter.Bravery +ScoutingGoalkeeperCharacter.Leadership +ScoutingGoalkeeperCharacter.Energetic +ScoutingGoalkeeperCharacter.RiskTaker +ScoutingGoalkeeperCharacter.Talkative +ScoutingGoalkeeperCharacter.Competitive +ScoutingGoalkeeperCharacter.Presence as Character, ScoutingGoalkeeperTacticalAbility.Vision +ScoutingGoalkeeperTacticalAbility.Awareness +ScoutingGoalkeeperTacticalAbility.Anticipation +ScoutingGoalkeeperTacticalAbility.StartingPosition as Tactical,<br />
ScoutingGoalkeeperTechnicalAbility.Handling +ScoutingGoalkeeperTechnicalAbility.Crosses +ScoutingGoalkeeperTechnicalAbility.ShotStopping +ScoutingGoalkeeperTechnicalAbility.KickingDeadBall +ScoutingGoalkeeperTechnicalAbility.KickingHands +ScoutingGoalkeeperTechnicalAbility.Distribution +ScoutingGoalkeeperTechnicalAbility.BackPasses +ScoutingGoalkeeperTechnicalAbility.Positioning +ScoutingGoalkeeperTechnicalAbility.CommandOfArea +ScoutingGoalkeeperTechnicalAbility.Sweeping +ScoutingGoalkeeperTechnicalAbility.ComingOffLine as Technical<br />
from<br />
ScoutingReport sr<br />
Join Person p on p.Id = sr.ScoutID<br />
left join ScoutingDefenderTechnicalAbility on ScoutingDefenderTechnicalAbility.ScoutingReportID = sr.Id<br />
left join ScoutingForwardTechnicalAbility on ScoutingForwardTechnicalAbility.ScoutingReportID = sr.Id<br />
left join ScoutingGoalkeeperAthleticAbility on ScoutingGoalkeeperAthleticAbility.ScoutingReportID = sr.Id<br />
left join ScoutingGoalkeeperCharacter on ScoutingGoalkeeperCharacter.ScoutingReportID = sr.Id<br />
left join ScoutingGoalkeeperTacticalAbility on ScoutingGoalkeeperTacticalAbility.ScoutingReportID = sr.Id<br />
left join ScoutingGoalkeeperTechnicalAbility on ScoutingGoalkeeperTechnicalAbility.ScoutingReportID = sr.Id<br />
left join ScoutingMidfielderTechnicalAbility on ScoutingMidfielderTechnicalAbility.ScoutingReportID = sr.Id<br />
left join ScoutingOutfieldAthleticAbility on ScoutingOutfieldAthleticAbility.ScoutingReportID = sr.Id<br />
left join ScoutingOutfieldCharacter on ScoutingOutfieldCharacter.ScoutingReportID = sr.Id<br />
left join ScoutingOutfieldTacticalAbility on ScoutingOutfieldTacticalAbility.ScoutingReportID = sr.Id<br />
where sr.type=0 <br />
and sr.Position in (Select ID From ScoutingPosition Where PositionGroup = 0)

To get averages for everyone scouted by a particular scout:

select avg(athletic) as athletic,avg(character) as character,avg(tactical) as tactical<br />
from vewScoutTotals<br />
where ScoutID = 11619

----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

tadhg8828-Jun-06 5:48

28-Jun-06 5:48

yes that wud work but to be honest i just wanted to get a bit of experience with functions so i said id try and use them, ran into trouble and cud not understand why it wudnt work(still dont infact) but i guess i never will now

Eric Dahlvang28-Jun-06 5:59

28-Jun-06 5:59

tadhg88 wrote:
i just wanted to get a bit of experience with functions so i said id try and use them, ran into trouble and cud not understand why it wudnt work

Oh. Well, I too am mystified over the AVG() oddity. Sorry I have no answer for it. Maybe Colin can explain it to us when he gets a little more free time. Smile | :)

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

tadhg8829-Jun-06 4:10