How to get OnPageIndexChanging in C# .NET to work with MySql

[Kurt Jakobsen]

Hello,
I have a MySql data table that I display in a GridView in ASP .NET. The table is too large to display without paging, so I have added and OnPageIndexChan ging event and corresponding handler to the GridView. But, in the GridView, when I click the next page index, the grid view disappear. See my code below.
Any help on this is very appreciated.
Best regards
Kurt Jakobsen



Code: ( text )
protected void g_GridCtrl_Page IndexChanging(o bject sender, GridViewPageEve ntArgs e)
{
g_GridCtrl.Page Index = e.NewPageIndex;
g_GridCtrl.Data Bind();
}



void FillInGridView( )
{
string myConnString = ConfigurationSe ttings.AppSetti ngs["DSN_Fyrr"];
MySqlConnection myConnection = new MySqlConnection (myConnString);
string strSQL = "SELECT LocationName FROM PartLocationDef ORDER BY LocationName;" ;
MySqlDataAdapte r myDataAdapter = new MySqlDataAdapte r(strSQL, myConnection);
DataSet myDataSet = new DataSet();
myDataAdapter.F ill(myDataSet, "FailTypeDe f");
g_GridCtrl.Data Source = myDataSet;
g_GridCtrl.Data Bind();
myConnection.Cl ose();
}


<asp:GridView ID="g_GridCtrl " runat="server" AllowSorting="t rue" AllowPaging="tr ue" DataKeyNames="L ocationName" OnPageIndexChan ging="g_GridCtr l_PageIndexChan ging" AutoGenerateCol umns="False" OnSorting="g_Gr idCtrl_Sorting" >
<Columns>
<asp:BoundFie ld ReadOnly="true" HeaderText="Loc ation Name" DataField="Loca tionName" SortExpression= "LocationNa me" />
</Columns>
</asp:GridView>

[leela mn]

in the onPageIndexchan ging event u need to bind the datasource again.......
u can keep the dataset in a session and bind it again in the event

[kenobewan]

Here is an article that may help:
Paging In ASP.NET

[Kurt Jakobsen]

in the onPageIndexchan ging event u need to bind the datasource again.......
u can keep the dataset in a session and bind it again in the event

Thanks for your reply, leela mn and kenobewan, I've looked at both solutions. On the '4 guys from rolla' page, DataGrid is used in stead of GridView, so I changed my code to reflect that, without success. Also, I tried to bind the datasource again without any success there either.

I get the first page displayed, but only receive the first page again when I push the arrow for displaying page 2.

There must be a minor detail that I am missing, but I just don't manage to see it.

Please see my modified code below. Any suggestions?

void FillInGridView( )
{
string myConnString = ConfigurationSe ttings.AppSetti ngs["DSN_Fyrr"];
MySqlConnection myConnection = new MySqlConnection (myConnString);
string strSQL = "SELECT LocationName FROM PartLocationDef ORDER BY LocationName;" ;
MySqlCommand myCommand = new MySqlCommand(st rSQL, myConnection);
MySqlDataAdapte r myDataAdapter = new MySqlDataAdapte r(myCommand);
DataSet myDataSet = new DataSet();
myDataAdapter.F ill(myDataSet, "FailTypeDe f");
g_GridCtrl.Data Source = myDataSet;
Session["MyDataSett "] = myDataSet;
g_GridCtrl.Data Bind();
myConnection.Cl ose();
}

protected void g_GridCtrl_Page IndexChanging(o bject sender, DataGridPageCha ngedEventArgs e)
{
g_GridCtrl.Data Source = (Int32)Session["MyDataSett "];
g_GridCtrl.Curr entPageIndex = e.NewPageIndex;
g_GridCtrl.Data Bind();
}


<asp:DataGrid ID="g_GridCtrl " runat="server" AllowPaging="tr ue"
OnPageIndexChan ging="g_GridCtr l_PageIndexChan ging">
</asp:DataGrid>

[leela mn]

you are typecasting the Session[""] to int32 it should be typecasted to Dataset......

[Kurt Jakobsen]

Thanks leela mn I really appreciate your help. I changed the cast type but it does not seem to help. So I added a MessageBox.Show popup in the event handler just to see if it actualy enters the event handler, and it turn's out that it does not. Hence I figure that there must be something with the control declaration or with the Web.config file.

I tried to add AutoPostBack="t rue" to the control declaration but that did not help.

My Web.config file looks like this:

