How do I display all the values from the DB. C# ASP.NET

Question

0.00/5 (No votes)

See more:

When I select from the drop down only one entry is being displayed from the database in the checkbox list but there are more entries with the same Subject Name in the database but they don't show. The drop down is populated using the table subjects and the checkboxlist is displayed using data from the table CLO. please help.

This is my code.

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        { CheckBoxList1.Items.Clear(); }

        String strConnString = ConfigurationManager.ConnectionStrings["facultylogConnectionString"].ConnectionString;
        String strQuery = "Select CourseOutcomes from CLO,subjects where " + " SubjectName = @Subject_Name";
        SqlConnection con = new SqlConnection(strConnString);
        SqlCommand cmd = new SqlCommand();
        cmd.Parameters.AddWithValue("Subject_Name", DropDownList1.SelectedItem.Value);
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strQuery;
                cmd.Connection = con;
                con.Open();
                using(SqlDataReader sdr = cmd.ExecuteReader())
                {
                     sdr.Read();
                     ListItem item = new ListItem();
                     item.Text = sdr["CourseOutcomes"].ToString();
                     CheckBoxList1.Items.Add(item);
                }
                con.Close();
                
            }

What I have tried:

I have tried using a while loop as well but it displays the same database entry 5 times.

{ CheckBoxList1.Items.Clear(); }

       String strConnString = ConfigurationManager.ConnectionStrings["facultylogConnectionString"].ConnectionString;
       String strQuery = "select CourseOutcomes from CLO,subjects where " + " SubjectName = @Subject_Name";
       SqlConnection con = new SqlConnection(strConnString);
       SqlCommand cmd = new SqlCommand();
       cmd.Parameters.AddWithValue("Subject_Name", DropDownList1.SelectedItem.Value);
       cmd.CommandType = CommandType.Text;
       cmd.CommandText = strQuery;
       cmd.Connection = con;
       try
       {
           con.Open();
           SqlDataReader sdr = cmd.ExecuteReader();
           while (sdr.Read())
           {
               ListItem item = new ListItem();
               item.Text = sdr["CourseOutcomes"].ToString();
               CheckBoxList1.Items.Add(item);

           }
       }
       catch (Exception ex)
       {
           throw ex;
       }
       finally
       {
           con.Close();
           con.Dispose();
       }

Posted 10-Jun-20 12:54pm

candijen

Updated 11-Jun-20 2:15am

Add a Solution

Comments

Richard MacCutchan 11-Jun-20 4:53am

In your first code sample you only extract a single record from the reader. In your second block you should extract all records that have been returned. You need to run it in the debugger to see exactly what is being returned.

Richard Deeming 11-Jun-20 5:05am

catch (Exception ex)
{
    throw ex;
}

Don't do that. You've just thrown away the stack-trace of the exception, which will make it much harder to track down the cause of any problems.

If you really need to re-throw an exception, just use throw; instead of throw ex;:

catch (Exception ex)
{
    throw;
}

But in this case, since you're not doing anything with the exception, just remove the catch block.

finally
{
    con.Close();
    con.Dispose();
}

If you wrap your SqlConnection, SqlCommand, and SqlDataReader objects in a using block, you won't need this finally block.

using (SqlConnection con = new SqlConnection(strConnString))
using (SqlCommand cmd = new SqlCommand())
{
    cmd.Parameters.AddWithValue("Subject_Name", DropDownList1.SelectedItem.Value);
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = strQuery;
    cmd.Connection = con;
    
    con.Open();
    using (SqlDataReader sdr = cmd.ExecuteReader())
    {
        while (sdr.Read())
        {
            ListItem item = new ListItem();
            item.Text = sdr["CourseOutcomes"].ToString();
            CheckBoxList1.Items.Add(item);
        }
    }
}

candijen 11-Jun-20 7:03am

Thanks I have removed the try block but I'm not able to figure out why
when I select Subject_Name from a drop down I want it to give me the CourseOutcomes from the table CLO according to the SubjectNames in checkboxes but it only gives me one CourseOutcome but there are more in the table for the same SubjectName.

these are the two tables

CREATE TABLE [dbo].[subjects] (
[Subject_Code] NVARCHAR (50) NOT NULL,
[Subject_Name] NVARCHAR (50) NOT NULL,
PRIMARY KEY CLUSTERED ([Subject_Code] ASC)
);

