Quiz to database, instructions and code

[joep]

Hello,
Like many people here, I was interested in sending quiz results to a database, rather than through email. Even with the tutorials people have posted here to simplify sending email, you still wind up with the person receiving the emailed results being forced to do a lot of manual work.

I've put together an application that stores all your quiz results cleanly in a database, and provides a very basic administrative interface for reviewing those results.

The app consists of two web pages and a database, together with a modification to quizmaker.html that's fairly similar to the ones people have posted relating to the email process. Everything is included in the attached zip file, including a screen shot of the results you should see once you have everything working.

Other folks have been posting work in PHP; I'm a .NET developer, so this code is all C#. I've also used Microsoft SQL Server 2005 for the database (creation scripts are included in the zip file), although there's no reason why you can't use Access or anything else if you want. You'll need to modify my code a bit to use a different database.

If you have a Windows-based webserver with the .NET 2.0 framework, ability to upload source code files into a web application or ability to create a new web application, ability to add tables to a SQL 2005 database or ability create a new SQL 2005 database, and are running on a Windows network, my code should work for you virtually as-is. If you need help with any of those things, you might very likely need a developer to help you adapt my code to your own situation.

My app starts off the same as the email modifications, altering the SendQuiz function within quizmaker.html. Rather than posting the results to an emailer, though, I post them to a web page containing code to parse the results and store them in a database.

The processing code is in q_process.aspx. I've commented it, so even if you don't know C# you should be able to follow my comments to see what I'm doing. Hopefully it will make enough sense to you that you can rewrite it in your own language of choice, if necessary. The admin code is in quiz_admin.aspx. There are no comments in there, but it's very straightforward. There are three dropdown boxes on the screen, and each one triggers code to load data into the others. All it does is read from the database. You select a quiz and it shows you a list of people who have passed it and a list of people who have failed. You select a person, and it displays a summary list of each time they've taken that quiz. Select a summary line and it displays all the detail relating to that quiz attempt. That's it.

Here's what you need to do to install it.

1) Create the database. Scripts are included in the zip file. There are three tables. Quiz, Quiz_Summary, and Quiz_Detail.

2) Update the connection string in both ASPX files. I just included a placeholder for a SQL 2005 connection string. You'll need to update that with your own database credentials, or replace it with a connection string for whatever other database you're using. Note -- in my own installation, I've compiled the .NET code into binaries and that's what my users run. For simplicity I've posted code here that will run with no precompiling, but this exposes your connection string (all your code, actually) to your users. You probably want to compile it for security.

3) Update the "thank you" page location in q_process.aspx. This is the destination page your users will land on after taking a quiz. It should be generic enough to be used for every quiz. The idea is that you create this page once and then don't have to mess with it again for future quizzes.

4) In q_process.aspx, I call System.Security.Principal.WindowsIdentity.GetCurre nt() in order to get the name of the person who is taking the quiz. If your user is logged into an NT or Active Directory domain on a Windows PC, this should work for you. If not, this will probably be your biggest challenge. You'll need some way to identify who is taking the quiz.

5) Upload q_process.aspx and quiz_admin.aspx into a web application. You probably don't want the admin page accessible to everyone, so be aware that both pages don't have to go together in the same website. They just both need access to the database. Their locations should not be quiz-specific -- the idea is that you install these files once, and they process all your quizzes going forward.

6) Modify the quizmaker.html file for your quiz. This is the one step you will have to do for each individual quiz, but at least it's the same edit each time. The edit is in the zip file.

That's it. Looks like a lot of work, but it really isn't. The "email results" button will now insert into the database.

Have fun,
-- Joe

[joep]

Here's a minor update to the admin page. I noticed that when I selected a quiz and then an individual who either passed or failed that quiz, I had forgotten to filter the summary list by the quiz id, so I was returning a summary line for every quiz the selected individual had taken, not just this individual's summary lines for the selected quiz.

