Stored Procedure Syntax - Get the last day of the month

Obtains the last day of the month:
Select
CAST(
CAST(
CAST(Month(getDate()) AS VARCHAR)
+ '/'
+ CAST(DAY(DATEADD(d, -DAY(DATEADD(m,1,GETDATE())),DATEADD(m,1,GETDATE()))) AS VARCHAR)
+ '/'
+ CAST(YEAR(GETDATE()) AS VARCHAR)
AS VarChar
)
AS smalldatetime
)

An error occurred on the server when processing the URL. Please contact the system administrator

Ok, have a client. Site is running fine. I went to develop locally on a new laptop running IIS 6. Browsed through the site locally, then hit the following message:An error occurred on the server when processing the URL. Please contact the system administrator.
I couldn't figure it out, and why did I see this generic message. To resolve it took some time, but here's what I found:
-----
Programmers Earn extra money on the side
-----

-Open up IIS 6
-Choose your site. I have multiple sites set up on different ports
-Look at the "IIS Home" for this site.
-Double click on the ASP icon
-Expand "Debugging Properties"
-Change "Send errors to browser" = true
-Reload your page.

Here's the new error:
Active Server Pages error 'ASP 0131'

Disallowed Parent Path

/xyz/zzz.asp, line 4

The Include file '../1243.asp' cannot contain '..' to indicate the parent directory.

I tried to make a quick fix with some other options, but it didn't work. Again, it's another setting in IIS 6. Here's the workaround.

Here's the reason: By default, the Parent Paths option is enabled in IIS 5.0, but it is disabled by default in IIS 6.0.

http://support.microsoft.com/kb/332117

Note: I could not recreate the steps below. For me, I had to go back to the ASP Icon and look under the category "Behavior." There I found the option to "Enable Parent Paths."

WORKAROUND
To resolve this problem without changing the application: 1. Click Start, click Administrative Tools, and then click Internet Information Services (IIS) Manager.
2. Double-click your computer name in the left pane, and then double-click Web Sites.
3. Locate the Web site and directory that houses the ASP application.
4. Right-click the application site or directory, and then click Properties.
5. Select Home Directory, and then click Configuration.
6. Click Options, and then click to select the Enable Parent Paths check box.
7. Click OK two times.

Active Server Pages (ASP) zip component

Client is looking to take multiple files such as Word documents, pdf's, etc and allow their clients to select any number of a group of files, and download them. So, because the files may be large, and there may be many, our best solution is to find something that will zip up the selected files, then present the user with the option to save it.

Research:
Look for free first

FREE
XStandard- http://xstandard.com/en/documentation/xzip/. Direct link to component: http://xstandard.com/get/qojtojqw/zip/
This tool lists out all of the commands on the home page. This allowed me to make sure it had what I needed. It looks like only 3 lines to create my zip file.

NOT FREE
WaspZip - http://www.activewebsoftwares.com/productinfo.aspx?ProductID=19
Allows you to zip and email all in one command. No specifics on the commands. I'm assuming you can just zip if necessary. Price is $396. Not worth it compared to the free.

SoftComplex - http://www.softcomplex.com/products/asp_zip_component/ - Gave a lot of information on it's capabilities. Would turn to this one prioro to waspzip. Cost is $49 for single license. Much cheaper than WaspZip

Results: We ended up using XStandard. It was simple and free. We have not had any issues with it and the commands were straight forward

Website Search engine component research

Goal here is to obtain a component in asp or .net (any) that allows us to save the indexing into SQL Server 2005.

Here are the goals:
-SQL Server 2005
-.NET or ASP
-Can schedule updates to the index
-Needs to be on a Windows 2000 or Windows 2003 server
-Will have to run once a week.
-We can choose a directory for file scans. This is not an entire site scan.

Keyoti
http://www.componentsource.com/products/keyoti-search-pro-asp-net/index.html
Price: 391.02 Single Developer license
Does not say where the indexing is stored, but references a directory.
-SQL Server 2005 - NA
-.NET or ASP - .NET
-Can schedule updates to the index
-Needs to be on a Windows 2000 or Windows 2003 server - fine
-Will have to run once a week.
-We can choose a directory for file scans. This is not an entire site scan.

