1

I have created a table name glossary in a database named ChatBotDataBase in SQL Server. I want to read the data in a special column of the table.

To do so, I have written this code:

    private void button1_Click(object sender, EventArgs e)
    {
        SqlConnection sc = new SqlConnection();
        sc.ConnectionString = @"Data Source=shirin;Initial Catalog=ChatBotDataBase; 
        Integrated Security=True";
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = new SqlCommand();
        sda.SelectCommand.Connection = sc;

        sda.SelectCommand.CommandText = "SELECT * FROM glossary";

        DataTable table = new DataTable();
        MessageBox.Show(table.Rows[0].ItemArray[3].ToString());
    }

But there is an error in last line.

The error is :

An unhandled exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll.

And here is a print screen of the mentioned table:enter image description here

Can anyone help please?

Improve this question
3
  • 1
    You didn't even fill your table, that's why it doesn't have any Rows[0] and that's why it doesn't have any ItemArray[3]. Commented Dec 26, 2014 at 8:30
  • @SonerGönül I did. there is a row with 4 columns. Commented Dec 26, 2014 at 8:33
  • 1
    Use Fill for sda after creating DataTable. And get sc and sda into using because they implement IDisposable Commented Dec 26, 2014 at 8:36

5 Answers 5

Reset to default
6

Looks like you are confusing the Datatable called table with your database table in your sql server. In your image you show us the glossary table in your sql server, not the DataTable called table.

You get this error because you create an empty DataTable called table with DataTable table = new DataTable() but you didn't even fill your table. That's why it doesn't have any rows by default.

SqlCommand cmd = new SqlCommand("SELECT * FROM glossary");
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(table);

Also use using statement to dispose your SqlConnection, SqlCommand and SqlDataAdapter.

using(SqlConnection sc = new SqlConnection(conString))
using(SqlCommand cmd = sc.CreateCommand())
{
   cmd.CommandText = "SELECT * FROM glossary";
   ...
   using(SqlDataAdapter sda = new SqlDataAdapter(cmd))
   {
      DataTable table = new DataTable();
      sda.Fill(table);

      if(dt.Rows.Count > 0)
         MessageBox.Show(table.Rows[0].ItemArray[3].ToString());
   }
}
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for your help. I did what you suggested and now it works.I know it was foolish but you know I'm beginner in programming. :)
4

Below code will help you

    sda.SelectCommand.CommandText = "SELECT * FROM glossary";
    DataTable table = new DataTable();
    sda.Fill(table , "glossary");
    MessageBox.Show(table.Rows[0].ItemArray[3].ToString());
Improve this answer

Comments

2

You haven't executed the query or populated the table. It is empty. It has no columns and no rows. Hence the error.

Frankly, though, I strongly suggest using a typed class model, not any kind of DataTable. With tools like "dapper", this can be as simple as:

var list = conn.Query<Glossary>("SELECT * FROM glossary").ToList();

With

public class Glossary {
    public int Id {get;set}
    public string String1 {get;set} // horrible name!
    ....
    public int NumberOfUse {get;set}
}
Improve this answer

2 Comments

Sorry, I can't understand. Can you please tell me what should I do?
@TTS probably ExecuteReader on the command, then Load(reader) on the table. However, I would advise an alternative approach (see edit)
0

Dear kindly first fill your table with the data. And you should use checks because if there is no data then you get a proper message.check is below..

if(dt.Rows.Count > 0)
     MessageBox.Show(table.Rows[0].ItemArray[3].ToString());
else if(dt.Rows.Count > 0)
      MessageBox.Show("Table is empty");

And other advice is that you should display data into DataGrid.... Displaying data from Database into a message box is not a good programming approach..

For displaying DataTable into DataGrid in C# is as simple as below.

        SqlCommand cmd = new SqlCommand("select * from student",con);
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        DataTable dt = new DataTable();
        dt.TableName = "students";
        da.Fill(dt);
        dataGridView1.DataSource =dt;
Improve this answer

Comments

0 
using System.Data.Odbc;

OdbcConnection con = new OdbcConnection("<connectionstring>");

OdbcCommand com = new OdbcCommand("select * from TableX",con);

OdbcDataAdapter da = new OdbcDataAdapter(com);
DataSet ds = new DataSet();

da.Fill(ds,"New");

DataGrid dg = new DataGrid();
dg.DataSource = ds.Tables["New"];

you can get the connection string from:

http://www.connectionstrings.com/

Improve this answer

1 Comment

The OdbcConnection, OdbcDataCommand and OdbcDataAdapter should be in using blocks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.