The attached update file fixes that. You can just replace the original quiz_admin.aspx file with this new one. The new lines are commented as "//new line 8-20".

-- Joe

[bigtoga]

This is great stuff - thanks for sharing.

[stars75238]

Jeop,

You mentioned people posting this action in PHP, that is where we are. We are a LAMP system, that being the case, I can not use any C# or ASP code and I do not know how to modify the code to PHP. Is there any help you have on this or is there anything you know where I can get the PHP type app?

Michael

[joep]

Hi Michael, (and hi bigtoga -- glad you find this useful),

Note to Articulate admins -- I just lost and had to rewrite this entire post because my login timed out while writing it over the course of just a few minutes. Is a timeout that short really necessary?

Anyway...

Hi Michael,

I'm afraid I don't know PHP, so I wouldn't even be able to modify my own code into PHP. But whatever language you're using, the basic idea is exactly the same. When you take a quiz and click the "email results" button, a javascript function called SendQuiz contained within quizmaker.html is executed, and it passes the quiz results as one long string of delimited text to an email application. What you'll want to do is redirect that string of text to code of your own, where you can then manipulate it as necessary.

In my case, I redirect that string of text and send it to an aspx page called q_process.aspx, where I then break it apart and store it in a meaningful way.

I the "Email Results Tutorial - PHP" thread (Email Results Tutorial - PHP), beeneeb posts PHP code that redirects the string of result text to a PHP page called sendmail.php, where he then sends it via email by a process different than the default method. But up to that point, our code is very similar.

In beeneeb's sendmail.php, he retrieves the string of result text with this code:

$QuizResults = $_REQUEST['QuizResults'];

In q_process.aspx, I retrieve it with this code:

quizString = Request.Form["QuizResults"];

From that point, we both have a single variable that contains all of the quiz results.

If you have people who can write PHP code, they should at least be able to follow along with the comments in my C# code to see what I'm doing from there on out, even if the code itself doesn't make any sense.

Of course, my comments may not make any sense either, so I'll certainly try to answer any questions you might have if I can.

-- Joe

[stars75238]

Joe,

Thank you and I think the idea of breaking apart the variable for the PHP page is a good reference. I will look at the PHP post and get what I can from that. I will end up being hte PHP writer here which is not a real issue, it has just been many a year since I have written and I was only learning when I stopped.

I should get up to speed and be able to extract what is needed, thank you for the help and I will start working on the alterations, when I am done I will post the code in the off chance someone comes along with the same issue.

And yes the short time-outs have caught me a few times.

Thank you,
Michael

[costalive]

Hi Joep,

Do you have the solution file .SLN or the CSPROJ file for the project available from Visual Studio?

I noticed in the instructions “ Note -- in my own installation, I've compiled the .NET code into binaries and that's what my users run. For simplicity I've posted code here that will run with no precompiling, but this exposes your connection string (all your code, actually) to your users. You probably want to compile it for security”.


Thanks in advance
Jim .. this question was requested by my tech person

[joep]

Hi Jim,
Here you are. This zip contains the solution file for the processor. Let me know if you have any questions about it.

-- Joe

[gabe]

Originally Posted by joep

Note to Articulate admins -- I just lost and had to rewrite this entire post because my login timed out while writing it over the course of just a few minutes. Is a timeout that short really necessary?

Hi Joe-

I'm not sure why that would've happened, but sorry to hear it! If you have the box checked to remember you, you should never be timed out. Did you, by chance, delete your cookies in a another browser session while writing the post? What browser are you using?

Gerry had some issues with Firefox a couple months ago. Maybe that will help?

[costalive]

Originally Posted by joep

Hi Jim,
Here you are. This zip contains the solution file for the processor. Let me know if you have any questions about it.

-- Joe

Joel,

Thank you so much! you rock .... just a quick question ... some how I might of overlooked this but, how are you publishing the quiz? For web or LMS. I just want to make sure I am adjusting the right html.