System.Data.OleDb.OleDbException: Unspecified error

I ran across this error in an ASP.NET project that was using ADO.NET to access the data in a .csv file (more info here). After much gnashing of teeth, I finally ran across this blog post, that explained the problem and provided a link to this MSDN article.

Basically, you need to make sure that the user has permissions on both the file in which the .csv file resides, and also the file in which the Jet Engine will create temporary files. It is this last piece that I didn’t know, and lead to days of heartache.

So I thought I’d post, in the hopes of minimizing the heartache for others…

Advertisements

Use HttpModule to turn tracing on via the querystring

Sometimes I want to turn tracing on in a production application to troubleshoot issues that I can’t replicate on my development machine.  It’s easy enough to do this by turning on application-level tracing in the web.config file, like so:

<configuration>
 <system.web>
  <trace enabled=”true” pageOutput=”true” />
 </system.web>
</configuration>

The problem with this is that changes to the web.config file cause the app domain to recycle, which can cause users currently in the application to lose data.  So I have to schedule a time when all users have to get out, or I have to work after hours, which is a drag.

It’s easy enough to turn on tracing programmatically – Page.Trace.IsEnabled = true.  Then you could write code in the page’s Load event, turning tracing on if the page detects a particular querystring.  The problem here is that you’d have to add the code to every page that you might someday want to trace.

So I decided to create an HttpModule that would turn on tracing via the querystring.  HttpModules allow you to tap in to the HttpRequest pipeline, so your code will run at a designated point in every ASP.NET request.  You can hook up your module to run at various points in the page life cycle, such as BeginRequest, AuthenticateRequest, Error, etc.  (HttpModules are also excellent for application-level error handling). 

In order to create an HttpModule, you have to create a class that implements the IHttpModule interface.  Do this by creating your class, and then typing “Implements IHttpModule” directly under the “Public Class Whatever” line.  Once you hit enter, the two methods that your class must invoke, Init and Dispose, are automatically created for you.

Here’s what my HttpModule class looks like (be sure to import the System.Web namespace):

Option Strict On

Imports Microsoft.VisualBasic
Imports System.Web

Public Class TraceModule
    Implements IHttpModule

    Dim WithEvents app As HttpApplication = Nothing

    Public Sub Init(ByVal context As HttpApplication) Implements IHttpModule.Init
        app = context
    End Sub

    Public Sub context_BeginRequest(ByVal sender As Object, ByVal e As EventArgs) _
    Handles app.BeginRequest

        Dim c As HttpContext = HttpContext.Current
        Dim strTrace As String = c.Request.QueryString(“trace”)

        If strTrace = “on” Then
            c.Trace.IsEnabled = True
        End If

    End Sub

    Public Sub Dispose() Implements IHttpModule.Dispose
        app = Nothing
    End Sub

End Class

After you’ve created your module, you just need to add a setting in the web.config file telling ASP.NET to use your module, as shown below.  If the class is in an assembly in the bin folder, you just need to provide the class name.  If it’s in the App_Code folder, then you need to add that to the setting.

<configuration>
 <system.web>
  <httpModules>
   <!– Module class in assembly called TKS.dll in bin folder –>
   <add name=”WebTraceModule” type=”TKS.TraceModule”/>
   <!– Module class in App_Code folder –>
   <add name=”WebTraceModule” type=”TraceModule, App_Code” />
  </httpModules>
 </system.web>
</configuration>

Now if you want to see the trace for any page in your application, just add the querystring “?trace=on” at the end of the URL, and the trace will display in the page without making any other changes.  If you want to add some security to this, so that not just anybody can run the trace, you can require additional querystrings, or only turn tracing on for certain users or groups, or just about anything else that you can do in code.

Get Max Number from Datatable using the Compute method

