Saturday 26 June 2010

ASP.NET: Inserting NULL Values Into SQL Server Database

I literally almost destroyed a laptop today when trying to get around the fact that empty dates in my application were being stored in SQL Server as the completely useless '1900-01-01 00:00:00.000'.

Note that SQL Server is not the issue here, it WILL insert a NULL value if it's told to. If you're seeing this 1900 date then it's coming from your application.

I'm using an XSD within my Visual Studio project, and therefore it auto-generates methods for each of the methods I define on my TableAdapters. I needed to get null dates into these methods and then into the database without the application passing the 1900 date to SQL Server.

I tried loads of different methods of checking the values, by trying to pass Nothing, SqlDateTime.Null or System.DBNull.Value where the date was empty, but to no avail, I either got cast errors or the database did the insert but continued to think we were at the dawn of the 20th century.

Finally, I figured it out, and really there are two ways of achieving this. One is to use a Typed DataSet, which allows you to use the autogenerated 'SetMyColumnNameNull()' method.

But I'm not using a typed DataSet for my database insert methods, rather I call the stored procedure methods in my BLL and pass in the values rather than a row object.

So anyway, the answer was this:

When passing your date to your method which calls your stored procedure, pass your dates as strings. Then in your code you can check them like this:



(System.ComponentModel.DataObjectMethodType.Insert, True)> _
Public Function InsertOrder(ByVal date_in_question as String)

Dim dateInQuestion As Global.System.Nullable(Of Date)

' Bust any empty dates
If date_in_question <> "" Then
dateInQuestion = CDate(date_in_question)
End If

' Do the Insert
Dim orderId As Integer = Adapter.db_Insert_Update_Order(dateInQuestion)

Return orderId

Now when the date is passed to the stored procedure's method, if no date is set then an empty date is passed and the database inserts a NULL value.

You might want a more robust way of parsing your dates etc etc, but this worked for me.

Hope I've provided enough detail.

Monday 21 June 2010

ASP.NET FileUpload Control Inside an UpdatePanel

I put a FileUpload control inside an UpdatePanel and ran into some problems, as many others did.

Since the UpdatePanel does not do a full postback by nature (and therefore only partially refreshes the screen), the FileUpload control does not work straight away.

To get it to work you need to add a PostBackTrigger which points at your Upload button. Here's a Microsoft example on that very topic.

Using a PostBackTrigger will cause a full postback to fire when a file is uploaded and will cause your control to work... in most cases.