<configuratio n>
<appSettings>
<add key="DSN_XXXX" value="server=Y YYY; user id=ZZZZ; password=WWWW; database=AAAA; pooling=false;"/>
<add key="bgColor" value="blue" />
</appSettings>
<system.web>
<compilation debug="true"/>
<customErrors mode="Off"/>
<pages theme="FyrrThem e"
enableEventVali dation="false"
compilationMode ="Always"
maintainScrollP ositionOnPostBa ck="true"
asyncTimeout="4 50">
<namespaces>
</namespaces>
<tagMapping>
<clear />
</tagMapping>
</pages>
<sessionState
mode="InProc"
stateConnection String="tcpip=1 27.0.0.1:42424"
sqlConnectionSt ring="data source=127.0.0. 1;Trusted_Conne ction=yes"
cookieless="fal se"
timeout="640"
/>
</system.web>
</configuration>


I recon the page_load handler should be ok:


void Page_Load(objec t sender, EventArgs e)
{
if (!IsPostBack)
{
AssignTextToLab els();
EnableEditContr ols();
FillInGridView( );
}
}

[leela mn]

set this EnableSortingAn dPagingCallback s property to true by default it is false........

[Kurt Jakobsen]

Tank's leela mn,
I tried to put that into the datagrid control definition. But still it does not enter the event handler.
I've come across an article that put some extra paging properties in the control declaration, but that did not help either:

CssClass="DataW ebControlStyle" >
<AlternatingRow Style CssClass="Alter natingRowStyle" />
<RowStyle CssClass="RowSt yle" />
<HeaderStyle CssClass="Heade rStyle" />
<FooterStyle CssClass="Foote rStyle" />
<SelectedRowSty le CssClass="Selec tedRowStyle" />
<PagerStyle CssClass="Pager RowStyle" />
<PagerSetting s Mode="NumericFi rstLast" PageButtonCount ="5" />

Also I've verified that the function that fill in the first page is not re called.

This is a hard nut to crack!

Any new ideas?

[leela mn]

can you paste both aspx and codebehind page code........

[Kurt Jakobsen]

Hello leela mn,
I've tried to strip down the script to the essence of it. If you want to try it in real life, you need to download and install the MySql community db server and the MySql connector. The connector dll should be copied into the C:\Inetpub\wwwr oot\bin directory. Further, the <appSettings> section of my Web.config that I pasted previously show how I connect. Basically you only need to make a db and a table with one or more columns and more than 5 rows, then you should be able to see my problem: When I want to select page 2, the whole gridview control disappear from the page.


My aspx file start here:

<%@ Page Language="C#" Debug="true" %>
<%@ import Namespace="Syst em.Data" %>
<%@ import Namespace="MySq l.Data.MySqlCli ent" %>

<script runat="server">


void BindData()
{
MessageBox.Show ("BindData") ;
MySqlDataAdapte r myDataAdapter = (MySqlDataAdapt er)Session["MyDataAdap ter"];
DataSet myDataSet = (DataSet)Sessio n["MyDataSett "];
myDataAdapter.F ill(myDataSet, "FailTypeDe f");
g_GridCtrl.Data Source = myDataSet;
g_GridCtrl.Data Bind();
}



void FillInGridView( )
{
string myConnString = ConfigurationSe ttings.AppSetti ngs["DSN_Fyrr"];
MySqlConnection myConnection = new MySqlConnection (myConnString);
string strSQL = "SELECT LocationName FROM PartLocationDef ORDER BY LocationName;" ;
MySqlCommand myCommand = new MySqlCommand(st rSQL, myConnection);
MySqlDataAdapte r myDataAdapter = new MySqlDataAdapte r(myCommand);
DataSet myDataSet = new DataSet();
myDataAdapter.F ill(myDataSet, "FailTypeDe f");
g_GridCtrl.Data Source = myDataSet;
Session["MyDataSett "] = myDataSet;
Session["MyDataAdap ter"]=myDataAdapter;
g_GridCtrl.Data Bind();
myConnection.Cl ose();
}


void Page_Load(objec t sender, EventArgs e)
{
if (!IsPostBack)
{
FillInGridView( );
}
}


void GridCtrl_IndexC hange(object sender, GridViewPageEve ntArgs e)
{
MessageBox.Show ("g_GridCtrl_In dexChange");
g_GridCtrl.Data Source = (DataSet)Sessio n["MyDataSett "];
g_GridCtrl.Page Index = e.NewPageIndex;
BindData();
}


</script>


