CODEWITHSUNDEEP
c#ms-accesslistbox
lexter
lexter

Reputation: 225

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

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'

Upvotes: 5

Views: 53866

Answers (7)

sgdhgsagsaf
sgdhgsagsaf

Reputation: 1

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

Upvotes: 0

Pir Fahim Shah
Pir Fahim Shah

Reputation: 10623

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

Upvotes: 1

Pyromancer
Pyromancer

Reputation: 2509

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,

Upvotes: 7

Jens Kloster
Jens Kloster

Reputation: 11277

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.

Upvotes: 1

markoo
markoo

Reputation: 738

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

Upvotes: 1

saeed
saeed

Reputation: 2497

change 

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

to

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

Upvotes: 1

whastupduck
whastupduck

Reputation: 1166

Your error is in this line:

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

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

Upvotes: 0

Related Questions