Scroll after postback

Situation: Button inside of a GridView fires the Command event and needs to scroll to the bottom of the page.

Solution:  Use an anchor tag, then use ClientScript.RegisterStartupScript on the end of the event method to scroll to it.

Credit goes to http://forums.asp.net/t/1311972.aspx/1

SQL string splitter

I really prefer to give credit for these types of things, but I don't remember where I found it :(

Basic syntax... select dbo.FN_STRING_HELPER_SPLIT('My name is James', ' ', 4) will return 'James'



CREATE FUNCTION [dbo].FN_STRING_HELPER_SPLIT
(
  @strToSplit as varchar(4000),
  @delimiter as varchar(50),
  @columnToReturn as int
)
RETURNS varchar(25)
AS
BEGIN
  -- Declartion of the return variable
  DECLARE @i as int
  DECLARE @occurrences as int
  DECLARE @delimiterIndex as int
  DECLARE @actSplitValue as varchar(4000)
 
  -- T-SQL statements to compute the return value here
  SET @i = 1
  SET @delimiterIndex = 1
 
  WHILE @i <= @columnToReturn
  BEGIN
    SET @delimiterIndex = CHARINDEX(@delimiter, @strToSplit)
    -- if not found end while
    SET @actSplitValue = @strToSplit
    IF @delimiterIndex = 0 BEGIN
      BREAK
    END
    SET @actSplitValue = LEFT(@strToSplit, @delimiterIndex - 1)
    -- SUBSTRING(string, von, bis)
    SET @strToSplit = SUBSTRING(@strToSplit ,@delimiterIndex +1, LEN(@strToSplit)- @delimiterIndex)
    SET @i = @i + 1
  END
  -- the result of the function
  RETURN @actSplitValue
END

Rendering a UserControl as string

I was given the task of creating an HTML based email that sent the contents of a webform.  This is where I explored the possibility of rendering an independant usercontrol into a string.

I did some searching and came across code like this:

Page pageHolder = new Page();

PrintVersion pv1 = (PrintVersion)pageHolder.LoadControl("PrintVersion.ascx");

pv1.BindData();

pageHolder.Controls.Add(pv1);

StringWriter output = new StringWriter();

HttpContext.Current.Server.Execute(pageHolder, output, false);

return output.ToString();

I was getting errors like:

System.Web.HttpException: Error executing child request for handler System.Web.UI.Page'.

System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown.

System.Web.HttpException: Control 'ctl00_listItems' of type 'Repeater' must be placed inside a form tag with runat=server.

I stumbled on this article:

http://deanhume.com/Home/BlogPost/rendering-a-usercontrol-as-a-string-for-webservices/13

It suggested overriding a method (notice no call to the base method)

public class PageOverride : System.Web.UI.Page

{

   public override void VerifyRenderingInServerForm(System.Web.UI.Control control) {}

}

Worked perfectly!

Panel with a Visible property that won't update

What's wrong with this pseudo-code?

Panel p = gridItems.Rows[0].FindControl("pnlHiddenByDefault");
//do some other random stuff
gridItems.DataBind();
//do more random stuff
p.Visible = true; //FAIL

The code will execute fine but even after setting the Visible property to true, a QuickWatch will show the property is still false.

The fix:

Panel p = gridItems.Rows[0].FindControl("pnlHiddenByDefault");
//do some other random stuff
gridItems.DataBind();
//do more random stuff
//get fresh reference after DataBind
p = gridItems.Rows[0].FindControl("pnlHiddenByDefault");

p.Visible = true; //WIN

using SqlDataSource in code

Sometimes there is a need to use a SqlDataSource object in the code...

ASPX:
<asp:SqlDataSource ID="sqlSelectUsers" runat="server"
ConnectionString="<%$ConnectionStrings:MainConnectionString %>"
ProviderName="<%$ ConnectionStrings:MainConnectionString.ProviderName %>"
SelectCommandType="StoredProcedure"
    SelectCommand="spSelectUsers">

<SelectParameters>
<asp:Parameter Name="RequestingUserID" Type="Int16" />
</SelectParameters>
</asp:SqlDataSource>


VB:
sqlSelectUsers.SelectParameters("RequestingUserID").DefaultValue = 2 //hard coded value for demo purposes
Dim dv As DataView = sqlSelectUsers.Select(DataSourceSelectArguments.Empty)
Dim dt As DataTable = dv.ToTable()

This will result in a DataTable for manual consumption.

Another way to parse CSV parameters for values not necessarily in a table

-- http://www.projectdmx.com/tsql/tblnumbers.aspx
CREATE TABLE dbo.Nbrs(n INT NOT NULL IDENTITY) ;
INSERT dbo.Nbrs DEFAULT VALUES ;
WHILE SCOPE_IDENTITY() < 500 INSERT dbo.Nbrs DEFAULT VALUES ;

-- http://www.projectdmx.com/tsql/sqlarrays.aspx

DECLARE @p VARCHAR(50)
SET @p = 'ALFKI,LILAS,PERIC,HUNGC,SAVEA,SPLIR,LONEP,GROSR'

SELECT SUBSTRING( ',' + @p + ',', n + 1, CHARINDEX( ',', ',' + @p + ',', n + 1 ) - n - 1 ) AS "value"
FROM Nbrs
WHERE SUBSTRING( ',' + @p + ',', n, 1 ) = ',' AND n < LEN( ',' + @p + ',' ) ;

drop table Nbrs    

Convert a SQL CSV Parameter into a table

Consider the need to pass in a CSV parameter like this:

DECLARE @UserIDs_CSV VARCHAR(50)
SET @UserIDs_CSV = '1,2,4,5,8,9'

If that CSV parameter happens to correlate to a table, like in this case, Users... Use a query like this to convert the CSV parameter into a temp table...

SELECT UserID
into #SelectedUserIDs
FROM Users
WHERE CHARINDEX(
',' + cast(UserID as varchar(10)) + ',',
',' + @UserIDs_CSV + ','
) > 0

So now just use the temp table as you need for future queries...

select * from #SelectedUserIDs

Credit goes to http://www.projectdmx.com/tsql/sqlarrays.aspx