Archive

Archive for the ‘Problems’ Category

ASP.Net dynamic control/postback/event-handler madness – Part 2

May 13, 2013 Leave a comment

As a continuation of the HOW I was able to deal with dynamically creating grids based on a query, I had a problem. The problem was that that the ItemCommand was NEVER firing on postback. If you already know where I am going with this, then I’m sorry, but this was a crazy problem that was having a tough time getting to the bottom of. None of my debug code would ever hit or do anything related to that generated command button click, even though the post back happened.

When testing, I also noticed that all of my grids were disappearing on the post back, which made sense, because I had done this is usual:

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindData();
}
}

But, it appears that when the Page_Load fires on the postback and it does NOT create the control that caused the postback in the first place (my grid with a command button), it never fires the event handler! I was able to get it all to work by removing the IsPostBack check, but it seems silly to think that I am recreating the control from scratch in order to fire a button click. Almost like the button that ASP.Net is saying was clicked is gone forever and is replaced by another one which IT thinks was clicked. Kind of bizarre!!

Categories: .Net, Musings, Problems

ASP.Net dynamic control/postback/event-handler madness – Part 1

May 13, 2013 Leave a comment

Well, I recently had a difficult problem that I solved in a way that I don’t really like, but it works all the same. In the process I discovered something bizarre about ASP.Net. Here was the problem: I had a dataset coming out of a SQL stored procedure (that I had no control over or information about, except the output). The dataset needed to be grouped (1 – by period) and subgrouped (2 – by description) based on the properties, then displayed in a table for editing (3), if conditions permitted (4).

I solved these 4 problems with some interesting logic, first based on a container object that I populated out of the database. Then I did the following to solve problem 1:

HashSet periods = new HashSet();
List report = Helper.GetReport();
foreach (objContainer container in report)
{
periods.Add(container.Period);
}

Dictionary<string, List> periodDictionary = new Dictionary<string, List>();
//need to get periods and descriptions within them.
foreach (string period in periods)
{
periodDictionary.Add(period, report.FindAll(p => p.Period == period));
}

This gave me the values broken down into a dictionary of the containers grouped by their period property, but I had to do this again in order to group them again, and then I had to break it down again in order to output the information to the screen. Which, presented a problem, incidentally. I needed a way to display this information in an aspx. So, I just created a div tag with a server side ID value (grids) and then added controls to it, which you’ll see here:

foreach (KeyValuePair<string, List> kvp in periodDictionary)
{
//Okay, broken down by periods now.
//Now we need to break down each value list by description in the same way.

//print header
Label title = new Label();
title.Text = kvp.Key;
title.CssClass = “PeriodHeader”;

grids.Controls.Add(title);

HashSet descriptions = new HashSet();
foreach (objContainer container in kvp.Value)
{
descriptions.Add(container.Desc);
}

Dictionary<string, List> descDictionary = new Dictionary<string, List>();
foreach (string description in descriptions)
{
descDictionary.Add(description, kvp.Value.FindAll(p => p.Desc == description));
}

foreach (KeyValuePair<string, List> kvp2 in descDictionary)
{
Label subtitle = new Label();
subtitle.Text = kvp2.Key;
subtitle.CssClass = “DescHeader”;

grids.Controls.Add(subtitle);
DataGrid grid1 = BuildDataGrid(kvp2.Value);
grids.Controls.Add(grid1);
}
}

I’m sure anyone could pick out issues here, primarly the use of a DataGrid instead of a GridView. I like the DataGrid, okay?!? Get off my case!! Anyway, this worked and allowed me to just generate the information and group it with classes as needed in my BuildDataGrid() method. The specifics aren’t important, but here are a few examples of how I created the grid:

dataSource = dataSource.OrderByDescending(o => o.TransactionDate).ToList();
DataGrid grid = new DataGrid();
grid.DataSource = dataSource;
grid.ItemDataBound += new DataGridItemEventHandler(this.grid_ItemDataBound);
grid.ItemCommand += new DataGridCommandEventHandler(this.Grid_ItemCommand);

grid.AutoGenerateColumns = false;
grid.BorderStyle = BorderStyle.None;

BoundColumn column1 = new BoundColumn();
column1.DataField = “TransactionDate”;
column1.DataFormatString = “{0:MMM dd,yyyy}”;

ButtonColumn column4 = new ButtonColumn();
column4.ItemStyle.CssClass = “editButton”;
column4.DataTextField=”ShowEdit”;
column4.CommandName = “Edit”;
column4.ButtonType = ButtonColumnType.PushButton;

grid.Columns.Add(column1);
grid.Columns.Add(column4);
grid.DataBind();

return grid;

Okay, this long post is getting very long, but the point is that I was able to dynamically generate the columns and buttons and data all on the PageLoad() call and entirely server side. The bolded lines show how I was able to add the event handler code to the grid so that I could get in the way of the binding and the itemCommand events to adjust things as needed (styling, and dynamic commands like delete or edit based on the CommandName).

Categories: .Net, Musings, Problems

Random SQL problem – null safe comparisons in MSSQL