<html>
<body bgcolor="beige" >
<form runat="server">
<table>
<tr>
<td>
<asp:GridView ID="g_GridCtrl " runat="server" AllowPaging="tr ue" AllowSorting="T rue"
OnPageIndexChan ging="GridCtrl_ IndexChange"
PageSize="5"
AutoPostBack="t rue" EnableSortingAn dPagingCallback s="true"
CssClass="DataW ebControlStyle" >
<AlternatingRow Style CssClass="Alter natingRowStyle" />
<RowStyle CssClass="RowSt yle" />
<HeaderStyle CssClass="Heade rStyle" />
<FooterStyle CssClass="Foote rStyle" />
<SelectedRowSty le CssClass="Selec tedRowStyle" />
<PagerStyle CssClass="Pager RowStyle" />
<PagerSetting s Mode="NumericFi rstLast" PageButtonCount ="5" />
</asp:GridView></td>
</tr>
</table>
</form>
</body>
</html>

[Kurt Jakobsen]

Hi again leela mn,
Thank you for your patience. I just managed to page now, see my solution below. I don't know if this is an optimal solution, but at least it work. If you have a better fix, please let me know.

Best regards
Kurt


void FillInGridView( )
{
string myConnString = ConfigurationSe ttings.AppSetti ngs["DSN_Fyrr"];
MySqlConnection myConnection = new MySqlConnection (myConnString);
string strSQL = "SELECT LocationName FROM PartLocationDef ORDER BY LocationName;" ;
MySqlCommand myCommand = new MySqlCommand(st rSQL, myConnection);
MySqlDataAdapte r myDataAdapter = new MySqlDataAdapte r(myCommand);
DataSet myDataSet = new DataSet();
myDataAdapter.F ill(myDataSet, "FailTypeDe f");
g_GridCtrl.Data Source = myDataSet;
g_GridCtrl.Page Index = (Int32)Session["GridPageIn dex"];
Session["MyDataSett "] = myDataSet;
Session["MyDataAdap ter"]=myDataAdapter;
g_GridCtrl.Data Bind();
myConnection.Cl ose();
}


void Page_Load(objec t sender, EventArgs e)
{
if (!IsPostBack)
{
Session["GridPageIn dex"]=0;
}
FillInGridView( );
}


void GridCtrl_IndexC hange(object sender, GridViewPageEve ntArgs e)
{
g_GridCtrl.Page Index = e.NewPageIndex;
Session["GridPageIn dex"]=g_GridCtrl.Pag eIndex;
FillInGridView( );
}

[leela mn]

FillInGridView( ) method should be called inside the (!IsPostBack){}
in your previous code u had placed it inside the postback .

chk ths modified code......

void FillInGridView( )
{
string myConnString = ConfigurationSe ttings.AppSetti ngs["DSN_Fyrr"];
MySqlConnection myConnection = new MySqlConnection (myConnString);
string strSQL = "SELECT LocationName FROM PartLocationDef ORDER BY LocationName;" ;
MySqlCommand myCommand = new MySqlCommand(st rSQL, myConnection);
MySqlDataAdapte r myDataAdapter = new MySqlDataAdapte r(myCommand);
DataSet myDataSet = new DataSet();
myDataAdapter.F ill(myDataSet, "FailTypeDe f");
g_GridCtrl.Data Source = myDataSet;
Session["MyDataSett "] = myDataSet;
g_GridCtrl.Data Bind();
myConnection.Cl ose();
}


void Page_Load(objec t sender, EventArgs e)
{
if (!IsPostBack)
{
FillInGridView( );
}

}


void GridCtrl_IndexC hange(object sender, GridViewPageEve ntArgs e)
{
g_GridCtrl.Page Index = e.NewPageIndex;
g_GridCtrl.Data Source = (DataSet)Sessio n["MyDataSett "];
g_GridCtrl.Data Bind();
}

[Kurt Jakobsen]

Thanks leela mn,
I agree, the solution I found was poor while it means that each time any control causes post_back, the db will be called, and the page will be sluggish. However, what's seems to happen is that the page handler is called, but it does not manage to call any function, only to set the page index variable. This I've concluded while calling the g_GridCtrl.Data Bind() from the handler (as you suggest) makes the grid disappear, and also if I put the FillInGridView( ) function inside the handler, the grid view disappear.

Apparently there is a configuration too much or too litle somewhere. The question is just which and how should it be. Any ideas?

best regards
Kurt

[leela mn]

i dont know what must have gone wrong i have the same code working fine........
if i will get to know something i will post........ :)

[Kurt Jakobsen]

Thanks,
Best regards
Kurt