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 ;

Dec 01

changing the display order of columns in a datagridview on windows forms

Recently I needed to specify the order that columns were supposed to show up in a datagridview. Normally, one could do this by just changing their SQL query to return the fields in the order that the user wanted to see them. But in my case, this wouldn’t work. You see, I was adding columns to the the gridview because I wanted calendar columns instead of plain text columns containing a date and I also wanted dropdown lists for some of the columns. So even though I could write my query with the columns in the right order I changed the order when I added the new columns (because I had to remove the old column that was formatted in the default way and add the new properly formatted column).

So what to do to get the columns to show up in the order I wanted? Well, just tell it what order you want them to show up in… duh.

Like so:

With dgvRBDDataStaged
 .AutoGenerateColumns =
False
 .Columns(“RBD_Key”).DisplayIndex = 0
 .Columns(“RBD_Carrier”).DisplayIndex = 1
 .Columns(
“Service”).DisplayIndex = 2
 .Columns(
“CountryCode”).DisplayIndex = 3
 etc…
 ‘Just for kicks make one invisible
 .Columns(“RBD_Record_Status”).Visible = False
End With

You will see that there is a secret to it. I never even knew that the AutoGenerateColumns property existed in the windows forms world. [Note: it has a default value of true]. I use it all the time in asp.net, but never have I used it on the windows forms side… until now. Without setting ‘AutoGenerateColumns = false’ first setting those displayIndex values just  doesn’t seem to work. Without setting that property first it seems to “sort of” works in that some of the columns are reordered to what you intended but some are not (which is worse than just not working at all) and will just confuse you to no end. It might work fine without setting that property, but if you did any manual column adding / removing forget about it… you have no hope without setting that property as specified. Just use it as I described and you should get the results you are looking for.

As for my sources, I did many google searches and found questions regarding this topic all over the place, but the most useful thread was this one.

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.

Nov 08

PHP Include function

Utilizing this function enables one to store commonly used html (text) snippets in a seperate file. When the page is generated by the server, this line will be replaced by the contents of “example.php”

<?php include (TEMPLATEPATH . ‘/example.php’); ?>

Nov 07

Learn CSS

I do not teach CSS. I link to CSS tutorials. Hopefully you can learn something there.

Okay, fine. I’ll teach one thing:
In CSS, speficially in style.css, the pound sign (#) is how you address a DIV with an id. The period is how you address a DIV with a class. For a class example, if your markup is

then use .wrapper instead of #wrapper to address the wrapper DIV.
Nov 07

Validating CSS and XHTML

Sometimes when working with CSS and / or XHTML (like when creating a new wordpress theme) we will want to validate any changes we have made. I have provided a pair of tools that allows for just that. Just copy the text from the file you want to validate into these tools and *poof* it will tell you if there are any errors.

A markup validator service – http://validator.w3.org/
Note: You can provide the url, upload the file, or just copy and paste the text of the file.

A CSS validator service – http://jigsaw.w3.org/css-validator/
Note: Same as above

Nov 07

Show Rownumber In ASP.net Datagrid

It is often useful to show a rownumber in a datagrid. For example if you have a list of things that are displayed in “Rank order”. How can we do this? It’s pretty easy actually. Just add the below column definition to your datagrid in order to show the rownumber.

<asp:templatecolumn headertext=”Row Number”>
 <itemtemplate>
  <span><%# Container.ItemIndex+1 %></span>
 </itemtemplate>
</asp:templatecolumn>  
Nov 03

wordpress theme tutorial

I had originally planned to do the PoolOfThought site in a completely different mold. However, after careful consideration I decided I could probably get just as much functionality (if not more) by using WordPress and just “themeing” it to look the way I want.  Once that decision was made all I needed was a tutorial.

I found several decent tutorials, but my favorite was this one. Maybe someone else will find it useful as well.

http://www.wpdesigner.com/2007/02/19/so-you-want-to-create-wordpress-themes-huh/