Jim's blog | .Net and SQL Server blog

Using the Ajax Tabs Control as a Menu

by JBrooks 24. October 2010 10:53

I have a regular menu for my site and I wanted to use the Ajax Tabs Control as a submenu when the user was doing point-of-sale (ordering cards that describe the wine).  So when the user was on POS  on the main menu (black background) they would see the submenu as shown below.

Menu

To make this, the first thing I did was create a new web user control and add the Ajax Tabs Control.  This user control is called wcPOSMenu and will be place on each of the 3 ASP.Net pages involved. You can see there isn’t much to the markup below.

 
<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="wcPOSMenu.ascx.cs" 
Inherits="ToutonWeb.wcPOSMenu" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<asp:TabContainer ID="TabContainerPOS" runat="server" AutoPostBack="true" BorderWidth="0px"
    Width="926px" Height="12px" CssClass="ajax__tab_xp" ActiveTabIndex="0" 
    OnActiveTabChanged="TabContainerPOS_ActiveTabChanged">
    <asp:TabPanel ID="tpSelect" runat="server" HeaderText="Select POS" BorderWidth="0px">
        <ContentTemplate>
            Enter Description or ItemNo to Search for and Hit Enter. Click a Column Heading
            to Sort by That Column.
        </ContentTemplate>
    </asp:TabPanel>
    <asp:TabPanel ID="tpEdit" runat="server" HeaderText="Edit SKUs" BorderWidth="0px">
        <ContentTemplate>
            Enter a SKU to Add or Edit.
        </ContentTemplate>
    </asp:TabPanel>
    <asp:TabPanel ID="tpSend" runat="server" HeaderText="Send POS" BorderWidth="0px">
        <ContentTemplate>
            Send POS.
        </ContentTemplate>
    </asp:TabPanel>
</asp:TabContainer>

One thing to note is that I couldn’t quickly figure out how to eliminate the tab’s containers border so I just made it a height of 12px and used that area as a subtitle to the rest of the content.

Below is the code behind, there isn’t much to this either.  First you have a user control’s property TabIndex that can be set on each of the parent pages.  So for my example the page for “Edit SKUs” will have this user control on it and it’s TabIndex property will be set to the value of 1. 

The page Page_Load event just uses that property to set the current tab.

The TabContainerPOS_ActiveTabChanged just redirects the user to the correct page whenever they change the tab.  To the user is appears they are selecting a menu option, but to the tab control they are actually changing the active tab.

 

 
using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
 
namespace ToutonWeb
{
    public partial class wcPOSMenu : System.Web.UI.UserControl
    {
        private int _tabIndex;
        public int TabIndex
        {
            get { return _tabIndex; }
            set { _tabIndex = value; }
        }
 
 
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
                this.TabContainerPOS.ActiveTabIndex = _tabIndex;
        
        }
 
 
        protected void TabContainerPOS_ActiveTabChanged(object sender, EventArgs e)
        {
            switch (TabContainerPOS.ActiveTabIndex)
            {
                case 0:
                    Response.Redirect("~/frmPOS.aspx");
                    break;
                case 1:
                    Response.Redirect("~/frmPOSEdit.aspx");
                    break;
 
                case 2:
                    Response.Redirect("~/frmPOSSend.aspx");
                    break;
 
            }
 
        }
    }
}

On each of the parent pages I just need to add 2 lines.  The first is to register the user control,  line 5 below. The second is line 9 below.  Note the TabIndex property of the user control being set in line 9.

   1: <%@ Page Title="" Language="C#" MasterPageFile="~/Site1.Master" AutoEventWireup="true"
   2:     CodeBehind="frmPOSEdit.aspx.cs" Inherits="ToutonWeb.frmPOSEdit" %>
   3:  
   4: <%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp"%>
   5: <%@ Register Src="wcPOSMenu.ascx" TagName="wcPOSMenu" TagPrefix="uc1" %>
   6: <asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
   7: </asp:Content>
   8: <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
   9:     <uc1:wcPOSMenu ID="wcPOSMenu1" runat="server" TabIndex="1" />

That is it.

Tags:

ASP.Net | Development

Passing IDs to Stored Procs Using XML

by jbrooks 15. October 2010 10:58

In the past we would use a Varchar to pass a comma delimited list of IDs to a stored proc. We would then build a big varchar string of the full SQL and run the dynamic SQL.  Starting with SQL Server 2005 you can pass the stored proc XML and do your select without using dynamic SQL.

