Build a Survey Manager

Create, deploy, and customize surveys on the Web.



TECHNOLOGIES: ADO.NET | Web.config | DataGrid


Build a Survey Manager

Create, deploy, and customize surveys on the Web.


By Essam Ahmed


ASP.NET and the .NET Framework System Classes provide a flexible platform for creating Web-based applications using Web Forms. This article shows you how to use ASP.NET in conjunction with ADO.NET to create a robust survey application, using a surprisingly small amount of code. I recently developed an application named EasyQuest for creating surveys. This ASP.NET application is based on an application that appears in my book JScript .NET Programming (Wiley).


EasyQuest's major features include a browser-based interface for creating new surveys - an administrator can define the survey question and possible responses. It also supports survey beginning and end dates, so surveys activate and expire without an administrator's intervention. It has a graphical results view, it's easy to deploy, it uses ADO.NET, and it's open-source - you can modify, use, and distribute EasyQuest's code as you like.


As you follow along the features of this app, you'll learn to design a flexible, easy-to-use database structure, use the ASP.NET DataGrid control, and generate page content with rendering blocks. You'll also customize web.config to store application-specific settings and optimize the app's performance. You can download the sample code for this app; this zip file contains a readme.txt file describing how to set up the application on your system's Web server.


EasyQuest's Interface is ... Easy

You can access all of EasyQuest's functionality using a browser. EasyQuest's start page displays a listing of questionnaires to which a visitor can respond (see Figure 1).


Figure 1. The EasyQuest start page shows the survey question, the date the questionnaire expires, and how many votes the questionnaire has registered.


All of EasyQuest's questionnaires are open to voters for a limited time as determined by an administrator. Only active questionnaires, those with a beginning date before and an expiration date after the current date, appear in the list of available questionnaires. Each questionnaire has two links (the underlined elements in Figure 1). The link in the Question column goes to the voting form, and the link in the Total Votes column takes you to the results screen.


EasyQuest's voting form is simple: Simply select your choice and click on the Vote button. EasyQuest records your vote in its database and takes you to the Results page (see Figure 2).


Figure 2. After voting, readers are taken to the results screen automatically, where they can see the status of the survey to date.


EasyQuest's start screen also has a link to a form that allows an administrator to create a new EasyQuest survey. Creating a questionnaire is a two-step process: First, you gather information about the questionnaire, then you acquire the text of each possible response.


