Verify Your SQL Connection / Get Connection String

I have been very fortunate to have some fantastic mentors.  One of them is a gentleman by the name of Ken Ammann, who taught me this trick when you need to verify your SQL connection or get your connection string.

1) From your desktop, right-click and create a new text document.

2) Change the file extension from .txt to .udl

becomes:

3) Click Yes to verify the change of extension type.

4) Open the UDL file and set properties.

5) Test the connection.

6) Click OK.

7) Change file extension back to .txt

8) Take a look at your connection string.

It is a nice easy way to verify your SQL connections when you need to verify that you still have a SQL connection.  The real reason that I wrote this post is that I was spending too much time trying to recall the UDL extension name.

Increase Time Before Connection Timeout Between SQL and SharePoint

I have run into an issue with a couple of clients where a Connection Timeout Error occurs on either very large SSRS reports or in some custom web parts pulling data from SQL.   By default, the connection between SharePoint and your SQL servers will timeout after 120 seconds.  My current client just had me create a report that takes about 8.5 minutes to complete rendering...  oops, error!
This is how you fix the timeout issue:
1)  Go to the virtual directory for the site that is timing out.
         C:\inetpub\wwwroot\wss\VirtualDirectories\yourSite
2)  Make a backup of the web.config file.
3)  Edit the web.config file and add an httpRuntime property called "executionTimeout"

In the example below, my connection will now timeout after 5 minutes (300 seconds).

Create a Data Grid View Using SPD 2007 and a SQL Stored Procedure

Let's say that we have a SQL table of Resources (people), and in another SQL table we have their schedules.  How can we view a list of people who are available to work certain dates?  Basically, we want the user to enter a "Start Date" and an "End Date" and retrieve a Data Grid list of all people in the company who are available to work within those dates.

1) Create the sample databases and data.

    Get SQL script here

2) Create the Stored Procedure

Get Stored Procedure script here

3) Next, we will open up SharePoint Designer 2007, and go to the top level of the site.  For example, http://pc2007.local.  We are now going to create a blank page based off the default,master page.

http://pc2007.local  > _catalogs > masterpage > default.master.  Right click the default.master and select "New from Master Page".  This will open up a blank web page.

 

4) To incorporate out own ideas, we will need to go to the Common Content Tasks for PlaceHolderMain and select Create Custom Content.

5) Within the PlaceHolderMain, I am going to add a 4x4 table.  Table > Insert Table 

6) I am going to label my columns and insert 2 Calendar Date Pickers.

7) We now want to change the ids from "Calendar1" to "indate" and from "Calendar2" to "enddate"

8) Within the Data Source Library tab, click "Connect to a database..."

9) Enter your SQL server information, click Next.  And you will receive a warning about passwords being saved as plain text.

10) Select the database where your tables are stored, and we are going to select the Stored Procedure radial button.

11) Click Finish!  A new window should pop-up to Edit Custom SQL Commands.

12) Under the Select tab, select the Stored Procedure radial button (again) and select the appropriate stored procedure, and click the "OK" button.

13) Select the "General Tab" and give your database connection a useful name... and save it!

14) This is just my preference, but I will select the bottom left cell of my table to insert my source control...

15) Hover your mouse over the newly created database connection and select "Insert Data Source Control"  A Refresh Data Source Schema will pop-up, and you will want to click the "Ok" button.

16) You will want to open the Common SqlDataSource Tasks and select "Configure Data Source..."

17) Click Next as we have already created the connection

18) However, we want to save the connection string locally, so give it a friendly name, and click Next.

19) Verify the "Specify a custom SQL statement of stored procedure" radial button is selected, and click Next.

20)  Under the Select tab, select the Stored Procedure radial button, find your Stored Procedure, and click Next.

21)  We now want to tell the connection what variables we want to pass to the Stored Procedure.  Our sources are both "Control" types.  However for the "enddate", make sure that your ControlID is the name of the control that has the End Date...  We had named the calendar "enddate".

