VB.Net or SSRS code for Ordinal Numbers

28-Jan-1212 Leave a comment

Easily found on web. See example in SRS.ReportingServices.Functions and also in my invoice report.
With acknowledgement to:
http://www.jigsawboys.com/2010/03/04/ordinal-number-in-reporting-services/

Public Function FormatOrdinal(day as Integer) As String
  SELECT CASE (day Mod 100)
    CASE 11,12
    CASE 13
      Return day.ToString() + “th”
  END SELECT

  SELECT CASE day Mod 10
    Case 1
      Return day.ToString() + “st”
    Case 2
      Return day.ToString() + “nd”
    Case 3
      Return day.ToString() + “rd”
    Case Else
      Return day.ToString() + “th”
  END SELECT
End Function

Good luck!

Filed under SQL SSRS, VB.Net

SSRS Adding Code and also custom assemblies to reports

28-Jan-1212 Leave a comment

How to add Code to SSRS reports

  1. Right click on report > Code > Type code as Visual Basic function
  2. In expressions within a data region use Code.<functionname> e.g. =Code.OrdinalFunction(4)

Code for Ordinal Number readily avialable on web. Example use in SRS.ReportingServices.Functions and also in my invoice report

How to add custom assemblies to SSRS reports

  1. Create a class library project in Visual Studio VB.Net or C#
  2. Use a meaningful assembly name e.g. <initials>.ReportingServices.Functions
  3. Add a class and methods to this class. Make the methods shared
  4. Compile to create a dll
  5. Copy the dll to the equivalent location of:
    C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies
    You may not need the (x86) and you may be on a lower or higher version than 9.0
  6. In VS BIDS report right click > report properties > References > Add the reference
  7. In expressions in a report use full Namespace.ClassName.FunctionName(variables)

Next deploying the assembly to the report server

  1. Copy your DLL to the following location or equivalent:
    C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin

Good luck. Acknowledgments to:

http://www.devx.com/codemag/Article/33656/0/page/3

Filed under SQL SSRS, VB.Net

Test Project Root Namespace disabling referenced project namespace

21-Jan-1212 Leave a comment

PROBLEM
Had a project SRS.Business.EntityModel
and so created TestProject.SRS.Business.

SOLUTION
I found that this project could not code against the first. However when I subtly changed the Test Project Root Namespace in this case to TestProject.SRSBusiness it worked.

Filed under Testing

Programmatic Web Search – Screen scraping – Web Page Input

08-Jan-1212 Leave a comment

To open a web page

Dim process3 As New Process
process3.StartInfo.FileName = Webpage
process3.Start()

To open a google search

use following plus search words separated by +

http://www.google.co.uk/search?q=

To read web page source – for screen scraping


Public Sub ReadWebPageSource()

Dim request As WebRequest = WebRequest.Create(Me.WebPage)
‘ If required by the server, set the credentials.
request.Credentials = CredentialCache.DefaultCredentials
‘request.Timeout = 3000

Try
‘ Get the response.
Dim response As HttpWebResponse = CType(request.GetResponse(), HttpWebResponse)
‘ Display the status.
‘Console.WriteLine(response.StatusDescription)
‘ Get the stream containing content returned by the server.
Dim dataStream As Stream = response.GetResponseStream()
‘ Open the stream using a StreamReader for easy access.
Dim reader As New StreamReader(dataStream)
‘ Read the content.
Me.Source = reader.ReadToEnd()
‘ Display the content.
‘Console.WriteLine(responseFromServer)

‘ Cleanup the streams and the response.
reader.Close()
dataStream.Close()
response.Close()

Catch ex As Exception
Me.Source = “”
MsgBox(ex.Message)
End Try

End Sub

WebBrowser and Set Inner Text

tag id=”lst-ib” is Googles search text box

