Discussion Forum Board Beta2

By: Saurabh Nandu - Thursday, July 12, 2001 Add Comment

<div align="center"> <table border="0" width="90%" class="outline"> <tr> <td width="30%" class="outline">Download File</td> <td width="10%" class="outline">SDK</td> </tr> <tr> <td width="30%" class="outline"><a href="../../file/forumbeta2.zip" class="wbox">forumbeta2.zip</a> (39kb)</td> <td width="10%" class="outline">Beta2</td> </tr> </table> </div> Introduction This example updates the <a href="http://www.mastercsharp.com/article.aspx?ArticleID=13&&TopicID=2" class="wbox"> previous forum example</a> that I had written for beta1. A lot has changed since then, even though from outside the forum has the same looks internally a lot has changed! Due to this I have taken the trouble to elaborate this example into easy to understand pieces. This example will teach you to work with OleDb.NET, Databind DataGrid's to DataReaders, Custom Paging in DataGrid's and Templates in DataGrid's. This is a long and detailed one so get your jugs of coffee ready :) Explanation This example consists of 3 pages. 1) forum.aspx - The Main Forum page. It lists all the topics in a DataGrid object with Custom Paging enabled. It also contains a form to post new topics. 2) reply.aspx - This page is called from the forum page when you click on any topic. It contains the details of the topic as well as all replies made to the topic. This page contains the form to reply to the topic. 3) postmessage.aspx - This page is called whenever any form is posted. It parses and stores the posted data into the Database.  Requirements 1) .NET SDK beta 2 (Note: This example may not work with future versions of the SDK). 2) Ms Access 2000 (Optional, only if you want to edit the database). 3) ASP.NET support on your web server. Database Structure <table width="90%" border="0" class="outline"> <tbody> <tr> <td width="100%" colSpan="3" class="outline">Table Name - newpost / This table will contain the new topics posted.</td> </tr> <tr> <td width="20%" class="outline">Column Name </td> <td width="15%" class="outline">Data Type</td> <td class="outline">Description</td> </tr> <tr> <td width="20%" class="outline">postid </td> <td width="15%" class="outline">AutoNumber</td> <td class="outline">The primary key field. The unique id for each new topic.</td> </tr> <tr> <td width="20%" class="outline">name</td> <td width="15%" class="outline">Text</td> <td class="outline">The name of the author of the message.</td> </tr> <tr> <td width="20%" class="outline">email</td> <td width="15%" class="outline">Text</td> <td class="outline">E-mail address of the author.</td> </tr> <tr> <td width="20%" class="outline">subject</td> <td width="15%" class="outline">Text</td> <td class="outline">Subject of the message.</td> </tr> <tr> <td width="20%" class="outline">ip</td> <td width="15%" class="outline">Text</td> <td class="outline">IP address of the author.</td> </tr> <tr> <td width="20%" class="outline">dt</td> <td width="15%" class="outline">Text</td> <td class="outline">Date / Time of the Post</td> </tr> <tr> <td width="20%" class="outline">message</td> <td width="15%" class="outline">Memo</td> <td class="outline">Message posted.</td> </tr> <tr> <td width="20%" class="outline">replies</td> <td width="15%" class="outline">Integer</td> <td class="outline">Number of replies to the post (if any)</td> </tr> <tr> <td width="20%" class="outline">views</td> <td width="15%" class="outline">Integer</td> <td class="outline">Number of times the message has been viewed. </td> </tr> </tbody> </table> <table width="90%" border="0" class="outline"> <tbody> <tr> <td width="99%" colSpan="3" class="outline">Table Name- reply / This table will contain the replies made to the new topics.</td> </tr> <tr> <td width="20%" class="outline">Column Name</td> <td width="15%" class="outline">Data Type</td> <td class="outline">Description</td> </tr> <tr> <td width="20%" class="outline">replyid</td> <td width="15%" class="outline">AutoNumber</td> <td class="outline">The primary key field. The unique id for each reply.</td> </tr> <tr> <td width="20%" class="outline">name</td> <td width="15%" class="outline">Text</td> <td class="outline">Name of the author.</td> </tr> <tr> <td width="20%" class="outline">email</td> <td width="15%" class="outline">Text</td> <td class="outline">E-mail address of the author.</td> </tr> <tr> <td width="20%" class="outline">subject</td> <td width="15%" class="outline">Text</td> <td class="outline">Subject of the post</td> </tr> <tr> <td width="20%" class="outline">ip</td> <td width="15%" class="outline">Text</td> <td class="outline">IP of the author.</td> </tr> <tr> <td width="20%" class="outline">dt</td> <td width="15%" class="outline">Text</td> <td class="outline">Date / Time of post.</td> </tr> <tr> <td width="20%" class="outline">message</td> <td width="15%" class="outline">Memo</td> <td class="outline">Message posted.</td> </tr> <tr> <td width="20%" class="outline">postid</td> <td width="15%" class="outline">Integer</td> <td class="outline">postid from the "newpost" table for which this message is a reply.</td> </tr> </tbody> </table>Code 1) forum.aspx :- The main forum page. (Only relevant pieces shown)  This page is one of the most important pages, read this code carefully! <table border="0" width="100%" class="code" cellspacing="0" cellpadding="0"> <tr> <td width="100%"> <pre><%@ Page Language="C#" EnableSessionState="False" %> <%@ Import Namespace="System.Data.OleDb" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System" %> <html><head> <title>Welcome to My Forum!</title> <script language="C#" runat="server" > int startIndex; void Page_Load(Object Src, EventArgs E) { //check if the page is loaded for the first time. if(!IsPostBack) { //Call the Binding Method Binding() ; } } ..... ...... </script></pre> </td> </tr> </table> Above, after importing the necessary namespaces, I have declared a global Integer variable 'startIndex' whose work we shall see a bit later. Next I declare the 'Page_Load' method, this method is an Event Handler of the Page class and gets fired automatically every time the page gets loaded. Within this method I check if the page is being loaded for the first time by checking the 'IsPostBack' property of the Page class. If the Page is called for the first time, 'IsPostBack' returns a 'false' and if the page is reloaded, it returns a 'true'. Since I want the DataGrid to be databound from the 'Page_Load' method only when the Page is first loaded, I call the 'Binding' method (defined a bit later) within the 'if' block.   <table border="0" width="100%" class="code" cellspacing="0" cellpadding="0"> <tr> <td width="100%"> <pre> public void DataGrid_Updt(object sender, DataGridPageChangedEventArgs e) { //store the next page index DataGrid1.CurrentPageIndex = e.NewPageIndex; //update the variable startIndex = (DataGrid1.CurrentPageIndex * DataGrid1.PageSize); //call the method to data bind the DataGrid Binding(); }</pre> </td> </tr> </table> The 'DataGrid_Updt' method defined above gets called every time the DataGrid is Paged. Generally in an active forum hundreds topics are listed everyday. Displaying all the topics to every user is a waste of bandwidth, users time, as well as the time taken to load the page will be very very high! So we use Paging support in the DataGrid object, whereby only fixed number of records are displayed on each page and a link to other pages like (1,2,3...) is given below the grid. This saves users time and bandwidth.  When the user changes the page, 'PageIndexChanged' Event is fired by the DataGrid. In my example the above method is an Event Handler for this Event. You will also note that this method has one different argument of the type 'DataGridPageChangedEventArgs'. This argument contains some important information regarding the DataGrid Page. I use the 'NewPageIndex' property to find out the Page number the user has clicked on the DataGrid. So if the user clicks on the third page, this variable will return the value '3'. I set the 'CurrentPageIndex' property of the DataGrid that denotes the current page being displayed to the value returned by the 'NewPageIndex'.  The 'startIndex' variable which I had declared as global (remember!) is set to the value obtained by multiplying the 'CurrentPageIndex' and the 'PageSize' of the DataGrid. 'CurrentPageIndex' denotes the current page number being viewed while the 'PageSize' property denotes the total number of records shown per page. So if the user click's on Page 3, then we store (3*20)  = 60 in the variable 'startIndex' (Note: I have set the 'PageSize' to 20, so I am using the value 20 above). Why do I do this? Answer to this a bit later....  Next, the 'Binding' method is given a call. As you know, this method will databind the DataGrid. One point I would like to make here is, that when the user click's on a new page number to change the page, the page gets refreshed and the 'Page_Load' method is called first, why? (I have answered the question above, read carefully) and then the 'DataGrid_Updt' method is called. If within the 'Page_Load' method I would not have put a check on 'IsPostBack' property and directly declared the 'Binding' method. The code would still work, but it would be a waste of resources since in the 'DataGrid_Updt' too calls the same 'Binding' method after setting some different arguments and the DataGrid would get re-binded twice! This is a common error made my many programmers (I too used to make it!), so always remember to check for 'IsPostBack' property in the 'Page_Load' method before DataBinding while using DataGrid's !   <table border="0" width="100%" class="code" cellspacing="0" cellpadding="0"> <tr> <td width="100%"> <pre> public void Binding() { //Connection string string strConn=@"Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source="; strConn+=Server.MapPath(".\\db\\board.mdb"); //Make a new connection OleDbConnection myConn = new OleDbConnection(strConn) ; string strCom; //Check is the Page is loaded for the first time if(!IsPostBack) { //Set the variable to its default value startIndex =0; //SQL statement to choose the last record strCom = "SELECT TOP 1 postid FROM newpost ORDER BY postid DESC" ; //Create a Command OleDbCommand countCommand = new OleDbCommand(strCom, myConn); //Open the connection myConn.Open(); //Create a DataReader OleDbDataReader countReader = countCommand.ExecuteReader(); //Check if the DataReader has any records if(!countReader.Read()) { //Set VirtualItemCount to 0 since no records exist DataGrid1.VirtualItemCount=0; } else { //Set VirtualItemCount to the total number of records in the table DataGrid1.VirtualItemCount= countReader.GetInt32(0); } //Close the connection myConn.Close(); } //SQL statement to choose just 20 records in Descending order strCom = "SELECT TOP 20 postid ,subject ,name ,replies ,views ,dt FROM newpost "; strCom+= "WHERE postid < "+(DataGrid1.VirtualItemCount-(startIndex-1) ); strCom+=" ORDER BY postid DESC" ; OleDbCommand forumCommand = new OleDbCommand(strCom, myConn); myConn.Open(); OleDbDataReader forumReader = forumCommand.ExecuteReader(); //Set the DataSource DataGrid1.DataSource = forumReader ; //DataBind the grid DataGrid1.DataBind(); myConn.Close(); }</pre> </td> </tr> </table> Before diving into the above code, I would like to make a few comments first. DataSet vs DataReader When we started programming with .NET SDK beta1, we were introduced to the idea of DataSet's. A DataSet object is in-memory representation of the database, but it's always disconnected from the database. Also the DataSet object does not have any idea of the Data Source from which the Data has been populated into it. Its neutral to the source of the data, and data can be either fed into it through DataAdapters (DataSetCommand in beta1) or can be programmatically entered. The DataSet object is so powerful and easy to use that everyone started using it instantly. When .NET SDK beta2 was released, Microsoft has clearly indicated that, even though DataSet's are powerful and very customizable, they should be used with caution! Since DataSet are created in-memory, they have a high memory requirement, and bring down the performance of your Web Application. As against DataSet, a DataReader object represents a fast, forward-only record cursor, which can be used to only read data in a systematic manner. Since the DataReader reads one record at a time its memory requirement is very low and its speed is very high.  Also note that when a DataSet object is populated through a DataAdapter, internally a DataReader is created to populate the DataSet. So whenever you are using a DataSet, a DataReader object is always created! Hence DataReader's offer a better performance for your Applications and they should be used wherever the need is to just display the Data without much customization. Hence I too will be using a DataReader in this example! DataReader and DataGrid Paging Above I have explained the need and use of Paging in DataGrid's. While data binding DataReader's to DataGrid's with just Paging enabled (through the 'AllowPaging' property) you get a error, Why? The reason for this is that a DataReader does not implement the 'ICollection' Interface. The 'ICollection' Interface defines a property called 'Count' which helps the DataGrid object know at runtime the number of records present and hence it can compute the number of pages to show. i.e. If the DataGrid object knows that there are 200 records and the 'PageSize' (number of records displayed per page) is set to 20 then it will draw links for pages 1 to 10 (200/20=10, right !!! :) ). You might think that while using DataSet's you don't get such a error even though DataSet's also don't implement the 'ICollection' Interface? (Rub your eye's hard!!) While using DataSet's we never DataBind to the DataSet, we always data bind to a DataView object, remember!! And the DataView object implements the 'ICollection' Interface so we have never faced this problem! So what to do now? Well we start of by setting the 'AllowPaging' and 'AllowCustomPaging' properties of the DataGrid to 'true' indicating that we want Paging support in our grid, but we will manually handle the Paging code. We also wire-up the 'PagingIndexChanged' event through which we shall manually handle paging of the DataGrid. Next we have to set the 'VirtualItemCount' property of the DataGrid programmatically (it cannot be set directly with the DataGrid declaration) to the total number of records present in the Table.  One advantage this approach gives us is that since actually only some records are shown per page and a fresh set of records are retrieved every time the page changes, in our custom paging code we can set the SQL Query in such a way that it only retrieves the data that will be shown in the DataGrid. This way we save a lot of traffic on our network! I hope you have been able to gasp some of the changes from beta1 to beta2. Now time to kick some heavy code! Re-fill your coffee cups and go through the above code of the 'Binding' method. Let's start... First I declare a 'OleDbConnection' variable and then I have put another If-Block which checks if the Page is loaded for the first time. If '!IsPostBack' (not PostBack) evaluates to true, that means  that the page is being loaded from the first time. Within this If-Block, I initialize the global integer variable 'startIndex' to zero, its default value, then I have written a SQL statement, which will return only the 'postid' column of the last record in the database. Why am I doing this? As I had mentioned in the "DataReader and DataGrid Paging" section, that in order to enable custom paging in a DataReader bound DataGrid you have to set the 'VirtualItemCount' property of the DataGrid to the total number or records. If you check the schema of the 'newpost' table you will find that the 'postid' column is of the type 'Auto Number' and contains sequential numbers in increasing order. So if we get the 'postid' of the last record, we can get the total number of records present in the database, since the 'postid' of the last record will be equal to the number of records in the table. One possible problem to this approach might be that if in the future someone deletes a record from the table for moderation purpose, then we will have change this approach. Since this feature is yet not implemented, I will continue with this approach.  Finally, a OleDBCommand and OleDbDataReader objects are made and the command is executed. Following it is one more If-block. This block check's if the 'Read' method of the DataReader returned any records, if so then a true is returned. I have implemented this check since the first time you run this code, there are no records to be read, at this time attempting to perform any operations on the DataReader will generate an exception. So in the If-block, if this condition evaluates to false i.e. no records have been read then I set the 'VirtualItemCount' property of the DataGrid to zero, else its set to the total number of records in the table, represented by the 'postid' field of the last record. Note that the 'VirualItemCount' is only set once, when the page is first loaded.  Moving ahead, I have formed one more SQL statement, which actually will retrieve the data to be shown in the DataGrid. This statement looks a bit too weird since I am trying to achieve many things from this statement. for simplicity purpose I will break down this statement into parts. "SELECT TOP 20 postid ,subject ,name ,replies ,views ,dt FROM newpost"  - This part selects the respective fields from the 'newpost' table. The directive 'TOP 20' only returns 20 records. If you remember I had mentioned before that you can customize the SQL statement in such a way that it should return only those records that have to be displayed on the page. Since the 'PageSize' property of our DataGrid is 20 I have chosen only 'TOP 20' records from the database. "WHERE postid < "+(DataGrid1.VirtualItemCount-(startIndex-1) )"  - This is where the real magic of the global variable 'startIndex' comes. In order to achieve our goal of returning only the necessary number of records (20), I have created a condition where I subtract the current value of 'startIndex' from the total number of rows (represented by the 'VirtualItemCount') property. If you would recall, in the 'DataGrid_Updt' I first update the 'startIndex' field and then I call this method. So say for example, the user clicked on 'Page 3', the page will refresh, and call the 'Page_Load' method, right? (I hope ur awake!!) and then it will call the 'DataGrid_Updt' method. In this method the 'startIndex' variable will be set to 60 (3*20, where 3 is the page number and 20 is the number of records displayed per page). Next the 'Binding' method will be called from the 'DataGrid_Updt' method. Here the above SQL statement will be evaluated to,  "WHERE postid < " +(200 - (60 -1))"  - (assuming the total number of records being 200)  "WHERE postid < 141"  - hence all records below 141 will be selected.  Also applying the above described "TOP 20" condition only records from 120 to 140 (total 20 records) get selected!! Thus we achieve our target of only retrieving 20 records from the Database!!!!!  "ORDER BY postid DESC"  - Lastly we sort these 20 selected records in descending order, Why? Simply since we want the latest/newest post to appear first and the oldest post to appear last! Once the SQL statement is ready, be make a command and DataReader object and execute the command. The derived reader is then databound to the DataGrid, displaying the records fetched by us. If you would observe carefully, while I have made multiple commands and readers, I have used the same instance of OleDbConnection class, just opening it and closing it. This is because there is a huge performance gain while using a single connection object to perform various activities rather than creating a new instance every time you want to connect to the database. Now wasn't that easy ..... (I am kidding .... :))  <table border="0" width="100%" class="code" cellspacing="0" cellpadding="0"> <tr> <td width="100%"> <pre>public void Submit_Click(Object sender, EventArgs e) { //proceed only if the page is valid if(Page.IsValid){ DateTime now = DateTime.Now ; errmess.Text="" ; string req = "name="+ System.Web.HttpUtility.UrlEncode(name.Text, System.Text.Encoding.UTF8); req+="&&email="+ System.Web.HttpUtility.UrlEncode(email.Text, System.Text.Encoding.UTF8); req+="&&subject="+ System.Web.HttpUtility.UrlEncode(subject.Text, System.Text.Encoding.UTF8); //Get the HostAddress of the Author req+="&&ip="+ System.Web.HttpUtility.UrlEncode(Request.UserHostAddress.ToString(), System.Text.Encoding.UTF8); req+="&&date="+ System.Web.HttpUtility.UrlEncode(now.ToString(), System.Text.Encoding.UTF8); req+="&&message="+ System.Web.HttpUtility.UrlEncode(message.Text, System.Text.Encoding.UTF8); //A 'yes' is used below to tell the postmessage page that this is a new topic req+="&&newpost="+ System.Web.HttpUtility.UrlEncode("yes", System.Text.Encoding.UTF8); //call the postmessage.aspx page and append the query to it. Response.Redirect("postmessage.aspx?" + req); } else { errmess.Text="Fill in all the Required Fields before Posting! " ; } }</pre> </td> </tr> </table> The above method is called when a form (code shown below) is submitted. In ASP.NET 'Form's' that run at sever behave a bit differently than the forms we are used to in HTML and ASP. In ASP.NET we have the concept of Post-Back, i.e instead of sending the form details to some other page, the same page gets posted/called again and it can handle all the logic for updating/inserting the data into the database. Example of this is that when you click on any page number in the DataGrid, the same page gets called again, but with the necessary coding you can change the contents of the page. In my example to have maintainability of posting the form from various pages, I did not want this, rather I wanted to have the same old functionality that HTML/ASP gave us. I found 2 solutions to solve my problem: 1) Use a normal <form> tag without the 'runat=server' attribute and specify its action attribute to the new page. There was one drawback to this solution, I could no longer the Form Validating Web Controls like 'RequiredFieldValidator' etc which make form validation task in ASP.NET very easy! 2) The second solution is to declare a 'Form' to run at server, and in the Event Handling code, take the all the values of the form, append it as a query string manually and call the next page. I have taken this path. Just one last hitch to this is that while appending the query after a page you should convert it essentially to the UTF-8 format. So in the above method I take all the fields from the Form, convert it into UTF-8 format and finally redirect the response to the 'postmessage.aspx' page after appending all the form contents as a query. Beta1 users note that the 'Page.Navigate' method available in beta1 has been removed in beta2 :(.   <table border="0" width="100%" class="code" cellspacing="0" cellpadding="0"> <tr> <td width="100%"> <pre><form method="post" runat="server" ID=Form1> <%-- The DataGrid settings. Its very interesting how much you can play with it --%> <asp:DataGrid id=DataGrid1 runat="server" ForeColor="Black" OnPageIndexChanged="DataGrid_Updt" PageSize="20" AllowPaging="True" width="80%" autogeneratecolumns="False" AllowCustomPaging="True" > <PagerStyle BackColor="Coral" Mode="NumericPages"> </PagerStyle> <AlternatingItemStyle BorderColor="#FFC080" BackColor="#FF9966"> </AlternatingItemStyle> <FooterStyle ForeColor="White" BackColor="DarkOrange"> </FooterStyle> <ItemStyle BackColor="Moccasin"> </ItemStyle> <HeaderStyle Font-Bold="True" ForeColor="White" BackColor="Coral"> </HeaderStyle> <%-- I am setting up the Individual columns myself using Templates --%> <Columns> <%-- Manipulate the subject entry so that it contains a link to the reply page --%> <asp:TemplateColumn HeaderText="Subject" itemstyle-width=50%> <ItemTemplate> <asp:Label runat="server" Text= '<%#"<a href=reply.aspx?postid="+DataBinder.Eval(Container, "DataItem.postid")+">" +DataBinder.Eval(Container, "DataItem.subject")+"</a>" %>' ID=Label2></asp:Label> </ItemTemplate> </asp:TemplateColumn> <asp:TemplateColumn HeaderText="Author Name" itemstyle-width=20%> <ItemTemplate> <asp:Label runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.name") %>' ID=Label3></asp:Label> </ItemTemplate> </asp:TemplateColumn> <asp:TemplateColumn HeaderText="Replies" itemstyle-width=10%> <ItemTemplate> <asp:Label runat="server" width=10% Text='<%# DataBinder.Eval(Container, "DataItem.replies") %>' ID=Label4> </asp:Label> </ItemTemplate> </asp:TemplateColumn> <asp:TemplateColumn HeaderText="Views" itemstyle-width=10%> <ItemTemplate> <asp:Label runat="server" width=10% Text='<%# DataBinder.Eval(Container, "DataItem.views") %>' ID=Label5> </asp:Label> </ItemTemplate> </asp:TemplateColumn> <asp:TemplateColumn HeaderText="Date of Post" itemstyle-width=10%> <ItemTemplate> <asp:Label runat="server" width=10% Text=' <%# DateTime.Parse(DataBinder.Eval(Container, "DataItem.dt").ToString()).ToShortDateString() %>' ID=Label6> </asp:Label> </ItemTemplate> </asp:TemplateColumn> </Columns> </asp:DataGrid></pre> </td> </tr> </table> Above, is the very interesting definition of the DataGrid, that will display the data from the 'newpost' table. Firstly, I wire-up the Event Handler for the 'OnPageIndexChange' Event, set the 'PageSize' property to 20 and the 'AllowPaging' / 'AllowCustomPaging' properties to 'true' since I want to enable paging in my grid with my custom method. I also set the 'AutoGenerateColumns' property to 'false' since I want to manually handle the creation of each column. Next, I define a few Templates, Templates allow you to easily customize different parts of the DataGrid to suit your needs. The 'PagerStyle', 'AlternatingItemStyle', 'FooterStyle', 'ItemStyle' and the 'HeaderStyle' templates are used to modify the display of the respective parts of the DataGrid. Under the 'Columns' template we define individual settings for each column. The 'TemplateColumn' control (note its a control not a template!) is used within the 'Columns' template to represent each column. The 'ItemTemplate' template within each is 'TemplateColumn' control used to define the custom display. Under the 'ItemTemplate' template we can include various other controls and program them to display required output. In the above code I have done some customization to each of the columns within the 'ItemTemplate' template. Beta1 users note how the different template names have changed, they are much more self-explanatory and easy to use in beta2!! Novice users should note that I have encapsulated the DataGrid within a ASP.NET 'Form' that run's at server. This is very important, or your DataGrid Paging won't work!!   <table border="0" width="100%" class="code" cellspacing="0" cellpadding="0"> <tr> <td width="100%"> <pre><table border="0" width="80%" align="center"> <tr > <td class="fohead" colspan=2>Post New Topic</td> </tr> <tr class="folight" > <td>Name :</td> <td ><asp:textbox text="" id="name" runat="server" /> <asp:RequiredFieldValidator ControlToValidate=name display=static runat=server> *</asp:RequiredFieldValidator></td> </tr> <tr class="folight"> <td>E-Mail&nbsp;:</td> <td><asp:textbox text="" id="email" runat="server"/> <asp:RequiredFieldValidator ControlToValidate=email display=static runat=server> *</asp:RequiredFieldValidator> <asp:RegularExpressionValidator runat="server" ControlToValidate="Email" ValidationExpression="[\w-]+@([\w-]+\.)+[\w-]+" Display="Static" Font-Name="verdana" Font-Size="10pt" ErrorMesage="Please enter a valid e-mail address"> </asp:RegularExpressionValidator></td> </tr> <tr class="folight"> <td> Subject:</td> <td><asp:textbox test="" id="subject" width=200 runat="server"/> <asp:RequiredFieldValidator ControlToValidate=subject display=static runat=server> *</asp:RequiredFieldValidator> </td></tr> <tr class="folight"> <td>Message&nbsp;:</td> <td> <asp:TextBox id=message runat="server" Columns="30" Rows="15" TextMode="MultiLine"></asp:TextBox></td> </tr> <tr class=folight> <td colspan=2> <asp:Button class=fodark id=write onClick=Submit_Click runat="server" Text="Submit"> </asp:Button></td></tr> </table> </form></pre> </td> </tr> </table> The final piece of the forum.aspx page is the form that can be used to post new topics to the forum. Nothing special here except a normal form and a few form validators. Phew! One down 2 to go......  Take a round, freshen your mind... and COME BACK!! 2) reply.aspx : The topic viewing and replying page. I am not describing this page in detail since it just retrieves some content from the database and updates the necessary fields. Later it displays the content in a tabular form. One exercise I have left for you to solve is to convert the code to use a DataReader and a Repeater.  <table border="0" width="100%" class="code" cellspacing="0" cellpadding="0"> <tr> <td width="100%"> <pre><%@ Page Language="C#" EnableSessionState="False" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <html><head> <title>Post New Topic.</title> <script Language="C#" runat="server"> DataSet ds ,rs; DataRow dr ; string postid ; public void Page_Load(object sender , EventArgs e) { //Check if the page is Post Back if(!Page.IsPostBack) { //Get the postid from the Query string postid = Request.Params["postid"] ; if(postid!=null) { //Database connection string. string strConn=@"Provider=Microsoft.Jet.OLEDB.4.0;"; strConn+="Data Source="+Server.MapPath(".\\db\\board.mdb") ; //Make a connection to the Database OleDbConnection myConn = new OleDbConnection(strConn) ; //string to select the records from the newpost table string strCon ="SELECT subject, name, email, message ,dt FROM newpost "; strCon+="WHERE postid="+postid ; //set a OleDbDataAdapter OleDbDataAdapter myCommand =new OleDbDataAdapter(strCon,strConn); ds = new DataSet(); //Fill the DataSet myCommand.Fill(ds,"newpost") ; //Get the Row at position '0' and store it in a DataRow object //Why put into a DataRow ? Its easy to access data from a DataRow dr = ds.Tables["newpost"].Rows[0] ; //Get the "subject" from the DataRow and set it up in the post reply //form's subject field subject.Text="Re:"+dr["subject"].ToString() ; //Select the replies to the post from the reply table strCon ="SELECT name , email, subject, message ,dt FROM reply "; strCon+="WHERE postid="+postid ; //Make a new OleDbDataAdapter and DataSet for the reply table OleDbDataAdapter myCommand2 =new OleDbDataAdapter(strCon,strConn); rs = new DataSet() ; //fill the DataSet myCommand2.Fill(rs, "reply") ; //Code to update the "views" field for the newpost Table //Select the views field from the table for a given postid strCon ="SELECT views FROM newpost WHERE postid = "+postid ; //Make a OleDbCommand here since we want a OleDbDataReader later OleDbCommand vicomm = new OleDbCommand(strCon, myConn) ; myConn.Open(); OleDbDataReader reader ; //execute the statement and create a OleDbDataReader reader = vicomm.ExecuteReader() ; //Read the First record (there can only be one record remember !) reader.Read() ; //Get a "Int32" value from the first Column int i = reader.GetInt32(0) ; //Increase the views count i++ ; reader.Close() ; //Update the newpost table with the new views value strCon ="UPDATE newpost SET views = "+i+" WHERE (postid= "+postid+")" ; //since we are using the same OleDbCOmmand object for this statement //too, so set the CommandText property vicomm.CommandText = strCon ; //Since this statement will result in no output we use "ExecuteNonQuery()" vicomm.ExecuteNonQuery() ; //close the connection myConn.Close(); } } } // This method is called when the submit button is clicked public void Submit_Click(Object sender, EventArgs e) { //Get the postid postid = Request.Params["postid"] ; //proceed only if Page is valid if(Page.IsValid){ DateTime now = DateTime.Now ; errmess.Text="" ; //We have to call the postmessage.aspx page with a query to post the data //Hence we have to first build the custom query from the Data posted by the user //also since we are using a query we have to encode the data into UTF8 format string req = "name="+ System.Web.HttpUtility.UrlEncode(name.Text, System.Text.Encoding.UTF8); req+="&&email="+ System.Web.HttpUtility.UrlEncode(email.Text, System.Text.Encoding.UTF8); req+="&&subject="+System.Web.HttpUtility.UrlEncode(subject.Text, System.Text.Encoding.UTF8); req+="&&ip="+System.Web.HttpUtility.UrlEncode(Request.UserHostAddress.ToString(), System.Text.Encoding.UTF8); req+="&&date="+ System.Web.HttpUtility.UrlEncode(now.ToString(), System.Text.Encoding.UTF8); req+="&&message="+ System.Web.HttpUtility.UrlEncode(message.Text, System.Text.Encoding.UTF8); //Encode "no" to indicate that the post is not a new post //but its a reply to a earlier message req+="&&newpost="+ System.Web.HttpUtility.UrlEncode("no", System.Text.Encoding.UTF8); req+="&&previd="+ System.Web.HttpUtility.UrlEncode(postid, System.Text.Encoding.UTF8); //Call the postmessage page with our custom query Response.Redirect("postmessage.aspx?" + req); } else { errmess.Text="Fill in all the Required Fields !" ; } } </script> <LINK href="mystyle.css" type=text/css rel=stylesheet></head> <body topmargin="0" leftmargin="0" rightmargin="0" marginwidth="0" marginheight="0"> <%-- Include a header file 'header.inc' --%>  <div align=center> <table border=0 width=80% cellspacing=2> <tr class=fohead><th width=20%>Author Name</th> <th width=80%>Message</th></tr> <%-- Below I am encapsulating the email of the author over the name of the author so that when you click on the author a e-mail gets sent to him Also I am getting the DateTime from the DataBase and Displaying the Date and Time separately --%> <tr class=folight><td rowspan=2 align="center"> <%= "<a href=mailto:"+dr["email"]+">"+dr["name"]+"</a>" %> <%= DateTime.Parse(dr["dt"].ToString()).ToShortDateString() %> <%= DateTime.Parse(dr["dt"].ToString()).ToShortTimeString() %> </td> <td>Subject: <%=dr["subject"] %></td></tr> <tr class=folight> <td><%=dr["message"] %> </td> </tr> <%-- Get all the replies to the Original post and show them --%> <% int no = rs.Tables["reply"].Rows.Count ; if(no>0) { for(int j=0 ;j<no ; j++) { DataRow rd = rs.Tables["reply"].Rows[j] ; %> <tr class=fodark> <td align="center"><%="<a href=mailto:"+rd["email"]+">"+rd["name"]+"</a>" %> <%= DateTime.Parse(rd["dt"].ToString()).ToShortDateString() %> <%= DateTime.Parse(rd["dt"].ToString()).ToShortTimeString() %> </td> <td><%=rd["message"] %> </td> </tr> <% } } %> </table> </div> <h3 align="center" class="fodark"> <a href=forum.aspx>Click Here</a> to go to back to Forum. Reply to the Above Post.</h3> <asp:label id="errmess" text="" style="COLOR:#ff0000" runat="server" /> <form runat="server"> <table border="0" width="80%" align="center"> <tr > <td class="fohead" colspan=2>Reply to the Post</td> </tr> <tr class="folight" > <td>Name :</td> <td ><asp:textbox text="" id="name" runat="server" /> <asp:RequiredFieldValidator ControlToValidate=name display=static runat=server> *</asp:RequiredFieldValidator></td> </tr> <tr class="folight"> <td>E-Mail&nbsp;:</td> <td><asp:textbox text="" id="email" runat="server"/> <asp:RequiredFieldValidator ControlToValidate=email display=static runat=server> *</asp:RequiredFieldValidator> <asp:RegularExpressionValidator runat="server" ControlToValidate="Email" ValidationExpression="[\w-]+@([\w-]+\.)+[\w-]+" Display="Static" Font-Name="verdana" Font-Size="10pt" ErrorMesage="Please enter a valid e-mail address"> </asp:RegularExpressionValidator></td> </tr> <tr class="folight"> <td> Subject:</td> <td><asp:textbox test="" id="subject" width=200 runat="server"/> <asp:RequiredFieldValidator ControlToValidate=subject display=static runat=server> *</asp:RequiredFieldValidator> </td></tr> <tr class="folight"> <td>Message&nbsp;:</td> <td> <asp:TextBox id=message runat="server" Columns="30" Rows="15" TextMode="MultiLine"></asp:TextBox></td> </tr> <tr class=folight> <td colspan=2> <asp:Button class=fodark id=write onClick=Submit_Click runat="server" Text="Submit"> </asp:Button></td></tr> </table> </form>  </body></html></pre> </td> </tr> </table>   3) postmessage.aspx : The page to save the posted data to the database. Here too, I am presenting the full code in one go! See inline comments for more information. One major change you will find here is that I am using a Parameterized SQL statements to insert a new record into the Database. This is to solve the BUG 'Brian R. Bondy' brought to my notice, thanks pal!! The bug is in using normal Insert SQL statements, eg:  string sqlString = "INSERT INTO newpost (name, subject) VALUES (' "+name+" ', ' "+subject+" ')";  Where the variables 'name' and 'subject' refer to string's which the user inputs.  Say if the user inputs a subject as "This prog's get a prob", then our sqlString evaluates to  "INSERT INTO newpost (name,subject) VALUES ('saurabh' , 'This prog's get a prob')" The extra apostrophe ( ' ) in the subject (prog's) will throw a exception since the SQL string becomes a faulty. That's why I have used Parameterized SQL statement which solves this BUG!! Note: One more important BUG (or feature whatever U call it) in OleDb.NET supplied with the .NET SDK beta2 is that than within a Ms Access column of the type 'Memo' you can only input some 4000 characters only! So if your message extends that limit a exception is thrown :(. The only solution is to use ODBC.NET, which can be downloaded from <a href="http://msdn.microsoft.com" target="_blank" class="wbox">msdn.microsoft.com/net</a> separately. ODBC.NET supplies a lot of left out features from OleDb.NET and is a MUST download. You will also see that below I have made use of the 'parsetext' method. This method is used to apply HTML formatting to the Message posted. (If your heart is still beating you can check out <a href="http://www.mastercsharp.com/article.aspx?ArticleID=27&&TopicID=2" class="wbox">this article</a> where I explain in detail the working of this method). <table border="0" width="100%" class="code" cellspacing="0" cellpadding="0"> <tr> <td width="100%"> <pre><%@ Page Language="C#" EnableSessionState="False" %> <%@ Import Namespace="System" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <%@ Import Namespace="System.Text" %> <%@ Import Namespace="System.IO" %> <html> <head> <title>Thank You for Posting !</title> <script language="C#" runat="server" > void Page_Load(Object Src, EventArgs E) { //Check id the page is loaded for the first time if (!Page.IsPostBack) { //Get the Parameters from the Query string and store it string name = Request.Params["name"] ; string email = Request.Params["email"] ; string subject = Request.Params["subject"] ; string ip = Request.Params["ip"] ; string date = Request.Params["date" ]; string message = Request.Params["message"] ; bool newmess =true ; string previd ="1"; //Check of the 'newpost' paramater is 'no' //indicating that its a reply to a previous post if(Request.Params["newpost"].Equals("no")) { newmess =false ; //Since its a reply, we get the ID of the topic //to which this post is a reply previd = Request.Params["previd"] ; } if(newmess) { //Execute the code below to insert a new topic string strConn=@"Provider=Microsoft.Jet.OleDb.4.0 ;Data Source="; strConn+=Server.MapPath(".\\db\\board.mdb") ; OleDbConnection myConn = new OleDbConnection(strConn) ; //SQL query with Parameters string insertStr =" INSERT INTO newpost (name, email, subject, ip, dt, message) VALUES "; insertStr+="(@name, @email, @subject, @ip, @dt, @message)"; //Create a new OleDbCommand OleDbCommand insertCommand = new OleDbCommand(insertStr, myConn); //Add a new Parameter '@name' of the type 'VarChar' //and set its value insertCommand.Parameters.Add(new OleDbParameter("@name", OleDbType.VarChar)); insertCommand.Parameters["@name"].Value = name; insertCommand.Parameters.Add(new OleDbParameter("@email", OleDbType.VarChar)); insertCommand.Parameters["@email"].Value = email; insertCommand.Parameters.Add(new OleDbParameter("@subject", OleDbType.VarChar)); insertCommand.Parameters["@subject"].Value = subject; insertCommand.Parameters.Add(new OleDbParameter("@ip", OleDbType.VarChar)); insertCommand.Parameters["@ip"].Value = ip; insertCommand.Parameters.Add(new OleDbParameter("@dt", OleDbType.VarChar)); insertCommand.Parameters["@dt"].Value = date; insertCommand.Parameters.Add(new OleDbParameter("@message", OleDbType.VarChar)); //Give a call the the 'parsetext' method to parse the message insertCommand.Parameters["@message"].Value = parsetext(message); myConn.Open(); //Execute Non Query to insert a new topic in the database insertCommand.ExecuteNonQuery(); myConn.Close() ; } else { //Insert a reply to a previous topic string strConn=@"Provider=Microsoft.Jet.OleDb.4.0 ;Data Source="; strConn+=Server.MapPath(".\\db\\board.mdb") ; OleDbConnection myConn = new OleDbConnection(strConn); //SQL statement with Parameters string insertStr =" INSERT INTO reply (name, email, subject, ip, dt, "; insertStr+="message, postid) VALUES "; insertStr+="(@name, @email, @subject, @ip, @dt, @message, @postid)"; //Create a new OleDbCommand OleDbCommand insertCommand = new OleDbCommand(insertStr, myConn); //Add a new Parameter and set its value insertCommand.Parameters.Add(new OleDbParameter("@name", OleDbType.VarChar)); insertCommand.Parameters["@name"].Value = name; insertCommand.Parameters.Add(new OleDbParameter("@email", OleDbType.VarChar)); insertCommand.Parameters["@email"].Value = email; insertCommand.Parameters.Add(new OleDbParameter("@subject", OleDbType.VarChar)); insertCommand.Parameters["@subject"].Value = subject; insertCommand.Parameters.Add(new OleDbParameter("@ip", OleDbType.VarChar)); insertCommand.Parameters["@ip"].Value = ip; insertCommand.Parameters.Add(new OleDbParameter("@dt", OleDbType.VarChar)); insertCommand.Parameters["@dt"].Value = date; insertCommand.Parameters.Add(new OleDbParameter("@message", OleDbType.VarChar)); //Give a call the the 'parsetext' method to parse the message insertCommand.Parameters["@message"].Value = parsetext(message); insertCommand.Parameters.Add(new OleDbParameter("@postid", OleDbType.Integer)); insertCommand.Parameters["@postid"].Value = previd; myConn.Open(); //Update the Database insertCommand.ExecuteNonQuery() ; myConn.Close(); //SQL string to get the 'replies' column of the topic //to which this post is a reply string replyno = "SELECT replies FROM newpost WHERE postid ="+previd ; insertCommand.CommandText =replyno ; myConn.Open(); OleDbDataReader reader =insertCommand.ExecuteReader() ; reader.Read(); //Get the number of replies to this post int rep =reader.GetInt16(0) ; myConn.Close(); rep++ ; //SQL statement to update the number of replies //of the topic to which this post is a reply string updtStr ="UPDATE newpost SET replies = "+rep +" WHERE (postid = "+previd+")" ; insertCommand.CommandText = updtStr; myConn.Open(); //Execute the command insertCommand.ExecuteNonQuery(); myConn.Close() ; } //Set the text of various textboxes to inform //the user of the text entered into the database NameLabel.Text = name; EmailLabel.Text= email ; SubjectLabel.Text=subject; MessageLabel.Text=message ; } else { errmess.Text="This Page Cannot be called directly."; errmess.Text+=" It has to be called from the Form posting page. " ; } } //Class to parse the Message into HTML format public string parsetext(string text) { //Create a StringBuilder object from the string input //parameter StringBuilder sb = new StringBuilder(text) ; //Replace all double white spaces with a single white space //and &nbsp; sb.Replace(" "," &nbsp;"); //Check if HTML tags are not allowed //Convert the brackets into HTML equivalents sb.Replace("<","&lt;") ; sb.Replace(">","&gt;") ; //Convert the double quote sb.Replace("\"","&quot;"); //Create a StringReader from the processed string of //the StringBuilder StringReader sr = new StringReader(sb.ToString()); StringWriter sw = new StringWriter(); //Loop while next character exists while(sr.Peek()>-1) { //Read a line from the string and store it to a temp //variable string temp = sr.ReadLine(); //write the string with the HTML break tag //Note here write method writes to a Internal StringBuilder //object created automatically sw.Write(temp+" ") ; } //Return the final processed text return sw.GetStringBuilder().ToString(); } </script> <LINK href="mystyle.css" type=text/css rel=stylesheet> </head> <body topmargin="0" leftmargin="0" rightmargin="0" marginwidth="0" marginheight="0">  <center> <asp:label id="errmess" text="" style="color:#FF0000" runat="server" /> <h2 class="fodark">Thank You , for posting on the Message Board.</h2> <table align=center width="60%" border="0" cellspacing="2" cellpadding="1" > <tr class="fohead"><td colspan="2">The information You Posted!</td></tr> <tr class="folight"> <td>Name :</td> <td><asp:label id="NameLabel" text="" runat="server" /></td> </tr> <tr class="folight"> <td>E-Mail :</td> <td><asp:label id="EmailLabel" text="" runat="server" /></td> </tr> <tr class="folight"> <td>Subject :</td> <td><asp:label id="SubjectLabel" text="" runat="server" /></td> </tr> <tr class="folight"> <td>Message :</td> <td><asp:label id="MessageLabel" text="" runat="server" /></td> </tr> </table> <h4 class="fodark"><a href="forum.aspx">Click here </a> to go back to the Forum. <%-- A little work to show the link to return back to the page if, the post was a reply --%> <% if(Request.Params["previd"]!=null) { %> <a href='reply.aspx?postid=<%=Request.Params["previd"] %>'> Click here </a>to go back where you came from. <% } %> </h4> </center>  </body> </html></pre> </td> </tr> </table>Conclusion Congratulations! You have JUST read through a HUGE article. And since you have reached till here, you might as well scroll a bit more and empty your mind below in the feedback form........ Your views will let me know if what I did over 2 days really made sense or was it a waste?? Till next time ..." Asta Lavista .... I will be BACK ..... " :)

Discussion Forum Board Beta2

Comments