How do I return specific value from database

Question

0.00/5 (No votes)

See more:

I have two labels on my page that needs to change according to which user is logged on.

First I want to retrieve the discipline name from the database. In my database I have a table called ATHLETE_MEETING_DISCIPLINE_RESULT which links to ATHLETE and MEETING_EVENTS.

The MEETING_EVENTS table is a linking table for MEETING and DISCIPLINE.

The email address is in the USER table which also links to ATHLETE_MEETING_DISCIPLINE_RESULT table

I am using the 3-tier method.

this method is in the data access layer where I tried to return the discipline name using a stored procedure.

C#

public string GetAthleteDiscipline(string email)
        {
            string disciplineName = null;
            string connString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
            //SqlConnection con = new SqlConnection(connString);
            try
            {
                //con.Open();
                using (SqlConnection con = new SqlConnection(CS))
                {
                    SqlCommand cmd = con.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "spGetAthleteDiscipline";
                    disciplineName = cmd.ExecuteNonQuery().ToString();
                }
            }
            catch (Exception)
            {

            }
            return disciplineName;
        }

In my business logic layer i have

C#

public string GetAthleteDiscipline(string emailAddress)
        {
            return db.GetAthleteDiscipline(emailAddress);
        }

On my presentation layer I have where I tried to set the label according to the result of my stored procedure

C#

lblDisciplineID.Text = bll.GetAthleteDiscipline(email)

What I have tried:

my stored procedure is as follows:

SELECT D.Name,AMDR.FinishingResult,AMDR.Placement
FROM DISCIPLINE As D,[USER] As U, MEETING_EVENTS As ME, ATHLETE_MEETING_DISCIPLINE_RESULT As AMDR
WHERE U.EmailAddress = @emailAddress
AND AMDR.MeetingDisciplineID = ME.MeetingDisciplineID
AND ME.DisciplineID = D.DisciplineID.

but the dicipline value I am trying to retrieve is always null or blank

Posted 7-Sep-16 22:48pm

KevinClaassens

Updated 7-Sep-16 23:51pm

v2

Add a Solution

4 solutions

Solution 2

Have you tried adding the parameter to the query?

cmd.Parameters["@emailAddress"].Value = email;

Posted 7-Sep-16 23:03pm

Wastedtalent

Comments

KevinClaassens 8-Sep-16 5:27am

Yes I have just tried to add the parameter and it doesnt work. I suspect that its the stored procedure

Solution 3

ExecuteNonQuery means run the query and ignore any results as you're not interested in them. Obviously that isn't what you want to do as you want the results back. ExecuteNonQuery is to be used for things like INSERT, UPDATE and DELETE that don't return data, not for SELECT queries.

Use ExecuteReader instead

SqlCommand.ExecuteReader Method (System.Data.SqlClient)[^]

Posted 7-Sep-16 23:06pm

F-ES Sitecore

Comments

KevinClaassens 8-Sep-16 6:13am

I changed my code to look like this now
public string GetAthleteDiscipline(string email)
{
string discipline = null;
string connString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
SqlConnection con = new SqlConnection(connString);

try
{

con.Open();
SqlCommand cmd = new SqlCommand("spGetAthleteDiscipline");

using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
discipline = (reader["Name"].ToString());
}
}
}
catch (Exception)
{

}
return discipline;
}
but discipline is still equals to null

F-ES Sitecore 8-Sep-16 8:56am

You need to learn to use the debugger, you can't always just look at code and come to a conclusion what the problem is.

If discipline is null then the ToString hasn't ran, so chances are your query is returning no data. You'd know this if you stepped through the code as the while loop will skip over. You aren't attaching the emailAddress param so your proc is never going to return data, and if you attach that and it still doesn't work then there is no data where u.EmailAddress satisfies the data you are passing in. We can't access your database so we can't suggest why that is, most likely the data just isn't there, or your table joins are wrong, only you can work these things out.

Solution 4

Can you check if your stored procedure is returning correct values if executed separately.

I feel the query on the SP is not proper. The relation between [USER] table and ATHLETE_MEETING_DISCIPLINE_RESULT is not provided. Can you check the below script.

SQL

SELECT * FROM DISCIPLINE d 
INNER JOIN MEETING_EVENTS me ON d.DisciplineID = me.DisciplineID
INNER JOIN ATHLETE_MEETING_DISCIPLINE_RESULT amdr ON me.MeetingDisciplineID = amdr.MeetingDisciplineID
INNER JOIN [USER] u ON u.UserId = amdr.UserId --This relation is missing
WHERE u.EmailAddress = @emailAddress --Put the email address here and see if it working then use it on the SP

Posted 7-Sep-16 23:51pm

NaibedyaKar

Comments

KevinClaassens 8-Sep-16 6:07am

Thank you so much. When executing the sp in SSMS it returns the correct results, but in in my code where I am trying to get the discipline in the discipline variable something goes wrong as the variable value stays at null

discipline = bll.GetAthleteDiscipline(email);

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)

OriginalGriff · Accepted Answer · 2016-09-07T23:02:00

Solution 1

