Software Development Notes

FileUpload control does not work in Update Panel and workaround

2009-02-11T16:37:00.000-08:00

All file upload controls (includes asp, telerik, componentart, component factory and others) does not working in any AJAX update panels. This means if your upload control located in an update panel, control does not post the file. If you look to the posted file property of the control, you will see it null.

This is a limitation comes from the XmlHttpRequest component, used in all AJAX frameworks for asynchronous calls to the application. In order to upload a file you should perform a full page postback.

<asp:updatepanel id="UpdatePanel1" runat="server" updatemode="conditional"> <triggers> <asp:postbacktrigger controlid="Button1"> </triggers> <contenttemplate> <ews:datepicker id="DatePicker1" runat="server" usingupdatepanel="True" onselectionchanged="DatePicker1_SelectionChanged"> <asp:label id="Label1" runat="server"></asp:Label> <asp:fileupload id="FileUpload1" runat="server"> <asp:button id="Button1" runat="server" text="Upload" onclick="Button1_Click"></contenttemplate> </asp:UpdatePanel>

The key is at the Triggers. Please note this will post a full postback even it's inside a Update Panel.

Solutions are actually available for gmail like async file upload using iframe:
http://vinayakshrestha.wordpress.com/2007/03/13/uploading-files-using-aspnet-ajax-extensions/
http://msmvps.com/blogs/luisabreu/archive/2006/12/14/uploading-files-without-a-full-postback.aspx

OnChange vs. OnExitControl event in SalesLogix

2009-02-02T12:14:00.000-08:00

Most of the user input controls have both OnChange and OnExitControl event. If no special reason, OnExitControl event should be handled instead of OnChange.

OnChange event will be fired every single time the user enters anything, but OnExitControl is fired only when user leaves that control, which means user finished data input. It makes more sense to handle the change event after the user is done, instead of in the middle. Especially in the case of date time entry, if users enter date manually instead of picking from the calendar control, the OnChange event will give you the every first number user inputs, which will break your date function.

Show DateTime value as boolean in SalesLogix DateGrid

2009-01-30T11:53:00.001-08:00

One time I received a request to add a new column to a dategrid. The client wanted to show a datetime field in the grid to indicate if a thing has been completed or not. However there are already too many dates in the current grid and the client only cares about if the field has a value or not, instead of the real value itself. I added the field but set the date type to be boolean, then in "Format" field put the desired wording when the value is not NULL. It will show blank when the datetime value is NULL. It perfectly solved the problem.

Another reason not to use Date type in SalesLogix

2009-01-30T11:46:00.001-08:00

In my previous post I logged some of my finding about Date vs. DateTime type. Here is another reason that Date type may cause problem.

In TABLE1 we have a StartDate which is a Date type. Through SLOX UI everything works fine - 00:00:00 is used as the time value when saving and SLOX knows not to do any conversion.

However there is a database view retrieving same field. It seems that SLOX doesn't know the exact type of the field in the view so it does the UTC-to-local conversion. The result is that we get back a value which is 7/8 hours earlier.

I don't know why SLOX provides different Date and DateTime type in table design. In SQL 2005 they are all DateTime. Maybe it saves spaces in other databases?

SalesLogix Picklist management and tips

2009-01-21T14:41:00.000-08:00

All the picklists and the picklist items are stored in sysdba.Picklist table. To get a list of all picklists:

select * from sysdba.picklist where picklistid='PICKLISTLIST'

The ShortText field seems to store settings code for each picklist. ItemID is the identifier for picklist. To get all items of a particular picklist:

select * from sysdba.picklist where picklistid='k6UJ9A00032L'

Worth noting is the 'USERID' field. If the picklist's setting is to allow user edit (Users cannot edit items is unchecked), the USERID field will have the user ID of the user who inserts the item, and this item will be visible for that particular user only. Only USERID='ADMIN' are shared by all users. Additionally, non-admin user can only edit/delete their own items.

There was a request that a business user wanted to be able to modify a picklist herself without asking ADMIN to do it and her change should be made available to all users. What I did is to insert a INSERT Trigger on Picklist table:

Declare @userID char(12), @itemID char(12)
SELECT @userID = USERID, @itemID=itemID FROM INSERTED

IF @userID = 'U6UJ9A00000Q'
BEGIN
UPDATE sysdba.PickList SET USERID='ADMIN' WHERE ITEMID=@itemID
END

The outcome is that any picklist item this user ('U6UJ9A00000Q') adds will be available to all users (after a refresh of course). However, since the USERID is changed to ADMIN, this user can't change/delete afterward.