The stored proc would look something like:

 
create proc [dbo].[getLocationTypes](@locationIds XML,
@typeIds XML=null)
as  
begin  
set nocount on  
 
   
SELECT locationId, typeId
FROM xrefLocationTypes 
WHERE locationId 
IN (SELECT Item.value('.', 'int' )
FROM @locationIDs.nodes('IDList/ID') AS x(Item))
AND (typeId IN
 (SELECT Item.value('.', 'int' )
FROM @typeIds.nodes('IDList/ID') AS x(Item)))
ORDER BY 1, 2
  
end  
 
 
 
And then you would call this like:
 
 
EXECUTE dbo.getLocationTypes '<IDList><ID>1</ID><ID>3</ID></IDList>', 
'<IDList><ID>200</ID><ID>300</ID><ID>400</ID></IDList>'    
 

Tags:

SQL | Development

Changing HTML Attributes on ASP.NET Pages in the Code Behind

by JBrooks 24. September 2010 11:40

Not many developers realize that you can change a plain HTML object’s attributes in the code behind without having to convert it into an ASP.Net control.

So if I have a checkbox like this:

<input type="checkbox" id="cbShowDef" name="cbShowDef"  onclick="javascript: show()" />

I can hide this in the code behind with:

((System.Web.UI.HtmlControls.HtmlInputCheckBox) Form.FindControl("cbShowDef")).Style.Add("display","none");

Tags:

ASP.Net | Development

Delete Spam Users from YetAnotherForum

by JBrooks 5. September 2010 14:21

 

I use YetAnotherForum (YAF) for my Forums here.   Like others, I get hundreds of spam users created each month.  I wrote the following SQL to delete any user that hasn’t visited in the last day, never posted or emailed a message, isn’t the last user on a forum and isn’t in the Active list.
 
-- first delete the UserGroup rows.
DELETE FROM dbo.yaf_UserGroup
WHERE UserID in
(
SELECT u.UserID
FROM dbo.yaf_User u
WHERE u.LastVisit < getdate() -1
AND u.NumPosts = 0)
AND UserID NOT IN
(
SELECT a.UserID
FROM dbo.yaf_Active a)
AND UserID NOT IN
(
SELECT f.LastUserID
FROM dbo.yaf_Forum f
WHERE f.LastUserID IS NOT NULL)
AND UserID NOT IN
(
SELECT pm.FromUserID
FROM dbo.yaf_PMessage pm)
AND UserID NOT IN
(
SELECT m.UserId
FROM dbo.yaf_Message m)
 
--
-- now delete the User rows.
--

DELETE FROM dbo.yaf_User
WHERE LastVisit < getdate() -1
AND NumPosts = 0
AND UserID NOT IN
(
SELECT a.UserID
FROM dbo.yaf_Active a)
AND UserID NOT IN
(
SELECT f.LastUserID
FROM dbo.yaf_Forum f
WHERE f.LastUserID IS NOT NULL)
AND UserID NOT IN
(
SELECT pm.FromUserID
FROM dbo.yaf_PMessage pm)
AND UserID NOT IN
(SELECT m.UserId
FROM dbo.yaf_Message m)

Tags:

SQL

Displaying a Long Description Column in a GridView

by JBrooks 6. July 2010 20:05

A lot of times you have something like a description column and you want to display it in a GridView.  But since for some of the rows the description column can be very long, it doesn’t always work very well. One solution is where you set the TD’s attribute NOWRAP, then the column runs off to the right for as long as is needed.  The solution I sometimes employ is the following: 

First, here is my sample table:

CREATE TABLE Categories
(CategoryName varchar(20),
Description varchar(1000))
 
And here is the SQL that is going to bind to my GridView:
 
SELECT c.CategoryName,
CASE WHEN LEN(c.Description) > 30
THEN SUBSTRING(c.Description,1,27)+'...'
ELSE c.Description END AS LineDescription,
c.Description AS FullDescription
FROM dbo.Categories c
ORDER BY c.CategoryName

Notice that the Description column is in there twice. Once where it maxes out at 30 characters (the LineDescription column), and the second one that contains the full text (the FullDescription column.) This brings back a table that looks like the following:

CategoryName LineDescription FullDescription
Beverages Soft drinks, coffees, teas,... Soft drinks, coffees, teas, beers, and ales
Condiments Sweet and savory sauces, re... Sweet and savory sauces, relishes, spreads, and seasonings
Confections Desserts, candies, and swee... Desserts, candies, and sweet breads
Dairy Products Cheeses Cheeses
Grains/Cereals Breads, crackers, pasta, an... Breads, crackers, pasta, and cereal
Meat/Poultry Prepared meats Prepared meats
Produce Dried fruit and bean curd Dried fruit and bean curd
Seafood Seaweed and fish Seaweed and fish

 

