Link Child/Master fields in ADP do not work for Numeric key

A long time ago I reported the problem with MS Access ADP Reports/Forms with subforms/subreports where Link
Master/Child fields are numeric but not integer (i.e scale greater than zero). 
I had the join field  defined as numeric(28.2).
However Access doesn’t recognize scale and generate incorrect SQL
statements, eg
exec sp_executesql N’SELECT  *  FROM “dbo”.”PickSlipOverdues” WHERE
((@P1 = “PatronID”))’, N’@P1 numeric(28)’, 7
instead of correct , N’@P1 numeric(28.2)’, 7.01
As a result join doesn’t work and subform is not populated.
The parent form is bound to the table with primary field defined as
numeric(28.2), child subforms are bound to the view or select
statements that has the same link field definition -numeric(28.2)

Today I had to fix the report with subreports where again Master/Child fields link fields are numeric (e.g . [BookID] [numeric](28, 2))
The workaround is to add to RecordSource  of each report/subreport string version of the key , e.g. (,cast(BookID as nvarchar(25)) as sBookID )
After this it is possible to specify sBookID as Master/Child fields and Access correctly generates string joins. 


Note: This post originally appeared on an thespoke web site, but site is down at the moment and the text was recovered from G o o g l e‘s cache

Extending DatabaseSpy to open UDL files.

I was asked to find utility to test OLEDB conection paramenters.
I desided to use EXCEL with the following procedure:

 
2. Open MS Excel (I’ve tested it with Excel 2003) , hope that previous versions are similar.
3. On the Data menu, point to Import External Data, and then click Import Data.
4. In the “Files of  Type” select “Microsoft Data Links( *.udl)”
5. Open UDL file created in step 1
6. Select table that you are interesting in.
7. The results should be shown in the spreadsheet.

However even the connection was tested successfully in Data Link Properties, Excel reported “no tables found“ when user tried to open the UDL file.
Then I was asked  to find another way to test OLEDB Connection.
Microsoft has KB article How to use Data Link files with the OleDbConnection object in Visual Basic .NET  shows only basic connection test.

Then I found An Approach to Viewing the Structure of a Database Using VB 2005 (AKA DatabaseSpy).

I’ve downloaded the code , but UI for different supported databases(Oracle,SQL Server and Access) doesn’t include generic OLEDB provider.

Fortunately, It was easy enough to add new connection tab UDL (in frmConnect.vb) with Browse  button and ability to load ,test and assign connection from UDL file.

 

 TODO: It would be better to call DATA Link APIs  directly as described in “Incorporating the Data Link Properties Editor into a VB.NET Application” article.

 

    Private Sub btnUDLBrouse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUDLBrouse.Click

        Dim openFile As System.Windows.Forms.OpenFileDialog = Me.OpenFileDialogUDL

        openFile.Title = “Universal DataLink”

        ‘openFile.DefaultExt = “mdb”

        openFile.Filter = “Universal DataLink (*.udl)|*.udl”

        openFile.ShowDialog()

        txtUDL.Text = openFile.FileName

    End Sub

 

    Private Sub btnUDLTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUDLTest.Click

        ‘Dim response As String

 

        Dim cn As New OleDbConnection()

        mConnectionString = “File Name=” & txtUDL.Text

        cn.ConnectionString = mConnectionString

        TestConnectionString(cn)

    End Sub

    Private Sub TestConnectionString(ByVal cn As OleDbConnection)

        Try

            cn.Open()

            If cn.State = ConnectionState.Open Then

                MessageBox.Show(“Connection opened successfully”)

            Else

                MessageBox.Show(“Connection could not be established”)

            End If

                        MessageBox.Show(“Connection attempt successful, the database connection information provided has been successfully used to connect to the database.”, “Connection Successful”, MessageBoxButtons.OK, MessageBoxIcon.Information)

        Catch ex As Exception

 

            MessageBox.Show(“Connection attempt failed.”, “Unable to Connect”, MessageBoxButtons.OK, MessageBoxIcon.Error)

        Finally

            cn.Close()

            cn = Nothing

 

        End Try

    End Sub

 

    Private Sub btnUDLCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUDLCancel.Click

        Me.Dispose()

    End Sub

 

    Private Sub btnUDLOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUDLOk.Click

        btnUDLTest_Click(sender, e)

        ‘set app setting global variables

        mCurrentDataModel = “UDL”

        mCurrentDatabaseType = “UDL”

        AppVars.gCurrentDataModel = mCurrentDataModel

        AppVars.gCurrentDatabaseType = mCurrentDatabaseType

        AppVars.gConnectionString = mConnectionString

        ‘create hashtable to hold settings

        Dim ht As New Hashtable

        ht.Add(“CurrentDataModel”, gCurrentDataModel)

        ht.Add(“CurrentDatabaseType”, gCurrentDatabaseType)

        ht.Add(“ConnectionString”, gConnectionString)

                ht.Add(“ProviderString”, gProviderString)

        ‘ht.Add(“ServerName”, gServerName)

        ‘ht.Add(“UserID”, gUserID)

        ‘ht.Add(“Password”, gPassword)

 

        ‘serialize data

        SerializeAppVars(ht)

 

        Me.Dispose()

 

    End Sub


 

