Navigation

4Guys
ASP.NET
Home
dasBlog

Search

Categories

 
 
 
 
 
 
 
 
 
 
 
 
 
 

On this page

Comparing Records from Two Tables
RegEx for validating email addresses with RegularExpressionValidator
Update records from two tables
Conditional GridView Cell Formatting
Displaying all files in a directory and in a datatable
Using XMLHttpRequest
Clearing a Form In ASP.NET
Google AJAX Search API
Javascript/AJAX Library
Using XP_EXECRESULTSET To Obtain Database Size Information
SQL Web Data Administrator
AJAX-Style Loading Icons

Archive

July, 2008 (4)
June, 2008 (3)
May, 2008 (8)
April, 2008 (21)
March, 2008 (4)
February, 2008 (2)
January, 2008 (8)
December, 2007 (8)
November, 2007 (11)
October, 2007 (5)
September, 2007 (1)
August, 2007 (1)
July, 2007 (11)
June, 2007 (24)

Blogroll

 Tim Mackey

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

RSS 2.0 | Atom 1.0 | CDF

Send mail to the author(s) E-mail

Total Posts: 112
This Year: 50
This Month: 4
This Week: 0
Comments: 0

Sign In

 Monday, July 23, 2007
Comparing Records from Two Tables
Monday, July 23, 2007 10:09:52 AM (Eastern Standard Time, UTC-05:00) ( )

I picked this up from SQLTeam.com and it's come in handy a few times.

CREATE PROCEDURE CompareTables(

@table1 varchar(100), 
@table2 Varchar(100),
@T1ColumnList varchar(1000),
@T2ColumnList varchar(1000) = '')
AS

-- Table1, Table2 are the tables or views to compare.
-- T1ColumnList is the list of columns to compare, from table1.
-- Just list them comma-separated, like in a GROUP BY clause.
-- If T2ColumnList is not specified, it is assumed to be the same
-- as T1ColumnList. Otherwise, list the columns of Table2 in
-- the same order as the columns in table1 that you wish to compare.
--
-- The result is all records from either table that do NOT match
-- the other table, along with which table the record is from.

declare @SQL varchar(8000);

IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList

set @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList +
' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' +
@t2ColumnList + ' FROM ' + @Table2

set @SQL = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList +
' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList +
' HAVING COUNT(*) = 1'

exec ( @SQL)

Comments [0] | Trackback | # 
 Thursday, July 19, 2007
RegEx for validating email addresses with RegularExpressionValidator
Thursday, July 19, 2007 1:34:44 PM (Eastern Standard Time, UTC-05:00) ( )

ASP.NET comes with a RegularExpressionValidator which provides for a lot of flexbility when validating user input.  This is the best regular expression for emails I've seen. It tends to error on the side of accepting bad emails rather then rejecting good ones.

 

