how do i populate a listbox with results from a database.

Question

0.00/5 (No votes)

See more:

how do i populate a listobx with results from a database. I want to select an id and a name from the database and use the be able to access the id from the listbox in which the results of the db search are displayed. in the code below i try to put the results in a database and the populate the listbox from the data table contents. but it doesnt seem to work. any help?

C#

String connection, sql;
connection = Properties.Settings.Default.cvmanagerConnectionString;


sqlA = "select FileLoc, Lname from apps where dept = '" + intdept.Text + "' and role = '" + introle.Text + "' and rating = '" + intratinglevel.Text + "' and yearsofexp = '" + intexp.Text + "' and hnd = '" + edulev.Text + "' ";
           
            //datatable creation for storing search results
            DataTable results = new DataTable("searchResults");
            DataColumn column;
            DataRow row;

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.Int32");
            column.ColumnName = "apps";
            column.ReadOnly = true;
            column.Unique = true;
            results.Columns.Add(column);


            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "LastName";
            column.ReadOnly = true;
            column.Unique = true;
            results.Columns.Add(column);

            DataColumn[] PrimaryKeyColumns = new DataColumn[1];
            PrimaryKeyColumns[0] = results.Columns["apps"];
            results.PrimaryKey = PrimaryKeyColumns;

            cvmanagerDataSet1.Tables.Add(results);



            SqlDataReader reader = null;
            SqlConnection conn = new SqlConnection(connection);
            SqlCommand cmd = new SqlCommand(sql, conn);

            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();
                intsearchresults.Enabled = true;

                while (reader.Read())
                {                                                           
                    row = results.NewRow();
                    row["apps"] = reader["apps_id"].ToString();
                    row["LastName"] = reader["Lname"].ToString();
                    results.Rows.Add(row);

                intsearchresults.Items.Add(reader["Lname"].ToString());
                    
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                reader.Close();
                conn.Close();

            }

        }

[edit]Just a tidy on the code block - OriginalGriff[/edit]

Posted 4-Dec-10 3:32am

Member 7250221

Updated 4-Dec-10 3:37am

OriginalGriff

v3

Add a Solution

Comments

Sandeep Mewara 4-Dec-10 9:41am

Whats not working?

[no name] 4-Dec-10 10:04am

firstly i cant seem to populate the datatable.

2 solutions

Solution 1

One of the best sample I am giving you. Make minor changes according to u r needs.
Default2.aspx

<body>
<form id="form1" runat="server">
<div>
<asp:ListBox ID="ListBox1" runat="server"></asp:ListBox>
</div>
</form>
</body>
</html>

=================================================================

Default2.aspx.cs

using System.Data.SqlClient;
using System.Data;

public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

if (!this.IsPostBack)
{
String strConnectionString =
"Data Source=.\\sqlexpress;Initial Catalog=CPTempDB;Integrated Security=True;Pooling=False";
SqlConnection con = new SqlConnection(strConnectionString);
String strQuery = "SELECT * FROM Student";
SqlCommand cmd = new SqlCommand(strQuery, con);

//SqlDataAdapter da = new SqlDataAdapter(cmd); OOORRR
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;

DataSet ds = new DataSet();

try
{
con.Open();
da.Fill(ds);
}
catch (Exception err)
{
// Handle the exception thrown
}
finally
{
con.Close();
}

ListBox1.DataSource = ds.Tables[0];
ListBox1.DataValueField = "ID";
ListBox1.DataTextField = "Name";
ListBox1.DataBind();
}

}
}

Posted 4-Dec-10 5:14am

sandipapatel

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)

JOAT-MON · Accepted Answer · 2010-12-04T19:47:00

There seem to be few spots that could use some attention:
1. You declared sql as a string but you assign the select query that you built to a variable called sqlA, which never gets used.
2. When you create the new row and assign values to it here:

C#

while (reader.Read())
                {
                    row = results.NewRow();
                    row["apps"] = reader["apps_id"].ToString();
                    row["LastName"] = reader["Lname"].ToString();
                    results.Rows.Add(row);
                intsearchresults.Items.Add(reader["Lname"].ToString());
                }

the reader never took in a colomn named 'apps_id', but your select statement does select a column named 'FileLoc'.
3. In your where clause:

" where dept = '" + intdept.Text + "' and role = '" + introle.Text + "' and rating = '" + intratinglevel.Text + "' and yearsofexp = '" + intexp.Text + "' and hnd = '" + edulev.Text + "' "

the controls(TextBox?) names seem to indicate that they are integers. If this is the case, meaning the values are stored in the database as integers, then you do not want to wrap them in single quote marks.

Hope this helps.