Populating a list box with values from the database

Hi Guys! I want to populate a list box when the user clicks a button.The data in the listbox would be displayed as
empid - empname
All is ok, but i get an error which says system.data.common.dbdatarecord. I would really appreciate if someone could tell me where i am wrong. Thanks in advance
Dim con As New System.Data.OleDb.OleDbConnection
        Dim dreader As System.Data.OleDb.OleDbDataReader
        Dim myCommand As New System.Data.OleDb.OleDbCommand
        Dim myPath As String
 
        myPath = Server.MapPath("hayat.mdb")
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source=" & myPath & ";"
 myCommand.CommandText = "select empid & " - " & empname from empdetails Order by empid"
        myCommand.Connection = con
        Try
            con.Open()
            dreader = myCommand.ExecuteReader
            ListBox1.DataSource = dreader
            ListBox1.DataBind()
            dreader.Close()
        Catch ex As Exception
            lblerror.Text = "There is an error" + ex.ToString
            con.Close()
        End Try

Open in new window

preethamonlineAsked:
Who is Participating?
 
bedanandCommented:
On the query you can contact the empid and empname and give it a alias and bind it.

See below.


Regards
Bedanand
http://www.dot4pro.com



Dim con As New System.Data.OleDb.OleDbConnection
        Dim dreader As System.Data.OleDb.OleDbDataReader
        Dim myCommand As New System.Data.OleDb.OleDbCommand
        Dim ds As New DataSet
        Dim da As System.Data.OleDb.OleDbDataAdapter
        Dim myPath As String
 
        myPath = Server.MapPath("hayat.mdb")
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source=" & myPath & ";"
        myCommand.CommandText = "select empid, empname,(empid + ' - ' + empname) as empname2 from empdetails Order by empid"
 
        myCommand.Connection = con
        Try
            con.Open()
            da = New System.Data.OleDb.OleDbDataAdapter(myCommand)
            da.Fill(ds)
             ListBox1.DataTextField = "empname2"
            ListBox1.DataSource = ds.Tables(0).DefaultView
            ListBox1.DataBind()
 
        Catch ex As Exception
            lblerror.Text = "There is an error" + ex.ToString
            con.Close()
        End Try
	 

Open in new window

0
 
jagmohan_gunwantConsultantCommented:
hello Friend

here is a simple example using Dataset and the Employees table in Northwind Database
in SQL Server 2000, it will display the data in the ListBox


System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection();
        DataSet t_DS = new DataSet();
        System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
        System.Data.OleDb.OleDbDataAdapter t_DA;
 
        con.ConnectionString = "Provider=SQLOLEDB;Data Source = .; Initial Catalog = NorthWind; User ID = sa; Password = ";
        myCommand.CommandText = "select EmployeeID, FirstName  + ' ' + LastName As Name from Employees1 Order by EmployeeID";
        myCommand.Connection = con;        
        try
        {
            con.Open();                        
            t_DA = new OleDbDataAdapter(myCommand);
            t_DA.Fill(t_DS);
            
            ListBox1.DataTextField = "Name";
            ListBox1.DataValueField = "EmployeeID";
            ListBox1.DataSource = t_DS;
            ListBox1.DataBind();
        }
        catch (Exception ex)
        {
            Response.Write("There is an error" + ex.ToString());
            con.Close();
        }

Open in new window

0
 
bedanandCommented:
Instread of binding to the datareader bind to the datatable or dataset.
Here it goes.


Dim con As New System.Data.OleDb.OleDbConnection
        Dim dreader As System.Data.OleDb.OleDbDataReader
        Dim myCommand As New System.Data.OleDb.OleDbCommand
        Dim myPath As String
 
        myPath = Server.MapPath("hayat.mdb")
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source=" & myPath & ";"
 myCommand.CommandText = "select empid & " - " & empname from empdetails Order by empid"
        myCommand.Connection = con
        Try
            con.Open()
Dim ds as DataSet= new DataSet
            ds = myCommand.ExecuteDataSet
            ListBox1.DataSource = ds.Tables(0)
            ListBox1.DataBind()
            dreader.Close()
        Catch ex As Exception
            lblerror.Text = "There is an error" + ex.ToString
            con.Close()
        End Try

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
preethamonlineAuthor Commented:
Dear bedanand,
i am getting an error in this line.
ds = myCommand.ExecuteDataSet
0
 
bedanandCommented:
What error are you getting. Can u provide the complete error message thrown?

Regards
Bedanand
http://www.dot4pro.com

0
 