The forms to manage the picklist are not listed in Plugin window. I assume that is the part that Sage doesn't allow you to customize. To give total control of picklists to a non-admin user, customized forms are needed. Basically you have to duplicate what SalesLogix already has, then add your function on top of that.

Inserting background picture to Crystal Report

2009-01-14T10:57:00.001-08:00

I don't know if this is only my dev machine - the background picture disappears after I reopen the report.

There are a few jpg files I want to use as the background of my report. I used Insert->Picture, then select the file. Everything looks good (move to back). However after I close and re-open the report, the picture is blank. I can tell the object is still there (by the blue
border when move mouse over) however it's just blank. Double clicking won't activate the edit mode.

I found that, in order for this picture to retain, I have to insert the picture to a Word file first then copy it in memory from Word. Copying from MSPaint doesn't work. Has to be Word. After inserting I had to adjust the size a little bit and the report file size is also much smaller than the version which I directly inserted picture file to.

Strategies to improve ASP.NET web app performance

2009-01-12T10:39:00.001-08:00

There are few more things you can do other than adding hardware and caching...

combine small files. A lot of small files is slower to download than a few large files
move static resource files such as images, CSS, and JS files off of the ASP.NET servers, and set cache settings on that server. A server tuned for ASP.NET is not especially well-suited to server these files. From the client side, the browser can open two additional concurrent connections to download the static files. Another benefit is that the browser doesn't need to send the cookies to the static files (ASP.NET cookies will be sent to web apps otherwise).
think twice before breaking services into different server. All those out-of-process calls between the web servers and business object servers create a lot of overhead.
IP affinity might be easier at the beginning (so that session can be managed in-process), but will create grief in the long run/bigger load. Scenarios: IIS process recyles, some megaproxy (ie AOL) will be bound to a single server etc
use compression. Yes it costs processor cycles but you typically have plenty of extra CPU capacity on a dedicated web server. IIS7 is even optimized so that when the processor gets really busy, it will suspend compression. Or check http://www.port80software.com/ for 3rd party solutions
optimizing the HTTP pipeline. There are some HTTP Modules that sit in the ASP.NET request pipeline by default that you may not need. For example, if you don't need session management, and only use FormAuthentication, you can remove Session, WindowsAuthentication, PassportAuthentication and FileAuthorization in httpmodules section.
reduce or even completely disable the ViewState
GET is preferred over POST for AJAX calls. GET carries much smaller footprint than POST and POST costs one extra HTTP call than GET.
Put CSS Files on Top and JS Files at the Bottom. This way, the page would start displaying as soon as the CSS files are downloaded on the client side and won't wait for the JavaScript files which could be loaded in the end once the page is fully loaded and client could interact with it. ScriptManager also has a parameter LoadScriptsBeforeUI which can be used for this purpose
programmatic caching add complexity but has great benefit too
scaling database. Move to cluster, partition the tables, use dedicated reader and writer databases (and of course the sync process too)

A tricky side effect of Firefox's "remember password" feature

2008-12-18T10:37:00.000-08:00

I think this is worth sharing.

I have a page that allows customer to modify their account password. The password field and 'password again' field are pre-filled with user's password (encrypted). (I know this is not the best practice and they should be left blank - this is a purchased software package).

What happens is that if no change made to the password, the client script that verifies password match always report unmatched password. The HTML source code does show both password fields are the same. But somehow this code

document.getElementById("password").value

always gets user's real password, not the encrypted one in the HTML source code. It turns out that it's the Firefox's "Remember Password" feature automatically puts in the remembered password, regardless the specified value in the source.

Lesson learned: use different ID for login and password change screen. However the best practice is to leave it blank and not to update it if password is blank.

Cache with HttpContext.Current.Items

2008-12-12T14:45:00.000-08:00

This is a often overlooked yet very powerful cache option. In my BlogEngine multiple user project, multiple components of the page need to resolve blogName/ID, user roles etc. Since each time same user may hit different blog and this user will have different role for each blog, this is a ever-changing value. This per-request cache is a perfect solution.

if (HttpContext.Current.Items["blogName"] != null)
return HttpContext.Current.Items["blogName"].ToString();
else
// parse the blogName

SLOX RecordChanges not working

2008-12-12T14:36:00.000-08:00

It's confirmed that "RecordChanges" checkbox on a controls property doesn't always work (SLXDev forum). Today I testified it too:

select * from sysdba.history where datediff( hour, startdate, GetUTCDate()) < 48

It only gives me a handful records while there are so many updates. The returned history are all in default SLOX objects. So I guess the custom objects/tables (e.g. Policy etc) have problems. I was thinking to put change logging in database triggers, but a few concerns:

it may not always have valid ModifyUser value
to add a new field to be logged is tedious and error-prone
when we remove a database field, we have to remember to remove it from the trigger
any unhandeled error will prevent saving.

That said, this isn't a very practical approach.

Redirect any pages in a old site to corresponding pages in new site

2008-12-12T11:52:00.000-08:00

This mostly involves moving a site to another hosting company, since we can't simply change the IP address of the new server to be the DNS-registered IP. DNS will be changed to the new IP but it takes hours for it to populate to all ISPs. We can blindly redirect all request to the home page of the new server, but that means no matter what page/querystring user requests, he/she will see the home page. Not very user friendly.

Step 1: register a new sub domain (e.g. new.myblog.com), so that user's ISP will get the brand new IP address.

Step 2: in old server, add a Wildcard application maps to have ASP.NET engine handle all requests (html, gif etc)

Step 3: in global.asax, redirect user in Application_BegineRequest:

string url = HttpContext.Current.Request.Url.AbsolutePath;
string QueryParams = HttpContext.Current.Request.QueryString.ToString();
if ( QueryParams != "")
{ Response.Redirect("http://new.myblog.com" + url + "?"+ QueryParams); }
else
{ Response.Redirect("http://new.myblog.com" + url ); }

DateTimeEdit Readonly and UTC conversion

2008-12-02T14:59:00.000-08:00

If a DateTimeEdit control is set to ReadOnly, user can still modifiy it through the popup date picker and the change WILL be saved. So uncheck "Enabled" is safer.

=============================================

SLOX DB provider has Date and DateTime 2 different data types - even though in SQL server they are all created as DateTime. However SLOX DB provider treats them differently. If it's a DateTime type, it converts it to UTC time and convert back to local time when getting it. However if it's a Date type, when saving the data, SLOX DB provider will always use the local date value and either 1) use current local time as time 2) use 00:00:00. (Among other scenarios, if the editing is done directly in a grid view control, 00:00:00 will be used) When retrieving the data, it doesn't do any conversion - except the Crystal Report, in which case it always try to convert to local time. This is annoying.

Some of the fields should be date only but were created as DateTime type. It created problem when I try to copy that field to another table which I created the field as Date type. The copying was done in SQL so SLOX doesn't get a chance to do the appropriate conversion. The outcome is some dates are displayed one day ealier in UI. I had to do the UTC to local conversion in SQL query to fix it.

However since it always converts to local time regardless of date type in Crystal Report, I would recommend to always use DateTime type.

===========================================

Internally, SLX stores it's schema information in SECTableDefs table. This command will list all fields that has Date type:

select * from sysdba.sectabledefs where datetimetype='D'

'U' in above field indicates a DateTime type.

The Architech interface does not allow you to change the data type of an existing field. However here is a workaround in database level:

UPDATE sysdba.sectabledefs SET datetimetype='U' WHERE TableName='[my-table]' AND FieldName='[my-field]' AND datetimetype='D' -- this changes the date type
UPDATE [my-table] SET [my-field] = DateAdd(hour, 8, [my-field]) WHERE DatePart(hour, [my-field]) < 8 -- this fixes the existing data so that it works for conversion.

GetSubDomain

2008-11-24T15:07:00.001-08:00

private static string GetSubDomain(Uri url)
{
if (url.HostNameType == UriHostNameType.Dns)
{
string host = url.Host;
if (host.Split('.').Length > 2)
{
int lastIndex = host.LastIndexOf(".");
int index = host.LastIndexOf(".", lastIndex - 1);
return host.Substring(0, index);
}
}

return null;
}

From: http://www.webpronews.com/expertarticles/2006/11/30/retrieve-subdomain-from-a-url-in-c

script to shink log

2008-11-18T11:37:00.001-08:00

First switch to that database,then run this:

==================================

DECLARE @DB varchar(100)
SET @DB = DB_NAME()
BACKUP LOG @DB WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (@DB, TRUNCATEONLY )

==================================
It worked very well for me in SQL 2005.

This works too but not very convenient:

alter database <mydb> set recovery simple
go

checkpoint
go

alter database <mydb> set recovery full
go

backup database pubs to disk = 'c:\mydb.bak' with init
go

dbcc shrinkfile (N'mydb_log' , 1)
g

Fix user mapping after restoring database to another server instance

2008-10-29T10:35:00.001-07:00

Often when you restore a backup from SQL server you run into funky
problems with users. Suppose you have login calvin and you restore a
database from another server that already has user called calvin. When
you try to map the server calvin to the database calvin, you might
get the error:

Error 15023: User or role 'calvin' already exists in the current database.