So to bind this to the GridView we bind the first two columns only.  The first GridView column is bound to the CategoryName data column and the second one is bound to the LineDescription data column with the heading title of “Description”.  On the second GridView column we also click the “Convert this field into a TemplateField” as shown below.

GridViewColumns

 

We save this and then edit the templates for the GridView columns.  For the Description column we edit the ItemTemplate and we bind the ToolTip to the FullDescription data column.

ToolTipBinding

 

Now we have the GridView column that is titled “Description” bound to the LineDescription data column and the tooltip is bound to the FullDescription data column.  We can run this and  see how it works:

GridViewWithToolTip

So now we have a short description in our grid, but the user can easily see the full description in the ToolTip.

Tags:

ASP.Net | GridView | Development

Quickly Finding Missing Objects in Two SQL Server 2005 Databases

by JBrooks 5. July 2010 09:22

Real quick I wanted to know what tables and stored procs were in my ReleaseManagerTest database and not in my ReleaseManager database.  So here is the SQL for that.

select min(DB) as DB, name, type
from
(
select 'ReleaseManagerTest' as DB, name, type 
from ReleaseManagerTest.sys.objects 
 
union all
 
select 'ReleaseManager' as DB, name, type
from ReleaseManager.sys.objects
) A
group by name, type
having count(*) <> 2
 

Tags:

Development | SQL

Finding Objects in SQL Server During Development

by JBrooks 13. March 2010 06:57

I kept writing the same SQL over and over while developing to find the names of stored procs or to see what objects I had recently added.  So I created a quick stored proc that I now have in my tool box.

create proc obj(@name varchar(50)=null, @type char(1)=null)
as
begin
set nocount on
 
select o.name, 
o.type, 
convert(varchar(10),o.create_date,1) created, 
convert(varchar(10),o.modify_date,1) modified
from sys.objects o
where (@name is null or o.name like @name)
and (@type is null or o.type = @type)
and o.is_ms_shipped = 0
order by o.modify_date desc
 
end
go
 
 

So if I just do:
obj ‘get%’, ‘P’

I’ll get

name type created modified
GetProjectsTree P 7/28/2008 2/27/2010
GetProjectOutputVariables P 10/10/2009 2/26/2010
GetXrefRequestIssue P 9/9/2008 2/11/2010
GetProjectOutputVariable P 2/11/2010 2/11/2010
GetRequestsItemsOverlap P 1/6/2010 2/9/2010
GetControl P 2/6/2010 2/6/2010
getSourceIdFromPath P 2/5/2010 2/5/2010
GetRequestedFiles P 10/14/2008 2/2/2010
GetRequestsForBranch P 12/30/2008 1/15/2010
GetRequestWithChildren P 7/28/2008 1/1/2010
GetRequestsItems P 4/28/2009 1/1/2010

and if I call obj with no parameters I will get a list of all my objects with the most recently modified objects first. This works with SQL 2005 and above.

Tags:

SQL

Unhide Exceptions Hidden By AJAX

by JBrooks 3. February 2010 09:35

If you work with AJAX for any length of time you will find that no matter what the exception thrown in the code behind the message displayed to the user is “Exception has been thrown by the target of an invocation.”  Not very useful.

image

My approach to debugging the exception had been to comment out the UpdatePanel, recreate the exception and see the real error.  But this doesn’t work at all when the code is deployed to the end-user.

So the solution is to capture the AsyncPostBackError event of the ScriptManager as shown here:

image

Then in the  code for this event (code behind) we can change the AsyncPostBackErrorMessage to the actual error message in the exception.

protected void ScriptManager1_AsyncPostBackError(object sender,AsyncPostBackErrorEventArgs e)
{
    if (e.Exception != null && e.Exception.InnerException != null)
    {
        ScriptManager1.AsyncPostBackErrorMessage = e.Exception.InnerException.Message;
        cApp.LogExceptionNoThrow(e.Exception.InnerException);
    }
}

Note that I also log the exception which includes the stack trace so I can later research the problem. 

One further enhancement would be to replace the alert box with a label on the page.  To do this I first wire up an event in JavaScript to capture the return from an Ajax call.  So here I am registering the JavaScript function “endRequest” to be called at the end of an AJAX request.

function pageLoad(sender, args) {
    Sys.WebForms.PageRequestManager.getInstance().add_endRequest(endRequest);
}