January 30, 2013 Leave a comment

Having worked with MySQL, Oracle and MS SQL Server (as well as a few NoSQL database types), I just ran across a random bug that I’ve not seen before and I thought I’d share it.

Obviously, NULL in SQLServer is a placeholder for missing data, and it has its own comparisons IS NULL and IS NOT NULL.  But, the problems with this entity (or lack thereof) is that within a script or stored procedure, you cannot use =,<,!=,or <>.  The problem happened when an old stored procedure was doing an update statement based on a comparison where one side had a null value that was not checked.  When a customer complained about some records not being updated, I determined that the comparison was the issue:

 

UPDATE    Accounts
SET        PAID_THRU = a.PAID_THRU
FROM    Accounts join Accounts a on Accounts.Company_ID = a.ID
WHERE    Accounts.PAID_THRU <> a.PAID_THRU  –ERROR HERE IF Accounts.PAID_THRU is NULL

 

The last line was the problem in this statement, as they needed to be checked for null values separately.

WHERE (Accounts.PAID_THRU <> a.PAID_THRU OR(Accounts.PAID_THRU is null AND a.PAID_THRU is not null))

 

This is especially tricky when the values are not always or rarely null, so the update does run and update values, but the NULL records will fall through the cracks until someone notices.

One final note: I believe that in MySQL you can use <=> and it will cover cases like this, but in Oracle and MSSQL, you’ve gotta check it yourself!

Categories: Problems, SQL

Infopath, Sharepoint and retrieving data; a terrible problem solved

December 4, 2012 2 comments

Recently I had worked on a temporary InfoPath application to be run on the internal Sharepoint server for doing performance reviews for staff.  This was a great learning experience that was a lot of trial and error to solve several upgrade requests from a previous version and to pull forward goals that had been previously defined in an earlier version.

So, to be clear, I was working with existing data in a predefined repeating section.  Anyone could have any number of goals and I had to figure out how to get the data out to then import it into a specific performance review tool.  Turns out there were about 750 goals total for all the employees.

First, I had to get the data out.  I found that in InfoPath, during a publish, you can select a repeating section and it will automatically give you all the contents of the fields merged. This is great, but there is no way to split the information out if its just a bunch of text, because of no delimiters put in automatically (boy, that’d be nice…).  So after searching around online, I discovered that for any type of repeating section, I would have to create a new text box to contain the data and to use some magic to concatenate them together with a specific delimiter.  I chose ~ because it was unlikely to be in the data.  So, I create a TextBox (this was later changed to a Rich Textbox, but more on that later) with the default value of:

xdMath:Eval(xdMath:Eval(../my:goal, ‘concat(my:goalDescription, “~”)’), “..”)

This function of evaluating an evaluation will go in and iterate through each goalDescription and concatenate them with the “~”.  It is important to note that in order to get this to work, I had to edit the XPath to make sure that the my:goalDescription did not start with “../”.  This took a ton of trial and error and getting it to work exactly was very frustrating.

This evaluation gave me a chance to see all the goals together, but my problem was that this box would only update when I added a new goal or changed the goals value (note the “Update this value…” checked box?  Without it, it would never update).  So, I had to come up with a reasonable way to update all the forms (200 total) as efficiently as possible.  I solved this by adding a form rule that was applied any time the form was opened.

This opens up a dialog that allowed me to add a Rule, which I did here:

I had to change the XPath to work for the global location, because the goal data was buried not at the relative “../”, but in sectionFour.  When I changed the XPath from that, it was successfully updating my AllGoals variable with my concatenated value.  Here is the global version:

xdMath:Eval(xdMath:Eval(my:sectionFour/my:goal, ‘concat(my:goalDescription, “~”)’), “..”)

 

It was a quick task to them open each of the forms from the Sharepoint library manually to have this run to populate the AllGoals value for each employee.  But yet there were issues.  I published the AllGoals variable for Sharepoint to show me (on the last screen of the publish wizard, adding the variable to access).  I saw the entire value in my TextBox, but when I viewed it in the sharepoint list view, it was truncating at 255 characters.  Apparently all textboxes default to “Single Line of Text” in the column settings.  I was able to get around this by removing the published variable, change it to RichTextBox and then publish it again.  That caused it to be “Multiple Lines of Text” which gave me all my data.

 

Okay, home stretch!

I could then export the view to Excel which gave me all my goals and employee names like this:

Employee1                   Goal1~Goal2~Goal3~

But I wanted it to look like this:

Employee1                   Goal1

Employee1                   Goal2

Employee1                   Goal3

So, I turned to macros in Excel.  I should say that I’m not a macro guy, having done VBA a few times before, I really don’t like it (explanation).  After frustrating hours of messing with it, I posted the question on MrExcel.com and got an answer in two hours 😉  Here it is, if you care to see yucky VBA that worked perfectly: MrExcel Forum Thread

 

I was able to get preconfigured data out of Sharepoint, format it correctly and then send it off to be imported to the new system.  Problem solved, but please don’t make me do it again!

Categories: Problems, Sharepoint