Searching for articles by tag and other criteria

Here is an expansion of the problem from the last post. Now I have many articles, each has a title, an author, a list of tags, and other parameters. Let's say for simplicity that all the data is in one table - Articles, with the fields: id, title, author, and the tags are in a separate table called Tags with the fields ArticleID and Name.
Now I am looking for a single SQL command to search for articles with some text in their title, some text in their author, and some text in one of their tags.
Since there could be more then one tag for each article, it cannot be done by a simple query on the articles table.
To do that, I need to use subquery in the WHERE clause.
Here is the solution:


SELECT Atricles.ID, Articles.Title, Articles.Author
FROM Articles
WHERE Article.Title LIKE '%someText1%'
AND Article.Author LIKE '%someText2%'
AND (0 < (SELECT COUNT(*) FROM Tags
WHERE Tags.ArticleID = Articles.ID
AND Tags.NAME LIKE '%someText3%')

Find an article with a list of tags

Let's say I have a web site with articles, and each article can have one or more tags, just like the blog posts in this web site (where they are called "labels").
I save all of the article's tags in a table, called ArticleTags, that have two columns: ArticleId and TagId.
Now let's say I have a list of tags - ASP.NET, SQL and WEB, for example, with IDs 1,2,3.
I want to find all of the articles that have all 3 tags.
If I try the simple query

SELECT ArticleID FROM ArticleTags WHERE TagId in (1,2,3)

I will get all the articles with either tag 1 or tag 2 or tag 3 - not the articles with all three of them.

When I looked for solutions on the web, I was surprised to find that many implemented this by saving all the tags as one long text field of the articles = "ASP.NET,SQL,WEB" - and used "LIKE" in the query. It is inefficient, because of the use of "like", and because the search is on the tag text itself and not on the tag ID, and also limits the number of tags to the capacity of one field, and I also want to save the text in a separate table, for localizations and other uses.

I finally got the following solution that gives me exactly what I was looking for:

SELECT ArticleID
FROM ArticleTags
WHERE (TagId IN (1, 2, 3))
GROUP BY ArticleID
HAVING (COUNT(*) = 3)

I am using the GROUP option to group all the same ArticleID together, and then count them. If their count is exactly the number of Tag IDs I am looking for, it means that this article has all the tags in my list. This condition has to be in the HAVING part of the query and not in the WHERE clause because it uses an aggregation field.

Validating CheckBoxList Control

ASP.NET provides the validation controls that enables checking if the input is valid before starting to process in on the server and thus save time for the user. These controls are very easy to use. However when I tried to user a required field validator for a CheckboxList control, to make sure that the user selected at least one item, I got an exception, saying that the control to validate is invalid. It turned out that the validation does not work for CheckboxList.
I found the following article about it:
http://aspnet.4guysfromrolla.com/articles/092006-1.aspx
It claims that there are 3 possible solutions: implement the validation not using the validation controls, use custom validation controls and write the validation methods, or create a custom control that does the validation the same way as the validation controls provided by the ASP.NET.
Since the third option is out of my scope for this project, I chose the second option, using the custom validation control.
However, when I created a custom validation control and set the control to validate as my checkbox list, I got the same exception again - a checkbox list cannot be the control to validate, even for a custom validation control.
So I decided to use a dummy control as the control to validate. The problem was that I needed a way to know which control to validate. These checkbox lists are created dynamically at runtime.
This is what I did: I created a dummy textbox and put the ID of the checkboxlist as the value inside the text box. I sent the textbox as the control to validate, and then got the ID of my "real" control to validate from the value that was sent to the validation function, and used it to find my checkbox list and perform the validation checks.
But of course I didn't want the user to see the dummy text box. So I set its "Visibility" to false.
When I did that, the validation method was never called. So it seems like the ASP.NET does not perform validation for a control whose visibility is false.
The solution was to hide it by setting its "display" attribute to "none".
One last problem I had when developing this solution: When I only had the server-side validation method, things didn't work well, I could not see the error message and the operation wasn't stopped. After I added the javascript client-side validation function, everything worked just fine.
Here is the code:

Creating the dummy text box and the custom validation control:

// add dummy text box just for passing to validation function
TextBox txtDummy = new TextBox();
txtDummy.ID = "txt_checkbox_" ID.ToString();
txtDummy.Text = chk.ID.ToString(); // chk is the checkboxlist to validate
txtDummy.Attributes.CssStyle.Add(HtmlTextWriterStyle.Display, "none");
Panel1.Controls.Add(txtDummy);
// add custom validation control
CustomValidator valCheck = new CustomValidator();
valCheck.ControlToValidate = txtDummy.ID;
valCheck.ErrorMessage = "You must select at least one value!";
valCheck.ServerValidate = new ServerValidateEventHandler(ValidateCheckbox);
valCheck.ClientValidationFunction = "CheckVal";
Panel1.Controls.Add(valCheck);

Server side validation method:

void ValidateCheckbox(Object source, ServerValidateEventArgs args)
{
// get the checkbox list
CheckBoxList chk = Panel1.FindControl(args.Value) as CheckBoxList;
// check if at least one option is selected
if (chk.SelectedIndex < 0)
args.IsValid = false;
else
args.IsValid = true;
}

Client side validation method:

function CheckVal(sender, args)
{
var checkID = args.Value;
var checDP = document.getElementById(checkID);

var selectedItemCount = 0;
var liIndex = 0;
var currentListItem = document.getElementById(checDP.id + '_' + liIndex.toString());
while (currentListItem != null)
{
if (currentListItem.checked)
selectedItemCount++;
liIndex++;
currentListItem = document.getElementById(checDP.id + '_' + liIndex.toString());
}

if (selectedItemCount == 0)
{
args.IsValid = false;
return;
}

args.IsValid = true;
}

ERROR: "Microsoft JScript runtime error: ASP.NET Ajax client-side framework failed to load" When using AJAX

I added some AJAX functionality to an existing AST.NET website. It was my first experience with AJAX.NET.
I started with a simple tutorial, built a new small web site with AJAX, and everything worked just fine.
Then I tried to implement it to my existing website.
First I had to upgrade the website from .NET 2.0 to .NET 3.5, to enable the java controls. I am working with Visual Studio 2008, but I set the website to 2.0, so that I could work with it on my laptop, where I had the previous version of visual studio.
After upgrading the website to version 3.5 of .NET, I added a very simple AJAX control: I wrapped an existing button with UpdatePanel, to prevent page refresh when the button is clicked.
When I ran the website, I got the error message:

Microsoft JScript runtime error: ASP.NET Ajax client-side framework failed to load.

I started searching the web for this error. Many programmers met this error message, and it seemed like the suggested solutions were of two types:

1. Checking if AJAX extension was properly installed - which is irrelevant to me, because I work with Visual Studio 2008 where AJAX is built-in, and also the simple AJAX website worked fine.
2. Set the web server - the IIS - to recognize *.axd files. Again it could not apply to me because I was running everything locally, through Visual Studio, and I don't even have IIS installed on my machine.

I went on searching and researching the problem, and found on some forum some semi-cryptic comment about wrong HttpHandlers on the Web.config files.
When I looked at my Web.config file, I could not find any HttpHandler or something like that.
It turned out that a whole section was supposed to be added to the Web.config file when I upgraded the website from .NET 2.0 to .NET 3.5, and for some reason this section was not there. Seems like something went wrong in the upgrade process. When I did the upgrade again, I could see the section about the handlers in the Web.config file, and this time everything worked just fine. This is part of what was missing in the Web.config that is relevant:

<modules>
<remove name="ScriptModule"/>
<add name="ScriptModule" preCondition="managedHandler" .../></modules>
<handlers>
<remove name="WebServiceHandlerFactory-Integrated"/>
<remove name="ScriptHandlerFactory"/>
<remove name="ScriptHandlerFactoryAppServices"/>
<remove name="ScriptResource"/>
<add name="ScriptHandlerFactory" verb="*" path="*.asmx" preCondition="integratedMode..."/>
<add name="ScriptHandlerFactoryAppServices" verb="*" path="*_AppService.axd" .../>
<add name="ScriptResource" verb="GET,HEAD" path="ScriptResource.axd" preCondition="integratedMode" .../>
</handlers>


Seems like it has something to do with recognizing the script files generated for the client side AJAX implementation.

Anyway, after fixing the Web.config file, the error code disappeared and AJAX works just fine.

Error: "MSDTC on server 'servername' is unavailable" when using nested transaction

I got this error when I used nested transaction: I opened an ASP.NET transaction

using (TransactionScope scope = new TransactionScope())
{

Inside the block I called a function, that started another transaction the same way.
Inside the second transaction, I got this exception when I tried to open a connection:

using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
cn.Open();

When I googled the error message, the advice that I found was to start the transaction manager on the server, or to enable remote access if the server is on a remote machine. It didn't seem like this was the problem since I was running everything locally, and transactions worked just fine when I wasn't trying to use nested transactions.
I looked for info on nested transaction, and found out there is no problem using them in ASP.NET - the implementation just joins all the actions of the nested transaction to the previous transaction. So I figured it is not the problem.
I tried closing VS and starting it again to no avail, then I restarted my machine, again, without solving the problem. I even tried running the code on another machine, thinking maybe something went wrong with the database installation on my main machine, but exactly the same thing happened on the other computer.

Eventually I did what all the posts that I found advised to do, and started the transaction manager on my machine, using the following procedure:

Start->All Programs->Administrative Tools->Services

Right click on "Distributed Transaction Manager" and "start".
I right clicked this service, clicked "properties", and set the startup type to "Automatic", to make sure it starts every time Windows starts.

Now everything works fine.
I still don't know why only nested transactions need this service, or found anything about it in the documentation, but at least it works, and I hope this post will save other programmers all the time I wasted on this problem.

Displaying a hidden panel on button click using client side code

Sometimes there is part of the web page we want to display only after the user clicked a button. For example, we are displaying a list of customer, and when the user clicks a button that says "Add New Customer", a panel is displayed with controls to create the new customer.
It is very simple to implement using ASP.NET with server-side code. The panel is created with Visible=False, and the button has an event handler that all it does is setting the visibility of the panel to true:
protected void Button1_OnClick(object sender, EventArgs e)
{
Panel1.Visible = true;
}

However, choosing this implementation means that there will be a full postback with every button click, and the user will have to wait for reload of the web page, when actually nothing is needed from the server.

To implement this code on the client using javascript, I needed a client-side button, instead of the Button control that forces postback when clicking it. I used the HTML button:
<input id="Add Customer" type="button" value="Add Customer" />

Now I had to add the code to display the hidden panel when the button is clicked. First I tried what seemed most logical, the javascript equivalent of the Visible ASP.NET's property.
So now the button definition in the aspx page looked like that:
<input id="Add Customer" type="button" value="Add Customer" onclick="document.getElementById('Panel1').style.visibility='visible'"/>

When I tried to run the code, I got an error saying that the search for "Panel1" did not get any valid object.
It turned out that when I set the Visible property of the panel to "false", the control wasn't created at all at the resulting html page, so the javascript cannot access it.
So I changed the Visible property to "true", and added a javascript right after its definition, setting its visibility to "hidden":
...
</asp:Panel>
<script type="text/javascript">
document.getElementById('Panel1').style.visibility='hidden';
</script>

Now it worked... almost. The html page did not display the panel until I clicked the button, but it left a blank place where it was supposed to be. That is not how I wanted it, and not as it worked before with the server-side implementation: I didn't want this blank space on the page when the panel is not displayed.
So I played a little with the style properties of javascript, and found out that the "display" property did just what I wanted.
So this is how the button definition looks now:

<input id="Add Customer" type="button" value="Add Customer"
onclick="document.getElementById('Panel1').style.display=''"/>

And the javascript right after the definition of the panel:

<script type="text/javascript">
document.getElementById('Panel1').style.display='none';
</script>

Now when I click the button, the panel for creating a new customer is displayed right away, without waiting for the page to load again.

Ho to display source code in the blog

When I wrote the last two posts, I noticed that everything I wrote between < and > disappeared. I assume it is some sort of hacking protection. Well, naturally writing about software development involves displaying some code examples from time to time. So as usual I starting searching for solutions. I found all kinds of solutions, but the simplest one was this web site:
http://www.elliotswan.com/postable/
Where I can copy the source code, and generate a "friendly" version of it, that blogger agrees to display in the post.

Regular Expressions in ASP.NET

I worked with PERL for a long time and of course I used the power of regular expressions intensively. I was glad to read that ASP.NET has built-in support for regular expressions, but after reading about it I realized I can say bye-bye to the elegant one-line commands I am used to from PERL.
After reading documentations, examples, and playing with the code myself, I can finally understand how to achieve what was so easy to do in PERL using the class hierarchy of ASP.NET.
This post is intended for people who know what are regular expressions, and want to understand how to implement them in C#.
This is what I needed to do:
I had a string consisting of some prefix, underscore and then a number: {prefix}_{num}
I wanted to get the number from that string.
The PERL way would be something like
my ($num) = $my_string =~ /_([0-9]+)$/
That's it... so simple.
Now this is how I did it in C#:

Match match = Regex.Match(my_string, @"_(?<num>[0-9]+)$");
int num = Int32.Parse(match.Groups["num"].ToString());

I used named grouping - I called the group I was looking for by a name, "num".
First I tried it without naming - omitting the ? from the pattern, and look for the first group: match.Groups[0] but what I got was the whole _ string, not only the number I was looking for. When I used named grouping I managed to get only the number, but it puzzled me, so I looked at it a little more. After some digging I found out that the first group - Group[0] - is the whole match, and the variables start at Group[1], so if I didn't want to use named groups, I would have to use Group[1] instead of Group[0].
It is quite confusing (and not really documented, at least where I was looking) so it seems clearer to me to remain with the named groups.

Displaying a Message Box from ASP.NET

ASP.NET does not have a class or method for displaying a message box. To display a message box one needs to write a javascript that looks something like that:
Response.Write("<script language='javascript'>window.alert('Hellow, World')</script>");

I always keep forgetting what is the exact syntax and how the javascript function is called and I find myself searching the internet or my previous code to find it out.
There are many solutions on the web, most of them sophisticated with lots of features, but I decided to write a super-simple static class that will wrap the previous javascript code with easy to remember and user c# code, and if I still won't remember, the development environment of ASP.NET will be happy to remind me, all I have to remember is "MessageBox" (this even I can remember) and type the magical "dot"...

So here is the class:

public static class MessageBox
{
public static void Show(string msg)
{
String str =
"<script language='javascript'>"
"window.alert('" msg "')</script>";
HttpContext.Current.Response.Write(str);
}
}

A simple date control

I needed a simple date control in my ASP.NET C# application. The calendar control provided with ASP.NET didn't work well for me because I wanted to make it easy to enter any year without the need to navigate through the years in the calendar. After I couldn't find such a control in a quick search I wrote a class that creates the controls I need dynamically.
I have a drop-down control for the day, with values 1 to 31, another drop down for the month, from "Jan" to "Dec", and a text control for the year.
Here is the class:

public class DateControl
{
static List _days = new List();
static List _months = new List();

public DateControl()
{
// fill days list
for (int i = 1; i <= 31; i++)
_days.Add(i.ToString());
// fill months list
_months.Add("Jan");
_months.Add("Feb");
_months.Add("Mar");
_months.Add("Apr");
_months.Add("May");
_months.Add("Jun");
_months.Add("Jul");
_months.Add("Aug");
_months.Add("Sep");
_months.Add("Oct");
_months.Add("Nov");
_months.Add("Dec");
} // DateControl

public void Create(Control container, string pref, string ID,
DateTime dateStart)
{
// day drop down
DropDownList dropDay = new DropDownList();
dropDay.ID = pref + "_drop_day_" + ID;
foreach (string strDay in _days)
dropDay.Items.Add(new ListItem(strDay));
dropDay.SelectedIndex = dateStart.Day - 1;
container.Controls.Add(dropDay);
// space
Literal li = new Literal();
li.Text = " ";
container.Controls.Add(li);
// months
DropDownList dropMonth = new DropDownList();
dropMonth.ID = pref + "_drop_month_" + ID;
foreach (string strMonth in _months)
dropMonth.Items.Add(new ListItem(strMonth));
dropMonth.SelectedIndex = dateStart.Month - 1;
container.Controls.Add(dropMonth);
// space
li = new Literal();
li.Text = " ";
container.Controls.Add(li);
// year
TextBox txt = new TextBox();
txt.ID = pref + "_txt_year_" + ID;
txt.MaxLength = 5;
txt.Columns = 5;
txt.Text = dateStart.Year.ToString();
container.Controls.Add(txt);
} // Create

public void Create(Control container, string pref, string ID)
{
Create(container, pref, ID, DateTime.Today);
}

And here is an example of how to use it:

DateControl date = new DateControl();
date.Create(panel, "prefix", uniq_ID, initialDate);

Where panel is a Panel control that will contain the date controls, "prefix" is some prefix all 3 control ID's will have, uniq_ID is some string that will appear at the end of each control's ID, and initialDate is a DateTime variable with the initial date value of the controls. This variable can be omitted and then the controls will have the initial value of today's date.

Building a business class to work with ObjectDataSource

The ObjectDataSource enables us to bind a UI control to a business class instead of accessing the database directly from the UI.
There are many on-line articles that explains this in details, and I will not do that here. I will give some tips and give some answers to questions I could not find anywhere and eventually found using trial and error.

• The business class can be defined using the [System.ComponentModel.DataObject] attribute, but it does not have to. When selecting the business class of the ObjectDataSource, we can select if we want to choose only from data components or not.
• The simplest way to define the methods used by the ObjectDataSource is making them static. That way we do not have to worry about instantiation and initialization of the class.
• The ObjectDataSource reads the public properties of the returned objects of the select method. Even if there are public members, they will not be visible to the controls using the ObjectDataSource as their source.
  

Finding records in one table that are NOT referenced in another table

Sometimes we have one table records referencing records in another table. What if we want to find all the records in one table that are not referenced by ANY record in the other table?
For example, say we have one record of people's names: a table called PEOPLE with two fields: ID and NAME. The other table holds all nicknames of the people in the first table. It has three fields: ID, PeopleID, and Nickname. Values for these table could be:

PEOPLE
ID Name
--------
1 Michael

NICKNAMES
ID PeopleID Nickname
------------------------
1 1 Micky
2 1 Mick

Now let's say we want to find all people who DON'T have any nick name.
Here is how we do that:

SELECT PEOPLE.ID, PEOPLE.Name
FROM PPEOPLE LEFT OUTER JOIN NICKNAMES
ON PEOPLE.ID = NICKNAMES.PeopleID
WHERE NICKNAME.PeopleID IS NULL

First we are using outer join to make sure we get these records, even though the matching records in the other table to not exist.

Then we use the IS NULL condition. Note that '= NULL' will not work (I know, I tried it first...)

Now we have all the people with not even one nickname.

Where to craete dymanically created controls in ASP.NET web site?

I have an AST.NET web site with controls I create dynamically according to data I read from the database.
At first I created the controls in the Page_Load event handler. I had a strange problem: sometimes the control's event handler was not called. This happened with check box controls, and mysteriously only the uncheck event of the first checkbox control was not handled.
I found this article about creating dynamic controls:
http://aspnet.4guysfromrolla.com/articles/092904-1.aspx
And moved the call to the function that creates these controls to the Page_Init event handler of my page. Now all events are handled correctly.