ASP.NET Data Bound UserControl

The .NET 2.0 provides   System.Web.UI.WebControls.BaseDataBoundControl  and  DataBoundControl classes that can be useful for Custom Data-Bound Web Server Controls  . However it often convinient to have User Control with similar data-binding capabilities.
Unfortunetely .Net doesn’t allow multiple inheritance, so I have to duplicate the functionality in my DataBoundUserControl class derived from UserControl class.  

At the moment the DataBoundUserControl class is not fully implemented.As it explained in “Data Source Controls – Under the Hood” , data-bound control has to use a wrapper class in order to expose the DataSource as a data source control (ReadOnlyDataSource that implements IDataSource, returning a view (ReadOnlyDataSourceView) that only supports the select method). MS made ReadOnlyDataSource and ReadOnlyDataSourceView not public, so we need to re-invent them if needed.
As a shortcut derived User Controls should overide PerformSelectDataBinding() 
method.

Imports Microsoft.VisualBasic

Imports System.ComponentModel

‘Based on http://msdn2.microsoft.com/en-us/library/ms366539.aspx article “Developing Custom Data-Bound Web Server Controls for ASP.NET 2.0”

Public Class DataBoundUserControl

    Inherits System.Web.UI.UserControl

    True), DefaultValue(CStr(Nothing))> _

    Public Overridable Property DataSource() As Object

        ‘from http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.basedataboundcontrol.datasource.aspx

        ‘ When you set the DataSource property, the ValidateDataSource method is called. In addition, if the data-bound control has already been initialized, the OnDataPropertyChanged method is called to set the RequiresDataBinding property to true.

        Get

            Return m_dataSource

        End Get

        Set(ByVal value As Object)

            If (Not value Is Nothing) Then

                ValidateDataSource(value)

            End If

            m_dataSource = value

            OnDataPropertyChanged()

        End Set

    End Property

    Private m_dataSource As Object

 

    Public Overridable Property DataSourceID() As String

        Get

            Dim o As Object = ViewState(“DataSourceID”)

            If (Not o Is Nothing) Then

                Return CStr(o)

            End If

            Return String.Empty

        End Get

        Set(ByVal value As String)

            ViewState.Item(“DataSourceID”) = value

            OnDataPropertyChanged()

        End Set

    End Property

    Protected Property RequiresDataBinding() As Boolean

        Get

            Return m_requiresDataBinding

        End Get

        Set(ByVal value As Boolean)

            m_requiresDataBinding = value

            If (m_preRendered AndAlso m_requiresDataBinding = True) Then

                Me.EnsureDataBound()

            Else

            End If

        End Set

    End Property

    Private m_requiresDataBinding As Boolean

    Protected Overridable Sub ValidateDataSource( _

    ByVal dataSource As Object _

    )

        ‘from ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.NETDEVFX.v20.en/cpref16/html/M_System_Web_UI_WebControls_DataBoundControl_ValidateDataSource_1_b4c5a2da.htm

        ‘The DataBoundControl class checks the type of the object that is set for the DataSource property to ensure that it is an instance of the IDataSource, IListSource, or IEnumerable interface.

        If (((Not dataSource Is Nothing) AndAlso Not TypeOf dataSource Is IListSource) AndAlso (Not TypeOf dataSource Is IEnumerable AndAlso Not TypeOf dataSource Is IDataSource)) Then

            Throw New InvalidOperationException(“DataBoundControl Invalid DataSource Type” & dataSource.GetType.ToString())

        End If

 

    End Sub

    Protected Overridable Sub OnDataPropertyChanged()

        RequiresDataBinding = True

    End Sub

 

 

    ‘Calls the DataBind method if the DataSourceID property is set and the data-bound control is marked to require binding.

    ‘Does nothing if DataSource is used

    ‘The EnsureDataBound method is called during the OnPreRender method, to call the DataBind method if the data-bound control is not yet bound.

    ‘The EnsureDataBound method might also be called during calls to CreateChildControls and LoadPostData methods.(?? -not tested yet)

    Protected Overridable Sub EnsureDataBound()

        If (RequiresDataBinding And IsBoundUsingDataSourceID()) Then

            Me.DataBind()

        End If

     End Sub

    ‘Overrides the Control.OnPreRender event handler to call EnsureDataBound before calling the base class OnPreRender method.

    ‘invokes data-binding logic as late as possible during the page life cycle and only when needed

    Protected Overrides Sub OnPreRender(ByVal e As EventArgs)

        m_preRendered = True

        EnsureDataBound()

        MyBase.OnPreRender(e)

        m_preRendered = False

    End Sub

    Private m_preRendered As Boolean = False

    Protected ReadOnly Property IsBoundUsingDataSourceID() As Boolean

        Get

            Return (DataSourceID.Length > 0)

        End Get

    End Property

    Public Overrides Sub DataBind()

        PerformSelect()

    End Sub

    Protected Sub MarkAsDataBound()

        ViewState(“DataBound”) = True

    End Sub

 

    Protected Overridable Sub PerformSelect()

 

        ‘ Call OnDataBinding here if bound to a data source using the DataSource property (instead of a DataSourceID) because the

        ‘ data-binding statement is evaluated before the call to GetData.

        If Not IsBoundUsingDataSourceID Then

            OnDataBinding(EventArgs.Empty)

        End If

 

        PerformSelectDataBinding()

        ‘ The PerformDataBinding method has completed.

        RequiresDataBinding = False

        MarkAsDataBound()

 

        ‘ Raise the DataBound event.

        ‘Not implemented yet OnDataBound(EventArgs.Empty)

    End Sub ‘PerformSelect

 

    Protected Overridable Sub PerformSelectDataBinding()

        Throw New NotImplementedException(” should be overridde in derived class”)

        ” The GetData method retrieves the DataSourceView object from the

        ” IDataSource associated with the data-bound control.

        ‘TODO  according to http://www.manuelabadia.com/blog/PermaLink,guid,678ed6d8-dce8-40d7-9117-0ffd016fe886.aspx

        ‘it has to use a wrapper class in order to expose the DataSource as a data source control (ReadOnlyDataSource that implements IDataSource, returning a view (ReadOnlyDataSourceView) that only supports the select method).

        ‘Unfortunately, MS made ReadOnlyDataSource and ReadOnlyDataSourceView not public, so we need to re-crete them if needed

        ‘GetData().Select(CreateDataSourceSelectArguments(),  AddressOf OnDataSourceViewSelectCallback)

    End Sub ‘PerformSelect