This is a nifty little thing I came across recently.  I wanted to get the maximum value of a number field from a datatable, and learned about the Datatable’s Compute method.  It’s really easy to use.  The method takes two arguments – an expression argument, which is the aggregate expression you want to use (Count, Max, Sum, etc), and a filter expression, which functions like a SQL WHERE clause.  If you don’t want to filter by anything, you can send Nothing for the second argument (Null in C#).

An example:

Dim expression As String
Dim filter as string
Dim maxNum As Integer

‘get maximum job number from table
expression = “Max(JobNumber)”
filter = “JobDate > 12/31/06 AND JobDate < 1/1/08”
maxNum = CInt(tblJobInfo.Compute(expression, filter))

If you don’t want to filter, it would look like this:

expression = “Max(JobNumber)”
maxNum = CInt(tblJobInfo.Compute(expression, Nothing))

Gridview cell text values all Empty Strings

I spent a good part of today butting up against this problem – puzzled and Googled and experimented and Googled some more, but could not for the life of me figure it out – I kept doing exactly what the examples said I should do.  But then I finally found the answer, and so thought I would post it in the hopes of saving someone else the same hassle.

First, what I was trying to do.  I have an updatable gridview of a set size – it always displays 5 rows, and all 4 columns for all 5 rows will need to contain data when the user saves the page.  So, on page load, I want to loop through the gridview, inspect each cell to see whether it contains a value, and set its backcolor to yellow if it doesn’t to remind the user to fill it in.

The articles I read said that there were basically two ways to see if the cell has a value.  For a regular boundfield, you just check the cell’s text property directly, like so (All these examples are in the Gridview RowDataBound event, using that event’s System.Web.UI.WebControls.GridViewRowEventArgs argument.  This event fires on every postback.):

Dim c As Integer
Dim cell As TableCell

If e.Row.RowType = DataControlRowType.DataRow Then
   For c = 0 To e.Row.Cells.Count – 1

      ‘get cell
      cell = e.Row.Cells(c)

      ‘if cell is empty, make cell yellow
      If String.IsNullOrEmpty(cell.Text) Then
          cell.BackColor = Drawing.Color.Yellow
      End If
   Next
End If

And for a named control in a Template field, you use the cell’s FindControl method to get the control, and then check the control’s text property.  So if you had a Template field with a label named lblValue, you’d do this:

Dim cell As TableCell
Dim lbl As Label

If e.Row.RowType = DataControlRowType.DataRow Then

      ‘get cell
      cell = e.Row.Cells(0)

      ‘get control
      lbl = CType(cell.FindControl(“lblValue”), Label)

      ‘if label text is empty, make cell yellow
      If String.IsNullOrEmpty(lbl.Text) Then
          cell.BackColor = Drawing.Color.Yellow
      End If
End If

Turned out my problem was that I was using Template fields, but not using named controls (at least not in the item template) – so I couldn’t use the FindControl method because I didn’t have a control name to feed it, but just checking the cell’s text property returned an empty string.  Because, I discovered, when you use a Template field without a named control, like this:

<asp:TemplateField HeaderText=”Value”>
    <ItemTemplate>
        <%#Me.FormatCell(Eval(“Value”))%>
    </ItemTemplate>
    <EditItemTemplate>
        <asp:TextBox ID=”txtValue” runat=”server” Text='<%# Bind(“Value”) %>’>
        </asp:TextBox>
    </EditItemTemplate>
</asp:TemplateField>

even though you don’t specifically put the Item Template value in a control, ASP.NET does.  You can’t tell this from looking at the source code – the value just appears to be in a regular <td> cell.  But if you look at the cell in the Command Window during Debugging (as I finally thought to do), you’ll see that there is a control in the cell’s control collection – a DataBoundLiteralControl.

So I needed to do a variation of the second get-the-value-from-the-control method.  Plus I had the added difficulty that I wanted the cell background to stay yellow even while the user was editing it.  But of course, when the row was in edit mode, the cell had 3 controls, not one – a LiteralControl, a Textbox, and another LiteralControl.  And, I didn’t want to have to name every control in order to use the cell’s FindControl method – I wanted to look at every cell.

So I worked out a procedure that found the controls I needed based on their position in the cell’s control collection, rather than their name – and adjusted for rows in edit mode.  My final code ended up looking like this:

Dim c As Integer
Dim cell As TableCell
Dim td As DataBoundLiteralControl
Dim txtBox As TextBox
Dim text As String

If e.Row.RowType = DataControlRowType.DataRow Then

   For c = 0 To e.Row.Cells.Count – 1

      ‘get cell
      cell = e.Row.Cells(c)

      ‘if row is in edit mode, get textbox control’s text value
      If e.Row.RowIndex = Me.Gridview1.EditIndex Then 
          txtBox = CType(cell.Controls(1), TextBox)
          text = txtBox.Text.Trim
      Else ‘otherwise, get DataBoundLiteralControl’s text value
          td = CType(cell.Controls(0), DataBoundLiteralControl)
          text = td.Text.Trim
      End If

      ‘if text is blank, set cell backcolor to yellow
      If String.IsNullOrEmpty(text) Then
          cell.BackColor = Drawing.Color.Yellow
      End If
   Next

End If

Use Generic Methods to create a Session Wrapper class

Getting values out of session in ASP.NET can be a bit of a pain for two reasons:

  1. You have to remember what you named the value when you put it in
  2. You have to type cast the value, as session stores it in an Object

One nice way to handle this is to build a wrapper class to handle getting values into and out of session. And an even nicer way is to use generic methods in your wrapper class.

Here’s how I implement this in my projects. First, I have a class called SessionBase that contains two shared Generic methods, LoadValue and RetrieveValue:

Public Class SessionBase

Public Shared Function RetrieveValue(Of T)(ByVal keyName As String) As T

If HttpContext.Current.Session(keyName) IsNot Nothing Then
Return DirectCast(HttpContext.Current.Session(keyName), T)
End If

End Function

Public Shared Sub LoadValue(Of T)(ByVal keyName As String, ByVal value As T)

HttpContext.Current.Session(keyName) = value

End Sub

End Class

The beauty of these methods is that they can handle any data type – I don’t have to have separate methods for integers, collections, custom types, etc. It’s all handled here in this generic and reusable class (I usually keep it in a .dll along with other base classes, and add a reference to it in my project). I use DirectCast rather than CType in my retrieve method because it is supposed to have less of a performance penalty.

Then, in my project, I create a class called sess that inherits the base class (because the base class contains shared methods, it doesn’t really need to be inherited, but I prefer to do it this way so there is less typing – I can type sess.LoadValue instead of TKS.SessionBase.LoadValue). In the project-specific class, I create constants for the session key names, and shared properties to load and retrieve values from session:

Public Class sess
Inherits TKS.Web.SessionBase

Private Const cProjID As String = "ProjID"
Private Const cProjName As String = "ProjName"

Public Shared Property ProjID() As Integer
Get
Return sess.RetrieveValue(Of Integer)(cProjID)
End Get
Set(ByVal value As Integer)
sess.LoadValue(of Integer)(cProjID, value)
End Set
End Property

Public Shared Property ProjName() As String
Get
Return sess.RetrieveValue(Of String)(cProjName)
End Get
Set(ByVal value As String)
sess.LoadValue(Of String)(cProjName, value)
End Set
End Property

End Class

Note how the generic methods are made specific at this point – Integer for ProjID, String for ProjName.

Now in my project, I can use the sess properties to easily access the session object:

Protected Sub btnLoad_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLoad.Click

sess.ProjID = Integer.Parse(Me.txtProjectID.Text)
sess.ProjName = Me.txtProjectName.Text

End Sub

Protected Sub btnRetrieve_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRetrieve.Click

Me.txtProjectID.Text = sess.ProjID.ToString
Me.txtProjectName.Text = sess.ProjName

End Sub

Dynamically change Master Page body tag

Today I spent several hours butting up against this, and finally, after much gnashing of teeth, found this great blog entry explaining what I needed to know.

Basically, the problem is that I needed to call a javascript function onload in the body tag of the page, but of course in an ASP.NET Content page there is no body tag – it only occurs once in the Master Page. But I didn’t want to put my javascript call in the Master page body tag, because then it would be called on every page.

I tried putting it in window.onload in a script tag at the bottom of the page, but this then screwed up some ASP.NET-generated javascript from a validation control, and also some of my other javascript.

The solution put forth in the blog entry is to dynamically put the onload function call in the Master Pages body tag from code in the Content page. Don’t have time to show the code myself (it’s quittin’ time), but please feel free to follow the above link.

By the way, this still didn’t solve my problem in Mozilla, but it works in IE, and as this particular project is for a company intranet that only uses IE, I’m happy (at least for now).

Pass multiple strings to stored procedure IN statement using XML

(Revised 3.18.08)

Let’s say you have a listbox with multiple items selected, like this:listboxAnd let’s say that you want to take the selected items and send them to a stored procedure to run in an IN statement in the WHERE clause, like this: SELECT * FROM tblProjects WHERE ProjectCity IN ('Lewiston', 'Portland', 'Spokane')

Turns out it’s not a simple operation, because you can’t send an array to a stored procedure, and if you send it as a comma-separated string, SQL Server just thinks it’s one big string.If you go the comma-separated string route, you can break down the string and loop through its parts using T-SQL, but it’s clunky. Another way is to send the values as XML and then query the XML in the stored procedure.

First, retrieve the values from the listbox and put them in an xml string:

'initialize a new stringbuilder object
Dim sb As New StringBuilder

'add the root element of the xml string to the stringbuilder
sb.Append("<root>")

'loop through the listbox items
For Each li As ListItem In Me.lstCities.Items

'if the item is selected, add it to an xml element string, and add
'the string to the stringbuilder
If li.Selected Then
sb.Append(String.Format("<item value=""{0}"" />", li.Text))
End If
Next

'add the root element closing tag to the stringbuilder
sb.Append("</root>")

Calling sb.ToString will produce the following value:

<root>
<item value="Lewiston" />
<item value="Portland" />
<item value="Spokane" />
</root>

At this point you’d load the sb.ToString value in a parameter and send it to the stored procedure:

'use stringbuilder value to create a sqlParameter object
Dim parameter As New Data.SqlClient.SqlParameter
With parameter
.ParameterName = "@XMLDoc"
.Value = sb.ToString
.DbType = Data.DbType.String
End With

'access database .....

OK, now we move to the SQL Server side, to figure out exactly what to do with this xml string once it arrives. In SQL Server 2000, you’ll need to use the OPENXML function to use XML as a datasource. In SQL Server 2005, because of its support for the xml data type, you can query the xml directly.

Let’s do a SQL Server 2000 example first. To use OPENXML, you’ve got to prepare the XML first by calling sp_xml_preparedocument.This system stored procedure prepares an internal representation of the xml document, and returns a handle that can be used to access the internal representation of the document.

Once you’ve got the document handle (which is an integer), you can use OPENXML to query the xml data:

declare @XMLDoc varchar(1000)
set @XMLDoc = '
<root>
<item value="Lewiston" />
<item value="Portland" />
<item value="Spokane" />
</root>
'

/* set variable for document handle*/
DECLARE @XMLDocHandle INT

/* call sp_xml_preparedocument. Send the document handle variable as an output parameter, and the xml document you want prepared*/
EXEC sp_xml_preparedocument @XMLDocHandle OUTPUT, @XMLDoc

/* use OPENXML to query the in-memory xml doc representation */
SELECT value
FROM OPENXML(@XMLDocHandle,'/root/item',2)
WITH (value VARCHAR(50) '@value')

/* call sp_xml_removedocument to remove the in-memory doc representation when you are done with it*/
EXEC sp_xml_removedocument @XMLDocHandle

OPENXML accepts three arguments – the handle of the in-memory xml doc to be queried, the XPath Query used to identify the nodes to be processed, and a flag that indicated the mappings between the xml doc and the in-memory rowset. The WITH clause identifies the XML data to be retrieved, and the SQL data type it should be returned as.

And here’s what you’d do in SQL Server 2005:

/*Note the xml data type, rather than varchar(n)*/
declare @XMLDoc xml
set @XMLDoc = '
<root>
<item value="Lewiston" />
<item value="Portland" />
<item value="Spokane" />
</root>'
SELECT x.Item.value('@value', 'varchar(50)')
FROM @XMLDoc.nodes('/root/item') AS x(Item)

The xml data type has five methods, two of which, nodes and value, are used here: nodes to propogate the ‘item’ elements in to a rowset, and value to retrieve a specific value from the rowset. The nodes method accepts one parameter, an XPath Query to retrieve the desired xml data. The value method accepts two parameters, an XPath Query to retrieve the desired xml data, and a SQL type that states what type the return value should be. Note that at the end of the FROM statement, there is an alias for both tablename (x) and column name (item), and that these aliases are used in the SELECT statement.

Both of the above T-SQL examples produce the following results:

value
----------
Lewiston
Portland
Spokane

(Note: if you copy and paste these examples in to SQL Server to run, you may need to re-type all the apostrophe’s and quotation marks in the T-SQL statement first or it will generate errors)

OK, that looks like something you could use in an IN statement. Now, we just need to build our stored procedure, querying the xml in a subquery in the IN statement. Here’s how it would look in SQL Server 2000:

CREATE PROCEDURE [dbo].[uspUseXML]

@XMLDoc VARCHAR(2000)

AS

DECLARE @XMLDocHandle INT

EXEC sp_xml_preparedocument @XMLDocHandle OUTPUT, @XMLDoc

SELECT * FROM tblProjects WHERE ProjectCity IN(
SELECT value
FROM OPENXML(@XMLDocHandle,'/root/item',2)
WITH (value VARCHAR(50) '@value'))

EXEC sp_xml_removedocument @XMLDocHandle

And here’s how we’d do it in SQL Server 2005 —
CREATE PROCEDURE [dbo].[uspUseXML]

@XMLDoc xml

AS

SELECT * FROM tblProjects WHERE ProjectCity IN(
SELECT x.Item.value('@value', 'varchar(50)')
FROM @XMLDoc.nodes('/root/item') AS x(Item))

Another way to do this would be to join the projects table and the xml using an INNER JOIN. Here’s the SQL Server 2000 example:

CREATE PROCEDURE uspUseXMLInJoin

@XMLDoc VARCHAR(2000)

AS

DECLARE @XMLDocHandle INT

EXEC sp_xml_preparedocumnet @XMLDocHandle OUTPUT, @XMLDoc

SELECT p.*
FROM tblProjects p
INNER JOIN
OPENXML(@XMLHandle, '/root/item',2)
WITH (city VARCHAR(50) '@value') as x
ON p.ProjectCity = x.city

And in SQL Server 2005:

CREATE PROCEDURE uspUseXMLInJoin

@XMLDoc xml

AS

SELECT p.*
FROM tblProjects p
INNER JOIN @XMLDoc.nodes('/root/item') AS x(City)
ON p.ProjectCity = x.City.value('@value', 'varchar(50)')

It’s beyond what I want to get in to here, but XML has many other nifty uses in SQL Server, especially in being able to update several rows of data without having to make several database calls. The MSDN site has lots of info, and there is also a very good article (free registration required) at SQLServerCentral.com that gives lots of examples and variations on its use. In the course of researching this, I also found an extremely excellent online article describing in great detail other ways of dealing with Arrays and Lists in SQL Server.

Determine Previous WizardStep using Wizard’s GetHistory Method

Recently I was working on a project that contained an ASP.NET Wizard Control. The control was configured to allow non-linear progression through the WizardSteps. I realized that I needed to know which step the user had been on previously, as that would determine which control would have focus in the current step.

So I Googled, and was able to find out that I could use the Wizard Control’s GetHistory method to find the previous step. GetHistory returns a collection of the WizardStepBase objects that have been accessed, arranged in the order that they were accessed – the first object in the collection is the WizardStep that was accessed last, the next is the WizardStep that was accessed second-to-last, etc.

OK, so far, so good. But the MSDN documentation didn’t include an example of how to use the GetHistory method, and neither did the couple other mentions I found in other articles – they all said you could do it, but they didn’t say how.

So I played around, and figured out a way to use the GetHistory method to get what I needed. Anyone who knows of a smoother or more elegant way, please feel free to post it – I just thought there should be at least one example out there:-)

