Filling gridview from codebehind

Question

I have a gridview control in my ASP.NET web form. I the past I have used the same code to fill a gridview with data in a Windows Forms App.

Here is my code:

var sql = new SQL_Statements();
var stringSql = "select col1, col2, col3, col4 from table1 where col5=" + stringPO_NUM;
var sql_ds = sql.SelectFromDB(stringSql);
int DataIndex;
if (sql_ds.Tables["CurData"].Rows.Count > 0)
    for (DataIndex = 0; DataIndex <= sql_ds.Tables["CurData"].Rows.Count - 1; DataIndex++)
    {
        sql_ds.Tables["CurData"].Rows[DataIndex]["col4"].ToString();
        //Fill the datagridview with the data that was just pulled from SQL_Statements
        GV_POI.DataSource = sql_ds;
        GV_POI.DataMember = "CurData";
    }

Here is the code for the SelectFromDB:

public DataSet SelectFromDB(String Sql)
        {
            var functionReturnValue = default(DataSet);
            var Class_Connection = new SQL_Connection();
            Class_Connection.cnn.Close(); //Let's close the connection, just in case it is open
            Class_Connection.cnn.Open();                
            var myDataAdaptor = new SqlDataAdapter(Sql, Class_Connection.cnn);
            var myDataset = new DataSet();

            try
            {
                myDataAdaptor.SelectCommand.Connection = Class_Connection.cnn;
                myDataAdaptor.SelectCommand.CommandText = Sql;
                myDataAdaptor.SelectCommand.CommandType = CommandType.Text;
                myDataAdaptor.Fill(myDataset, "CurData");
                functionReturnValue = myDataset;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                Class_Connection.cnn.Close();
            }
            return functionReturnValue;
            Class_Connection.cnn.Close();
        }

Here is the code for the GridView in the ASPX page:

<asp:GridView ID="GV_POI" runat="server" AutoGenerateColumns="False">
                        </asp:GridView>

I am not sure what I am doing wrong. When the page loads, the gridview is blank. I checked the code in the debugger, and code is firing. Can someone please tell me what I am doing wrong?

Is col5 a character or numeric type? Shouldn't you be using a parameter to set that value in any case? — ron tornambe, Jul 18 '14 at 16:15
Have you checked postback? `if(!Page.IsPostBack) { CallBindFunction(); }` — Hassan, Jul 18 '14 at 16:17
@HassanNisar No. The code was fired in the Page_LoadComplete, I checked in the debugger — nate, Jul 18 '14 at 16:20
check `@Lorin's` answer I think you are missing `GV_POI.DataBind();` — Hassan, Jul 18 '14 at 16:21
Seeing your comment to @meda, have you made sure sql_ds actually contains data and not just 1 row with null or blank data? — sr28, Jul 18 '14 at 16:29
What do you think that `sql_ds.Tables["CurData"].Rows[DataIndex]["col4"].ToString();` does? — John Saunders, Jul 18 '14 at 16:32
@sr28 Yes I just doubled checked. sql_ds has data from the table. — nate, Jul 18 '14 at 19:44
As in what's the code you've got in the asp.net page for the datagrid? Something like Your columns here... — sr28, Jul 21 '14 at 14:57
I've updated my previous answer. I think it's because you're not specifying the columns and have 'AutoGenerateColumns' set to 'false'. — sr28, Jul 22 '14 at 07:57

GMD · Accepted Answer · 2014-07-23T04:04:39.587

If you are getting all data in dataset "sql_ds" then there is no need of for loop , you can do as below

GV_POI.DataSource = sql_ds.Tables[0];
GV_POI.DataBind();

and also either make AutoGenerateColumns="True" or use BoundField or TemplateField.

Example:

<asp:GridView ID="gvTest" runat="server" AutoGenerateColumns="False" GridLines="Vertical"
     OnRowEditing="gvTests_RowEditing"  AllowPaging="True" PageSize="20"
     OnRowDataBound="gvTest_RowDataBound">
<Columns>
<asp:BoundField HeaderText="ID" DataField="ID" ItemStyle-CssClass="hidecss" HeaderStyle-CssClass="hidecss" >
      <ItemStyle Width="80px" HorizontalAlign="Left" />
      <HeaderStyle Width="80px" Wrap="False" />
</asp:BoundField>                           
<asp:BoundField HeaderText="Name" DataField="Name">
      <ItemStyle Width="200px" HorizontalAlign="Left" />
      <HeaderStyle Width="200px" Wrap="False" />
</asp:BoundField>
<asp:BoundField HeaderText="Description" DataField="Description">
      <ItemStyle Width="200px" HorizontalAlign="Left" />
      <HeaderStyle Width="200px" Wrap="False" />
</asp:BoundField>                          
<asp:CommandField ShowEditButton="true" EditImageUrl="~/images/edit.png" ButtonType="Image">
      <HeaderStyle Width="20px" />
      <ItemStyle Width="20px" />
</asp:CommandField>
<asp:TemplateField>
    <ItemTemplate>
      <asp:ImageButton ID="btnDelete" runat="server" CommandName="Delete"
      ImageUrl="~/images/delete.png" AlternateText="Delete"
      ToolTip="Click to Delete"></asp:ImageButton>
    </ItemTemplate>
    </asp:TemplateField>
</Columns>
    <HeaderStyle CssClass="HeaderCss" />
    <PagerStyle CssClass="PagerCss" />                        
    <AlternatingRowStyle CssClass="AltRowCss" />
    <EditRowStyle CssClass="EditRowCss" />
