help me with this queries,,

Question

0.00/5 (No votes)

See more:

Hi,,

I'm stuck in this situation two days ago and I hope to help me in solving it ..
i have 3 tables ..
stages >stag_Id,stage_name
tests >test_Id,stage_Id
results >player_Id,test_Id,mark

I'm trying to do two queries ..
First .. Show "stage name" tested by the player ..
Second .. the choice of the previous phases show the results of these tests
I've written the first but does not work properly where it appears only one phase and be the first always

SQL

"SELECT STAGES.stage_ID,STAGES.stage_name,TESTS.test_ID from STAGES INNER JOIN TESTS ON STAGES.stage_ID=TESTS.stage_ID INNER JOIN RESULTS ON TESTS.test_ID=RESULTS.test_ID"

Edit
My question is ..
I want to query about the stages that tested by a specific player
Then query for this test mark

source code:https://skydrive.live.com/redir.aspx?cid=20b9c18cb92126e5&resid=20B9C18CB92126E5!288&parid=root[^][]

plz help

Posted 6-May-12 22:54pm

TageTage

Updated 7-May-12 11:14am

Maciej Los

v4

Add a Solution

Comments

deeptibansal 7-May-12 5:04am

Not able to understand your question, please improve your question.

TageTage 7-May-12 8:11am

actually,I did in the beginning of the question

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2012-05-06T23:25:00

Solution 1

Try this:
Stage name tested by all players

SQL

SELECT R.player_id, T.test.id, S.stage_name, R.mark
FROM RESULTS AS R
    INNER JOIN TESTS AS T ON R.test_id=T.test_ID
    INNER JOIN STAGES AS S ON S.stage_Id=T.stage_Id

Stage name tested by selected player
Use the same query as above and

SQL

WHERE R.player_id = @player_id

replace @player_id with correct value

Posted 6-May-12 23:25pm

Maciej Los

Comments

TageTage 7-May-12 9:56am

hi losmac,,
i tried it and it show me this excption [Failed to convert parameter value from a DataRowView to a Int32] i don't know how can i fix it
this is my all code,,

adapter = new SqlDataAdapter("SELECT R.player_id, T.test.id, S.stage_name, R.mark
FROM RESULTS AS R
INNER JOIN TESTS AS T ON R.test_id=T.test_ID
INNER JOIN STAGES AS S ON S.stage_Id=T.stage_Id WHERE R.player_id = @player_id"
adapter.SelectCommand.Parameters.Add("player-id", SqlDbType.Int, 0).Value = listBox.SelectedValue;
dt = new DataTable();
adapter.Fill(dt);
comboBox1.DataContext = dt.DefaultView;

Maciej Los 7-May-12 10:35am

Parameters name is: @player_id
And you adding: "player-id"
Do you see the difference?
More at: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx#Y114

TageTage 7-May-12 10:56am

Forgot to write it here
I do add it in the code
Can I put an example source code here to help illustrate the problem better?

Maciej Los 7-May-12 11:11am

Yes, use: "Impove question" button.

Maciej Los 7-May-12 14:02pm

I've downloaded your source code... Sorry, i can't help you because of 2 reasons: 1) my Visual Studio works only with VB, 2) i can't attach your database into my SQL Sever 2005 Express.
Try to replace SqlDataAdapter with sqldatareader object and datatable.load method or take a look here: http://jesseliberty.com/2008/04/04/tutorial-data-from-a-sql-db-via-wcf-using-linq/

TageTage 7-May-12 15:24pm

thank you very much mr.losmac

Maciej Los 7-May-12 15:47pm

You're welcome ;)
Dear TageTage, upgrade your question and add tag: WPF.
Did you check the link to the jesseliberty site? Was it helpful? If yes, please rate my answer.

TageTage 7-May-12 18:57pm

Yes,it's about Linq,WindowsPhone..
Unfortunately i did not find what I want in it.