Getting data from MS Access database and display it in a listbox

Question

How do I read data in ms access database and display it in a listbox. I have the codes here but i got errors.

 private void button3_Click(object sender, EventArgs e)
    {
        using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\Sisc-stronghold\mis!\wilbert.beltran\DataBase\DataStructure.accdb"))
        using(OleDbCommand cmd = new OleDbCommand(" SELECT * from TableAcct", conn))
        {
            conn.Open();
            OleDbDataReader Reader = cmd.ExecuteReader();
            //if (Reader.HasRows)
            if (Reader.HasRows)
            {
                Reader.Read();
                listBox1.Text = Reader.GetString("FirstName");
            }
        }

the errors are here: 1. Error 1 The best overloaded method match for'System.Data.Common.DbDataReader.GetString(int)' has some invalid arguments. 2. Error 2 Argument '1': cannot convert from 'string' to 'int'

Get string Gets the value of the specified column as a string. and the specification is by zero base numbers, if your query result returns a table with 5 column and you want to get 3rd column's value call this function with 2 as parameter — saeed
– saeed, Commented Feb 28, 2013 at 5:58

Pyromancer · Accepted Answer · 2013-02-28 07:04:22Z

try this one,

       List<String> firstName = new List<String>();
       List<String> lastName = new List<String>();

       private void loadButton_Click(object sender, EventArgs e)
       {
                cn.Open();
                OleDbDataReader reader = null;
                cmd = new OleDbCommand("select* from Records", cn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    firstName.Add(reader["FirstName"].ToString());
                    lastName.Add(reader["LastName"].ToString());
                }
                cn.Close();
       }

then in your search button, insert this,

private void searchButton_Click(object sender, EventArgs e)
        {
            clearSearchResult();
            try
            {
                int totalItems = FirstName.Count;
                int count = 0;
                while (count < totalItems)
                {
                    if (textBox6.Text == FirstName[count].ToString())
                    {
                        listBox1.Items.Add(FirstName[count].ToString());
                        count = 100;
                    }
                    else
                    {
                        count++;
                    }

It's good to use when you want to show the information of the "FirstName" in the listBox1_SelectedIndexChanged if you want. here's an example,

private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
                int totalItems = lastName.Count;
                int count = 0;
                while (count < totalItems)
                {
                    if ((listBox1.SelectedItem.ToString()) == firstName[count].ToString()))
                    {
                        textBox1.Text = firstName[count].ToString();
                        textBox2.Text = lastName[count].ToString();
                        count = 100;
                    }
                    else
                    {
                        count++;
                    }
               }

hope this helps,

I'm done displaying the specific data i want to display in listbox. thanks for your help. Now, what i want is that when i click on the data display in the listbox, all other information related to it should be display in the individual textbox used to input the data into the database. :)

saeed · Accepted Answer · 2013-02-28 05:54:25Z

1

change

listBox1.Text = Reader.GetString("FirstName");

to

listBox1.Text = Reader.GetString(0); // zero base ordinal of column

answered Feb 28, 2013 at 5:54

saeed

2,5172 gold badges25 silver badges40 bronze badges

Comments

markoo · Accepted Answer · 2013-02-28 06:05:29Z

1

GetString() takes an int as the parameter and not a string. Meaning that you must use the index of the column.

In your specific circumstance as "FirstName" is the second column the index would be 1:

listBox1.Text = Reader.GetString(1);

http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdatareader.getstring.aspx

edited Feb 28, 2013 at 6:05

answered Feb 28, 2013 at 5:51

markoo

7581 gold badge7 silver badges22 bronze badges

5 Comments

lexter Over a year ago

@markoo- I'm sorry but I'm just new to C#. How will i do that, how will I use the index of the column. :) thanks so much

whastupduck Over a year ago

@lexter If Firstname is the first column returned by your query, then the index would be 0.

markoo Over a year ago

@lexter as you are selecting * from the table, count from the left starting at 0, then when you reach "Firstname" you have the index. Or you could only select "FirstName", then the index would be 0.

lexter Over a year ago

@markoo - thanks. It works :) the "firstname" column is the second column in my table so i will use "1" as the index :) thanks so much :)

saeed Over a year ago

@lexter Get string Gets the value of the specified column as a string. and the specification is by zero base numbers, if your query result returns a table with 5 column and you want to get 3rd column's value call this function with 2 as parameter