End Class

 

 

StyleSheetsManage class for DotNetNuke

I found that code to create CSS links is duplicated in a few places. I’ve created a common class, that can be used from Default.aspx,ComponentsSkinsSkin.vb and third-party modules if required.

Originally I’ve posted it to DNN support , but it is not searchable by Google. So I desided to post it here as well.

 The code is the following:

Imports System.Diagnostics

Imports System.IO

Imports Microsoft.VisualBasic

Imports DotNetNuke

Imports DotNetNuke.Framework

 

Public Class StyleSheetsManage

    ‘Based on C:ProjectsFuncSolnFSDNNDefault.aspx.vb ManageStyleSheets

    Public Shared Sub AddStyleSheetFile(ByVal objCSS As Control, ByVal sFileName As String)

        ‘ initialize reference paths to load the cascading style sheets

        ‘Dim objCSS As Control = Me.FindControl(“CSS”)

        Dim ID As String

        Dim PortalSettings As PortalSettings = PortalController.GetCurrentPortalSettings

        Dim sHref As String = “”

        Dim objCSSCache As Hashtable = CType(DataCache.GetCache(“CSS”), Hashtable)

        If objCSSCache Is Nothing Then

            objCSSCache = New Hashtable

        End If

        If Not objCSS Is Nothing Then

            Select Case (sFileName.ToLower())

                Case “default.css”

                    AddStyleSheetLink(objCSS, Common.Globals.HostPath, sFileName)

                Case “skin.css”

                    ‘ skin package style sheet

                    sHref = AddToCacheIfRequired(objCSSCache, PortalSettings.ActiveTab.SkinPath, “skin.css”)

                    If sHref <> “” Then

                        AddStyleSheetLink(objCSS, PortalSettings.ActiveTab.SkinPath, sFileName)

                    End If

                Case “skinsrc”

                    ‘ skin file style sheet

                    ID = CreateValidID(Replace(PortalSettings.ActiveTab.SkinSrc, “.ascx”, “.css”))

                    sHref = AddToCacheIfRequired(objCSSCache, ID, “”)

                    If sHref <> “” Then

                        AddStyleSheetLink(objCSS, ID, “”)

                    End If

                Case “portal.css”

                    AddStyleSheetLink(objCSS, PortalSettings.HomeDirectory, “portal.css”)

                Case Else

                    Debug.Assert(False)

            End Select

        End If

    End Sub

    Public Shared Function AddStyleSheetLink(ByVal objCSS As Control, ByVal sPath As String, ByVal sFileName As String) As String

        Dim sHref As String = “”

        If Not objCSS Is Nothing Then

            Dim objLink As HtmlGenericControl = New HtmlGenericControl(“LINK”)

            Dim ID As String = CreateValidID(sPath)

            objLink.ID = ID

            objLink.Attributes(“rel”) = “stylesheet”

            objLink.Attributes(“type”) = “text/css”

            sHref = sPath & sFileName

            objLink.Attributes(“href”) = sHref

            objCSS.Controls.Add(objLink)

        End If

        Return sHref

    End Function

    Public Shared Function AddToCacheIfRequired(ByVal objCSSCache As Hashtable, ByVal sPath As String, ByVal sFileName As String)

        Dim PortalSettings As PortalSettings = PortalController.GetCurrentPortalSettings

        Dim sHref As String = “”

        Dim ID As String = CreateValidID(sPath)

        If objCSSCache.ContainsKey(ID) = False Then

            If File.Exists(HttpContext.Current.Server.MapPath(sPath) & sFileName) Then

                sHref = sPath & sFileName

            End If

            objCSSCache(ID) = sHref

            If Not Common.Globals.PerformanceSetting = Common.Globals.PerformanceSettings.NoCaching Then

                DataCache.SetCache(“CSS”, objCSSCache)

            End If

        Else

            sHref = objCSSCache(ID)

        End If

        Return sHref

    End Function