</asp:GridView>

@nate i have edited my answer with example for BoundField, CommandField & TemplateField. — GMD, Jul 23 '14 at 04:05

score 0 · Answer 2 · answered Jul 18 '14 at 16:12

0

Well it appears to me you are binding GV_POI to a string

var stringSql = "select col1, col2, col3, col4 from table1 where col5=" + stringPO_NUM;
GV_POI.DataSource = stringSql;

Not an actual list of data.

I think you wanted

GV_POI.DataSource = sql_ds;

answered Jul 18 '14 at 16:12

bowlturner

1,968
4
23
35

That is how it is coded, it was a typeo, sorry about that. I edited the question – nate Jul 18 '14 at 16:15

sr28 · Answer 3 · 2014-07-22T07:56:11.227

0

I think you need to do the following where you're currently setting the datamember as it looks like you're currently not binding the data to the datagrid:

string member;
member = "CurData";
GV_POI.SetDataBinding(sql_ds, member);

EDIT - following update:

If you've set 'AutoGenerateColumns' to 'false' then you need to specify the columns. An example would be:

<Columns>
    <asp:TemplateColumn>
        <HeaderTemplate>Job Number</HeaderTemplate>
        <ItemTemplate><%# Eval("Job Number") %></ItemTemplate>
    </asp:TemplateColumn>
</Columns>

Obviously you might want to simply do a 'BoundColumn' or something similar. You'd also need to substitute in your own header data etc. You'd need to do this for every column you want to display basically. Alternatively, just set 'AutoGenerateColumns' to 'true'.

edited Jul 22 '14 at 07:56

answered Jul 18 '14 at 16:17

sr28

4,728
5
36
67

Ok, seeing as you're only returning 1 table of data anyway in your dataset then you can use GV_POI.DataBind(). – sr28 Jul 18 '14 at 16:26
I just tried that at the end of the loop. The gridview still is blank – nate Jul 18 '14 at 16:28
@nate I see you marked an answer for this. Did you try my suggestion in my edit done way before the marked answer? – sr28 Jul 22 '14 at 16:01
I am looking @ your edit now. And to answer your question, no I didn't since gmd answered the question hours before your edit. – nate Jul 22 '14 at 16:45
@nate I appreciate you looking at my edit. It would be good to know what the issue was. As for the timing of the answers my edit was done 23 hours ago (at the time of this comment) and the marked answer was done 21 hours ago... – sr28 Jul 23 '14 at 07:48

score 0 · Answer 4 · edited Jul 18 '14 at 16:34

0

You should set the DataSource property and then Bind it. Good Example can be found there: http://msdn.microsoft.com/en-us/library/fkx0cy6d(v=vs.110).aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-2

  DataSet ds = GetData(queryString);
  if (ds.Tables.Count > 0)
  {
    AuthorsGridView.DataSource = ds;
    AuthorsGridView.DataBind();
  }

edited Jul 18 '14 at 16:34

John Saunders

160,644
26
247
397

answered Jul 18 '14 at 16:19

Lorin

86
6

Still can't resolve symbol GetData or AuthorsGridView – nate Jul 18 '14 at 19:03

score 0 · Answer 5 · answered Jul 18 '14 at 16:21

0

At the end of the loop don't forget to databind()

GV_POI.DataBind();

answered Jul 18 '14 at 16:21

meda

45,103
14
92
122

Tried that, and the gridview is still blank – nate Jul 18 '14 at 16:25
@nate please show `SelectFromDB` it should return a dataTable – meda Jul 18 '14 at 16:32

score 0 · Answer 6 · answered Jul 21 '14 at 23:32

If you tried GV_POI.DataBind() appended to your code, and that didn't work, then I would look at what event is triggering your code to execute. Is it the Page Load event? Also check to see if you have any GV_POI events that could be over-writing your data binding event? For example GV_POI_DataBound or GV_POI_RowDataBound?

score 0 · Answer 7 · answered Jul 22 '14 at 02:15

There u go:

    public DataTable SelectFromDB(String Sql)
    {

        var Class_Connection = new SQL_Connection();

        Class_Connection.cnn.Open();                
        var myDataAdaptor = new SqlDataAdapter(Sql, Class_Connection.cnn);
        var myDataTable = new DataTable();

        try
        {
            myDataAdaptor.SelectCommand.Connection = Class_Connection.cnn;
            myDataAdaptor.SelectCommand.CommandText = Sql;
            myDataAdaptor.SelectCommand.CommandType = CommandType.Text;
            myDataAdaptor.Fill(myDataTable);
            Class_Connection.cnn.Close(); 
            return myDataTable;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
            Class_Connection.cnn.Close();
            return null;
        }
    }


var sql = new SQL_Statements();
var stringSql = "select col1, col2, col3, col4 from table1 where col5=" + stringPO_NUM;
var sql_ds = sql.SelectFromDB(stringSql);

GV_POI.DataSource = sql_ds;
GV_POI.DAtaBind();


<asp:GridView ID="GV_POI" runat="server" AutoGenerateColumns="False">
    <EmptyDataTemplate>No records registered</EmptyDataTemplate>
</asp:GridView>`

score 0 · Answer 8 · answered Jul 22 '14 at 06:53

0

Try This

Get Your data in DataTable and than

if (dt.Rows.Count > 0)
{
    grid.DataSource = dt;
    gridDataBind();
}

answered Jul 22 '14 at 06:53

Manoj

4,951
2
30
56

Filling gridview from codebehind

8 Answers8