Code to create a DataSet

Published on by matt Category: ASP.Net, C#

Use this code to create a table and place it in a DataSet.

// Create a new DataTable.
System.Data.DataTable table = new DataTable(“Details”);
// Declare variables for DataColumn and DataRow objects.
DataColumn column;
DataRow row;
// Create new DataColumn, set DataType,
// ColumnName and add to DataTable.
column = new DataColumn();
column.DataType = System.Type.GetType(“System.Int32”);
column.ColumnName = “fk_agent_id”;
column.ReadOnly = false;
column.Unique = true;
// Add the Column to the DataColumnCollection.
table.Columns.Add(column);

// Create second column.
column = new DataColumn();
column.DataType = System.Type.GetType(“System.String”);
column.ColumnName = “agent_name”;
column.AutoIncrement = false;
column.Caption = “agent_name”;
column.ReadOnly = false;
column.Unique = false;
// Add the column to the table.
table.Columns.Add(column);

// Make the ID column the primary key column.
DataColumn[] PrimaryKeyColumns = new DataColumn[1];
PrimaryKeyColumns[0] = table.Columns[“fk_agent_id”];
table.PrimaryKey = PrimaryKeyColumns;
// Instantiate the DataSet variable.
dataSet = new DataSet();
// Add the new DataTable to the DataSet.
dataSet.Tables.Add(table);

Code to create a DataRow and add it to a DataSet

Published on by matt Category: ASP.Net, C#

Use this code to create a DataRow from a table in a DataSet and place it in the table in a DataSet.

// Call a method to create the DataSet with a table in it.

DataSet resultData = MakeDetailTable();
DataRow row = resultData.Tables[“Details”].NewRow();
row[“fk_agent_id”] = userInfo.account_id;
row[“agent_name”] = userInfo.lname + “, “ + userInfo.fname;
resultData.Tables[“Details”].Rows.Add(row);

// Use the data in the GridView.
gvReport.DataSource = resultData.Tables[“Details”];
gvReport.DataBind();

ASP On Error Resume Next

Published on by matt Category: Classic ASP

Last night I found a page with an “On Error Resume Next” that has a script error that has been causing problems on one of my servers for months. It took a long time to narrow down the source to a single site but then it didn’t take long to find the page with the problem. Then I found an “On Error Resume Next” line in the code and just wanted to shake my fist at somebody (in a good natured sort of way, of course). The code was really, really bad. But I was so relieved to have found the problem.

 

To clarify how this can be a problem, you don’t want code to continue to execute in a loop when there is an error. For example. This would be bad:

 

On Error Resume Next

Set rs = oConn.Execute(“Select * From Orders”)

If Not rs.Eof Then

                Do While Not rs.Eof

                                Response.Write(“OrderID=” & rs(“OrderID”) & “<br>”)

                                rs.MoveNxt

                Loop

End If

 

Since the error (“rs.MoveNxt”) is the move to the next record it will be stuck on the first record and the end of data will never be reached. Yes the script should timeout but an accumlation of errors can cause memory issues on the server and even hang the web service.

 

Here is the recommended way to use the ASP error handler:

 

On Error Resume Next

               

                … do code here

 

If Err.Number <> 0 Then

                sMessage=”There was an error: “ & Err.Description

                Err.Clear

                On Error Goto 0

End If

 

SQL Server 2005 SSIS Packages

Published on by Zachary Lyons Category: Server Setup

Today I used SSIS packages for the first time. SSIS packages are used by SQL Server Integration Services. There are a couple things I have found about SSIS packages that I want to write about.

I am using an SSIS package to copy data from an Access database to SQL Server. This SSIS package can be executed on a schedule, or from a web browser. I couldn’t figure out how to execute an SSIS package, so I found some excellent instructions on this blog.

  1. I believe you can only run SSIS packages on SQL Server 2005. SQL Server Integration Services doesn’t come with SQL Server Express. 
     
  2. In order to run my SSIS package, I needed to have xp_cmdshell enabled in SQL Server Surface Area Configuration.
     
  3. I can execute the stored procedure from VB or CS code in ASP.Net, but it is more secure to hard-code the command line in a Stored Procedure and call that from the VB or CS code.
     
  4. The command line I use is:

    Declare @SSISPackage varchar(50)
    exec     master..xp_cmdshell ‘dtexec /F “‘ + @SSISPackage + ‘”‘

  5. To run this stored procedure, the user must have access to the mssqlsystemresource database. Thus, as far as I know, you have to log in with the sa account. I use the sa account only on this one page that needs it. Everywhere else on this site cannot access the sa account. If you don’t have privileges, you will get this message:
    The EXECUTE permission was denied on the object ‘xp_cmdshell’, database ‘mssqlsystemresource’, schema ‘sys’.

These are just my notes for SQL Server Integration Services. SSIS allows me to easily perform this import process very easily.

Zachary Lyons

ASP.Net AJAX Animations