Example:

Dim steps As ArrayList
Dim stp As WizardStep

'get WizardSteps history collection
steps = CType(Me.wzDataEntry.GetHistory, ArrayList)

'if any wizard steps have been previously accessed...
If steps.Count > 0 Then

'get last wizard step
stp = CType(steps.Item(0), WizardStep)

'if last step was 'Project Info', set focus on region dropdownlist
If stp.ID = "stpProjectInfo" Then
Me.ddlRegion.Focus()
End If

End If

Recursive method to reset controls in a ControlCollection

This is a shared public method used to recursively interate through the controls in a control collection and set specified controls to an empty or unselected state.

First, I build two private shared helper methods to deal with an individual control. The first one checks to see what kind of control it is:

‘Helper function to check for type of control in a collection
Private Shared Function CheckControlType(ByVal ctl As Control) As Boolean

If TypeOf (ctl) Is TextBox Or TypeOf (ctl) Is DropDownList Or _
TypeOf (ctl) Is CheckBox Or TypeOf (ctl) Is RadioButton Or _
TypeOf (ctl) Is ListBox Or _
(TypeOf (ctl) Is Label And ctl.ID.StartsWith("lbl")) Then

Return True

End If
End Function

You’ll notice that for a label control I also test to see if the label’s ID starts with the characters ‘lbl’. This is because the ASP.NET Validation Controls are treated as Labels when looping through the collection, and I don’t want to evaluate those. So I make sure I name labels I want to check lblSomething so I can specify that only those will be checked.

