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.