Dec 26

Link checker – Bad neighborhood

I often get requests for me to add links to my sites. Usually it is just someone looking for something simple that will deliver them some relevant traffic.
What I have found though is that one should ALWAYS verify that the link destination is okay. It should not be in a bad neighborhood. In addition, it should not link out to bad neighborhoods. These bad neighborhoods will get sites that link to them penalized in the search engines.  That’s right – the sites that you link to can get your site penalized. Not only that, but the sites THEY link to might get your site penalized.
The link below has a bad-neighborhood checker. It will scan a URL and determine if there are questionable links. Then it will scan the linked to pages to see if any of their links are questionable in nature. It’s a great little tool and I highly recommend using it.

Dec 28

A comma seperated list as parameter to mysql stored procedure

In MySQL there appears to not be support for passing a variable to a stored procedure and using that variable in an IN CLAUSE. Here’e the body of an extremely simple stored procedure that I will use to illustrate
the problem.

CREATE DEFINER=`root`@`localhost` PROCEDURE `spMySampleStoredProc`(idlist varchar(1000))

select * from tblSampleTable where theid in (idlist);

END$$
DELIMITER ;

If you run this with ‘123,456’ as the lone parameter then the result will annoyingly be the result as if you had just sent in ‘123’. That is, it only pays attention to the FIRST ITEM in the csl (comma seperated list) when
it is passed as a parameter.

So how can one work around this? Well, what I did was create a temporary table in the stored proc and parsed the parameter string, inserting each item in the string into the temporary table as I went. Then just just used an INNER JOIN (or an IN CLAUSE) against the temporary table. My code to create the temporary table is listed below:

declare seperatorcount int;
 declare idstring varchar(10);
 declare testifdonestring varchar(1000);

 CREATE TEMPORARY TABLE temptbl_IdList (pid int);

 set seperatorcount=1;
 myloop: LOOP
 set aplayeridstring = (
    SELECT trim(SUBSTRING_INDEX(SUBSTRING_INDEX(idlist, ‘,’, seperatorcount), ‘,’, -1))
    );
 
 insert into temptbl_IdList (pid) values (idstring);
  
 set testifdonestring = (
    SELECT trim(SUBSTRING_INDEX(SUBSTRING_INDEX(idlist, ‘,’, seperatorcount), ‘,’, -seperatorcount))
    );
 if idlist = testifdonestring
  then LEAVE myloop;
 end if;
 SET seperatorcount = seperatorcount + 1;
 
END LOOP myloop;

Now how whole proc would look something like this:

CREATE DEFINER=`root`@`localhost` PROCEDURE `spMySampleStoredProc`(idlist varchar(1000))

 declare seperatorcount int;
 declare idstring varchar(10);
 declare testifdonestring varchar(1000);

 CREATE TEMPORARY TABLE temptbl_IdList (pid int);

 set seperatorcount=1;
 myloop: LOOP
 set aplayeridstring = (
    SELECT trim(SUBSTRING_INDEX(SUBSTRING_INDEX(idlist, ‘,’, seperatorcount), ‘,’, -1))
    );
 
 insert into temptbl_IdList (pid) values (idstring);
  
 set testifdonestring = (
    SELECT trim(SUBSTRING_INDEX(SUBSTRING_INDEX(idlist, ‘,’, seperatorcount), ‘,’, -seperatorcount))
    );
 if idlist = testifdonestring
  then LEAVE myloop;
 end if;
 SET seperatorcount = seperatorcount + 1;
 
END LOOP myloop;

select * from tblSampleTable where theid in (select pid from temptbl_IdList);

drop table temptbl_IdList;

END$$
DELIMITER ;

Nov 19

Fix AJAX Error – PageRequestManagerParserErrorException: The message received from the server could not be parsed

===== Update: Clarified need for global.asax file and contents =====

A fantasy sports (fantasy basketball / fantasy football) website I do some work for had some Ajax related issues some time ago. I recently came across the problem somewhere else and figured it might make sense to write it up.

There were error messages that were not consistent. In testing a page there might not be any problems, but then when it goes to a production server the problem shows up. Or maybe it does not have a problem when being accessed from one location, but then another user does have a problem. If you have these sorts of symptoms I’m here to tell you it might not be Ajax’s problem. Instead, we may need to blame the firewall. Typically the ajax error will say something like :

“Sys.WebForms.PageRequestManagerParserErrorException: The message received from the server could not be parsed. Common causes for this error are when the response is modified by calls to Response.Write(), response filters, HttpModules, or server trace is enabled. Details: Error parsing near ‘ … [some more here] …

Some firewalls do not recognize AJAX headers. This causes the firewall to alter the message (by removing the header) that our servers send to the browser viewing the page. When that happens, the user’s browser cannot make sense of the response and gives the error mentioned above. We actually had users who were able to convince their network admin to disable an option on their firewall (temporarily) that is typically called something like “REMOVE UNKNOWN HEADERS”. When the option was disabled, the site functioned normally for them.

Unfortunately, disabling the users firewall is not a viable solution. One recommendation might be to have the admin “tell” the firewall about the header so it will recognize it and quit removing it. Depending on the type of firewall (a product called Watchguard was the offender in our test case) there may be a way to make certain headers (the ajax header) known rather than disabling them all. There is nothing to “fix” for us as it is more a flaw with the firewall than anything else; if you are encountering this problem you will need to work with your network admin on the problem.

All of that being said, we found that it can also often be fixed by code on the website end! That is a much better option, yes? So here’s all you have to do.