End Class

Unreliable in ASP.NET 2.0 Cache usage in MetaBuilders.DialogWindow

I am using  MetaBuilders.DialogWindow[^] and recently got
System.NullReferenceException: Object reference not set to an instance of an object.
at MetaBuilders.WebControls.DialogHandlerFactory.get_IsRegistered()
.

Investigation of the code showed that the problem related to the changed in ASP.NET 2.0 Cashe behavior – asyncronous Insert.
    if ( context.Cache[cacheKey] == null ) {
     context.Cache.Insert(cacheKey, DetermineIsRegistered());
    }
    return (Boolean)context.Cache[cacheKey];

The similar problem was known in DotNetNuke 4.0 (see my post).
The solution is the following:
                bool bRet=false;
                //fixed cache delay, do not rely on cache available immediately after insert
                if (context.Cache[cacheKey] == null
                {
                    bRet = DetermineIsRegistered();
                    context.Cache.Insert(cacheKey, bRet);
                }


                else


                    bRet = (Boolean)context.Cache[cacheKey];


                return bRet;


Related issue:Note that in ASP.NET 2.0 DialogHandlerFactory.DetermineIsRegistered should be changed as discussed in this thread to avoid System.ArgumentException: Object of type ‘System.String’ cannot be converted
to type ‘System.Web.VirtualPath’.


 


 

Nested Data Bound UserControl

<asp:datagrid id=Datagrid1 runat="server" SPAN

I needed to have ASP.NET report with DataRepeater and nested data bound user control.
The similar approach is used in 
ASP.NET Reports Starter Kit  with datagrid nested in datalist itemtemplate.


       <asp:datalist id=”CategoriesList” runat=”server” >
                <itemtemplate>
                                <asp:datagrid id=Datagrid1 runat=”server”  DataSource=”
<%# GetDetails(DataBinder.Eval(Container.DataItem, “CategoryID”)) %>
                                </< FONT>asp:datagrid>
                </< FONT>
itemtemplate
>
                <
headerstyle cssclass=”ReportTitle”></< FONT>headerstyle
>
            </< FONT>asp:datalist>
 So my markup should be like this: 


<%@ Register Src=”MyUserControl.ascx” TagName=”MyUserControl” TagPrefix=”uc1″ %>


<asp:Repeater ID=”Repeater1″ runat=”server”>


<ItemTemplate>


<uc1:MyUserControl  DataSource=<%# GetDetails(DataBinder.Eval(Container.DataItem,”PatronId”)) %>


 ReportId= <%# ReportID %> id=”patronNotice” runat=”server” >


</uc1:OverdueNotice>


</ItemTemplate>


</asp:Repeater>


 


I’ve created DataSource property and on DataBinding event used it to populate my UserControl data.
But it didn’t work. DataBinding event occurred earlier than
DataSource was set from DataBinding expression and  DataSource=Nothing.


I’ve read carefully the DataBinding documentation and understood that
DataBind can be called more than once during the same request and it is important to re-do binding if DataSource property has been changed.
So I had to implement
Data Bound User Control class to resolve the issue.


 

DataSetHelper.SelectDistinct method for multiple columns

UPDATE: In .Net 2.0 there is DataView.ToTable Method (String, Boolean, String[]) that does, what my function (and MS KB  article 326176) was created to address. The article is now obsolete.

UPDATE: there is also optimized for performance class in CodeProject DataTable with SelectDistinct in VB .

I was using a DataSetHelper class based on MS kb article 326176 HOW TO: Implement a DataSet SELECT DISTINCT Helper Class.

However SelectDistinct  function from the article works only if you select distinct on single field.
To support multiple fields I created a function, based on Nageswara Reddy’s post .

            public static DataTable SelectDistinct( DataTable SourceTable, string[] FieldNames)

            {// select distinct on multiple fields.

//From: Nageswara Reddy http://www.dotnet247.com/247reference/msgs/43/218182.aspx

            FieldNames = StringHelper.ToLower(FieldNames);

            DataTable DistinctTable = SourceTable.Clone();

                  DataColumn [] constraintColumns = new DataColumn[FieldNames.Length];

            int nFound=0;

                  for (int i =0 ; i< DistinctTable.Columns.Count ; i++)

            {

                if (Array.IndexOf(FieldNames, DistinctTable.Columns[i].ColumnName.ToLower()) >= 0)

                {

                    if (nFound >= FieldNames.Length)

                    {

                        throw new ApplicationException(“Too many fields are similar to passed FieldNames “ + FieldNames.ToString());

                    }

                    constraintColumns[nFound++] = DistinctTable.Columns[i];

                }

                  }

            //Report if passed field names are not found in the table

            if (nFound < FieldNames.Length)

            {

                throw new ApplicationException(“Some of fields “ + FieldNames.ToString() + ” not found in the database”);

            }

                  UniqueConstraint _UniqueConstraint = new UniqueConstraint(constraintColumns);

                  DistinctTable.Constraints.Add(_UniqueConstraint);

 

                  for (int i =0 ; i< SourceTable.Rows.Count ; i++)

                  {

                        try

                        {

                              DistinctTable.ImportRow(SourceTable.Rows[i]);

                        }

                        catch(Exception ex)

                        { // Keep quite

                              Debug.WriteLine(ex.ToString());

                        }

                  }

                  return DistinctTable;

            }

 

See also my previous post: “Handling missing source columns in DataSetHelper.InsertInto method”