OraDeveloper tools: Doesn't integrate with SQL Server, looks like it focuses on Oracle

FindinSite-MS - .NET - V1.51 : Looks like it places all in a directory

Summary: After spending an hour looking for components, it looks like we are going to use search engine builder professional. This will allow us to put our search information into an Access database. We are then going to migrate the data from there into SQL Server. It appears as though most of these search engine components are building text indexes in a directory. My assumption is that they can work on sites and on distributable disks as this seems to be a common high note on the sales pitches.

Researching how to move data from Access to SQL Server 2005 automatically

Challenge: We have an access database. This is automatically updated and generated. We want to integrate this information with our website which uses SQL Server. So, how do we do it. This blog posts some of the research.

A lot of information seems to be based around migrating from access to sql server. But, this is not what we want.

Microsoft Data Transformation Systems seems to have our solution:
Links:
Good step by step of how to set it up: http://support.microsoft.com/kb/285829
Frequently asked questions page for DTS: http://msdn2.microsoft.com/en-us/library/ms345120.aspx#dtsfss05df_topic5
The last link told me to do a search for this string for more info on Access: Creating a Package Using the DTS Import/Export Wizard
First reference: Creating a DTS Package with the DTS Import/Export Wizard: http://msdn2.microsoft.com/en-us/library/aa176533(SQL.80).aspx

NOTES: You need to specify whether you are exporting data from an instance of Microsoft® SQL Server™ to another data source (for example, a second instance of SQL Server 2000) or importing data from another data source to an instance of SQL Server. Both choices are available in SQL Server Enterprise Manager, through the Data Transformation Services node of the console tree, and as command switches through the dtswiz command prompt utility

When you want to run the package.
You can run the package after the DTS Import/Export Wizard completes, or you can schedule the package to execute on a regular basis using SQL Server Agent.

You can use the Create Publication Wizard to publish the data. For more information, see Replication Wizards.


Important Do not open a Microsoft Excel file that is being used as a source or destination during the wizard creation or execution, because a "file in use" error will occur.

Start:
I went ahead and created a new sql server 2005 database for testing. Started out by right clicking on the database name, then tasks, then import/export. I chose a microsoft access database as the source, also chose to save this package for later use. Ran the program and the tables as well as all data were created in sql server.

Now that I created it, it looks like I can use a SQL Server Agent to schedule this to run when i need it to.
http://www.microsoft.com/technet/prodtechnol/sql/2005/newsqlagent.mspx

After reading more, only the systems admin has the right to grant someone access to create an agent. This may be an issue as hosting may not allow us to run agents. Double check on hosting, otherwise, this may be a dead end.

SQL Statements within SQL Statements

I ran into this one while working with discussions. I wanted to show a summary for the discussion and for some reason was stuck when trying to bring back a description and then show the number of replies. Here's how it worked.

SELECT
d.*,
u.FirstName ,
u.LastName ,
(Select Count(*) as ResponseCount from DiscussionThreads dt where dt.DiscussionID=d.DiscussionID) AS ResponseCount
FROM
Discussions d,
Users u
WHERE
u.UserID=d.UserID

Themes in .net

Here's the situation, I'm trying to use one theme for the entire site. I also want individuals to allow themselves to change the theme of their areas.

Here's what I found:

• http://msdn2.microsoft.com/en-us/library/950xf363.aspx
• Had to investigate the web.config. In doing so, found the pages property maintainScrollPositionOnPostBack. I thought this was great. So, I set it to true considering all of the forms we have
• Set pages property smartNavigation="true" to see how it worked.
• Had issues because I couldn't understand how the theme and the master page interacted. I ended up reading this article...http://msdn2.microsoft.com/en-us/library/wtxbf3hh(VS.80).aspx After reading it, I found out that master pages inherit the theme from their calling pages. Here's an excerpt:
• Master Pages and Themes
  You cannot directly apply an ASP.NET theme to a master page. If you add a theme attribute to the @ Master directive, the page will raise an error when it runs.
  However, themes are applied to master pages under these circumstances:
  If a theme is defined in the content page. Master pages are resolved in the context of content pages, so the content page's theme is applied to the master page as well.
  If the site as a whole is configured to use a theme by including a theme definition in the pages Element (ASP.NET Settings Schema) element.