Try a JOIN - I can't be specific without knowing teh table schema and teh column relationships, but if you have two tables Users and Addresses

Users      (ID, Name)
Addresses  (ID, UserID, Address)

Then

SQL

SELECT u.Name, a.Address
FROM Users u
JOIN Addresses a ON a.UserId = u.ID

Then that returns each user name with it's appropriate address - which sound like what you are trying to do with your SP.

EDIT:

OK - the first thing to notice is you have no ATHLETE entries!
You have USERs, but no ATHLETEs.
So anything where you try to use AthleteID is going to give problems.
And Alvin Green competed in the 100m and the Discus, not the 400m hurdles:

SQL

SELECT * FROM ATHLETE_MEETING_DISCIPLINE_RESULT a
JOIN DISCIPLINE d ON a.MeetingDisciplineID = d.DisciplineID

SELECT * FROM ATHLETE_MEETING_DISCIPLINE_RESULT
SELECT * FROM DISCIPLINE

Alvin is ID 18, which is discipline IDs 1 and 20.

So try this:

SQL

SELECT u.FirstName, u.LastName, d.Name, AMDR.FinishingResult, AMDR.Placement FROM ATHLETE_MEETING_DISCIPLINE_RESULT AMDR 
JOIN [USER] u ON AMDR.AthleteID = u.UserID
JOIN MEETING_EVENTS ME ON me.MeetingDisciplineID = AMDR.MeetingDisciplineID
JOIN DISCIPLINE d ON d.DisciplineID = AMDR.MeetingDisciplineID
WHERE U.EmailAddress = @emailAddress

Which gives

FirstName LastName  Name          FinishingResult Placement
Alvin     Green     100m          12.09           4
Alvin     Green     Discuss Throw 57.4            3

Posted 7-Sep-16 23:02pm

OriginalGriff

Updated 8-Sep-16 3:55am

v2

Comments

KevinClaassens 8-Sep-16 5:32am

I want to return a dicipline name. Since I am using the email as the primary key I thought I could use that. I have a Users table and in that table I have the email addresses of the users. Can I send you a picture of the database schema? I am having a hard time trying to explain what exactly I am struggling with. But i think its the stored procedure

OriginalGriff 8-Sep-16 5:42am

Don't use email as a primary key - people change emails addresses from time to time and it makes like complicated when they do. Use an auto increment ID value instead and use that as the "linking field" - it also saves a heck of a lot of DB space.
Don't send pictures, ever: copy and paste your schema instead. That way if people need to test anything, they don't have to type - help us to help you!

KevinClaassens 8-Sep-16 5:59am

This may be a very stupid question, but how do I copy and paste the schema?

OriginalGriff 8-Sep-16 6:18am

Open the DB in SSMS, open the Tables branch, and right click the table name.
From the context menu, select "Script Table as...CREATE To...Clipboard"
Then paste the definition where you need it.

KevinClaassens 8-Sep-16 6:29am

USE [XcellIT]
GO