And in my endRequest function I set the label and turns off the alert box.

function endRequest(sender, args) {

    if (args.get_error() != undefined) {

        var lbl = $get('<%= this.lblMessage.ClientID %>');
        lbl.innerHTML = args.get_error().message;
        lbl.style.color = 'Red'

        // This will stop the alert box from displaying.
        args.set_errorHandled(true);

        // go to the top so they can see the error message.
        window.scroll(0, 0);

    }
}

Note that the lblMessage label control needs to be inside the UpdatePanel so that it is cleared on the next PostBack. 

So now when I get an exception it displays the real error to the user in a label with red text, something like below. 

image 

And the details are logged in my exceptions table.

image

Much better than the default.

Tags:

ASP.Net

Forgotten Windows Password Recovery and the "no tables found" error.

by JBrooks 4. January 2010 17:59

A friend of mine brought over his laptop because he couldn't remember his new Windows Vista login password. I said I would work on it and worst case I would have to clear everything and reinstall Vista.

Googling around I found that there were tools for clearing the password. I downloaded and burned to a CD the tool here: here at pogostick.net and later Ophcrack found here. Both are free if you are just clearing a password.

Obviously, I first cleared all of the BIOS based passwords (he did remember these). Then I tried to run the 2 tools listed above. The first one couldn't mount the hard drive and the second gave the error "no tables found".

So I Googled for this error. Found 1 comment that said something about not having a drive for the controller card... I see that this is Linux, so maybe the drive isn't there for my friends laptop's controller card, after all, it is a cheap eMachine. (I'm not a Linux guy so I'm not even sure how this works.) So I pop the hard drive out of his laptop and put it in my laptop.

I then booted and tried the Ophcrack CD and it worked. Case closed.

Tags:

General

Knowing What to Test When Changing a SQL Server Object

by jbrooks 23. October 2009 12:50

We were changing our linked server and needed to test every page in our website that used that link server.  None of the pages used it directly, but they did call stored procs that would use it or stored procs that would in turn call other stored procs that us it.    I need to find all of the pages that eventually would use it so that we could test them all.  The fact that it could be nested within stored proc calls made it a little more difficult.   Here is how I did it:

Step 1:  A simple stored proc used for searching for text within other stored procs. 

This work for SQL Server 2005

create proc dbo.Find(@search VARCHAR(100) = '') AS  
begin  
set nocount on  
  
SET @search = '%' + @search + '%'  
  
select object_name(o.object_id) Object, o.type
from sys.sql_modules m  
join sys.objects o on m.object_id = o.object_id  
where m.definition like @search  
order by 1  
  
end  

Step 2: Make a working table to store you work as you process the stored procs.

create table spDoc
(parentObj varchar(500),
obj varchar(500),
[type] varchar(3),
level int)

You will be storing the object name (stored proc, function, or trigger) in the column “obj”.  You will be storing the calling stored proc in the “parentObj” column. 

Step 3: Create a stored proc to process your search.

create proc doc(@obj varchar(256))
as
begin
set nocount on
declare @level int


if exists (select 1 from spDoc where parentObj = @obj)
    return  -- already done.

select @level = isnull(level,0)+1
from spDoc
where obj = @obj

if @level is null
    set @level = 1


--Find all of the objects that have the text of the current object.
insert into spDoc
(obj, [type])
exec Find @obj

-- The @obj is the parent of the rows just created.
update spDoc
set parentObj = @obj,
level = @level
where parentObj is null

--Delete where they are both the same.
delete from spDoc
where parentObj = @obj
and obj = @obj


--Loop thru each of the rows just created
--and recursively call this function.
DECLARE @currentObj varchar(256)

DECLARE @getObjs CURSOR

SET @getObjs = CURSOR FOR
select obj
from spDoc
where parentObj = @obj


OPEN @getObjs

FETCH NEXT
FROM @getObjs INTO @currentObj

WHILE @@FETCH_STATUS = 0
BEGIN
    exec doc @currentObj  -- recursive call

    FETCH NEXT
    FROM @getObjs INTO @currentObj
END

CLOSE @getObjs
DEALLOCATE @getObjs

end

If you were trying to document the object called “MyLink” you would call it like:

   doc MyLink

                          

Step 4: Get your results.

Once the processing is done you only need a listing of the parent objects and this is the list that you would use to check your front end code.  

select distinct parentObj
from spDoc
order by 1

 

And that is all there is to it.

Tags:

SQL

One Script

OneScript Continuous Integration for you database Scripts in version control are automatically combined into a release script.

RecentPosts