Published on by Zachary Lyons Category: AJAX, ASP.Net, JavaScript

Some of the best resources for the animation framework that comes with the ASP.Net AJAX Control Toolkit can be found at the live toolkit site at this Sample Site. I often look at the Animation Reference section of this site.

While adding animations to ASP.Net pages, it is sometimes necessary to call animations directly from Javascript code. This can be achieved relatively easily using the great instructions at this MSDN blog.

Combined with the Animation Reference on http://asp.net/ajax website, any animation can be called directly from Javascript. If you can’t use the <OnLoad> animation event handler in the AnimationExtender control, one way to play animations on the page load is to use ClientScriptManager.RegisterStartupScript to create a function with the animation code, and call that function on the next line.

Page.ClientScript.RegisterStartupScript(Me.GetType, “PlayLoadAnimation”, “function playNotificationAnimation(){ AjaxControlToolkit.Animation.ColorAnimation.play($get(“”divNotification””) , 1 , 30 , “”style”” , “”backgroundColor”” , “”#181840″” , “”#E9E8FF””); }”, True)

Page.ClientScript.RegisterStartupScript(Me.GetType, “LoadAnimationFunction”, “playNotificationAnimation();”, True)

Happy Animating!
Zachary Lyons

Server.ScriptTimeout Not Working

Published on by matt Category: ASP.Net, Server Setup

I had a file upload page that gave an HttpException with message “Request timed out”. Natrually, I set the Server.ScriptTimeout to 360. But I still got the error rmessage. To solve this I added the following to the web.config file:

<httpRuntime
executionTimeout=“360“
maxRequestLength=“41000“ />

The maxRequestLength did not solve the problem by itself. The executionTimeout was needed as well.

 

Setting Page Meta Information at Runtime

Published on by Zachary Lyons Category: ASP.Net, HTML

I have a MasterPage and some child pages. The content for the child pages comes from a database. I have a form in the Admin section of my site for editing pages. You can set the Page Title, the Meta Description, Keywords, and page Content.

Accessing the title is easy, because the title of the content pages (in the @Page directive) applies to the MasterPage. Getting to the Meta information is a little trickier.

Thanks to Chris Garrett for his tip at http://aspalliance.com/411_Setting_the_ASPNET_Page_Title_and_Meta_Tags.

First, in the content pages, I have to add

<%@ MasterType VirtualPath=”~/MasterPage.master” %>

to the child pages, right under the @Page directive. With this, I can type “Master.” in the codebehind and access all the public properties of the MasterPage.

Second, on the MasterPage, I have to add two Public Properties. One for the Meta Description, and one for the Meta Keywords.

Public MetaDescription As String = “”
Public MetaKeywords As String = “”

Third, in the HTML code for the page, I make the two Meta tags server controls. I always set the Head tag to runat=”server”.

<head runat=”server” id=”Head1″>
<title />
<meta id=”Description” runat=”server” content=”description” name=”description” />
<meta id=”Keywords” runat=”server” content=”keys” name=”keywords” />

Fourth, on the Page_Load of the MasterPage, I can set the Title and Keywords at runtime.

Description.Attributes(“content”) = “Demo Description”
Keywords.Attributes(“content”) = “Keyword 1, Keyword 2”

I am now pulling the Meta information directly from the database at runtime, and setting Page.Title, Master.Description, and Master.Keywords from the Load event of the Child page.

In the Load event of the MasterPage, I can add some simple logic to give all pages a meta description.

If MetaDescription.Length = 0 Then
    Description.Attributes(“content”) = “My Generic Description. This description appears on all pages where the MetaDescription in the database is empty.”
Else
    Description.Attributes(“content”) = MetaDescription
End If

If MetaKeywords.Length = 0 Then
   Keywords.Attributes(“content”) = “Generic, Default, Consistent, Normal”
Else
    Description.Attributes(“content”) = MetaKeywords
End If

This is how I use MasterPages, and it works pretty well!

Zachary Lyons

Outlook Stationary

Published on by matt Category: HTML

To create stationary for Outlook follow these steps:

  1. Create stationary as a local .htm page. Put the images in the same folder.
  2. Copy the .htm page and images into one of these folders:
    Vista – C:\Users\<logon user>\AppData\Roaming\Microsoft\Stationery
    XP – C:\Documents and Settings\<logon user>\Application Data\Microsoft\Stationery

Then your stationary will be listed as a theme under the Signatures and Stationary settings in Outlook.

This page has a list of various Outlook file locations: http://www.slipstick.com/config/backup2007.asp

 

SQL Like with Parameter

Published on by matt Category: SQL Server

I have a stored procedure that will do a search on records based on a value passed in. To use a parameter value with the SQL ‘Like’ command use this format:

   SELECT * FROM clients WHERE FirstName LIKE ‘%’ + @sWhere + ‘%’ OR LastName LIKE ‘%’ + @sWhere + ‘%’