Solved

Database lookup in a GridView?

Posted on 2010-08-25
6
479 Views
Last Modified: 2012-05-10
I have a gridview where I'm getting a number of rows returned...

SELECT SessionName, Capacity from Sessions

However,  this makes my GridView look something like this

Session 1 |  20
Session 2 |  25

However, I'd like to do another lookup on the registrants table to see how many are registered for each session, so that my GridView would look something like this...

Session 1 | 14/20
Session 2 | 11/25

Is there a way that I could do a database lookup from the registrants table for each row??  Kind of hard to explain...

But basically it will add a database lookup for every session in the sessions table.  That might be 30-40 lookups.  Is that a problem?

Can I do this in a GridView?  Or do I need to use a C# loop?
0
Comment
Question by:sayguh
  • 4
  • 2
6 Comments
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 33525286
There should be no problem with that. You could use the OnRowDataBound event of the GridView to perform the subsequent lookups.

However, it would be more efficient to perform all of the data retrieval using SQL. If your registrants table has a foreign key field to your Sessions table, you should be able to retrieve a count of the registrants per session. From there, you can either use SQL to format the returned information (registrants / capacity), or return them as separate values in your dataset.
0
 
LVL 2

Author Comment

by:sayguh
ID: 33531144
Anyway you could provide some sample code?  Everything I'm googling looks overly complicated

Thanks if you can!
0
 
LVL 2

Author Comment

by:sayguh
ID: 33531640
Alright, I have it doing the database lookup for each row now..   but I'm just doing

"SELECT count from registrants"

How can I do "SELECT count from registrants WHERE session_ID=" + ???

How can I reference the dynamic session ID for that row?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 2

Author Comment

by:sayguh
ID: 33531740
I tried

session_ID=" + e.Row.Cells[1].Text

But that didn't seem to work...

(the session ID is in that cell)
0
 
LVL 2

Author Comment

by:sayguh
ID: 33532121
Got it!
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 33533050
Just to follow up on the my SQL comment above, you could use a SQL call such as:

SELECT Sessions.Session_ID, Sessions.SessionName, Sessions.Capacity, Count(*) Registrant_Count
FROM Sessions
   INNER JOIN registrants ON Sessions.session_ID = registrants.session_ID
GROUP BY Sessions.Session_ID, Sessions.SessionName, Sessions.Capacity

This should bring back all of the parts you would need to display the data that you want in your gridview and, therefore, reduce the number of database calls your application makes.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now