preethamonlineAuthor Commented:
Sure. The error message is
ds = myCommand.ExecuteDataSet
'ExecuteDataSet' is not a member of System.Data.OleDb.OleDbCommand'
0
 
bedanandCommented:
Try the following.



Dim con As New System.Data.OleDb.OleDbConnection
        Dim dreader As System.Data.OleDb.OleDbDataReader
        Dim myCommand As New System.Data.OleDb.OleDbCommand
        Dim myPath As String
 
        myPath = Server.MapPath("hayat.mdb")
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source=" & myPath & ";"
 myCommand.CommandText = "select empid & " - " & empname from empdetails Order by empid"
        myCommand.Connection = con
        Try
            con.Open()
Dim ds as DataSet= new DataSet
System.Data.OleDb.OleDbDataAdapter ta = new System.Data.OleDb.OleDbDataAdapter(myCommand );
           ta.Fill(ds);
            ListBox1.DataSource = ds.Tables(0)
            ListBox1.DataBind()
            dreader.Close()
        Catch ex As Exception
            lblerror.Text = "There is an error" + ex.ToString
            con.Close()
        End Try

Open in new window

0
 
preethamonlineAuthor Commented:
I get an error. It says
Input string was not in a correct format.

error in line
  myCommand.CommandText = "select empid & " - " & empname from empdetails Order by empid"
0
 
bedanandCommented:
Use  like this


myCommand.CommandText = "select empid + ' ' + empname from empdetails Order by empid"

Open in new window

0
 
preethamonlineAuthor Commented:
Its not working bedanand. I guess you need to set the
ListBox1.DataTextField = "empid"
only then does it populate list box.
I wanted the list box to look like 1001 - JOJO but now it looks like 1001.

The following code works for me
         
Dim con As New System.Data.OleDb.OleDbConnection
        Dim dreader As System.Data.OleDb.OleDbDataReader
        Dim myCommand As New System.Data.OleDb.OleDbCommand
        Dim ds As New DataSet
        Dim da As System.Data.OleDb.OleDbDataAdapter
        Dim myPath As String

        myPath = Server.MapPath("hayat.mdb")
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source=" & myPath & ";"
        myCommand.CommandText = "select empid, empname from empdetails Order by empid"

        myCommand.Connection = con
        Try
            con.Open()
            da = New System.Data.OleDb.OleDbDataAdapter(myCommand)
            da.Fill(ds)
             ListBox1.DataTextField = "empid"
            ListBox1.DataSource = ds.Tables(0).DefaultView
            ListBox1.DataBind()

        Catch ex As Exception
            lblerror.Text = "There is an error" + ex.ToString
            con.Close()
        End Try
0
 
preethamonlineAuthor Commented:
I am getting the follwoing error. I think you're pretty  close to cracking it.

There is an errorSystem.InvalidOperationException: The provider could not determine the Double value. For example, the row was just created, the default for the Double column was not available, and the consumer had not yet set a new Double value. at System.Data.OleDb.DBBindings.get_Value() at System.Data.OleDb.OleDbDataReader.GetValues(Object[] values) at System.Data.Common.SchemaMapping.LoadDataRow(Boolean clearDataValues, Boolean acceptChanges) at System.Data.Common.DbDataAdapter.FillLoadDataRow(SchemaMapping mapping) at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at hris.WebForm4.LinkButton2_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\hris\WebForm4.aspx.vb:line 56
0
 
bedanandCommented:
Some changes on the query



 myCommand.CommandText = "select empid, empname,(Str(empid) + ' - ' + empname) as empname2 from empdetails Order by empid"
 

Open in new window

0
 
preethamonlineAuthor Commented:
Perfecto bedanand. I knew you could do it. Thanks very much. I also have another problem now. This value 1001-JOJO is passed to a text box, based on which the results are displayed to the datagrid.
Let me post the query, so that you could tell me what is the change i need to make.

myCommand.CommandText = "select * from empattendence where empid = " & txtempid.Text & " order by adate"
I used this query earlier when i only had empid. But now that we have empid-empname, how could i write this query.

Since this is a seperate qestion, i would raise the points by 200
0
 
bedanandCommented:
Very simple

Just split the text with the - seperator

Regards
Bedanand
http://www.dot4pro.com




Dim emp as String = txtempid.Text
Dim empid as String = emp.Substring(0, emp.IndexOf(" -"))
 
myCommand.CommandText = "select * from empattendence where empid = " & empid & " order by adate"

Open in new window

0
 
preethamonlineAuthor Commented:
Thanks Bedanand. It works like a charm. Thank you for all your help. And please continue to help me out. I am in the midst of creating an HRIS for my office. Thanks once again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.