However I found an odd issue where the file upload did not work the first time (the FileUpload control's HasFile property was always False) but it would upload successfully the second time.

I had to root around in a few places and eventually found the answer on StackOverflow's forums.

Turns out the answer is to define this in your Page_Load event:


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Page.Form.Enctype = "multipart/form-data"

End Sub

After I added this, the FileUpload control started to work every time.

Monday 14 June 2010

Syntax Highlighter Success

Finally, I managed to get SyntaxHighlighter working.

I had tried a few things previously, but it seems that some of the tutorials I had seen were based on older versions. The joy arrived when I found this post.

Now I can post code snippets in a way which is actually readable!


SELECT
COUNT(dbid) as TotalConnections
FROM
sys.sysprocesses
WHERE
dbid > 0

Hurrah!

I'm still using Stanley Shilov's utility to escape my HTML so that I can post using these simple tags:


<pre class="brush: html">
</pre>

SQL Server 2005: Determining the Total Number of Open/Active Connections

Sometimes applications can become unresponsive or generate SQL Server timeout exceptions (System.Data.SQLClient.SQLException: Timeout Expired) and sometimes you just need to find out how much traffic is hitting your database.

You can find out a few things by using these commands:

Total Number of Connections by Database


SELECT
DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections,
loginame as LoginName
FROM
sys.sysprocesses
WHERE
dbid > 0
GROUP BY
dbid, loginame

Total Connections

SELECT
COUNT(dbid) as TotalConnections
FROM
sys.sysprocesses
WHERE
dbid > 0

Detailed description of all running processes

sp_who2 'Active'

NB: You probably need to be logged in with SA privileges to run these commands.

Wednesday 9 June 2010

ASP.NET: ListView Control and Nested ListViews

I have grown to love (in a purely sensible way) the ASP.NET ListView control. It's very flexible and gives you a lot of control over how you display your data on your site.

I needed to display on a summary screen not only the orders belonging to a specific customer, but the products belonging to each order. So I built a ListView to display the orders, then nested another ListView inside it which displays the products belonging to that order.

After some rooting around on the net, I found this excellent tutorial for nesting ListViews that I'll definitely be revisiting in future.

Monday 7 June 2010

ASP.NET: Select DISTINCT from DataTable

I needed to filter a GridView using a bunch of DropDownLists which were placed in its header. As you filter the GridView, the drop down lists had to update according to the filtered data in the GridView (i.e. they should not display filter options for data that's not visible in the table).

In order for this to work, I had to get a distinct list of values for each column with which to populate the drop down lists. This required getting a distinct list of values from a DataTable, and this behaviour does not come naturally to a DataTable since it's a representation of a SQL Database table rather than an actual database table which you can run SQL against.

I found a nice tutorial on this, which I'm linking to here for future reference, in both VB.NET and C#.NET versions.

I had to pass the DataSource my GridView was pointing at into a method which bound the DropDownLists using the function demonstrated in Microsoft's tutorials above.

Implemented, hooked into my GridView and Bob's My Uncle.

Very nice!

Friday 4 June 2010

ASP.NET: Handing an empty Repeater control

The ASP.NET Repeater control is very useful and gives you lots of control over your output when compared to other controls such as the GridView. However, that comes at a cost of having to wire up all of your own events, sorting and paging etc.

Additionally, another drawback is that for a reason I can't explain, there is no EmptyDataTemplate or EmptyDataText property in a repeater which you can use when there are no items to display. Rumour abounded that ASP.NET 2.0 would include such a template, but it was not included.

Several people have tried different methods of handling this situation, but I found one which I think works nicely. I have a user control with a repeater in it, and on the Page_PreRender event I check that the Repeater's DataSource has rows in it. If not, then a label is made visible that tells the user no rows are present. If it does have data, then the Repeater is displayed.

Code

Label:


<asp:Label ID="LabelNoProducts" runat="server" Text="You haven't added any products yet" visible="false" ></asp:Label>

Event: (User Control PreRender)

Private Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRender
Dim ds As DataTable = RepeaterProductsList.DataSource

If ds.Rows.Count = 0 Then
RepeaterProductsList.Visible = False
LabelNoProducts.Visible = True
Else
RepeaterProductsList.Visible = True
LabelNoProducts.Visible = False
End If
End Sub

Et voila. Each time your user control is loaded, it checks if there are rows in the repeater, and lets the user know if not.

ASP.NET: Read Contents of Text File

In my Web Application Project, I wanted to read some CSS styles in from a file to dynamically style a system generated email.

It was pretty easy, but I thought I should record my code for future:


Dim emailCss As String = vbNullString

Dim fileName As String = HttpContext.Current.Server.MapPath("~/utilities/Email.css")

If File.Exists(fileName) Then
Dim ioFile As New StreamReader(fileName)

emailCss = ioFile.ReadToEnd()

ioFile.Close()

End If

Thursday 3 June 2010

ASP.NET: Render Control into HTML String

Occasionally there is a need to get string representation of ASP.NET control in other words - render it into string instead of let it be rendered on the page. For example, you may want to grab the HTML rendering of an ASP.NET server control and attach it to an email.

The following method renders control into HTML string.

Namespaces used:


using System.Text;
using System.IO;
using System.Web.UI;

Below is RenderControl method implementation, which receives any control and returns its HTML string representation.

C#

public string RenderControl(Control ctrl)
{
StringBuilder sb = new StringBuilder();
StringWriter tw = new StringWriter(sb);
HtmlTextWriter hw = new HtmlTextWriter(tw);

ctrl.RenderControl(hw);
return sb.ToString();
}

VB.NET

Public Function RenderControl(ByVal ctrl As Control) As String
Dim sb As New StringBuilder()
Dim sw As New StringWriter(sb)
Dim htw As New HtmlTextWriter(sw)

ctrl.RenderControl(htw)

Return sb.ToString()

End Function

There are a few drawbacks to this approach, in that any LinkButtons or other controls which depend on being inside an HTML form will fail unless you include the form in the email body too.

See these two (rather old) posts on 4GuysFromRolla.com by Scott Mitchell:

Part 1
http://www.4guysfromrolla.com/articles/091102-1.aspx

Part 2:
http://www.4guysfromrolla.com/articles/102203-1.aspx