c# SQLDATAadapter issue with GridView

Question

I am trying to attach the results of an SQL query to a gridview without creating a collection and instead using the SQLdataAdapter.

DataTable dt = new DataTable();
            using (SqlCommand cm = cn.CreateCommand())
            {
                StringBuilder ct = new StringBuilder();

                ct.AppendLine("SELECT DISTINCT........");
                ct.AppendLine("FROM ...... er");
                ct.AppendLine("INNER JOIN ....... ev ON er.eKey = ev.eKey");
                ct.AppendLine("WHERE UserId ='" + _CurrentUser.UserId + "' AND [Type] = 'R' AND EndDate is NULL");
                ct.AppendLine("Order By er.eKey DESC");
                Debug.WriteLine(ct.ToString());
                cm.CommandType = CommandType.Text;
                cm.CommandText = ct.ToString();
                System.Diagnostics.Debug.WriteLine(ct.ToString());
                SqlDataAdapter dr = new SqlDataAdapter(cm);
                dr.Fill(dt);
            }

Previously I was using a simple table format to represent my data and it worked fine.

        //    int i = 0;
        //    foreach (DataRow row in dt.Rows)
        //    {
        //        if (i++ == 0)
        //            Response.Write("<span class='style5'><strong><span class='style6'><center>Existing Enrollments:</center><br/><br/></span></strong></span><table><tr><th>Enroll Key<br/></th><th>Name<br/><br/></th><th>DBA<br/><br/></th><th>Address<br/><br/></th><th>City<br/><br/></th><th>State<br/><br/></th><th>Zip<br/><br/></th><th>TIN<br/><br/></th><th>UserID<br/><br/></th></tr>");
        //        Response.Write("<tr>");
        //        foreach (DataColumn column in dt.Columns)
        //        {
        //            Response.Write("<td>" + row[column].ToString() + "</td>");
        //        }

Now I am trying this method:-

gvwERAenrollments.DataSource = dt;;

and it gives me an empty gridview.

my reference:- http://csharp.net-informations.com/dataadapter/datagridview-sqlserver.htm

Can anyone help me with this? There are going to be multiple rows returned by the query.

Brinky · Accepted Answer · 2014-02-12 22:41:47Z

1

I would assign the datatable to the gridview directly: gvwEnrollments.DataSource = dt;

Since this is not a DataSet that you are returning from the sql query, directly assigning the results table to the gridview should work just fine.

answered Feb 12, 2014 at 22:41

Brinky

4183 silver badges9 bronze badges

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

9 Comments

Philo Over a year ago

I realized that I changed that quickly, however my gridview shows up with no records... however previously I was using a simple table and that worked.

Brinky Over a year ago

I would suspect the sql query is not returning any rows, then. Put your dt in a Watch, a breakpoint on the DataSource assignment, and inspect the dt for rows. Then you'll know if your dt is actually getting values from the database.

Philo Over a year ago

huh, its quite strange that with my initial table method I get all the values populated as expected, but with the dt i am getting an {} set.

Brinky Over a year ago

I think you'll be fine with the direct assignment, once you get your data into that table :). Maybe try writing the sql query without using the data adapter?

Philo Over a year ago

whats confusing is that with the same sqldataadapter i get all the values populated into my table...did u take a look at my table code? its in my question .. i updated it to show how i was populating the table.

|

Tom Brown · Accepted Answer · 2014-02-12 22:43:13Z

0

you defined dt as a DataTable

DataTable dt = new DataTable();

perhaps it should be a DataSet ?

Or .. just use gvwEnrollments.DataSource = dt;

answered Feb 12, 2014 at 22:43

Tom Brown

8331 gold badge13 silver badges21 bronze badges

2 Comments

Philo Over a year ago

yes i tried both DataSet and the direct assignment. but my gridview shows up as empty. I updated my question.

Tom Brown Over a year ago

If you have SSMS, use the output of Debug.WriteLine(ct.ToString()); and paste into SSMS - see if you get results, remove the where clause - check if any results, re-add where clause bit by bit until you find the part that is excluding all results.

RamblinRose · Accepted Answer · 2014-02-12 22:49:08Z

0

Tables is not a property of DataTable, but it is a property of DataSet. Also the DataSource may need to be updated, unless you set the control's datasource before actually fetching the data.

You could try:

DataSet ds = new DataSet();
ds.Tables.Add(dt);
gvwEnrollments.DataSource=ds;

answered Feb 12, 2014 at 22:49

RamblinRose

1926 bronze badges

Collectives™ on Stack Overflow

c# SQLDATAadapter issue with GridView

3 Answers 3

9 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related