The EasyQuest database has a logical structure (see Figure 3) that separates information about questionnaires and questionnaire options. The physical database design, representing the implementation of the diagram in Figure 3, mirrors the database's logical structure. The application's OnStart event triggers EasyQuest to establish a connection to the database through the Application_OnStart event handler, which resides in the global.asax file. The details of the connection reside in the web.config file (I'll describe global.asax and web.config later).


Figure 3. This is the structure of the EasyQuest database. EasyQuest uses ADO.NET to communicate with the database.


Explore EasyQuest's Start Page

EasyQuest's start page, default.aspx, uses a database connection stored in an Application variable to query the database for a list of active questionnaires. The page displays the list in an ASP.NET DataGrid control. The DataGrid control makes creating pages that display tabular data easy because the control handles the details of rendering the data in HTML.


When a user accesses a page, ASP.NET loads the page and fires a Page_Load event. The code in default.aspx implements a Page_Load event handler that initiates page processing:


Sub Page_Load(sender As Object, e As EventArgs)

    listQuestions.DataSource = acquireData()


End Sub


The listQuestions variable in the listing is the name of the DataGrid control. The Page_Load event handler calls another function, which acquires the data for the control and returns a DataView object. The DataGrid control is bound to the DataView object through the DataGrid's DataBind method, at which point the control renders itself using HTML. The code in Figure 4 shows the code that acquires the data from the database.


Function acquireData() As ICollection

  Dim startOfDay As String = DateTime.Today & " 00:00:01"   Dim endOfDay As String = DateTime.Today & " 23:59:59"

  dbCmd.Connection = databaseConnection

  dbCmd.CommandText = _

   "SELECT questionnaires.questionnaire_id," _

   & "questionnaires.beginDate, " _

   & "questionnaires.expireDate, " _

   & "questionnaires.question, " _

   & "Sum(questionnaire_options.votes) AS SumOfVotes " _

   & "FROM questionnaires " _

   & "INNER JOIN questionnaire_options ON " _

   & "questionnaires.questionnaire_id = " _

   & "questionnaire_options.questionnaire_id " _

   & "WHERE questionnaires.beginDate <= #" + startOfDay " _

   + "# and " & "questionnaires.expireDate >= #"+endOfDay+"# " _

   & "GROUP BY questionnaires.questionnaire_id, " _

   & "questionnaires.beginDate, "

   & "questionnaires.expireDate, " _

   & "questionnaires.question "


  Dim dbAdapter As OleDbDataAdapter

  Dim dbDataSet As New DataSet()

  Dim listQuestions As New DataGrid()

  dbAdapter = New OleDbDataAdapter()

  dbAdapter.SelectCommand = dbCmd




  acquireData = dbDataSet.Tables(0).DefaultView


End Function

Figure 4. The acquireData function gathers information for display in the DataGrid control. The query is based on the time and date.


Figure 4 shows how default.aspx composes the query based on the current date using the System.DateTime structure's Today property. The query selects a list of active questionnaires by comparing the current date with the values of the beginDate and endDate fields (the current date must be between these values). The query calculates the total number of votes users have cast for the questionnaire using the Sum function. The Sum function works in conjunction with the GROUP BY clause to calculate the total number of votes for each questionnaire. Using the Sum function to calculate the total number of votes allows the database to determine the most effective way of calculating the value and is more effective than maintaining the total programmatically in another field or calculating it within the application's code.


The ASP.NET DataGrid control, by default, renders all fields in the result set as a simple HTML table. You can customize the control's output, however, by specifying what columns you want to render and how you want the control to render them. Figure 5 shows the declaration of the listQuestions DataGrid control.



 BorderStyle="Solid" Font-Name="Verdana" CellPadding="5"

 ItemStyle-Wrap="false" MaintainState="false">



     HorizontalAlign="Left" Font-Names="Vardana,Arial">






         DataTextFormatString="{0}" DataTextField="question"



         "showEasyQuestSurvey.aspx?survey={0}" />


         HeaderText="Expires" DataField="expireDate" />







         itemstyle-horizontalalign="Right" />


Figure 5. This DataGrid control declares how to display a result set. It specifies the font as well as how column headers should be rendered.


Figure 5 demonstrates how to use an asp:HyperLinkColumn to generate links with customized text and references. The links the DataGrid control generates reference the questionnaire page (which allows users to vote) and the questionnaire's result page (which displays the survey's results using a chart).


Build the Show Questionnaire Page

The code in Figure 6 shows how showEasyQuestSurvey.aspx builds the questionnaire page (which allows users to vote) based on queries that extract information from the questionnaires and questionnaire_options tables. It uses the OleDbCommand.ExecuteReader method to return an OleDbDataReader object that fetches data directly from the database when you call its Read method.



' File: showEasyQuestSurvey.aspx

Dim rst as OleDbDataReader


Dim dbCmd As OleDbCommand = New OleDbCommand()


if dbCmd.Connection.State = ConnectionState.Open Then _



dbCmd.CommandText= _

 "select question from questionnaires where "

 & "questionnaire_id=" & Request("survey")


rst = dbCmd.ExecuteReader()











dbCmd.CommandText= _

 "select text,rank from questionnaire_options " _

 "where questionnaire_id=" & Request("survey") & _

 " order by rank asc"


rst = dbCmd.ExecuteReader()


While( rst.Read() )



End While



Figure 6. showEasyQuestSurvey.aspx renders a page dynamically based on content from the EasyQuest database.


The code in Figure 6 uses rendering blocks - Visual Basic .NET code that's embedded in HTML code, delimited with <% and %> character sequences - to render the voting form. The code, although concise, actually does a lot of work. The listing begins by getting the survey question on which the user wants to vote from the database, based on the link the user clicked on at the start page (default.aspx). Then, the code renders each radio button, along with its caption (the option's text), based on the value of the Rank field. In both cases, the code demonstrates how to use the OleDbCommand object's ExecuteReader method to access rows, one by one, through an OleDbDataReader object.


Figure 7 shows the code that executes when a user clicks on the Vote button.


dbCmd.CommandText= _

 "select votes from questionnaire_options where " _

 &" questionnaire_id=" & Request("survey")   _

 & " and rank=" & (Request("surveyOpt"))



rst = dbCmd.ExecuteReader()


Dim nextVoteCount As Integer = CInt(rst("votes"))+1




dbCmd.CommandText= _

 "UPDATE questionnaire_options set votes=" _

 & nextVoteCount _

 & " where  questionnaire_id=" & Request("survey") _

 & " and rank=" & (Request("surveyOpt"))





