Selector tool

Antoine has a rather nice selector tool that is used on the main page of his Contaminated Sites database. It defaults to site listed by username. The user can then select sites by beginning letter or by searching the list by keywords.
The problem I have run into using this selector is that I don't want to display my tables by username, which is the default. Some of the tables with which I work do not have a username. To use the selector, the table would have to have a field labeled 'Username'; otherwise, an error would result. Another problem will be listing a selector by comparing values from two tables; which this does not. I will delve into this topic later.
I just found out how he has the selector default to filling the gridview selector with sites based on username. In the code behind the main page, I define the database and the table along with other information about my data to display in the selector:

    protected void Page_Load(object sender, EventArgs e)
    {
        //assign page name to master page
        Label pageLabel = (Label)Page.Master.FindControl("ux_PageTitleLabel");
        pageLabel.Text = "Main";

        //set up the user control
        MainSelector.Database = "CostRecovery";
        MainSelector.Table = "CRList";
        MainSelector.DataKey = "LC";
        MainSelector.OrderBy = "CO#";
        MainSelector.ResultText = "Sites";

        MainSelector.SelectorChanged += MainSelector_SelectorChanged; //register the event from the selector

        if (!IsPostBack)
        {
           MainSelector.ShowUserButton = false;        // Setting this to true causes the selector to default fill by username
            MainSelector.AddResultColumn("SiteDescription", "Site Name");
            MainSelector.AddResultColumn("LC", "Ledger Code");
            MainSelector.AddResultColumn("CO#", "Contract Number");
            MainSelector.AddResultColumn("PjMgr", "Project Manager");
            MainSelector.AddResultColumn("CRStatus", "CR Status");

It is the MainSelector.ShowUserButton that sets off the default fill by username. When called and if true, the procedure calls the FillResultsByUserName routine.

    public bool ShowUserButton
    {
        set
        {
            HttpContext.Current.Session[UniqueName + "ShowUserButton"] = value;
            if (value)
            {
                ux_UserResults.Visible = true;
                FillResultsByUserName();  //add the results that belong to the user to the gridview
            }
        }
        get
        {
            if (HttpContext.Current.Session[UniqueName + "ShowUserButton"] == null)
                HttpContext.Current.Session[UniqueName + "ShowUserButton"] = false;  //default to false
            return (bool)HttpContext.Current.Session[UniqueName + "ShowUserButton"];
        }
    }

I don't expect anyone without all the code to fully understand what is going on here. I don't completely. But as a reminder to me of what I found today, it will do. I do have a foundation on how this selector works now and will be able to customize it to work with multiple tables hopefully.

Recent View table

While trying to set up my database, I wanted to develop a wiki to track my database changes. I chose this blog. However, I came across a website in the process. I has wiki; but more importantly, it has database capabilities. I copied an Excel table and converted it over to a database online. While working with this data, I noticed the site has a Recent View feature which keeps track of records that you have viewed. This is a nice feature that I would like to add to my application. It proved rather difficult overall, but now I have it working. Working the way I want it to as well.
First of all, I set up a web user control for this Recent View. I am using a master page and a web form to designate the content. My content consists of a GridView of the Billing Log records, Main Menu user control, and this control. I will explain this set up in later postings.
In the user control, I set up a DataList with the SqlDataSource is my RecentViewed table. I went into SQL Management Studio and created this table.
My DataList looked something like this:

<asp:DataList ID="RecentViewDataList" runat="server" DataSourceID="RecentViewedSqlDataSource" onselectedchange="RecentViewedDataList_SelectorChanged">
     <HeaderTemplate>
          <asp:Label ID="RecentViewedHeaderLabel" Text="Site" runat="server"/>
     </HeaderTemplate>
     <ItemTemplate>
          <asp:LinkButton ID="RecentViewedIDLabel" CommandArgument='<%# Eval("ID") %>' Text='<%# Eval("Site") %>' runat="server" OnClick="RecentViewClick" />
     </ItemTemplate>
</asp:DataList>

Below this was my SqlDataSource:

<asp:SqlDataSource ID="RecentlyViewedSqlDataSource" runat="server" connectionstring="<%$ ConnectionStrings:DevConnectionStringCostRecovery %>" selectcommand="SELECT TOP 10 RecentViewed.RecentKeyID as 'ID', BillingLog.Site as 'Site' FROM RecentViewed INNER JOIN BillingLog ON RecentViewed.RecentKeyID = BillingLog.ID ORDER BY RecentViewed.RecentStamp DESC" />

I use SELECT TOP 10 to only show the first 10 records ordered by RecentStamp. RecentStamp is a timestamp field with a default value of GETDATE(). This automatically puts in the time when a record is inserted into the table. I set my RecentKeyID as "ID" as used in the CommandArgument above. Same for Site. The Text in the LinkButton is displayed on the webpage, but the CommandArgument is the value passed to the RecentViewClick code behind the page. This is how I can show the name of the site, but pass the record number when viewing the record.
I also added come color to the backgound item style and alternating style of the DataList, but I won't go into that here. I even put an background image similar to the background image posting.
As far as the code behind, see my next posting...

SQL Server Error

Well, I resolved the error about which I wrote in this previous post.

I installed SQL Server 2005 where I uninstalled SQL Server 2005 Express. In my web application, I defined the database connection string to point to the Cost Recovery database in SQL Server 2005 Express. I moved it over to SQL Server 2005 (different folder).
In the web.config file, I change the following entry:

<add name="DevConnectionStringCostRecovery" connectionString="Data
Source=mySQLServer/SQLExpress;Initial
Catalog=CostRecovery;Integrated Security=True"
providerName="System.Data.SqlClient"/>

Where mYSQLServer is the server name. I removed "SQLExpress" from this statement and saved the file. It worked.

IIS

IIS or Internet Information Services is a web server that can be installed from Windows XP Professional.

1. In XP Pro, click the Start button.
2. Select Control Panel.
3. Select Add or Remove Programs.
4. Select Add/Remover Window Components.
5. Check Internet Information Services.
6. Select OK.

IIS will then be installed.

New VS 8.0 Pro and SQL Server 2005 DVDs in!

First of all, my apologies. Normally, I am writing to document any new information or knowledge that gained while developing this Cost Recovery database application. In this case, I am noting the receipt and installation on Visual Studio 8.0 Professional and SQL Server 2005 Developer edition!
I just installed SQL Server 2005 Developer. I uninstalled the Express version first. I don't know per se if that was the best method, but I did have to install IIS before installing the developer edition. I will note how to install IIS in the next posting.
The one problem I am running into is that all the SQL scripts I wrote under Express are in the Express folder of SQL Server Management Studio where the new version is in a separate folder. That is, all the old scripts are there (whew! At first, I thought it had erased all my scripts.) but in the Express folder, not the now default SQL Management project folder.
The other problem now is my Cost Recovery application now gives errors after compiling:

Server Error in '/CostRecovery' Application.

An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact
that under the default settings SQL Server does not allow remote connections.
(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance
Specified)

I will work this out. At the moment, I will install the Visual Studio 8.0 pro... Wish me luck!

Footnote:

I fixed the error. It is in this post.

Excel Linked Server

With my new found knowledge of importing Excel data into SQL tables, I came across the scenario of importing a table with no headers. In my case there are column headers, but a spacer row was added to separate it from the actual data. In addition, there are additional tables below also separated by a spacer row to differentiate categories. It is a matter of formatting the tables for look where the simple tables just don't appeal to the eye.

For your sake and not simplicity, I will detail how to create a linked server here.

To link to an Excel workbook and import with no headers, I created a linked server by the following these steps:

1. Open Microsoft SQL Server Management Studio Express
2. Connect to your server which has the database in which you wish to create a table.
3. In the Object Explorer tab, expand Server Objects.
4. Right-click Linked Servers.
5. Select New Linked Server.
6. Under the General page, type in a name for the Linked Server.
7. Select Other data source for Server Type.
8. Select Microsoft Jet 4.0 OLE DB Provider.
9. For Product Name, type in Excel.
10. Type in the full path and filename of the Excel workbook to which you are linking in Data Source.
11. In the Provider String, type Excel 8.0; HDR=NO.
12. Select OK.

You should now have a linked server to your Excel workbook and it will not import the first row as headers.

In my case, my tables were separated by spacer rows. I opened the worksheet and highlighted each table. I defined names for each and used these names to import each table rather than the worksheet name.

Example:

SELECT * INTO mySQLTable FROM myExcelLinkedServer...myDefineNamedTable

Notice there are no brackets or the $ symbol. Brackets and the $ are used when importing a worksheet, not a named range.

If I had only one table and it started in cell A1, I could use the following:

SELECT * INTO mySQLTable FROM myExcelLinkedServer...[myExcelWorksheet$]

Importing Excel data into SQL Server 2005

I have been at a total loss trying to import Excel data into a SQL database. When I first started this project, my task was to use the existing data in the Cost Recovery database to build and develop a working model. I had absolutely no luck getting the data into tables. The method to which I finally resorted was importing the data into Microsoft Access tables; afterwhich, I used the following SQL statement to create a table and import the data:

SELECT * INTO mySQLTable FROM myLinkedServer...myAccessTableName

This just an example where mySQLTable is the name of the table created in SQL, myLinkedServer is a linked server (I have a posting on how to do this), and myAccessTableName is the name of the table I created in Access. This all works, but is tedious.

What I found out when importing data directly from Excel is closing the Excel spreadsheet when trying to import. Otherwise, I kept getting this error:


-- Msg 7399, Level 16, State 1, Line 22
-- The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
-- Msg 7303, Level 16, State 1, Line 22
-- Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

I googled and re-googled looking for an answer. I tried suggestion after suggestion with no luck. It was when I closed the workbook, did I have success. The workbook can NOT be open while trying to import.
The two following statements work, but the first uses a Linked Server and is simpler for me:


SELECT * INTO mySQLTable FROM myLinkedServer...[myExcelWorksheet$]

Again, mySQLTable is the name of the SQL table I want to create and into which import data, myLinkedServer is the name of the Linked Server accessing the Excel workbook, and myExcelWorksheet is the name of the worksheet in the workbook. Include the brackets [ ] and the $ in the worksheet name.

The second method is as follows:


SELECT * INTO mySQLTable FROM openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;database=myFullFilePath\myExcelWorkbook.xls;HDR=YES','SELECT * FROM [myExcelWorksheet$]')

Names are the same as above. myFullFilePath is the complete and full path of where the workbook is located. myExcelWorkbook.xls is the filename of the workbook itself.

Background Image in GridView

I found out how Antoine displays this in his GridViews in the CSP database. It is a background image for that GridView. You don't see it if there are records to display, but do if there are none.

I was racking my brain how he was doing this because I found the EmptyDataText property to show when there is no data; he was not using this. In Larry's Report page, I wrote a stored procedure to show "No Records." I don't need this now.

Display HTML code in a Blog

I found a couple of site that allow me to convert HTML tags so that I can display code here. These are as follows:

http://www.felgall.com/htmlt47.htm

or

http://www.plus2net.com/html_tutorial/tags-page.php

Just copy, paste, and convert. Recopy the result and paste into the blog. Preview before publishing to ensure layout is correct.

Customize GridView

Microsoft has an fairly simple example of how to customize the GridView when displaying database entries. I turned off the Autogenerate columns because it automatically appends the same columns on my GridView.

I want to reformat the currency to two decimal places and the dates to date only, not time. I have yet to get to that point. But here is my code:

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" DataSourceID="SqlDataSource1" EmptyDataText="No Data Available." AutoGenerateColumns="false" PageSize="20">
     <Columns>
          <asp:BoundField DataField="Bill_Number" HeaderText="Bill Number" />
          <asp:BoundField DataField="RP" HeaderText="Responsible Party" />
          <asp:BoundField DataField="LC" HeaderText="Ledger Code" />
          <asp:BoundField DataField="Amount" HeaderText="Bill Amount" />
          <asp:BoundField DataField="DateRcvdDOL" HeaderText="Date Rcvd" />
          <asp:BoundField DataField="DatePmtSentDEC" HeaderText="Date Payment Sent to DEC" />
          <%--<asp:BoundField DataField="Comments" HeaderText="Comments" />
          <asp:BoundField DataField="DateSentRP" HeaderText="Date Sent to RP" />
          <asp:BoundField DataField="Check" HeaderText="Check Number" />
          <asp:BoundField DataField="AmountPaid" HeaderText="Amount Paid" />
     </Columns>
</asp:GridView>

Ajax Hover Menu

I have been using a menu format that is used in the CSP application. My thought is if my application is stand-alone, I want it to have a similar look and feel as the CSP application. If it becomes part of the CSP or another application, this would probably facilitate an easier assemblance into an existing database.

CSP is the web application Antione and Coleen have developed to track contaminated sites here at ADEC. It is a very nice program / database.

One of the problems I have run into placing this menu on the right like CSP, is that some of my gridviews of data run off the page. This puts the menu off screen. My initial thought is to use Ajax to put this menu in a drop down that overlays the grid view. It worked after some tweaking.

First, I added this to the top of the page just under the <%@ Page ... %>:

• <%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %><%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>

The script manager is needed in the content page:

• <asp:ScriptManager ID="ScriptManager1" runat="server" />

Add the HoverMenuExtender afterwards:

• <cc1:HoverMenuExtender ID="HoverMenuExtender1" runat="server" TargetControlID="MainMenuButton" PopupControlID="PopUpMainMenu" PopDelay="5000" />

Where:

• TargetControlID is the name of the object over which the mouse hovers to pop up the menu.
• PopupControlID is the name of the menu object which will pop up.
• PopDelay is the amount of delay the menu stays visible after the mouse loses focus on the menu.

For the pop up menu, I added this to the bottom of the page within the area of my page so that is on top of any content when displayed:

• <asp:Panel ID="PopUpMainMenu" runat="server">
  <!-- Add the Main Menu buttons -->
  <div style="z-index: 9999;">
  <mm1:MainMenu ID="MMCB" runat="server" />
  </div>
  </asp:Panel>

The defines the menu to display. is the web control I made separately to house the menu. Like the Ajax HoverMenu is registered on the top of the page, so is the control:

• <%@ Register Src="MainMenu.ascx" TagName="MainMenu" TagPrefix="mm1" %>