Sometimes, you will be programmatically changing tables or the number of rows, and binding them to an existing datagrid. If you have a higher page number selected when one of these changes happens, you will get a page count error like this:
Invalid CurrentPageIndex value. It must be >= 0 and < the PageCount.
To fix this, do a Try/Catch error routine like this:
With MyDataGrid Try binddata ' where binddata is a databinding routine Catch .currentpageindex=0 binddata End Try end with
This tutorial is not meant to be an exhaustive one, but merely a ‘look’ at some of the syntax used in Regular Expressions, so that it will not look quite so ‘foreign’ to you, the next time you look at a Regular Expression inside the Regular Expression Validator in ASP.Net.
The use of regular expressions is based on the contents of a string, matching criteria set in play by the assigned Regular Expression. It tests for a pattern within a string. For instance, let’s say the string to search comes from a text box called ‘Text1′. Let’s say that the Regular Expression (matching characters) we are searching for is any lower case letter. The Regular Expression would be [a-z]. Therefore, the string returned from ‘Text1.text’ will be searched and matched against the Regular Expression. Regular Expressions in ASP.Net, used in Validator Server Controls are kind of like applying rules to the text input. If the Regular Expression above were put into a validator for ‘Text1′, then, since anything other than lower case letters would NOT match, if we put in the number 6, it would FAIL validation. On the otherhand, ‘top’ would PASS validation.
At this point, we need to stop and discuss several characters (Metacharacters) we must come to understand when using Regular Expressions (Regex). Above, we saw two of them – the brackets ([])and the dash (-). First of all, we call any characters we use for search or matching, ‘Literals’. The ‘a’ and the ‘z’ in the example above are ‘Literals’. Next we come to the ‘Metacharacter’. Metacharacters in the above example are the brackets and the dash. You can also use the caret (^)/(Shift-6). Also, we can use the dollar sign ($), the question mark (?), the asterisk (*), the plus sign (+), and the period (.). The meanings for these Metacharacters are described below.
Continues…
There are many times which you might need to change the properties of an ASP.Net WebControl. There are many more properties available in ASP.Net than there are in pure HTML. Also, if you’ve ever programmed in VB (before DotNet), Though the concept is pretty much the same, The path to get to where you are going is quite different in some cases.
Remember, if you want to do any programmatic color changes, you must do a System.Drawing Import (<%@ Import Namespace=”System.Drawing” %>). In all cases, checking out the Class Browser in the Quickstart Tutorials will help you get a feel of the syntax needed for each control. Check out the syntax used in the example below, then, go over to the Class Browser for Web Controls – choose TextBox and you’ll see what I mean by drilling down through the properties.
As always, in order to try it out, merely copy the following code directly into a blank page, save it as an aspx file and run it from your web server. Naturally, we recommend using ASP Express for this.
<%@ Import Namespace=”System.Drawing” %>
<html>
<head>
<meta name=”GENERATOR” Content=”ASP Express 2.0″>
<title>Text Box Programmatic Changes</title>
<script language=”VB” runat=”server”>
Sub Button1_click(Source as Object, E as EventArgs)
text1.BackColor=Color.FromName(BC.SelectedItem.text)
text1.forecolor=color.white
text1.bordercolor=color.blue
text1.borderwidth=unit.point(bW.selecteditem.text)
text1.height=unit.point(100)
text1.width=unit.point(500)
text1.Font.Size = FontUnit.Point (FS.SelectedItem.text)
End Sub
</script>
</head>
<body>
<Form id=”Form1″ runat=”server”>
<table with=”75%” border=”1″> <tr>
<td align=”Center” valign=”Top”><b>Border Width:</b><br>
<asp:dropdownlist id=”bW” runat=”server”>
<asp:listitem selected=”true”>2</asp:listitem>
<asp:listitem>3</asp:listitem>
<asp:listitem>5</asp:listitem>
<asp:listitem>7</asp:listitem>
<asp:listitem>10</asp:listitem>
</asp:dropdownlist></td>
<td align=”Center” valign=”Top”><b>Font Size:</b><br>
<asp:dropdownlist id=”FS” runat=”server”>
<asp:listitem selected=”true”>24</asp:listitem>
<asp:listitem>48</asp:listitem>
<asp:listitem>56</asp:listitem>
<asp:listitem>72</asp:listitem>
</asp:dropdownlist></td>
<td align=”Center” valign=”Top”><b>BackColor:</b> <br>
<asp:dropdownlist id=”BC” runat=”server”>
<asp:listitem selected=”true”>Gray</asp:listitem>
<asp:listitem>Khaki</asp:listitem>
<asp:listitem>Blue</asp:listitem>
<asp:listitem>Black</asp:listitem>
<asp:listitem>Green</asp:listitem>
<asp:listitem>AntiqueWhite</asp:listitem>
<asp:listitem>LightGray</asp:listitem>
</asp:dropdownlist>
</td>
</tr>
</table>
<asp:TextBox id=”text1″ style=”text-align:center” text=”This is the text” runat=”server” /><br>
<asp:Button id=”Button1″ Text=”Submit” onclick=”button1_click” runat=”server” />
</Form>
</body>
</html>
Many times, when you are returning results from a database, there will be nulls. Here’s how to handle it when it happens while using a DataReader:
While objDR.Read
sAuthor=objDR("Author")
sBorn=FixBirthDate(objDR("YearBorn"))
End While
DataBind
End Sub
Here’s the FixBirthDate Function:
Function fixBirthDate(sItem) as String if sItem is System.DBNull.Value Then fixBirthDate="<i><font Color='#FF0000'>No Date Specified</font></i>" else fixBirthDate="<b>" & sItem & "</b>" End If End function
Then, on the page, you can refer to the items like this:
<%# sAuthor%> – <%# sBorn%>
Here’s a complete Code Sample, showing this in a slightly varied way, but with the same general concept:
Handling Database Nulls with a DataReader
The basics of using Parameterized Queries in ASP.Net is another tutorial. Be sure to read Part 1.
Using ‘Like’ in the Where Clause, in Parameterized Queries, is one of the more powerful options in SQL. However, it can also be very draining, resource-wise, if used improperly. Sometimes, way too many records can be returned – and in doing so, not only does it take much longer to retrieve, but the resource drain is very costly.
When using ‘Like’ in a Where Clause, we must use a ‘wildcard’. In the old days, SQL Server’s wildcard was the percent sign (%), while pre-Jet MS Access databases used the asterisk (*). Luckily, now, for both the OleDb Managed Provider (since it uses the Jet engine), and the SQL Server Managed Provider, no matter which database we’re using, we can use the percent sign (%).
For instance, let’s say we have a Name field in the table that includes both first and last names. To query the table for all those people with the last name of Smith, we couldn’t use an SQL statement like:
Sql=”Select * from TableName where Name=’Smith’”
Since first AND last names are in one field, this query wouldn’t return any records. To accomplish the above scenario, we’d need to use ‘Like’ in the Where Clause.
Sql=”Select * from TableName where Name Like ‘Smith’”
But, as you can probably guess – here’s where the wildcards come into play. Actually, this SQL statement isn’t good enough quite yet. We need to use a wildcard either before, after or both before AND after the criteria, like:
Sql=”Select * from TableName where Name Like ‘%Smith’”
This would return records if any portion of the field searched ENDED with ‘Smith’.
Continues…
Just copy and paste this code into a new blank page. Change the Database name, UID and password. It uses the pubs database with SQL Server and save it as a ‘.aspx’ page.
This code will, upon loading the page, present you with a Text Box, where you will enter a last name. In this particular search, the lastname field in the database is what is searched. Then, based on the search criteria, will search for all authors with the last name chosen, displaying all items in a Datagrid.
As you will also see, if you type in a name that is not actually listed in the table, you will see how to handle this problem with an if/then statement.
SQL Server
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<head>
<script language="VB" runat="server">
Sub doQuery(Source as Object, E as EventArgs)
Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyCommand As SqlDataAdapter
Dim strName as string
strName=Name.Text
Label1.Text="You searched for : " + strName
Dim MySQL as string
MySQL="select * from Authors where au_lname =’" & strName & "’"
panel1.visible="true"
label2.text=MySQL
MyConnection = New SqlConnection("server=localhost;uid=sa;pwd=pwd;database=pubs;")
MyCommand = New SqlDataAdapter(MySQL, MyConnection)
DS = new DataSet()
MyCommand.Fill(ds, "Authors")
if ds.tables(0).rows.count>0 then
MyDataGrid.DataSource=ds.Tables("Authors").DefaultView
MyDataGrid.DataBind()
else
lblResults.text="Sorry – no Data Found!"
End if
End Sub
</script>
<title>ASP Express.com – Search a Database – Display Results in a DataGrid Control</title>
</head>
<body>
<h3><font face="Verdana">Search a Database – Display Results in a DataGrid Control</font></h3>
<Form id="form" runat="server">
<asp:textbox id="Name" runat="server" />
<asp:Button id="button" Text="Search" onclick="doQuery" runat="server" />
</Form>
<b>Hint:</b> :<br>
<i>Type in White, Greene, Hunter, Stringer, Smith or Carson</i>
<asp:Panel ID="Panel1" runat="server" visible="False">
<hr>
<div align="center"><b><i>Search Results</i></b>
<asp:Label ID="label1" runat="server" /><br>
<asp:Label ID="label2" runat="server" /><p>
<ASP:DataGrid id="MyDataGrid" runat="server"
Width="700"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
EnableViewState="false"
/>
</div>
<b><i><asp:Label ID="lblResults" runat="server" /></i></b>
</asp:Panel>
</body>
</html>
With MS Access, replace the code at the top with:
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<html>
<head>
<script language="VB" runat="server">
Sub doQuery(Source as Object, E as EventArgs)
Dim DS As DataSet
Dim MyConnection As OleDbConnection
Dim MyCommand As OleDbDataAdapter
Dim strName as string
strName=Name.Text
Label1.Text="You searched for : " + strName
Dim MySQL as string
MySQL="select * from Authors where au_lname =’" & strName & "’"
panel1.visible="true"
label2.text=MySQL
MyConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("\biblio.mdb") & ";")
MyCommand = New OleDbDataAdapter(MySQL, MyConnection)
DS = new DataSet()
MyCommand.Fill(ds, "Authors")
if ds.tables(0).rows.count>0 then
MyDataGrid.DataSource=ds.Tables("Authors").DefaultView
MyDataGrid.DataBind()
else
lblResults.text="Sorry – no Data Found!"
End if
End Sub
</script>
When using DropdownLists and Listboxes, or any control which uses ‘selecteditem.text’ to get the selected item, the major reason for not getting the correct item is as follows. If you are binding the control at Page_load, you MUST surround the binding code with a page.ispostback/if/then statement.
Something like this:
if not Page.IsPostBack then
‘do your databinding and/or list selection here
end if
Inside Page_load, you need to make sure that, when it posts back, the actual item selected is maintained….
The reasoning is as follows:
If you have as specific item being selected during page_load – it will always be selected upon every subsequent page_load – the page_load sub is exactly that – it tells the page what to do when the page is loading.
If you post back, based on a selection, you naturally, would not want this to happen – you only want it to happen the first time the page loads – not on a postback…
SO — to get around this, you need to qualify whether or not the page is loading for the first time, or it’s posting back.
For further information, check out the tutorial on PostBack
At this point, this is not really much of a tutorial, other than to show you some of the neat things that can be done with ASP.Net.
The Book, Chapter and verse DropDownlists are set up with AutoPostBack=”True”, along with Subs triggered by the OnSelectedIndexChanged Event, so that whenever an item is chosen, it can trigger the event and not need to have a ‘Submit’ button to do it.
Past that, you will see that the display portions are done using a DataGrid and a DataList.
The Source Code for the User Control is shown below. Enjoy.
Source Code:
<%@ Import Namespace=”System.Data” %>
<%@ Import Namespace=”System.Data.OleDB” %>
// <![CDATA[
Sub Page_Load(Source as Object, E as EventArgs)
if not Page.IsPostBack then
doData()
end if
End Sub
Sub doData()
Dim strConn as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
server.mappath("\data\bible.mdb") & ";"
Dim MySQL as string ="Select ID, name from books"
Dim MyConn as New OLEDBConnection(strConn)
Dim Cmd as New OleDBCommand(MySQL,MyConn)
MyConn.Open()
Mybooks.DataSource = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Mybooks.DataBind()
Mybooks.items.insert(0,"")
End Sub
Sub dd1Books_SelectedIndexChanged(Source as Object, E as EventArgs)
txtSearch.text=""
doChapters
End Sub
Sub doChapters()
DGSearch.visible="False"
MyDataGrid.currentpageIndex=0
lblcount.text=MyDataGrid.currentpageindex & " - " & MyDataGrid.pagecount
Dim strConn as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
server.mappath("\data\bible.mdb") & ";"
Dim MySQL as string = "Select Distinct Chapter from chapters Where book = " & _
MyBooks.selecteditem.value
Dim MyConn as New OleDBConnection(strConn)
Dim Cmd as New OLEDBCommand(MySQL, MyConn)
MyConn.Open()
MyChapters.DataSource = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
MyChapters.DataBind()
MyChapters.items.insert(0," ")
MyChapters.visible="True"
lblChapters.visible="True"
MyVerses.visible="false"
lblVerses.visible="false"
MyDataList.visible="false"
MyDataGrid.visible="false"
lblHdr.visible="false"
End Sub
Sub ddlChapters_SelectedIndexChanged(Source as Object, E as EventArgs)
doVerses
MyDataGrid.visible="True"
DGSearch.visible="False"
FillGrid
End Sub
Sub doVerses()
if MyVerses.visible="True" then
MyDatalist.visible="false"
else
MyVerses.visible="True"
lblVerses.visible="True"
end if
Dim strConn as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
server.mappath("\data\bible.mdb") & ";"
Dim MySQL as string = "Select distinct verse from chapters Where book = " & _
MyBooks.selectedItem.value & " and " & _
"chapter=" & MyChapters.selectedItem.text
Dim MyConn as New OleDBConnection(strConn)
Dim Cmd as New OLEDBCommand(MySQL, MyConn)
MyConn.Open()
MyVerses.DataSource = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
MyVerses.DataBind()
MyVerses.items.insert(0," ")
End Sub
Sub ddlVerses_SelectedIndexChanged(Source as Object, E as EventArgs)
doText
End Sub
Sub doText()
Dim strConn as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("\data\bible.mdb") & ";"
Dim MySQL as string = "Select book, chapter, verse, versetext from chapters Where " & _
"book = " & MyBooks.selectedItem.value & " and chapter=" & MyChapters.selectedItem.text & " and " & _
"verse=" & MyVerses.selecteditem.text
Dim MyConn as New OleDBConnection(strConn)
Dim Cmd as New OLEDBCommand(MySQL, MyConn)
MyConn.Open()
MyDataList.DataSource = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
MyDataList.DataBind()
MyDatalist.visible="True"
FillGrid
lblHdr.text=MyBooks.selecteditem.text
End Sub
Sub FillGrid()
DGSearch.visible="false"
MyDataGrid.visible="true"
Dim strConn as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
server.mappath("\data\Bible.mdb") & ";"
Dim MySQL as string = "Select books.name, chapters.chapter, chapters.verse, chapters.versetext from " & _
"chapters INNER JOIN books ON books.ID= chapters.book where Chapters.Book=" & _
MyBooks.selecteditem.value & " and " & _
"Chapter = " & MyChapters.selecteditem.text & " order by verse"
Dim MyConn as New OleDBConnection(strConn)
Dim ds as DataSet=New DataSet()
Dim Cmd as New OleDBDataAdapter(MySQL,MyConn)
Cmd.Fill(ds,"chapters")
MyDataGrid.Datasource=ds.Tables("chapters").DefaultView
With MyDataGrid
Try
.DataBind()
Catch
.currentpageindex=0
finally
.databind()
End Try
end with
lblHdr.visible="True"
lblHdr.Text="Book of " & MyBooks.selecteditem.text
lblcount.text=MyDataGrid.currentpageindex & " - " & MyDataGrid.pagecount
End Sub
Sub doSearch(Source As Object, E As EventArgs)
dgSearch.currentpageindex=0
getData
End Sub
Sub getData()
Dim MySQL as String
lblHdr.Text="Text Searched For : " & txtSearch.text
lblHdr.visible="true"
DGSearch.visible="True"
MyDataGrid.visible="false"
Dim strConn as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
server.mappath("\data\bible.mdb") & ";"
if NewTestament.Checked "True" then
MySQL = "Select books.ID, books.name, chapters.book, chapters.chapter, chapters.verse, chapters.versetext " & _
"from chapters INNER JOIN books ON books.ID= chapters.book Where " & _
"versetext Like '%" & txtSearch.text & "%' Order by Chapters.book,Chapters.Chapter, Chapters.Verse"
else
MySQL = "Select books.ID, books.name, chapters.book, chapters.chapter, chapters.verse, chapters.versetext " & _
"from chapters INNER JOIN books ON books.ID= chapters.book Where versetext Like '%" _
& txtSearch.text & "%' And Books.id > 39 Order by Chapters.book,Chapters.Chapter, Chapters.Verse"
End If
Dim MyConn as New OleDBConnection(strConn)
Dim ds as DataSet=New DataSet()
Dim Cmd as New OleDBDataAdapter(MySQL,MyConn)
Cmd.Fill(ds, "chapters")
MyDataList.visible="false"
dgSearch.Datasource=ds.Tables("chapters").DefaultView
With DGSearch
Try
.DataBind()
Catch
.currentpageindex=0
finally
.databind()
End Try
end with
lblcount.text=dgsearch.currentpageindex & " - " & dgsearch.pagecount
End Sub
Sub Change_Search(sender As Object, e As DataGridPageChangedEventArgs)
DGSearch.CurrentPageIndex = e.NewPageIndex
getData
End Sub
Sub Page_Change(sender As Object, e As DataGridPageChangedEventArgs)
MyDataGrid.CurrentPageIndex = e.NewPageIndex
FillGrid
End Sub
// ]]>
<!–
–>
Just copy and paste this code into a new blank page. Change the Database name, UID and password. It uses the pubs database with SQL Server and save it as a ‘.aspx’ page.
This code will, upon loading the page, fill a DropDown List with the States from the authors table. Then, based on the search criteria, will search for all authors from the state chosen, displaying all items in a Datagrid.
<%@ Page Debug=”False” Trace=”False” %>
<%@ Import Namespace=”System.Data” %>
<%@ Import Namespace=”System.Data.SqlClient” %>
<html>
<script language=”VB” runat=”server”>
Sub Page_Load(Sender As Object, E As EventArgs)
if not Page.IsPostBack then
doData()
end if
End Sub
Sub doQuery(Source as Object, E as EventArgs)
Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyCommand As SqlDataAdapter
Dim strState as string
strState=state.SelectedItem.Text
‘label1.text=”State queried is : ” & strState
Label1.Text=”You chose: ” + strState
Dim MySQL as string
MySQL=”select * from Authors where state=’” & strState & “‘”
panel1.visible=”true”
label2.text=MySQL
MyConnection = New SqlConnection(“server=localhost;uid=userid;pwd=pwd;database=pubs;”)
MyCommand = New SqlDataAdapter(MySQL, MyConnection)
DS = new DataSet()
MyCommand.Fill(ds, “Authors”)
MyDataGrid.DataSource=ds.Tables(“Authors”).DefaultView
MyDataGrid.DataBind()
End Sub
Sub doData()
Dim strConn as string =”server=localhost;database=pubs;uid=sa;pwd=password”
Dim Conn as new SQLconnection(strConn)
Dim strSQL as string =”select distinct state from authors”
Dim Cmd as New SQLCommand(strSQL,Conn)
Conn.Open()
state.DataSource = Cmd.ExecuteReader()
state.DataBind()
End Sub
</script>
<body>
<h3><font face=”Verdana”>Search & Database – Display Results in a DataGrid Control</font></h3>
<Form id=”form” runat=”server”>
<asp:DropDownList id=”state” datatextfield=”state” runat=”server” />
<asp:Button id=”button” Text=”Select State” onclick=”doQuery” runat=”server” />
</Form>
<asp:Panel ID=”Panel1″ runat=”server” visible=”False”>
<hr>
<div align=”center”><b><i>Search Results</i></b>
<asp:Label ID=”label1″ runat=”server” /><br>
<asp:Label ID=”label2″ runat=”server” /><p>
<ASP:DataGrid id=”MyDataGrid” runat=”server”
Width=”700″
BackColor=”#ccccff”
BorderColor=”black”
ShowFooter=”false”
CellPadding=3
CellSpacing=”0″
Font-Name=”Verdana”
Font-Size=”8pt”
HeaderStyle-BackColor=”#aaaadd”
EnableViewState=”false”
/>
</div>
</asp:Panel>
</body>
</html>
How to obtain ERROR message into to your browser ?
1. Open file Web.config of your site and set CUSTOM ERROR MESSAGES to
2. Open your IIS manager and config the web site as Application.
Do’not forget to re-set into when
your site goes in production for security reason.
ASP.Net gives more error info than ASP classic.
Enjoy programming with ASP.NET
Best Regards,
AnCo