WebBrowser1.Navigate(“http://www.google.co.uk“)
WebBrowser1.Document.GetElementById(“lst-ib”).InnerText = InnerTextTextBox.Text
WebBrowser1.Document.GetElementById(“lst-ib”).Focus()

Filed under VB.Net

SQL Server Compact Edition and Visual Studio

27-Dec-1111 Leave a comment

Motivation

To distribute applications to friends and family

Limitations

  1. No Schemas
  2. No Views
  3. No Stored Procedures
  4. Data types missing include:
    1. nvarchar(max)

Issues

  1. Difficult to switch a dataset from SQL to SQL compact at runtime. Considering having to projects with the datasets one SQL and one SQL Compact. Then having an IObjectData and implementations of both
  2. SQL Server Compact 4.0 is not supported by SSMS SQL Server Management Studio
  3. SQL Server Compact 4.0 is not supported by datasets

Because of limitations of 2 and 3, it may be best to stick with 3.5 SP2 for now. 4.0 seems more linked to Web projects for the minute.

If using 4.0 or in anycase it is possible to use Visual Studio > Views > Server Explorer or also Visual Studio > Data drop down menu item

Tools

Visual Studio Tools for SQL Server Compact 4.0. Unable to find microsoft site but link from here works.
Until this tooling is installed the pre-requisites offers only 3.5 SP2

http://erikej.blogspot.com/2010/12/visual-studio-tools-for-sql-server.html

Error Messages

“Format of the initialization string does not conform to specification starting at index 0” Means that the connection string format is not correct. I had missed out the “Data Source=”

Filed under ADO.Net, SQL Server, Visual Studio Tagged with

VB.Net programming of SQL Server Replication

26-Dec-1111 Leave a comment

The article http://msdn.microsoft.com/en-us/library/ms146869.aspx suggests that Microsoft.SQLServer.Replication.dll is in the .Net references. For me it was not, it could however be found at:
C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies

Also needed:

  1. Microsoft.SQLServer.Rmo
  2. Microsoft.SQLServer.Replication
  3. Microsoft.SQLServer.Replication.BusinessLogicSupport
  4. Microsoft.SQLServer.ConnectionInfo

Filed under ADO.Net, SQL Server, VB.Net Tagged with

Visual TFS Reports setup

23-Dec-1111 Leave a comment

Will not work on Windows 7 – See other post in this blog

  1. Pre-requisites
    SQL Server 2008 R2 with Reporting Services and Analysis Services
  2. Use Team Foundation Administration Console > Application Tier > Reporting
    Edit > Use Reporting ticked > Warehouse Tab > Server (enter name) Database (Tfs_Warehouse)
    Analysis Services Tab > Server (enter name) Database (Tfs_Analysis) Account for accessing data sources Username maybe Domain\User
    Reports Tab > Server (enter name) > Populate URLs Web Service in format http://server:port/ReportServer and Report Manager http://server:port/Reports
    Account for accessing data sources Username maybe Domain\User
    Default Path /TfsReports
  3. May need to Use the WarehouseControlWebService.asmx as in Connect link below. Address is
    http://localhost:8080/tfs/TeamFoundation/Administration/v3.0/WarehouseControlService.asmx
    http://connect.microsoft.com/VisualStudio/feedback/details/653006/team-foundation-reports-broken-the-warning-parameter-is-missing-a-value-iterationmdxparam-parameter-is-missing-a-value
  4. For me the standard out of the box reports for MSF for Agile.. were missing
    This seems to be because I created the “Visual Studio > Team Explorer > New Team Project ” before setting up TFS reporting.
    First attempt in brackets below. My second attempt was “Visual Studio > Team Explorer > New Team Project > this creates reports for the process of your choice. > Move the reports to the project folder of your choice > Detach the project > Go to Team Foundation Server Administration Console > Team Project Collections > Delete the Team Project”

    (Initially tried to resolve this by finding the Compressed Zip file with the standard reports in. Then uploading the reports into Report Server one by one.
    From the source I had, this had the affect of wrong datasources. After pointing the datasource to the new name Tfs2010ReportDS instead of TfsReportDS this then went further)

  5. Bug Status report uploaded send the “Warning” parameter was missing a value.
    First attempt > Manage report > Parameters > Click the web button for Use Default or something like this> Report then ran but had no data
    Second attempt > TFS Administration Console > Reporting > Start Rebuild (takes a while)
    See http://msdn.microsoft.com/en-us/library/cc668753.aspx
    and http://social.msdn.microsoft.com/Forums/en-US/tfsadmin/thread/c8a324ea-c329-41bf-901e-82f464fac4bb

    STILL NOT WORKING. Getting Closer I Feel.

Filed under Visual Studio TFS

Error 3031: Problem in mapping fragments starting at line… nullable column

16-Dec-1111 Leave a comment

Error 3031: Problem in mapping fragments starting at line… nullable column

In SQL I added a column to a table and initially had the column as Not Nullable. I then updated a view to use the new column. I then updated Entity Framework model from the database. I then changed my mind and made the column nullable. I then updated EF model once again and it would not compile. No-manner of updating column Nullable properties worked.

After some reading suggested this was a bug in EF. As a precaution I updated the view in SQL to refresh the schema went to EF and removed and added it again.

Filed under ADO.Net, Entity Framework, Errors, Troubleshooting, Visual Studio Tagged with ,

Entity Framework possible feedback to Microsoft Connect – IBindingListView

15-Nov-1111 Leave a comment

 

https://connect.microsoft.com/data/feedback/details/704105/entity-framework-winforms-datagridview-support-for-bindingsource-filter-sort-and-column-heading-sort

 

Entity Framework WinForms DataGridView support for BindingSource.Filter .Sort and column heading Sort

Entity Framework looks useful and I have limited use of it in my code. Also several developers have expressed to me how they have found it useful.

I currently use a lot of WinForms databinding using DataSets and BindingSource. Out of the box I get column header sorting. Also it is a simple matter to set BindingSource.Filter and BindingSource.Sort as strings. I have used this feature to provide a lot of data-driven filter and sort strings for UI quick filter and sort which works very well.

However when I use Entity Framework the column header sort and BiningSource.Filter and .Sort properties do not work. My understanding is that this is because EF does not support IBindingListView. Although it might but not out of the box. Please advise or consider implementing this in future editions of Entity Framework.

Thank you

Stephen, UK

Filed under ADO.Net, Entity Framework, Windows Forms Tagged with , ,

Calculate Code Metrics – Troubleshooting

26-Sep-1111 Leave a comment

Project A references B
Code metrics work fine for B. Project A builds OK in Visual Studio. However “Calculate Code Metrics” on project produces error.

Message: An error occurred while calculating code metrics for target file ‘<My Assembly1 path>’ in project <My Assembly1>. The following error was encountered while reading module ‘<My Assembly2>’: Could not resolve member reference: <My Assembly3>.Models.IModel::get_FunctionTable.

SOLVED
It is possible that Code Metrics is using a different reference to Visual Studio. In my particular case my “Common Assemblies” folder had an older reference for Project B. By deleteing the reference from Common Assemblies, Code Metrics was forced to use the more up to date Project Reference, and then worked.

Filed under Errors, Visual Studio Tagged with ,

← Older posts

Newer posts →