The second helper method resets the control:

‘Helper function to reset fields
Private Shared Sub ResetFields(ByVal ctl As Control)

If TypeOf (ctl) Is TextBox Then
CType(ctl, TextBox).Text = “”
ElseIf TypeOf (ctl) Is Label Then
CType(ctl, Label).Text = “”
ElseIf TypeOf (ctl) Is DropDownList Then
CType(ctl, DropDownList).SelectedValue = “-1”
ElseIf TypeOf (ctl) Is CheckBox Then
CType(ctl, CheckBox).Checked = False
ElseIf TypeOf (ctl) Is RadioButton Then
CType(ctl, RadioButton).Checked = False
ElseIf TypeOf (ctl) Is ListBox Then
CType(ctl, ListBox).SelectedValue = “”
End If

End Sub

Once these helpers are in place, I recursively loop through the control collection and call the helpers as needed.

‘Clear fields in a collection
Public Shared Sub ClearFields(ByVal Controls As ControlCollection)

‘Iterate through control collection – if type of control is textbox,
‘dropdownlist, checkbox, radiobutton, label or listbox, clear control
For Each ctl As Control In Controls

‘clear main controls in collection
If CheckControlType(ctl) Then
ResetFields(ctl)

‘Recursion – if control has its own control collection (e.g., panel, div, etc)
‘method calls itself to clear that collection
ElseIf ctl.HasControls Then
ClearFields(ctl.Controls)