• Also learned that you can set the master page based on the page level, folder level (interesting for admin backends), and the application level (web.config)
• It did appear that when I added a new stylesheet to the app_themes directory, it took a minute to load. Essentially, at one point, i thought i had to update the .master file to see the difference. Although, i think that was just timing.
• Cool thing. If I add a stylesheet to the app_themes folder theme, it is automatically added as a property for this theme. So, i don't have to reference it in my file.

Ajax: Text value blinks or changes back to what it was before

The situation was I was loading a form. This form had a drop down treeview control that would update a text box. Every time, a selection was made, it would change the value of the text box. But, what was happening was the following:

• I would choose the drop down value
• Textbox would change
• Change the drop down value again
• Textbox value would change
• within 1 second, the value would change back.

To fix this, i just put the treeview rendering within a !isPostBack block. This fixed the issue. Essentially what I learned which.. is obvious, is that each time Ajax makes an update it posts back, and, of course this IS a postback. So, make sure if you're loading controls through datasets, etc that you only run it once.

Setting properties in included controls

How to change properties or information in an included control?
Ok, typical situation, basic quesiton. I had a page which included a control. This control contained a label and drop down. This was included several times on the page. So, what was happening is that the label showed the same thing every time. So, how did I get around this?

I added a public property to the codebehind of the included control. Once this was done, I used the codebehind of the parent page and set the label values.

Ajax and form doesn't work when setting focus

I had this issue on a registration form. Each time the person typed in a value, we checked for an error. This caused a postback to occur which validated the text. Problem was the input box would lose focus. My initial thought was to set the focus in the codebehind. After some research, there is a different solution:

Key to this whole issue: When Ajax does a postback, it completely rewrites the html for that portion. I had to take my individual text boxes out of one updatepanel and put it into multiple.

See a great description on it here: http://forums.asp.net/p/1062555/1529151.aspx

How to place non-html comments in an aspx file

I ran across a small issue. It's easier for me to put in comments in the html to see what's happening in my aspx file. But, i didn't want to clutter up the html that was sent to the browser for performance issues and cleanliness. Here's how I did it.

< % - - whatever the comment is - - % > Remember there are no spaces between the < and the -

Update SQL Syntax

Update query SQL Syntax

Allows you to update records in a database. You typically will want to update a record using the where clause.

UPDATE tablename
SET columnfield = [parameter/value], columnfield2 = [parameter2/value2] = expression2, ....
[ WHERE expression ]
[ LIMIT limit_amount ]

