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'

Raguelragweed

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 – Cohune

Answer 1

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,

Fortna

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. :) – Raguelragweed

Answer 2

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

Taffy

@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 – Raguelragweed

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

@Raguelragweed 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. – Taffy

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

@Raguelragweed 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 – Cohune

Answer 3

change

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

to

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

Cohune

Answer 4

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.

Shoup

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 – Raguelragweed

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 – Fortna

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

Answer 5

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

1. Click Data
2. Add New Data Base
3. Click Next
4. Click New Connection
5. Now change the Data Source by clicking Change and select Microsoft Access data base files
6. 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

Hefter

Answer 6

Your error is in this line:

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

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

Creed

Answer 7

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

Waisted