ASP.NET and C# webform. Need help with query to DB

[h0meopathic]

I need a way to check my database and see if a record is repeated and if it's repeated twice.

I'm making a webform that will allow the user to enter his or her personal information and select a radio button of specific times.  The radio button will correspond to a SlotID in my database. When the SlotID has been used twice I need to grey out the radio button because it can no longer be selected. Any help would be nice.

[wraith808]

I'm not sure what database you're using, but with ASP.NET and C#, I'll assume SQL Server.  Also assuming that the table name is Slot, and with everything else you've given, this query would give the number of times each SlotID has been used.

Code: Text [Select]

1. SELECT count(1), SlotID
2. FROM Slot
3. GROUP BY SlotID

If you only wanted to get the ones that had been used more than 1 time then you could add a having clause, i.e.

Code: Text [Select]

1. SELECT count(1), SlotID
2. FROM Slot
3. GROUP BY SlotID
4. HAVING count(1) > 1

HTH

[h0meopathic]

What is count(1)?
I am using SQL server and my table is as follows:

Table AppointmentName: AppID, LastName, FirstName, Email, BirthDate, Slot.

Slot gets an SelectedIndexValue from the radio button on submitClick.

On Page_Load is where I need to have the SQL statement to see how many times the value from the column "Slot" has been used.

[wraith808]

Count(1) just counts the number of records in the query.  You could use count(slotid), but by using 1, the data for the slotid doesn't have to actually be loaded.

[h0meopathic]

I figured it out, thanks for the help.

Code: Text [Select]

1. SELECT Slot, COUNT(Slot)
2. AS SlotCount
3. FROM AppointmentName
4. GROUP BY Slot

[h0meopathic]

Code: C# [Select]

1. SqlConnection conn;
2.         DataSet dataSet = new DataSet();
3.         SqlDataAdapter adapter;
4.         SqlCommandBuilder commandBuilder;
5.  
6.         conn = new SqlConnection("Server=localhost\\SqlExpress; Database=HealthFair;" +
7.                 "Integrated Security = True");
8.         adapter = new SqlDataAdapter("SELECT Slot, COUNT(Slot) AS SlotCount FROM AppointmentName " +
9.             "GROUP BY Slot HAVING COUNT(Slot) > 1", conn);
10.         adapter.Fill(dataSet);
11.         commandBuilder = new SqlCommandBuilder(adapter);
12.         adapter.Update(dataSet);

Now how do I get the data into objects so I can determin which records in Slots to cut off?