To fix this:

sp_change_users_login 'update_one', 'calvin', 'calvin', 'password'
-- this command will link the server user to the database level user.

xcopy

2008-10-23T21:59:00.001-07:00

echo ---------------- >> c:\backup_log.log
echo \calvin\*.* >> c:\backup_log.log
xcopy d:\calvin\*.* "\\192.168.1.99\F$\calvin-backup\calvin\*.*" /H /D
/E /C /R /Y>> c:\backup_log.log
time /t >> "c:\backup_log.log"

truncate SQL 2005 Log file

2008-09-26T17:01:00.001-07:00

Unlike SQL 2000, you can't conveniently just truncate the transaction
log. But you can do this:

1. Highlight the database-> Tasks->Detach..-> Click OK
2. Go to log file folder -> rename the xxx_log.ldf to something else
3. Highlight Databases->Attach…-> Click Add -> add the database MDF
file, highlight the log file in the lower part of the window and click
the 'Remove' button.
4. After this is done, you can verify the contents of the attached
database and then delete the log file.

AJAX Calendar Extender only shows partial weekdays

2008-08-18T10:55:00.001-07:00

The Calendar extender in my page only shows 5 days in a week, instead
of a full 7 days. There is no parameters controlling this behavior.
After some investigation I found that's because I have defined padding
in my stylesheet for TD:

TD {padding-left:5px;padding-right:5px;}

Generally it's not a good idea to change the general TD style,
especially third-party control will be used. I put the padding in my
own table class then problem solved!

Call server postback from ModalPopup extender

2008-08-06T12:28:00.000-07:00

The Atlas AJAX Toolkit has a very convenient ModalPopup extender. However by default it doesn't do post back on button clicks, even the event is wired with the button:

< runat="server" id="btnAddExistingPart" text="Add" cssclass="SplashButton" onclick="btnAddExistingPart_Click">

Upon click it only dismiss the modal popup.

You have to explicitly call the post back client method to initiate the post back:

protected void Page_Load(object sender, EventArgs e)
{
...
btnAddExistingPart.OnClientClick = "__doPostBack('" + btnAddExistingPart.UniqueID + "','')";
...
}

System admin script

2008-08-04T13:26:00.001-07:00

To lock the workstation (tested on Win2K/XP): rundll32 user32.dll,LockWorkStation

OleDbCommand doesn't support named parameters

2008-07-17T10:57:00.001-07:00

When calling a parameterized stored procedure, if you pass parameters to a SqlCommand object

(i.e.
cmd.Parameters.AddWithValue("@productID", strProductID);
cmd.Parameters.AddWithValue("@productName", strName);
...
)

SqlCommand will correctly map the value with the parameter regardless of the order these AddWithValue methods are called. However, OleDbCommand doesn't support named parameters (MS confirmed it: http://support.microsoft.com/kb/316744). You have to make sure the order you call the AddWithValue method matches the order of your SP parameters. Obviously the SqlCommand is much more convinient because you can simply skip some parameters that have default values defined in SP. In my database utility class for SqlClient, I can pack the parameters in a HashTable and pass them in:

public DataSet ExecuteSPQuery(string spName, Hashtable param)
{
SqlConnection myConnection = null;
DataSet dataSet = new DataSet();
try
{
myConnection = ConnectToDB();
SqlCommand cmd = new SqlCommand();
cmd.Connection = myConnection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = spName;

// a different way of looping a hashtable
if (param != null)
{
foreach (DictionaryEntry de in param)
{
cmd.Parameters.AddWithValue("@" + de.Key.ToString(), de.Value.ToString());
}
}

SqlDataAdapter myAdapter = new SqlDataAdapter();
myAdapter.SelectCommand = cmd;
myAdapter.Fill(dataSet);

}
finally
{
if (myConnection != null)
myConnection.Close();
}

if (dataSet != null && dataSet.Tables.Count == 0)
return null;
else
return dataSet;
}

However for OldDb client, I have to use a queue to do that, and anytime there is a change to the SP, I have to change everywhere that queue is populated, or to define a method for each SP I call. How nice...

Sorted view in SQL 2005

2008-07-15T10:51:00.000-07:00

It looks like something new in SQL 2005. If you try to add a "order by" in the view, the management studio will automatically add a "TOP (100) PERCENT" after the SELECT clause. However this only works in "preview" mode. If you query "select * from [view_name]", you still get the records unsorted.

To get the sorted records from a view, a workaround is to use
SELECT top 1000000000 * FROM

This is not ideal but it works if you know your data count range. Microsoft has a hotfix for this. I can't believe MS let SQL 2005 out of the door with such a bug.