reusable datagrid, using viewstate items as for SQL statements / problems with life cycle

[TB]

Hi All:

I am trying to create a variation on the standard datagrid, whereby the
datagrid is only shown after pressing some buttons. This reason for
this is that I would like to use the same datagrid for several tables,
and the idea is that the button events store the the SQL select
statement and the SQL update statement in view state items, which can
the be reused for all the datagrid events (paging and editing).

However I seem to have a problem understanding the life cycle of the
page, which is why I would like to ask for some help here.

The error message I keep getting is:
"Object reference not set to an instance of an object.", always
referring to the line:
dgStaffOptions.DataSource = myDataSet.Tables("mytable"

(contained in a sub called showdatagrid() - 'dgStaffOptions' is the ID
of the datagrid).

Below is the code, which I have simplied for clarity (only the first
button works). The asp:labels and the various reponse.write lines are
elements I have introduced during the bug testing.

HTML side contained in a user control (counter1u.ascx):

<%@ Control Language="vb" AutoEventWireup="false"
Codebehind="counter1u.ascx.vb" Inherits="qmsnet.counter1u"
TargetSchema="http://schemas.microsoft.com/intellisense/ie5" %>
<h1>Staff Management - Standard options</h1>
<table>
<TBODY>
<tr>
<td vAlign="top">
<TABLE id="Table1" cellSpacing="1" cellPadding="1" width="300"
border="0">
<TR>
<TD><p>Options</p></TD>
<TD><p>Change</p></TD>
</TR>
<TR>
<TD><p>Positions</p></TD>
<TD align="center"><asp:button id="Btn_Postions" Text="Edit"
runat="server"></asp:button></TD>
</TR>
<TR>
<TD><p>Company</p></TD>
<TD align="center"><asp:button id="Btn_Company" Text="Edit"
runat="server"></asp:button></TD>
</TR>
</TABLE>
<asp:label id="lbltest" Runat="server"></asp:label><br>
<asp:label id="lbltest2" Runat="server"></asp:label>
</td>
<td vAlign="top" width="600">
<asp:datagrid id="dgStaffOptions" runat="server" Runat="server"
OnUpdateCommand="EditDataGrid_Update"
OnCancelCommand="EditDataGrid_Cancel" OnEditCommand="EditDataGrid_Edit"
OnPageIndexChanged="PageChange" AllowPaging="True" PageSize="4"
autogeneratecolumns="False" Width="400px">
<Columns>
<asp:BoundColumn DataField="ID" ReadOnly="True"
HeaderText="#"></asp:BoundColumn>
<asp:BoundColumn DataField="Options"
HeaderText="Options"></asp:BoundColumn>
<asp:EditCommandColumn ItemStyle-CssClass="myListItem"
ButtonType="LinkButton" UpdateText="Update" HeaderText="Edit"
CancelText="Cancel" EditText="Edit"></asp:EditCommandColumn>
</Columns>
</asp:datagrid>
</td>
</tr>
</TBODY>
</table>

Code-behind side (counter1u.ascx.vb):

Imports MySql.Data.MySqlClient
Imports qmsnet.test2
Public Class counter1u
Inherits System.Web.UI.UserControl

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()

End Sub
Protected WithEvents lbltest As System.Web.UI.WebControls.Label
Protected WithEvents Btn_Postions As
System.Web.UI.WebControls.Button
Protected WithEvents Btn_Company As
System.Web.UI.WebControls.Button
Protected WithEvents dgStaffOptions As
System.Web.UI.WebControls.DataGrid
Protected WithEvents lbltest2 As System.Web.UI.WebControls.Label

'NOTE: The following placeholder declaration is required by the Web
Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Dim myConnection As MySqlConnection
Dim myDataAdapter As MySqlDataAdapter
Dim myDataSet As DataSet
Dim strSQLSelect As String
Dim strSQLUpdate As String
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
myConnection = New MySqlConnection("server=mysql.mydomain.com;
user id=root; password=password; database=mybase; pooling=false;")
If Not Page.IsPostBack Then
strSQLSelect = "Select ID, Options from qmsPositions order
by Options"
strSQLUpdate = "Update"

Else
strSQLSelect = ViewState("StrSQLSelect")
strSQLUpdate = ViewState("StrSQLUpdate")

End If
'Response.Write("load:" & strSQLSelect)
LoadDataFromDB()

End Sub
Private Sub Page_Prerender(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles MyBase.PreRender
Viewstate("strSQLSelect") = strSQLSelect
ViewState("strSQLUpdate") = strSQLUpdate
lbltest.Text = strSQLSelect
lbltest2.Text = strSQLUpdate



End Sub
Sub LoadDataFromDB()
Order by Options"
myDataAdapter = New MySqlDataAdapter(strSQLSelect,
myConnection)
myDataSet = New DataSet
Response.Write("load3:" & strSQLSelect)
myDataAdapter.Fill(myDataSet, "mytable")
End Sub
Private Sub Btn_Postions_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles Btn_Postions.Click
strSQLSelect = "Select ID, Options from qmsPositions Order by
Options"
strSQLUpdate = "Update qmsPositions set Options = 'columnvalue'
where ID = idvalue"
Showdatagrid()

End Sub
Private Sub Btn_Company_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles Btn_Company.Click

End Sub
Sub PageChange(ByVal sender As Object, ByVal e As
DataGridPageChangedEventArgs)
dgStaffOptions.CurrentPageIndex = e.NewPageIndex
Showdatagrid()

End Sub
Sub Showdatagrid()
dgStaffOptions.DataSource = myDataSet.Tables("mytable")
dgStaffOptions.DataBind()
End Sub
Sub EditDataGrid_Edit(ByVal Sender As Object, ByVal E As
DataGridCommandEventArgs)
dgStaffOptions.EditItemIndex = E.Item.ItemIndex
Showdatagrid()
End Sub
Sub EditDataGrid_Cancel(ByVal Sender As Object, ByVal E As
DataGridCommandEventArgs)
dgStaffOptions.EditItemIndex = -1
Showdatagrid()
End Sub
Sub EditDataGrid_Update(ByVal Sender As Object, ByVal E As
DataGridCommandEventArgs)
Dim IDint As String = E.Item.Cells(0).Text
Dim Options As TextBox = E.Item.Cells(1).Controls(0)
Dim SQLtemp As String
Dim objCommand As MySqlCommand
SQLtemp = Replace(strSQLUpdate, "columnvalue", Options.Text)
SQLtemp = Replace(SQLtemp, "idvalue", IDint)
objCommand = New MySqlCommand(SQLtemp, myConnection)
Try
myConnection.Open()
objCommand.ExecuteNonQuery()
myConnection.Close()
Catch Ex As Exception
Response.Write("<p><strong>An Error Occurred:</strong> " &
Ex.ToString() & "</p>" & vbCrLf)

Finally
myConnection.Close()
End Try
LoadDataFromDB()

dgStaffOptions.EditItemIndex = -1
Showdatagrid()
End Sub

End Class

=====

Thanks a bundle in advance!!

TB

 

[TB]

I am not getting a lot of takers on this one, perhaps because I have
included so much code. But without it, I didn't feel I could explain my
case properly.

Any replies will higly appreciated.

Thanks in advance.

Trym

 

[Guest]

Hi Trym,

Welcome again. I looked briefly at your code. The error you get is
basically saying that myDataSet.Tables("mytable") was nothing at the time you
tried to bind it to the datagrid. It has nothing to do with the page
lifecycle.

If you have VS use the debugger to step through the code to see if
myDataSet.Tables("mytable") is nothing or simply add an if statement:

If not myDataSet is nothing andAlso not myDataSet.Tables("mytable") is
nothing Then
dgStaffOptions.DataSource = myDataSet.Tables("mytable")
dgStaffOptions.DataBind()
Else
Response.Write("The DataSet at this stage is empty")
End If

If you get the message that the DataSet is empty, step again using the
Debugger to see if the method LoadDataFromDB is called before you reached to
that point again.

Let me know how far you get using the debugger.

 

[TB]

Thanks a lot for replying to my message.

I have inserted the code you had provided with one small change:
Iinstead of:
"If not myDataSet is nothing and Also not myDataSet.Tables("mytable")
is nothing Then"

I wrote:
"If Not myDataSet Is Nothing And Not myDataSet.Tables("mytable") Is
Nothing Then"

The result was that on the first run, i.e. without pressing the
btnPosition button and therefore without any postback, your inserted
condition was false and therefore the "The DataSet at this stage is
empty" message was printed.

Almost same thing happened after pressing the button and postback
therefore was true, the difference being that on the first run the line
"Response.Write("load3:" & strSQLSelect)"
inside the LoadDataFromDB did return:
"load3:Select ID, Options from qmsPositions order by Options"
but the second time (pressing the button and Page.IsPostBack = true)
that same code line returned only:
"load3:",
i.e StrSQLSelect = "". That lead me to discover through the debugger,
that that the Private Sub Page_Prerender does not run at all, not the
first time and not during postback. I am not not an ace programmer but
I should think that if the Private Sub Page_Prerender had fired, the
"Response.Write("load3:" & strSQLSelect)" line should returned the same
result during postback because the StrSQLSelect variable would have
contained the value of the ViewState("StrSQLSelect") item.

Could this be the beginning of the path leading to the solution of the
problem at hand?

TB

 

[TB]

Oh, and yes: LoadDataFromDB( ) always loads.

TB

 

[Guest]

Somewhere there you are reseting the value of the variable StrSQLSelect
before you save it in the ViewState. Try place a break (during debugging) on
the first line within the Page_PreRender method and look at what values are
you saving in the ViewState for that variable. Then place a break on every
the end of every method to see when this variable (which private to the
class) turned into an empty string.

 

[TB]

Once again thanking you for the attention you are paying to this issue.

Experiement 1:

I have set a break point at
"Viewstate("strSQLSelect") = strSQLSelect" (first line of Private Sub
Page_PreRender)

And just as you predicted the program halted right there, which means
that Private Sub Page_PreRender is loaded.

Anyway, once the pogram had stopped at the above-mentioned line, I
opened Debug - Windows - Locals windows and I could see that both
Me.StrSQLSelect and Me.StrSQLUpdate contained the correct string values
("Select ID, Options from qmsPositions order by Options" and "Update
qmsPositions set Options = 'columnvalue' where ID = idvalue"). I could
also see that Me.viewstate.keys.count = 2 although next to
Me.viewstate.item it said "<cannot view indexed property>".

Next experiment: Following your advice, I inserted breakpoints at every
"End Sub" statement.

Result for the first run (Page.IsPostBack = False):

At all breaks StrSQLSelect = Select ID, Options from qmsPositions order
by Options" and StrSQLUpdate = "Update" - completely as intented, so no
problems so far.

Result for the second run (Pressing Btn_Postions and therefore
Page.IsPostBack = True):

Break 1 (at the beginning of Private Sub Page_Load): StrSQLSelect and
StrSQLUpdate are both nothing. 2 viewState items exist, but I cannot
see the values.

Break 2 (at the end of Private Sub Page_Load but just before jumping to
LoadDataFromDB): StrSQLSelect and StrSQLUpdate are STILL nothing - THAT
IS NOT GOOD, because since Page.IsPostBack = true, the else part of the
if - End if statement has loaded where the two variables should be
filled witth corresponding viewstate values. If StrSQLSelect and
StrSQLUpdate remain nothing after that, then the viewstate item do not
contain the right data at postback. Why?? 2 viewState items still exist
but I cannot see the values.

Break 3 (at the end of LoadDataFromDB): Same situation

Break 4 (at the of Private Sub Btn_Positions, but just before jumping
to Showdatagrid): Now StrSQLSelect and StrSQLUpdate now contain the
correct values: "Select ID, Options from qmsPositions Order by Options"
and "Update qmsPositions set Options = 'columnvalue' where ID =
idvalue" (marked in red in the locals window)

Break 5 (at the end of showdatagrid): Same situation

Break 6 (at the end of Private Sub Page_PreRender): Same sitiation
except that the no values are marked in red in the locals window.

No datagrid is shown at the end of entire postback run.

Would you like me to send you the files and and SQL command file (so
that you can create the corresponding data source) so that you try it
yourself? (it might be faster - I don't want to waste too much of your
time) Perhaps I commit some stupid mistake handling VS that distort the
results.

TB

 

[Guest]

:

...

Break 2 (at the end of Private Sub Page_Load but just before jumping to
LoadDataFromDB): StrSQLSelect and StrSQLUpdate are STILL nothing - THAT
IS NOT GOOD, because since Page.IsPostBack = true, the else part of the
if - End if statement has loaded where the two variables should be
filled witth corresponding viewstate values. If StrSQLSelect and
StrSQLUpdate remain nothing after that, then the viewstate item do not
contain the right data at postback. Why?? 2 viewState items still exist
but I cannot see the values.

Add the following line at the beginning of the Page_Load:
Response.Write("EnableViewState = " & Page.EnableViewState)

If you get a message that "EnableViewState=False" then basically your
problem is that you have a setting somewhere that disabled the ViewState.
Look into both your web.config and the machine.config (which is located at
\Windows\Microsoft.Net\Framework\xxx\config directory (where xxx is 1.0.3705
for version 1.0 of the Framework, or 1.1.4322 for version 1.1 or 2.0.50215
for version 2.x) Search for enableViewState. Usually you should find an
entry that looks like this:

<pages buffer="true" enableSessionState="true" enableViewState="true"
enableViewStateMac="true" autoEventWireup="true" validateRequest="true"/>

[...]

Would you like me to send you the files and and SQL command file (so
that you can create the corresponding data source) so that you try it
yourself? (it might be faster - I don't want to waste too much of your
time) Perhaps I commit some stupid mistake handling VS that distort the
results.

I think you have done great using the debugger.

 

[TB]

Please hang on for another 24 hours. I will answer you tomorrow.

TB

 

[TB]

Hi again:

The result is:

EnableViewState = True

TB

 

[Guest]

I just cut and paste your code in my project, commented out the database
connection and retrieval lines ran it and realized where the error is. The
problem you have is one of case-sensitivity. You saved to the ViewState
using the following lines:

Viewstate("strSQLSelect") = strSQLSelect
ViewState("strSQLUpdate") = strSQLUpdate

and you retreived using the following lines:

strSQLSelect = ViewState("StrSQLSelect")
strSQLUpdate = ViewState("StrSQLUpdate")

Do you see the difference in the variable names between the ones you saved
and the ones you attempted to retrieve? The ViewState variable names are
case-sensitive.

 

[TB]

I have rewritten the whole thing, simplying somewhat and now using
session state instead of view state. It basically works. Thanks for
your help which has been a valuable lesson in bugtesting.

TB

 

[Guest]

You are welcome.