Jens Kloster · Accepted Answer · 2013-02-28 06:47:06Z

1

Thy using a While loop

while(reader.Read())
{
   listbox1.Items.Add(reader["FirstName"]);
}

This moves through all the rows you selected. reader.Read() returns false if there are no more rows.

Also: if you Want to retrive valmue from a column I suggest you do it with the index ón the reader instance. Like my example.

var value = reader["ColumnName"];

This increases readability comparing to

var value = reader.GetString(0);

UPDATE

If you want to only display the fist value - I suggest you use cmd.ExecuteScalar() and the adapt you sql to only return the value you need:

using(OleDbCommand cmd = new OleDbCommand("SELECT firstname from TableAcct", conn))
{
   conn.Open();
   var firstName = cmd.ExecuteScalar();
}

Be aware the this will give you the first "FirstName" in the table. And since there is no "order by firstname" or "where someKey = 1" - this might not rturn that you expected.

edited Feb 28, 2013 at 6:47

answered Feb 28, 2013 at 6:03

Jens Kloster

11.3k5 gold badges44 silver badges55 bronze badges

3 Comments

lexter Over a year ago

thanks :) but what your codes did is that it displays all the data in the "firstname" column. what if I wanted to display only specific data for example. I have a data named "Jens" in the first column and when i typed it in the textbox and click the search button, it will only display "Jens" in the listbox

Pyromancer Over a year ago

how about load them first in a list<string> then if ur searchTextBox.text is equal to one of the values in the List<string> the data will be displayed in the listBox

lexter Over a year ago

@AlfredSanz - how will i do that. can you possibly post your answers :) thanks :P

Pir Fahim Shah · Accepted Answer · 2014-03-02 13:28:17Z

If you want to create MS Access data base and to access it, and to display data in some component, like here i will show you.how to connect with MS Access Data Base and display data from data base in Label. First of all create any Access data base like here "PirFahimDataBase". Now in your Visual Studio go to the menu and do this

Click Data
Add New Data Base
Click Next
Click New Connection
Now change the Data Source by clicking Change and select Microsoft Access data base files
Click Browse for selecting your created data base

Now in Button ClickEvent paste these code which will get data from data base and will show it in the label

using System.Windows.Forms; //these two lines should be written before namespace at top of the program
using System.Data.OleDb;

private void button1_Click(object sender, EventArgs e)
    {        
      System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
     conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
    @"Data source= C:\Users\pir fahim shah\Documents\PirFahimDataBase.accdb";

    try
     {
     conn.Open();
     MessageBox.Show("connected successfuly");
     OleDbDataReader reader  = null;  // This is OleDb Reader
   OleDbCommand cmd = new OleDbCommand("select TicketNo from Table1 where Sellprice='6000' ", conn);
    reader = cmd.ExecuteReader();
    while (reader.Read())
    {
     label1.Text= reader["TicketNo"].ToString();           

    }

}
    catch (Exception ex)
{
    MessageBox.Show("Failed to connect to data source");
}
finally
{
    conn.Close();
}    
 }//end of button click event

whastupduck · Accepted Answer · 2013-02-28 05:52:26Z

0

Your error is in this line:

listBox1.Text = Reader.GetString("FirstName");

You must pass a number in the GetString() function.

answered Feb 28, 2013 at 5:52

whastupduck

1,16611 silver badges25 bronze badges

Comments

Pang · Accepted Answer · 2016-05-07 04:58:00Z

0

DataColumn[] PrimaryKeyColumn = new DataColumn[1]; //Define Primary coloumn
DataSet dataSet = new DataSet();
DataTable dataTable = new DataTable();
ReadAndUpdateExcel.ReadExcel(strPath, sheetName, out dataSet);
dataSet.Tables.Add(dataTable);
PrimaryKeyColumn[0] = dataSet.Tables[0].Columns[0];
dataSet.Tables[0].PrimaryKey = PrimaryKeyColumn;
string num = dataSet.Tables[0].Rows[dataSet.Tables[0].Rows.IndexOf(dataSet.Tables[0].Rows.Find(strTCName))]["ACNO"].ToString();
//string country

edited May 7, 2016 at 4:58

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered May 7, 2016 at 4:48

sgdhgsagsaf

1

Collectives™ on Stack Overflow

Getting data from MS Access database and display it in a listbox

7 Answers 7

1 Comment

Comments

5 Comments

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

Comments

5 Comments

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related