CREATE TABLE [dbo].[CLO] (
[Id] INT NOT NULL,
[SubjectName] NVARCHAR (50) NOT NULL,
[CourseOutcomes] NVARCHAR (500) NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

The content kind of looks like this

Subject_Code Subject_Name
ETM203 Recent Trends in IT
ETM302 Technology Management
ETM316 Digital Marketing
ISM303 Consumer Behaviour
STM302 Software Engineering

ID SUbjectName CourseOutcomes
1 Recent Trends in IT Explain basics of Recent Trends in technology.
2 Recent Trends in IT Describe various applications of recent trends.
3 Recent Trends in IT Explain importance of advancement in technology.
4 Recent Trends in IT Key points and theory aspect discussed.
5 Digital Marketing Describe basics in digital media marketing.
6 Digital Marketing Hi
7 Digital Marketing Hi

DerekT-P 11-Jun-20 5:57am

What is your d/b structure? Your SQL statement is
Select CourseOutcomes from CLO,subjects where subjectname = @SubjectName

So you're querying two separate tables, but not defining the relationship between them. Run that query in ManagementStudio or whatever tool you use, and you'll see the results that your code are getting. And, as Richard M says above, using the debugger to step through the code and examine what's actually happening is usually the best way to understand what you're asking your code to do!

candijen 11-Jun-20 7:01am

these are the two tables

CREATE TABLE [dbo].[subjects] (
[Subject_Code] NVARCHAR (50) NOT NULL,
[Subject_Name] NVARCHAR (50) NOT NULL,
PRIMARY KEY CLUSTERED ([Subject_Code] ASC)
);

CREATE TABLE [dbo].[CLO] (
[Id] INT NOT NULL,
[SubjectName] NVARCHAR (50) NOT NULL,
[CourseOutcomes] NVARCHAR (500) NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

The content kind of looks like this

Subject_Code Subject_Name
ETM203 Recent Trends in IT
ETM302 Technology Management
ETM316 Digital Marketing
ISM303 Consumer Behaviour
STM302 Software Engineering

ID SUbjectName CourseOutcomes
1 Recent Trends in IT Explain basics of Recent Trends in technology.
2 Recent Trends in IT Describe various applications of recent trends.
3 Recent Trends in IT Explain importance of advancement in technology.
4 Recent Trends in IT Key points and theory aspect discussed.
5 Digital Marketing Describe basics in digital media marketing.
6 Digital Marketing Hi
7 Digital Marketing Hi

I'm not really sure what to do with them.

but my issue is that when I select Subject_Name from a drop down I want it to give me the CourseOutcomes from the table CLO according to the SubjectNames in checkboxes but it only gives me one CourseOutcome but there are more in the table for the same SubjectName

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)

DerekT-P · Answer 1 · 2020-06-11T02:16:00

Well it doesn't look like you need the Subjects table at all, since Subject_Name is replicated in each table (why??) and you're not referring to any other data from the subjects table. I think your query simply needs to be "Select courseoutcomes from CLO where SubjectName = @SubjectName" (I've no idea why you break your strQuery into two separate strings...). That at least will give you the rows you need from the database. Then when you use your while... loop you will pick up each row and create an item in the dropdown. So your final code can be simplified to something like:

C#

CheckBoxList1.Items.Clear();
String strConnString = ConfigurationManager.ConnectionStrings["facultylogConnectionString"].ConnectionString;
String strQuery = "select CourseOutcomes from CLO where SubjectName = @Subject_Name";
using (SqlConnection con = new SqlConnection(strConnString)) {
    SqlCommand cmd = new SqlCommand();
    cmd.Parameters.AddWithValue("Subject_Name", DropDownList1.SelectedItem.Value);
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = strQuery;
    cmd.Connection = con;
    con.Open();
    using (SqlDataReader sdr = cmd.ExecuteReader()) {
        while (sdr.Read())
        {
            CheckBoxList1.Items.Add(new ListItem(sdr["CourseOutcomes"].ToString());
        }
    }
    con.Close();
}

(I've typed the above straight into CP so may have got brackets unbalanced or made typos, hopefully you get the idea). As previously, if your code doesn't give you the results you want, then step through it in the debugger to understand WHY it's not doing what you expect. You say (about the data tables) "I'm not really sure what to do with them" ... then learn SQL, at least its basics, before you start programming with SQL databases. Experiment with SELECT queries in the environment of something like SSMS, or HeidiSQL, or even just link to the database with Access. Learn how relational databases work and get comfortable with SQL - it's not hard (at least not at this level!)
In the above I've dispensed with the try...catch block since you're not handling any exception anyway, so it really offers little other than, arguably, closing the connection in the "finally". As suggested above, I've put the connection in its own using block (and explicitly closed it when successful) as this will ensure it gets properly cleaned up whatever happens.