End If
Next

End Sub

I keep this method in a class called Presentation, which contains a variety of utility methods having to do with formatting and configuring pages and controls. I can then call this public shared method from anywhere in my code, passing it whatever ControlCollection I want it to process:

Presentation.ClearFields(divEdit.Controls)

Using System.Net.Mail namespace to send email

Here’s a little demo of how to use the System.Net.Mail namespace to have your application automatically send an email. This sample is a class method that sends an email notification to the developer/webmaster/website owner/whoever when a new user registers on the site.

First, some set up. I keep the values for the From email address and the host IP address in the appSettings section of the web.config file. To retrieve them, I have a couple shared ReadOnly properties in my class, like so:

Public Shared ReadOnly Property Host() As String
Get
Return System.Configuration.ConfigurationManager.AppSettings("Host").ToString
End Get
End Property

Public Shared ReadOnly Property FromAddress() As String
Get
Return System.Configuration.ConfigurationManager.AppSettings("From").ToString
End Get
End Property

Now that I can easily get to these settings, I build my method, as below:

”’ <summary>
”’ Send email notification when a new user registers
”’ </summary>
”’ <param name=”ToAddress”>System.String containing the email
”’ address that the message should go to
”’ </param>
Public Shared Sub NotifyEmail(ByVal ToAddress As String)

Try

‘instantiate new MailMessage object and pass in from and to
’email addresses (FromAddress is a readonly property pulling from
‘web.config file)
Using message As New System.Net.Mail.MailMessage(FromAddress, ToAddress)

‘set rest of message properties
message.Subject = "New User"
message.IsBodyHtml = True
‘set to true if you want to use html in body
message.Body = "A new user has registered"

‘instantiate new SmtpClient object and pass in host information
‘(Host is a readonly property pulling from web.config file)
Dim smtp As New System.Net.Mail.SmtpClient(Host)

‘send message
smtp.Send(message)

‘dispose of MailMessage object
End Using

Finally

‘dispose of SmtpClient object
smtp = Nothing

End Try

End Sub