<asp:contentplaceholder id=”ContentPlaceHolderCodeForAjaxStrippingFirewalls” runat=”server”>
<script language=’javascript’ type=”text/javascript”>
function beginRequest(sender, args)
{
   var r=args.get_request();
   if (r.get_headers()[“X-MicrosoftAjax”])
   {
      
b=r.get_body();
       
var a=“__MicrosoftAjax=” + encodeURIComponent(r.get_headers()[“X-MicrosoftAjax”]);
       if (b!=null  && b.length>0)
       {
             b+=
“&” ;
       }
       else b= “” ;
       r.set_body(b+a);
     }
}
</script>
</asp:contentplaceholder>

Note: I put this inside a contentplaceholder (I am using masterpages) so I didn’t have to include it manually on every page. If you are not utilizing masterpages then you could just put the script on each page that uses ajax. 

Now, we need something to call this. I put a call to it in my global.asax file within the method Application_BeginRequest:

void Application_BeginRequest(object sender, EventArgs e)
    {
        // first  event in the pipeline.
        // I’m going to use this to try to intercept ajax headers when they get all messed up by firewalls
        // I got the information about how to do this the below url:
        // http://forums.asp.net/p/1144748/1850717.aspx
        HttpRequest request = HttpContext.Current.Request;
        if (request.Headers[“X-MicrosoftAjax”] == null && request.Form[“__MicrosoftAjax”] != null)
        {
         ArrayList list = new ArrayList();
         list.Add(request.Form[“__MicrosoftAjax”]);
         Type t = request.Headers.GetType();
         //lock (request.Headers)
         //{
            t.InvokeMember(“MakeReadWrite”, System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance, null, request.Headers, null);
            t.InvokeMember(“InvalidateCachedArrays”, System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance, null, request.Headers, null);
            t.InvokeMember(“BaseSet”, System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance, null, request.Headers, new object[] { “X-MicrosoftAjax”, list });
            t.InvokeMember(“MakeReadOnly”, System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance, null, request.Headers, null);
         //}
        }
    }

 

Do those two things and and the problem goes bye bye, and the network admins get to keep their silly little rules in place.

Note2: I found the hint about what might be causing the problem here. I’m not entirely sure where I found the fix… it was a long day thank you… but it works.

Nov 11

HOWTO create a Pop-up menu (context menu) for single cell of a datagrid

Recently I needed fo find a way for a user to be presented with a list of possible values, but to also be able to ignore that list and enter their own value into a datagrid cell. I figured I could just use a combo box column (dropdownlist) and it would behave the same way as a combobox does when it is on a form (not within a datagrid). In that situation the user can just enter a value into the text portion of the combo box assuming they did not like any of the options in the box itself. Well, that’s not the way a comboboxcolumn works… you choose from the list it makes available… that’s all you get for free.

Unfortunately, that was not an option. I had to allow the user to enter their own value if they didn’t see the one they wanted in the list. So, what I did was I set the column up as a regular Text based column. Then I implemented a pop-up menu that showed when the user right clicked on a cell in a particular column. Before popping up it determines what the correct menu items for cells in the columns in which it resides  and then makes those options available to the user. It took me a little while to figure out – hopefully this will save someone some time.

My sample code below is in VB. Basically it makes use of the fact that when in a datagrid a right click causes “CellContextMenuStripNeeded” to be fired. So we build up our menu strip there. You could ofcourse also have a static menustrip and set striptoShow directly to it rather than building up stripToShow everytime.

”””””””””””””””’ SET UP SOME PRIVATE VARIABLES WE WILL NEED ””””””””””””””’

 Private theCellImHoveringOver As DataGridViewCellEventArgs
Private stripToShow As ContextMenuStrip

 ”””””””””””””””’ DETERMINE WHICH POPUP TO SHOW (IF ANY) ””””””””””””””’

‘NOTE: dgv is a DataGridView

Private Sub dgv_CellContextMenuStripNeeded(ByVal sender As Object,
ByVal e As DataGridViewCellContextMenuStripNeededEventArgs) _
Handles dgv.CellContextMenuStripNeeded

 Try

      If (theCellImHoveringOver .ColumnIndex = -1) Then
          Return ‘ nothing special to pop up here
     
End If

     If (dgv.Columns(theCellImHoveringOver.ColumnIndex).Name = “Column_Name_Here”) Then
          If (dgv.Rows(theCellImHoveringOver.RowIndex).Cells(“Column_Name_Here”).Selected) Then   �
               ‘ NOTE: I only want it to do this if the cell i right click on is SELECTED
              
If stripToShow Is Nothing Then
                      stripToShow = New ContextMenuStrip()
                      
stripToShow .Items.Add(“Item1”, Nothing, New System.EventHandler(AddressOf Me.stripToShow _Item_Click))
                      stripToShow .Items.Add(“Item2”, Nothing, New System.EventHandler(AddressOf Me.stripToShow _Item_Click))
                      ‘NOTE: I added a handler for the click. In my handler I will set the cell value to whatever the user selects from the menu
               End If
               e.ContextMenuStrip = stripToShow�
          E
nd If
     
End If

Catch ex As Exception
        MessageBox.Show(
“There was an error popping up the menu. Please try again.”)
End Try

 End Sub 

          ”””””””””””””””’ Keep up with which cell I am on ””””””””””””””’

Private Sub dgv_CellMouseEnter(ByVal sender As Object, _
ByVal e As DataGridViewCellEventArgs) _
Handles dgv.CellMouseEnter

     theCellImHoveringOver = e

End Sub

”””””””””””””””””” Handler of Menu Clicks  ””””””””””””””””””’

 

Private Sub stripToShow _Item_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
      Dim str As String = CType(sender, System.Windows.Forms.ToolStripItem).Text
      dgv.Rows(theCellImHoveringOver.RowIndex).Cells(“Column_Name_Here”).Value = str

End Sub

I think that is all there is to it! Let me know if you use it. It’s nice to hear others find this type of stuff useful as well.