ASP.NET / MySQL

Newbie FAQ and/or Problems.

•  If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation.

Set EnableEventValidation="false"

• DataPager -- Why do I have to click it twice to get it to function? DataPager not functioning properly?

If you are using the DataPager you need to put your data binding after Page_Load event, otherwise your first postback fails. Just put binding in PreRender or Render and it will solve the issue. This article doesn't demonstrate this issue, and you can spend hours trying understand why it doesn't work.

Default.aspx.vb
Protected Sub ListView1_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView1.PreRender oConnection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ConnectionString oConnection.Open() Dim command As MySqlCommand Dim adapter As New MySqlDataAdapter Dim ds As New DataSet Dim i As Integer Dim SqlString As String SqlString = "Query" command = New MySqlCommand(SqlString, oConnection) adapter.SelectCommand = command adapter.Fill(ds, "First Table") ListView1.DataSource = ds ListView1.DataBind() Label1.Text = ID oConnection.Close()

• Can you have an SqlDataSource with multiple queries?

If there is a way to do this, I'm not aware of how. To have multiple queries, it needs to be done in the code behind and bound to a DataReader, DataSet, ListView, Label, etc.

Example: Here is an example of two MySQL queries on one connection. The first is an executescalar that sums a bit in a table and sets a string to the value. The second is a query bound to a DataReader. The string created in the first query is used as a dynamic LIMIT statement on the second query:

 Default.aspx.vb
Dim cmd As MySqlCommand = New MySqlCommand("select SUM(_DSPLY) from _TABLE;", oConnection) oConnection.Open() Dim dLimit As String = cmd.ExecuteScalar Dim oCom = New MySqlCommand oCom.CommandType = Data.CommandType.Text oCom.CommandText = "QUERY LIMIT " & dLimit oCom.Connection = oConnection Dim oDataReader As MySqlDataReader oDataReader = oCom.ExecuteReader() GridView2.DataSource = oDataReader GridView2.DataBind() oConnection.Close()

• Can you create a dynamic LIMIT statement that inserts a variable into the MySQL query statement?

Example: Here is an example of a LIMIT statement created by a variable set in another query. The first query is an executescalar that sums a bit in a table and sets a string to the value. The second is a query bound to a DataReader. The string created in the first query is used as a dynamic LIMIT statement on the second query:

 Default.aspx.vb
Dim cmd As MySqlCommand = New MySqlCommand("select SUM(_DSPLY) from _TABLE;", oConnection) oConnection.Open() Dim dLimit As String = cmd.ExecuteScalar Dim oCom = New MySqlCommand oCom.CommandType = Data.CommandType.Text oCom.CommandText = "QUERY LIMIT " & dLimit oCom.Connection = oConnection Dim oDataReader As MySqlDataReader oDataReader = oCom.ExecuteReader() GridView2.DataSource = oDataReader GridView2.DataBind() oConnection.Close()

• Is it possible to dynamically set a DataPager PageSize in the code behind? Is it possible to set a DataPager PageSize at runtime or using a variable set in another query?

Yes. 

• How do you Lazy Load an Ajax TabContainer? Here is one of the few working examples of a TabContainer Lazy Load that I could find in VB. There are plenty in C#, but it took me hours to find a working example in VB. Much thanks to Shawn Burke's Blog, Where you can find OnDemandTabs.zip. It was created in .net 2.0 framework, but converted to 3.5 with no problems and worked on the first try. The code is nice and clean and much simpler than some solutions out there.

• AjaxTabContainer Auto-resize Border? If you download the Ajax toolkit, and drag a container to resize it, it will create a static height and width. If you create a Gridview with a large amount of rows, it will cross over the border. The problem is the static height. If you leave the width, but delete the height, the tab container border will resize to fit the table when it is created (this may sound like an obvious "duh," but hours were wasted on this issue and other people have experienced this issue as well). 
  
  Also, if you set a static height on a panel inside the tabcontainer, it will cause the same issue. Note, too, that with no height set, the border, especially on Lazy Loads, will be almost directly right under the tabs. There is a way to set a minimum height that will expand if needed to fit controls. The solutions is put a Div tag around the contents of the tabcontainer. The problem is, this method doesn't work correctly across all browsers. Specifically, IE 6 and under will not render it correctly.  The div tags should look like this:

            <ContentTemplate> <div style="min-height:100px;height:auto!important;height:100px;"> <asp:UpdatePanel ID="UpdatePanel3" runat="server"> <ContentTemplate> <asp:Button ID="TabButton3" runat="server" OnClick="TabButton_Click" Style="display: none;" /> <asp:Panel ID="Tabcontent3" runat="server" BorderStyle="None" howsidebar="false" Visible="False" Width="624px"> <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource2"></asp:GridView> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:scadademoConnectionString %>" ProviderName="<%$ ConnectionStrings:scadademoConnectionString.ProviderName %>" SelectCommand="SELECT id, _NAME, _VALUE FROM _Table Limit 50"> </asp:SqlDataSource> /div>

Tags:  asp.net 3.5, Visual Basic, VB, MySQL 5.1, MySQL .net connector