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