C# Add column in datatable from database

Question

I have a datatable from a stored procedure. I want to add a column named "Assessment" which will be populated from a sql query. Finally I want to populate a store with this datatable. In visual studio I see that the datatable column that contains the "SiteId" is named 7. But I receive this error:

Load failed:Column '7' does not belong to table .

and the code:

DAL.DBDataContext dc = new DAL.DBDataContext();
try
{
    SqlConnection sqlConnection1 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);
    SqlCommand cmd = new SqlCommand();
    SqlDataReader sqlreader;

    cmd.CommandText = "StorProc";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@CTid", ctid));

    cmd.Connection = sqlConnection1;
    sqlConnection1.Open();
    sqlreader = cmd.ExecuteReader();

    var dt = new DataTable();
    dt.Load(sqlreader);

    //Add assessment column
    dt.Columns.Add("Assessment", typeof(System.String));

    var data = new List<object>();
    foreach (DataRow r in dt.Rows)
    {
        if (r[7] != null)
        {
            var res = (from d in dc.Doctors
                       join ct in dc.CTUsers on d.id equals ct.DoctorId
                       where ct.CTid == ctid && ct.SiteId == Int32.Parse(r["7"].ToString())
                       select d).FirstOrDefault();
            r["Assessment"] = res.Assessment;
        }                     
        data.Add(r);
    }

    this.storeSites.DataSource = dt;
    this.storeSites.DataBind();
    sqlConnection1.Close();
}
catch (Exception a)
{
    X.Msg.Alert("Warning", "Load failed:" + a.Message).Show();
}

Does r[6] or r[8] work? Seems odd that the column name is 7. — pookie
– pookie, Commented Apr 4, 2017 at 13:14
if I run the store procedure in the sql server there are column names like "id" etc. But if I open ItemArray of r in visual studio I see only numbers. Check uploaded photo — atroul
– atroul, Commented Apr 4, 2017 at 13:19
Essentially, Int32.Parse(r["7"].ToString() is your problem, I believe. — pookie
– pookie, Commented Apr 4, 2017 at 13:56

pookie · Accepted Answer · 2017-04-04 15:52:02Z

1

Please try this:

foreach (DataRow r in dt.Rows)
{
  foreach(DataColumn column in dt.Columns)
  {
    if (r[column] != null && r[column].ColumnName == "some column name")
    {
     var res = (from d in dc.Doctors
     join ct in dc.CTUsers on d.id equals ct.DoctorId
     where ct.CTid == ctid && ct.SiteId == Int32.Parse(r[column].ToString())
     select d).FirstOrDefault();
     r["Assessment"] = res.Assessment;
    }                     
  }
  data.Add(r);
}

Int32.Parse(r["7"].ToString() is not correct. You are requesting a column named 7.

I think the above code is more clear.

Edit

You can also access the row-column value like this:

r.ItemArray[i].ToString()

Like so:

for (int i = 0; i < dt.Rows.Count; i++)
{
  var r = dt.Rows[i];
  var value = r.ItemArray[7].ToString();
    if (!String.IsNullOrEmpty(value))
    {
     var res = (from d in dc.Doctors
     join ct in dc.CTUsers on d.id equals ct.DoctorId
     where ct.CTid == ctid && ct.SiteId == Int32.Parse(value)
     select d).FirstOrDefault();
     r["Assessment"] = res.Assessment;
    }   
  data.Add(r);
}

edited Apr 4, 2017 at 15:52

answered Apr 4, 2017 at 13:52

pookie

4,16213 gold badges61 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

atroul Over a year ago

But using this approach it will check all columns to find a value that is equal to ct.SiteId right? I want to check only a specific one to avoid any error

pookie Over a year ago

I see. Could you not add to the if statement? r[column] != null && r[column].ColumnName == "some column name"?

Olivier Jacot-Descombes · Accepted Answer · 2017-04-05 15:08:57Z

The DataRow class has an overloaded indexer to access the columns.

public object this[int columnIndex] { get; set; }

... accesses the column by its int index (this is what we can see on your image). You use it by passing it an int:

object o = r[7];   // NOT r["7"] !

The other overload

public object this[string columnName] { get; set; }

... requires a column name passed as string:

object o = r["Assessment"];

If the column was really named "7", then you would have to access it like this:

object o = r["7"];  // But this is wrong in your case!

Note that the type of the argument passed to the index makes the difference. So r[7] and r["Assessment"] both work in your case. Using the name is preferred, as the index might change if the schema or the query changes. r["7"] does not work, as there is no column named like this.

See:

Member Overloading on MSDN.
Overloading, C# in Depth by Jon Skeet.

Collectives™ on Stack Overflow

C# Add column in datatable from database

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related