Figure 7. EasyQuest records a user's vote. By issuing a select query, the listing retrieves the total number of votes registered for the option the user selected.


The code continues by updating the number of votes, using an UPDATE query. Because an UPDATE query doesn't return any rows, the code uses the OleDbCommand object's ExecuteNonQuery method, which doesn't store a query's result. This reduces the application's memory requirements because the application doesn't need to allocate memory for an empty result set.


Display a Questionnaire's Results

The showEasyQuestResult.aspx page displays a questionnaire's results to the user (see Figure 2). Similar to showEasyQuestSurvey.aspx(which displays a questionnaire to a user), the code in showEasyQuestResult.aspx uses rendering blocks to generate a chart dynamically that represents the questionnaire's results. Figure 8 shows the code that acquires a survey's question and the total number of votes registered for all the question's possible responses.


Dim rst As OleDbDataReader


Dim dbCmd As OleDbCommand = new OleDbCommand()



dbCmd.CommandText="select question from questionnaires " & _

 "where questionnaire_id="   & Request("survey")



' ExecuteScalar returns the value of the first field

' in the first row of the result set

Dim surveyQuestion As String = dbCmd.ExecuteScalar()



dbCmd.CommandText="select sum(votes) from " & _

 "questionnaire_options where questionnaire_id=" & _




Dim totalVotes As Integer = dbCmd.ExecuteScalar()


Figure 8. showEasyQuestResult.asp gets the survey question and total number of votes from the database.


Unlike the code in Figure 7, which reads the value of the first field of the first record in the result set, the code in Figure 8 uses the ExecuteScalar method. ExecuteScalar is designed to retrieve the value of the first field of the first row from a result set and reduce the amount of code you need to write from three lines (as shown in Figure 7) to a single line (as shown in Figure 8). ExecuteScalar has the added advantage that it doesn't require the creation of a DataReader; instead, the scalar value is passed back in the ExecuteScalar method's return value.


The code in Figure 9 illustrates how EasyQuest generates the chart that appears after the survey question dynamically on the Results screen. The code renders a series of table rows that contain the option's text (rst("text") in the listing), a bar representing the percentage of total votes the option has registered for it (based on the pcntVotes variable in the listing), a textual representation of the percentage of votes, and the actual number of votes the option has registered for it (rst("votes") in the listing).



Dim pcntVotes As Decimal


dbCmd.CommandText="select text,votes from " & _

 questionnaire_options where questionnaire_id=" & _

 Request("survey") & " order by rank asc"