22)  Set up the In Date control and press the Next button, and then the Finish button.  You will get a warning about where the configuration for the connection is being saved.

23)  Merge the rows under the calendar.

24) Under the Toolbox tab, we are going to expand the Data Section, grab the GridView control and drop it into the row under the calendar...  The one where we just merged the cells...

25) Open up the Common GridView Tasks and under the Choose Data Source, select your Data Source.

26)  Under the Common GridView Tasks, enable Paging and Enable Sorting.

27)  Save your work, check in your document, navigate to your new page, select an In and Out Date, and look at who is available!

Reading Excel in VB- Connection Strings and Registry Edits

A client had a request to upload a .xls file and place the data into a SQL table.  At the time there was only the Microsoft Jet 4.0 engine in x86 (32bit).   The connection string for VB is:

myconnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=C:\filename.xls;Extended Properties=" + Chr(34) + "Excel 8.0;HDR=Yes;IMEX=1" + Chr(34) + ";")

The problem was that as the code ran through the rows, some of the values would be returned as null even though there was data in the cell.  The reason this happens is that the reader is defaulted (in the registry), to look at the first 8 rows and determine the cell format.  The way to get around this issue is to edit the registry values.

HKEY_LOCAL_MACHINE>>SOFTWARE>>Wow6432Node>>Microsoft>>Jet>>4.0>>Engines>>Excel

We want to make sure that when there is a "Mixed Type" of data imported, we want the data read as Text, and we want set TypeGuessRows to 0, meaning scan all rows before deciding the format of the data.

As time progressed, the client decided that in 2010, people should be able to upload .xlsx files as well.  So I downloaded the appropriate 2007 Office System Driver Connectivity Components, which would only handle .xlsx files, not .xls files...  No worries, my connection string turned into:

 If LCase(Right(strFilePath, 3)) = "xls" Then

myconnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=C:\filename.xls;Extended Properties=" + Chr(34) + "Excel 8.0;HDR=Yes;IMEX=1" + Chr(34) + ";")

End If

If LCase(Right(strFilePath, 4)) = "xlsx" Then

myconnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;data source=C:\filename.xls;Extended Properties=" + Chr(34) + "Excel 12.0;HDR=Yes;IMEX=1" + Chr(34) + ";")

End If

Pretty simple fix... 

A couple of weeks later I read about the 2010 Office System Driver Connectivity Components, which will handle .xls and .xlsx files, and can be downloaded here.  I uninstalled the 2007 package and installed the 2010, and went to update my connection string as per Microsoft's How to Use Instruction #2.

myconnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;data source=C:\filename.xls;Extended Properties=" + Chr(34) + "Excel 14.0;HDR=Yes;IMEX=1" + Chr(34) + ";")

This returned a "Could not find installable ISAM" error.  After searching and reading, I discovered that the only modifications necessary were to remove the "If/Then" statements.  My connection string now looked like:

myconnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;data source=C:\filename.xls;Extended Properties=" + Chr(34) + "Excel 12.0;HDR=Yes;IMEX=1" + Chr(34) + ";")

Everything ran well except now the .xls and .xlsx files were returning null values where cells had data.  Time to run Regedit.

HKEY_LOCAL_MACHINE>>SOFTWARE>>Wow6432Node>>Microsoft>>Office>>14.0>>Access Connectivity Engine>>Engines>>Excel

And just like before, we want set TypeGuessRows to 0.

2 folders below the Engines folder is the ISAM folder.  Take a look in there, and you will notice that the Excel 12.0 is for *.xlsb files, and that 12.0 Xml is for *.xlsx files.  My final connection string looks like this:

Dim myconnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\filename.xl; Extended Properties=" + Chr(34) + "Excel 12.0 Xml;HDR=YES;IMEX=1" + Chr(34) + ";")

It's the only one needed for reading both .xls and .xlsx files.