Example:
Note, this has been altered for protection

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Joe Bailey
-- Create date: 10/09/2007
-- Description: Updates the values from the xyz database
-- =============================================
CREATE PROCEDURE [dbo].[xyz_Update]
-- Add the parameters for the stored procedure here
@xyzID integer,
@xyzName nvarchar(200),
@xyzPage nvarchar(500),
@xyzPageTitle nvarchar(500),
@xyzMetaDescription nvarchar(1000),
@xyzMetaKeywords nvarchar(1000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
UPDATE xyzSET
xyzName = @xyzName,
xyzPage = @xyzPage,
xyzPageTitle = @xyzPageTitle,
xyzMetaDescription = @xyzMetaDescription,
xyzMetaKeywords = @xyzMetaKeywords
WHERE xyzID = @xyzID
END
GO

Stored Procedure Guidelines

Naming conventions:

proc_[maintable][function(optional)]_insert - Insert
proc_[maintable][function(optional)]_select - Select
proc_[maintable][function(optional)]_delete - Delete
proc_[maintable][function(optional)]_update - Update

Select Example:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Joe Bailey
-- Create date: 10/09/2007
-- Description: Gathers all values from the xyz database
-- =============================================
CREATE PROCEDURE [dbo].[xyz_Select]
-- Add the parameters for the stored procedure here
@xyzID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT xyzName,xyzPage, xyzPageTitle, xyzMetaDescription, xyzMetaKeywords
FROM XYZ
WHERE xyzID=@xyzID
END
GO

HTML showing up in my data grid

I had the issue where I was using the FCKEditor in .NET. Each time I viewed the datagrid, the html tags would show as text. The database showed the correct information, but not the screen.

To fix, I added
HtmlEncode="false"
To the boundfield, this fixed everything.

Standard steps for creating an administrative form

Grid View

1. Add SQL Datasource client
2. Add Grid
3. Update the properties of the grid (Skin)
4. Set DataSource, set yes to refresh
5. Add command field (Select)
6. Add Select ImageURL
7. Move the select image to the top
8. Change Select button type to image
9. Find key, set the property to invisible
10. Add Delete
11. change delete image
12. change deleteimage button type to image
13. Properties: Change the EmptyDataText to read "There are no records to display." This will display if no records exist.
14. Look at all fields. For any fields that contain html tags coming from the database, put
    HtmlEncode="false" into the boundfield tag.

Details View

1. Add SQL Datasource
2. Configure Data source
3. Choose "Where"
4. Choose unique ID
5. Choose =
6. Choose Control
7. Select the correct control
8. make sure to click advanced, this will allow us to create insert, delete, add statements automatically.
9. Edit fields in Detail view
10. Add Command field for Edit
11. Edit - Set button type to button
12. Edit - Change buttontext to Update
13. Add comand field for Insert
14. Insert - Change buttontype to button
15. Insert - Change buttontext to Add
16. Handle 0 rows - Add EmptyDataTemplate
17. 
    
    

Adding FckEditor so we can edit large areas of text with the editor

1. Go to code view of aspx file
2. In page tag at top of page, add the following parameters: ValidateRequest="false" StyleSheetTheme="xyz" Note: Stylesheet theme is optional

Copying and Reusing an existing admin form

1. Copy and paste the existing form in Visual Studio
2. Rename the new form
3. Open up the .aspx file. Change the Inherits value to match the name of your page
4. Open up the .aspx.vb file. Change the name of the class to match the name of your page.
5. Open the .aspx file in design view
6. Go to the initial datasource of the datagrid, click on it to configure the datasource

Working with GridView contol

GridView, Columns, asp:BoundField - sortexpression acts as the order parameter. You can make the grid sort based on this field. It doesn't have to be the field of the displayed value.

GridView, asp:boundfield, insertvisible - when working with the control, set this to true or false to state whether or not this field shows up when the user is makeing an insert.

Had a challenge on what to name the uploaded files. Just settled on naming them with a randomly generated name. This doesn't help seo though.

Reminder: after all of this is implemented, because i had a datagrid talking to a dataview, i had to update the datagrid after the items were updated. So, i had to put in code... datagrid.databind() for the _itemupdated and _iteminserted, _itemdeleted.

Ajax, DetailsView, FileUpload, _ItemUploading and _ItemInserting -

Situation: I am allowing a client to upload a graphic. To make things convenient, I want them to see the graphic.

Challenge: The fileUpload does not fill in or show the client the file name. Plus, when they click on the update, i don't have a filename to reference, so it deletes the filename that was there before.

Solution:
When uploading a graphics file, i had to do a workaround.
I've added a couple of new lines in the update view:

• IMG - An image to show the graphic
• asp:label - I'm using this to display the file name. I'm also using this as a reference to the id of the record. This way, I can take this value and put it in place of the value for the fileupload filename.

In the code behind, I have code that handles it. Here is an example:

Dim fupTemp As FileUpload = CType(sender.findControl("fupx"), FileUpload)
If fupTemp.HasFile Then
Dim sPath = sImagePath & fupTemp.FileName
fupTemp.SaveAs(sPath)
e.NewValues("filename") = fupTemp.FileName
Else
Try
e.NewValues("filename") = CType(sender.findControl("imgFileName"), Label).Text
Catch ex As Exception
e.NewValues("filename") = "transparent.gif"
End Try
End If