rst = dbCmd.ExecuteReader()





  If CInt(rst("votes")) = 0 Then

    pcntVotes = 0


    pcntVotes=Math.Round((CInt(rst("votes"))/ _


  End If





End While




Figure 9. EasyQuest generates the chart that appears after the survey question dynamically on the Results screen.


As before, the code resides in a rendering block, uses the ExecuteReader method to execute the query, and iterates over the result set using the Read method. As the code iterates over the result set, it calculates the percentage of total votes an option received. The code uses the Math.Round method to truncate the percentage to zero decimal places. The resulting value is used to determine the width of the bar that appears beside each option.


One thing you need to watch out for when working with the OleDbCommand object is to make sure you close the connection to the database when you're finished working with the result set. As soon as you call the ExecuteReader method, the associated database connection object, OleDbConnection, becomes unavailable for any other objects until you close it. The code in Figure 10 closes the underlying database connection explicitly. However, ExecuteReader has an overloaded implementation that accepts a CommandBehavior value. Set the value of ExecuteReader's parameter to CommandBehavior.CloseConnection to have it close the underlying database connection when you close the OleDbDataReader object, like this:


rst = dbCmd.ExecuteReader(CommandBehavior.CloseConnection)


' No need to close the OleDbConnection

' now because it is closed automatically here.


Create a New Questionnaire

Two pages, newEasyQuestSurvey.htm and setEasyQuestSurvOpts.aspx, work together to gather information about a new questionnaire. The code in newEasyQuestSurvey.htm is all client-based JavaScript that validates the values the user enters and the beginDate and expireDate fields. setEasyQuestSurvOpts.aspx renders the text box fields shown in Figure 5 and forwards all the information to a page that creates the new survey (see Figure 10).


Dim numberOfOptions As Integer = _


Dim surveyQuestion As String = Request.Form("txtSQ")

Dim surveyStartDate As String = Request.Form("dtStart")

Dim surveyExpireDate As String = Request.Form("dtEnd")


Dim dbCmd As OleDbCommand = New OleDbCommand()


' Create the new questionnaire

dbCmd.CommandText="INSERT into questionnaires " _

  & "(beginDate,expireDate,question) values('" _

  & surveyStartDate & " 00:00:01" & "','" _

  & surveyExpireDate & " 23:59:59" & "','" _

  & surveyQuestion.Replace("'","''") & "')"






' Get the questionnaire_id of the last (new) questionnaire

dbCmd.CommandText= _

 "select max(questionnaire_id) from questionnaires"


Dim surveyID As String = dbCmd.ExecuteScalar()



' Add each option to the questionnaire_options table

Dim nIndex As Integer

Dim strTmp As String

For nIndex=1 To numberOfOptions


  strTmp = Request.Form("txtSurveyOption" & nIndex)

  dbCmd.CommandText="insert into questionnaire_options " & _

   "(questionnaire_id,rank,[text],votes) values(" _

    & surveyID & "," _

    & nIndex & ",'" _

    & strTmp.Replace("'","''") _

    & "',0)"






' Redirect to the questionnaire screen...

Response.Redirect("showEasyQuestSurvey.aspx?survey=" & _


Figure 10. Create a new survey by adding it to the EasyQuest database.


The code in Figure 10 uses an INSERT query to add the new questionnaire to the questionnaires table and executes it using the ExecuteNonQuery method. Before the page adds the survey options (possible responses) to the questionnaire_options table, the page must determine what the value of the questionnaire_id field is for the survey it just created to maintain the relationship between the question and its possible responses. The code determines the value of the field by executing a select query for the largest value of the questionnaire_id field (MAX(questionnaire_id) in the listing's select query). Once the code acquires the value of the new questionnaire_id, it uses the value to add the possible responses to the questionnaire_options table by using a series of INSERT queries that execute using the ExecuteNonQuery method.


The code assumes there aren't any other administrators adding new questionnaires to the EasyQuest database concurrently. If you want to allow more than one administrator to create new questionnaires concurrently, create an OleDbTransaction object to frame all the queries that create the new survey within a transaction.


Store Application-specific Settings in Web.config

An ASP.NET application's web.config file uses XML and contains a lot of information about how ASP.NET should handle your application. You can add custom, application-specific settings that are exposed to your application through the System.Configuration.ConfigurationSettings class. The advantage to storing application-specific settings in the web.config file is you have a means of providing administrators with a way to change the settings your code uses without modifying your code. Web.config also is protected from being downloaded by hackers and thereby offers greater security for sensitive application settings such as database-connection strings, which often contain user IDs and passwords. Here's an excerpt from the application's web.config file that includes the database connection string and the database name:




     value="Provider=Microsoft.JET.OLEDB.4.0;data source="/>




You get at the application-specific settings stored in web.config using the ConfigurationSettings class, as shown here taken from the global.asax's Application_OnStart event handler:


Sub Application_OnStart()

  Dim dsn As String = _

   ConfigurationSettings.AppSettings("connectionString") _

    & Server.MapPath(ConfigurationSettings.AppSettings( _


  Dim appDbConnection As New OleDbConnection(dsn)


End Sub


Optimize Application Performance

Here are three settings you can change in the web.config file that can make a lot of difference in how your application performs:








The tag disables debugging. Having debugging enabled for your production application can slow it down a great deal. The tag disables trace output, a feature that provides detailed timing information for every method your pages call.


The tag is possibly the most important of the three settings because it disables a feature EasyQuest doesn't use and is normally enabled by default: session-state management. You can write your ASP.NET applications to store information that lasts for as long as a user's session is active. Storing per-user session data consumes a lot of memory and, as a result, affects how many concurrent users the application can service. Because EasyQuest does not use per-session data, I have disabled the feature, which greatly reduces EasyQuest's memory requirements.


The files referenced in this article are available for download.


Essam Ahmed is an accomplished developer with more than 10 years of professional programming and design experience. He enjoys writing and is author of JScript .NET Programming (Wiley). He is a book reviewer for and has written articles for and He is a practicing member of the Worldwide Institute of Software Architects ( and spoke at VSLive! 2001 in Sydney, Australia. Visit Essam's Web site at


Tell us what you think! Please send any comments about this article to [email protected]. Please include the article title and author.




Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.





   & " name=""surveyOpt"" value=""" (CInt(rst("rank"))) " _

   & """>" & rst("text") & "

" & rst("text") & _


" _

   & "

   & width=" & (pcntVotes*3) & "> ("& pcntVotes & _

   & "%)

" & rst("votes") _

   & "