/****** Object: Table [dbo].[ATHLETE_MEETING_DISCIPLINE_RESULT] Script Date: 2016/09/08 12:20:32 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ATHLETE_MEETING_DISCIPLINE_RESULT](
[AthleteMeetingDisciplineID] [int] IDENTITY(1,1) NOT NULL,
[AthleteID] [int] NULL,
[MeetingDisciplineID] [int] NULL,
[FinishingResult] [float] NULL,
[Placement] [int] NULL,
CONSTRAINT [PK_amr] PRIMARY KEY CLUSTERED
(
[AthleteMeetingDisciplineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [XcellIT]
GO

/****** Object: Table [dbo].[MEETING_EVENTS] Script Date: 2016/09/08 12:21:07 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MEETING_EVENTS](
[MeetingDisciplineID] [int] IDENTITY(1,1) NOT NULL,
[MeetingID] [int] NULL,
[DisciplineID] [int] NULL,
[DateTime] [smalldatetime] NULL,
[ConditionID] [int] NULL,
CONSTRAINT [PK_MEETING_EVENTS] PRIMARY KEY CLUSTERED
(
[MeetingDisciplineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [XcellIT]
GO

/****** Object: Table [dbo].[DISCIPLINE] Script Date: 2016/09/08 12:21:26 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[DISCIPLINE](
[DisciplineID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Description] [varchar](50) NULL,
[DisciplineType] [varchar](50) NULL,
CONSTRAINT [PK_DISCIPLINE1] PRIMARY KEY CLUSTERED
(
[DisciplineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

USE [XcellIT]
GO

/****** Object: Table [dbo].[MEETING] Script Date: 2016/09/08 12:22:07 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[MEETING](
[MeetingID] [int] IDENTITY(1,1) NOT NULL,
[VenueID] [int] NOT NULL,
[MeetingName] [varchar](50) NULL,
[MeetingStart] [date] NULL,
[MeetingEnd] [date] NULL,
CONSTRAINT [PK_MEETING1] PRIMARY KEY CLUSTERED
(
[MeetingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

USE [XcellIT]
GO

/****** Object: Table [dbo].[USER] Script Date: 2016/09/08 12:25:13 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[USER](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[DateOfBirth] [date] NULL,
[Gender] [varchar](50) NULL,
[EmailAddress] [varchar](50) NULL,
[Password] [varchar](50) NULL,
[City] [varchar](50) NULL,
[PhoneNumber] [varchar](50) NULL,
[ClubID] [int] NOT NULL,
[UserType] [varchar](50) NOT NULL,
CONSTRAINT [PK_USER1_1] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

USE [XcellIT]
GO

/****** Object: Table [dbo].[ATHLETE] Script Date: 2016/09/08 12:27:40 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ATHLETE](
[AthleteID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Gender] [varchar](50) NULL,
[DOB] [smalldatetime] NULL,
CONSTRAINT [PK_ATHLETE1] PRIMARY KEY CLUSTERED
(
[AthleteID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

I did it the way you told me to. I just copied and pasted all the different scripts in

OriginalGriff 8-Sep-16 6:43am

OK - I just copied that and I have a copy of your DB (sans data) - see how much easier that is? :laugh:
First thing I notice is that you don't establish any FOREIGN KEY relationships - and you probably should, so that SQL can enforce relational integrity.
And you don't appear to refer into your USER table anywhere, which is probably a mistake - as I'm guessing that ATHLETE and USER are linked in some way?
So what you want is the DISCIPLE.Name, and some details from the ATHLETE_MEETING_DISCIPLE_RESULT table, based on an Email address (which needs to come from the USER table) - that's probably a pretty simple JOIN, but there's nothing in your schema which connects the USER to anything else!

KevinClaassens 8-Sep-16 6:51am

Thank you so much for your time! It is much appreciated:). The athlete table would have all the athletes data whether they are registered or not and the USER table is for the registered users. I modified by stored procedure too look like this now

SELECT D.Name
FROM DISCIPLINE As D,[USER] As U, MEETING_EVENTS As ME, ATHLETE_MEETING_DISCIPLINE_RESULT As AMDR
WHERE U.EmailAddress = @emailAddress
AND AMDR.MeetingDisciplineID = ME.MeetingDisciplineID
AND ME.DisciplineID = D.DisciplineID
AND U.UserID = AMDR.AthleteID

In SSMS it returns the right result now,but in my code the the discipline is still returning null

OriginalGriff 8-Sep-16 7:08am

So the AthleteID and the UserID are the same?
In that case, you are duplicating theFirstName, LastName, Gender and DOB fields - I'd have the UserID being different (and unique to the USER table), and replace the four fields with AthleteID.
For now look at using a JOIN to get the tables combined into one row:

SELECT d.Name, AMDR.FinishingResult,AMDR.Placement
FROM DISCIPLINE d
JOIN ATHLETE_MEETING_DISCIPLINE_RESULT AMDR ON d.DisciplineID = AMDR.MeetingDisciplineID
JOIN MEETING_EVENTS ME ON ME.DisciplineID = d.DisciplineID
JOIN ATHLETE a ON a.AthleteID = AMDR.AthleteID
JOIN [USER] u ON u.UserID = a.AthleteID
WHERE U.EmailAddress = @emailAddress

Try that in SSMS and you should get a single row with just the right data.

KevinClaassens 8-Sep-16 7:54am

it does not return anything:(

OriginalGriff 8-Sep-16 8:24am

OK. Do you have a simple data sample that should return what you want?
It needs to be all the related table info.

KevinClaassens 8-Sep-16 8:28am

When I enter the email address ex. s214311848@nmmu.ac.za it should return 100m and 400m hurdles. Is that what you want to know?

OriginalGriff 8-Sep-16 8:38am

Well, yes - but I need the raw data for that user that generates the result.
That's data for all of the 6 tables so I can see what the query needs to fetch. (You can obfuscate names and emails for Data Protection reasons if it's "real" data).

What I'll do then is plug that into the table schemas you gave, and test queries to get the "right" result. Make sense?

KevinClaassens 8-Sep-16 8:44am

Makes perfect sense. For getting the data part. Should I just copy and paste everything? or is the some way you can do it in SSMS?

OriginalGriff 8-Sep-16 9:07am

"Everything" is going to make for a big post!
If you use SSMS, you can export it:
Right click the DB name, and select "Tasks...Generate Scripts"
That brings up a wizard, which guides you through.
In the "Set Scripting options" dialog, click "Advanced" and change "Types of data to script" to "Schema and data"
That will create a text file contain the schema and INSERT statements to make sure we have the same data.
Load it to Dropbox or similar, and post the link.

KevinClaassens 8-Sep-16 9:17am

Once again. Thank you so much for your time and effort to help me:) https://www.dropbox.com/s/b97wsw2a2q4zgku/script.sql?dl=0

OriginalGriff 8-Sep-16 9:55am

Answer updated

Dave Kreskowiak 8-Sep-16 10:05am

Over and above as usual. A 5.

KevinClaassens 8-Sep-16 15:33pm

Thank you very much for your time and effort. It is very much appreciated

OriginalGriff 8-Sep-16 16:07pm

You're welcome!