^([a-zA-Z0-9_'+*$%\^&!\.\-])+\@(([a-zA-Z0-9\-])+\.)+([a-zA-Z0-9:]{2,4})+$

 

Here is an example:

E-mail: <asp:textbox id="textbox1" runat="server"/>
<asp:RegularExpressionValidator id="valRegEx" runat="server"
ControlToValidate="textbox1"
ValidationExpression="^([a-zA-Z0-9_'+*$%\^&!\.\-])+\@(([a-zA-Z0-9\-])+\.)+([a-zA-Z0-9:]{2,4})+$"
ErrorMessage="* Your entry is not a valid e-mail address."
display="dynamic">*
</asp:RegularExpressionValidator>

Comments [0] | Trackback | # 
 Wednesday, July 11, 2007
Update records from two tables
Wednesday, July 11, 2007 3:17:55 PM (Eastern Standard Time, UTC-05:00) ( )

UPDATE table1
SET col1 = t2.col1
FROM table1 AS t1
JOIN table2 AS t2
ON t1.col2 = t2.col2

Comments [0] | Trackback | # 
 Monday, July 09, 2007
Conditional GridView Cell Formatting
Monday, July 09, 2007 8:31:48 AM (Eastern Standard Time, UTC-05:00) ( )

This code sample shows how to either show or make invisible, a checkbox in each row of the Gridview, along with making text conditional, based on certain criteria. In this case, if the Postal code starts with a non-numeric character, we change it to "Alt Text", and we set the Visible property of the checkbox in that row to "False"

<%@ Import Namespace="System.Drawing" %>
<script language="VB" Runat="server">
Sub DataCheck(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
Dim chkExp as CheckBox
If e.Row.RowType = DataControlRowType.DataRow Then
Dim sCode as String=e.Row.Cells(7).text
If Not isNumeric(sCode.Substring(1,1)) Then
e.Row.Cells(7).text="<i style='color:red'>(Alt Text)</i>"
        chkExp= CType(e.row.FindControl("ck1"), Checkbox)
        chkExp.Visible="False"
End If
End If
End Sub    
</script>
<html>
<head runat="server">
    <title>Conditional GridView Cell Formatting</title>
</head>
<body>
<form id="form1" Runat="server">
<asp:GridView OnRowDataBound="DataCheck" AutoGenerateColumns="False"
    DataSourceID="SqlDataSource1" ID="GridView2" runat="Server">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:CheckBox ID="ck1" runat="server"></asp:CheckBox>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="FirstName" HeaderText="First" />
<asp:BoundField DataField="LastName" HeaderText="Last" />
<asp:BoundField DataField="Title" HeaderText="Title" />
<asp:BoundField DataField="Address" HeaderText="Address" />
<asp:BoundField DataField="City" HeaderText="City" />
<asp:BoundField DataField="Region" HeaderText="Region" />
<asp:BoundField DataField="PostalCode" HeaderText="PostalCode" />
<asp:BoundField DataField="country" HeaderText="country" />
</Columns>
<HeaderStyle BackColor="Blue" Font-Bold="True" ForeColor="White" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="Select FirstName, LastName, Title, Address, City, Region, PostalCode, country from Employees">
</asp:SqlDataSource>
        </form>
    </body>
</html>

Comments [0] | Trackback | # 
Displaying all files in a directory and in a datatable
Monday, July 09, 2007 8:27:20 AM (Eastern Standard Time, UTC-05:00) ( )

This sample shows how to create a datatable manually, adding files from a directory (using System.IO), and then binding the datatable to a Gridview

<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Data" %>
<script language="VB" Runat="server">
Dim dt As DataTable
Dim dr As DataRow
    Sub Page_Load(Source as Object, E as EventArgs)
dt = GetFiles()
gvFiles.DataSource = dt
gvFiles.DataBind()
    End Sub
Public Function GetFiles() As DataTable
Dim strFilePath = Server.MapPath("\test\")
Dim DirInfo As New DirectoryInfo(strFilePath)
Dim Files As FileInfo() = DirInfo.GetFiles()

Dim myTable As New DataTable
myTable.Columns.Add("File Name", Type.GetType("System.String"))
myTable.Columns.Add("Last Write Time", Type.GetType("System.String"))
Dim i As Integer
For i = 0 To Files.Length - 1
Dim Filename As String = Files(i).Name
Dim sWrite As String = Files(i).LastWriteTime
Dim myrow As DataRow
' create new row
myrow = myTable.NewRow
' add files/write times into cells
myrow("File Name") = Filename
myrow("Last Write Time") = sWrite
myTable.Rows.Add(myrow)
Next
Return myTable
End Function    
</script>
<html>
    <head runat="server">        
        <title>Displaying all files in a directory and in a datatable</title>
    </head>
    <body>
<asp:GridView ID="gvFiles" runat="server" Width="432px">
</asp:GridView>
        </form>
    </body>
</html>

Comments [0] | Trackback | # 
 Tuesday, July 03, 2007
Using XMLHttpRequest
Tuesday, July 03, 2007 12:48:50 PM (Eastern Standard Time, UTC-05:00) ( )

A coworker passed along this link from IBM which provides a nice series of tutorials on AJAX. The example below is a classic example of how to use the XMLHTTPRequest object while checking for different browser types and then using the server's response for something.

<script language="javascript" type="text/javascript">
var request = false;
try {
request = new XMLHttpRequest();
} catch (trymicrosoft) {
try {
request = new ActiveXObject("Msxml2.XMLHTTP");
} catch (othermicrosoft) {
try {
request = new ActiveXObject("Microsoft.XMLHTTP");
} catch (failed) {
request = false;
}
}
}

if (!request)
alert("Error initializing XMLHttpRequest!");

function getCustomerInfo() {
var phone = document.getElementById("phone").value;
var url = "/cgi-local/lookupCustomer.php?phone=" + escape(phone);
request.open("GET", url, true);
request.onreadystatechange = updatePage;
request.send(null);
}

function updatePage() {
if (request.readyState == 4) {
if (request.status == 200) {
var response = request.responseText.split("|");
document.getElementById("order").value = response[0];
document.getElementById("address").innerHTML = response[1].replace(/\n/g, "<br />");
} else if (request.status == 404) {
alert ("Requested URL is not found.");
} else if (request.status == 403) {
alert("Access denied.");
} else
alert("status is " + request.status);
}
}

</script>

<body>
<p><img src="breakneck-logo_4c.gif" alt="Break Neck Pizza" /></p>
<form action="POST">
<p>Enter your phone number:
<input type="text" size="14" name="phone" id="phone"
onChange="getCustomerInfo();" />
</p>
<p>Your order will be delivered to:</p>
<div id="address"></div>
<p>Type your order in here:</p>
<p><textarea name="order" rows="6" cols="50" id="order"></textarea></p>
<p><input type="submit" value="Order Pizza" id="submit" /></p>
</form>
</body>

Comments [0] | Trackback | # 
Clearing a Form In ASP.NET
Tuesday, July 03, 2007 8:07:15 AM (Eastern Standard Time, UTC-05:00) ( )

Clearing a form of all entries or values is a common task in HTML but seemed to be a bit more challenging in ASP.NET.  It seemed the only way to do this was by posting the page back to itself using server.transfer("page.aspx")

I just stumbled across this method new to ASP.NET 2.0 using OnClientClick and it works great.

<asp:Button ID="btnReset" CssClass="smalltext" OnClientClick="Form1.reset();return false;" runat="server" Text="Clear Form" />

HTML version:

<input type="reset" value="Clear Form" name="Reset" onclick="ResetControls(this.form)">

Comments [0] | Trackback | # 
 Monday, July 02, 2007
Google AJAX Search API
Monday, July 02, 2007 11:48:54 AM (Eastern Standard Time, UTC-05:00) ( )

Google makes a cool API that utilizes AJAX to which allows developers to add a Web or local search to their sites relatively easily. 

I created an example of it here: http://www.stonecoastwebdesign.com/search/example.htm

Comments [0] | Trackback | # 
Javascript/AJAX Library
Monday, July 02, 2007 9:37:01 AM (Eastern Standard Time, UTC-05:00) ( )

A co-worker passed this along to me which looks pretty cool

http://extjs.com/

Comments [0] | Trackback | # 
 Tuesday, June 26, 2007
Using XP_EXECRESULTSET To Obtain Database Size Information
Tuesday, June 26, 2007 10:01:39 PM (Eastern Standard Time, UTC-05:00) (  |  )

Came across this article and code which provides a script for obtaining the size of every database on your SQL Server 2000 box. Very nice...

http://www.sqlservercentral.com/columnists/jtshyman/usingxp_execresultsettoobtaindatabasesizeinformati.asp

Comments [0] | Trackback | # 
SQL Web Data Administrator
Tuesday, June 26, 2007 9:56:40 PM (Eastern Standard Time, UTC-05:00) ( )

I came across this nifty web-based administrator for SQL Server from Codeplex.   I haven't tried it yet but it looks promising and it's open source.

 

 

Comments [0] | Trackback | # 
 Monday, June 25, 2007
AJAX-Style Loading Icons
Monday, June 25, 2007 11:52:42 AM (Eastern Standard Time, UTC-05:00) ( )

Some useful icons for displaying those "loading" messages used in AJAX-enabled applications.

 

Comments [0] | Trackback | #