Ronda's Web Log

Total Summary Rows in Datagrid with DataItem

Wed, 16 Jan 2008 08:43:24 GMT

There are a gazillion ways to create a summary row in the footer of a datagrid. I think this is one of the slicker, faster ways. Using the dataitem enables you to use friendly names and variables throughout - so when you have many many columns it makes it a lot easier to keep track and manage.

Declare some integers for holding the values

Private MonthlyGoal As Integer = 0
Private FiscalYrYTD As Integer = 0

In your Databound event add up the values:

Public Sub dgAttainment_ItemDataBound(ByVal sender As Object, ByVal e As DataGridItemEventArgs)

If e.Item.ItemType = ListItemType.Item Or _
e.Item.ItemType = ListItemType.AlternatingItem Then
‘add the items as you bind each row
‘Intellisense will not have the datafield name when you choose
‘e.item.dataitem but just add it anyways – it is the datafield
‘that you have used in your datagrid column
MonthlyGoal += e.Item.DataItem.MonthlyGoal
FiscalYrYTD += e.Item.DataItem.FiscalYrYTD

ElseIf (e.Item.ItemType = ListItemType.Footer) Then
‘put the added values into the appropriate column in the footer

e.Item.Cells(6).Text = FormatCurrency(MonthlyGoal, 2)
e.Item.Cells(9).Text = FormatCurrency(FiscalYrYTD, 2)

end if

End Sub

Community Server

Wed, 23 Aug 2006 18:54:14 GMT

WOW.... those guys at telligent are really on the ball. I installed community server for my hometown and haven't advertised it yet but it looks like it is truly going to rock!

I installed it here http://downtowncleburne.com/cs/Default.aspx but plan on changing the URL soon

I am trying to get some folks in the area to add some commentary before I start exploring avenues for disseminating it to the public. Take a look and please feel free to send me any comments regarding Forums/Blogs or other content.

I am looking forward to seeing how receptive the public is to this project.

Cheers,

Ronda

Populate a DropDown List from SQL Query

Tue, 24 May 2005 06:27:21 GMT

'Declare your connection, command and SQL
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim strSQL As String
'Assign your values
'I have concatenated the partnumber with the product name for ease of the user
strSQL = "select item_id,part_number,(part_number + '-' + productname) as theitem FROM products "
myConnection = New SqlConnection(conString)
myCommand = New SqlCommand(strSQL, myConnection)
'Open your connection
myConnection.Open()
'My dropdownlist is named drpProducts
drpProducts.DataSource = myCommand.ExecuteReader()
'Use the values from the SQL to populate the text and value fields of dropdown
drpProducts.DataTextField = "theitem"
drpProducts.DataValueField = "item_ID"
drpProducts.DataBind()
'Give your dropdownlist a default value
drpProducts.Items.Insert(0, "Choose Item")
'Close your connection
myConnection.Close()

ExecuteScaler to populate a label

Tue, 24 May 2005 06:20:50 GMT

Easy example of using ExecuteScaler to populate a single value

'Declare your connection & command objects along with your SQL String
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim strSQL As String
'Build your SQL and populate your other values
strSQL = "select myPrice FROM myproducts where itemid= " & drpProducts.SelectedItem.Value
myConnection = New SqlConnection(conString)
myCommand = New SqlCommand(strSQL, myConnection)
'Open your connection
myConnection.Open()
'Assign your retrieved value to an object
lblItemPrice.Text = myCommand.ExecuteScalar()
'Close your connection
myConnection.Close()

Busier than a ...

Tue, 05 Apr 2005 23:43:01 GMT

It has been SO long since I have blogged I had to look up my admin page to create this entry. I bought a new house - which is going to be awesome after a LOT of work. It is 1930's rock cottage right in the midst of town on an acre of land with all kinds of kitschy out buildings and character. I still have a house in Dallas to sell - thus am making two house payments right now if anyone is thinking of relocating - I am becoming more and more negotiable as the weeks roll by.

Oh on the subject of remodeling - I offer a tidbit of wisdom -- NEVER buy cheap sandpaper when sanding wood floors. Always spring for the most expensive stuff

I came across a rollover image control that Chris Garrett put together - I don't usually do rollovers but am trading out a web site for a heavy metal band (they are doing some construction for me) and they want rollovers. I started to do it with client side javascript and googled it with .net and found this clean, neat little user control to throw into your library.

I finally had some time tonight and spent a couple of hours in the microsoft newsgroups - it is amazing how much time programmers don't have - most especially if they have any outside interests like gardening, moving, side-work to support double house payments, not to mention any kind of social life (right). My listservers are all going to my gmail account (which really is the best solution in the world for lists) and I haven't checked it in more than a week or two- so I almost want to go in and just archive everything but I have never been able to do that. I really do admire those of you with clean mailboxes - I keep almost everything; which results in tons of folders for organinzing and lots of archived .pst files.

When I started this post I had some interesting items (VB.net) to mention but they are eluding me now - so maybe I will consolidate my thoughts, empty out my pda, get my notes together (darn I have been meaning to install one-note) and try another post soon... ~

Mobility To Go Roadshow

Sat, 13 Nov 2004 11:27:35 GMT

On Tuesday I attended the .Net To Go Mobility Roadshow and really enjoyed it. It looks like the show is about done but if you get the chance to catch it in the future, well it is a winner! Thom Robbins was one of the most compelling and inspiring speakers I have heard, he has a conversational style and excellent timing that prevents one from ever glancing at the clock.

I was fortunate and scored "The Definitive Guide to the .Net Compact Framework" which was nice since my 'Hello App' was created before I went to bed that night and it has been a good resource while working on a CF project all week in my free time (like programmers ever really have free time - so much to learn). The framework is not so different than any other .Net development but there are limitations and nuances to deal with and I haven't yet ventured into ADO with the SQLServerCE so I have some challenges ahead.

Robbins offered a challenge for a free CF Device and I thought a few of you may want to rise to the challenge. His October 20th post on his blog http://weblogs.asp.net/trobbins has all of the details. Basically submit an app and the top 5% will score "A Windows Powered Mobile Device!", either a phone or PDA.

Stored Procedure to DataSet - Easy Example

Mon, 04 Oct 2004 11:25:12 GMT

Most of the stored procedure examples out there seem to be for tying the data to a datagrid. But if you are working with XML or need a Dataset you may want an easy example to fill a dataset with your Stored Procedure returned data. Here goes...

'Set up your connection Info
Dim conDB As New SqlConnection(Me.conString)
Dim Command As New SqlClient.SqlCommand("GetVendorOrders")
Command.CommandType = CommandType.StoredProcedure
Command.Connection = conDB

'Create your Parameters (I created 2)
Dim vendorCode As New SqlClient.SqlParameter()
vendorCode.ParameterName = "@vendorCode"
vendorCode.SqlDbType = SqlDbType.NVarChar

Dim marking As New SqlClient.SqlParameter()
marking.ParameterName = "@flaggedValue"
marking.SqlDbType = SqlDbType.Int

'Set Parameter Values
vendorCode.Value = "AZ"
marking.Value = 3

'Add your Parameters
Command.Parameters.Add(vendorCode)
Command.Parameters.Add(marking)

'Create DataAdapter and place into Dataset
Dim daSalesByCategory As New SqlClient.SqlDataAdapter()
daSalesByCategory.SelectCommand = Command
Dim dsOrders As New DataSet()
daSalesByCategory.Fill(dsOrders)

It really doesn't get any easier than that... gotta love .Net!

Return Values from Stored Procedure

Sun, 03 Oct 2004 11:32:35 GMT

When dealing with multiple tables and you need to connect them with common information such as the ID of an order it is often necessary to insert a record, get back the ID and then go on to do other things utilizing that ID. The easiest way is using the @@Identity of the first table.

Example Sproc might look something like this:

Create Procedure "InsertnewOrder"

As

Insert into Orders(orderDate, orderStatus) Values(getdate(),'Shopping')

RETURN (@@IDENTITY)

In our page we could do something like this to get back the ID of the new record:

Dim myCon As SqlConnection
myCon = New SqlConnection(conString)
Dim myCmd As SqlCommand
myCmd = New SqlCommand("InsertnewOrder", myCon)
myCmd.CommandType = CommandType.StoredProcedure
myCmd.Parameters.Add("@return", SqlDbType.Int)
myCmd.Parameters("@return").Direction = ParameterDirection.ReturnValue
myCon.Open()
myCmd.ExecuteNonQuery()
Session("orderID") = myCmd.Parameters("@return").Value.ToString()

That is about it... pretty simple huh?

Create a printable page

Sat, 25 Sep 2004 07:48:32 GMT

In your Page Load or wherever you want it - add attributes for a Print button. Because my content is small I made a small popup window.

butPrint.Attributes.Add("onclick", "window.open('http://www.mydomain.com/myfolder/temp.html',null,'height=400, focus=yes,width=450,status= no, resizable= yes, scrollbars=yes, toolbar=yes,location=no,menubar=yes ');")

For this example I put the content of what I wanted to print into a Label (lblOutput)

When I pulled the content from the database I went ahead and made a temp file with the content I wanted to print

Dim sw As StreamWriter
sw = File.CreateText("C:\webs\myWebPath\www\foldername\temp.html")
sw.WriteLine("<html><head></head><body onload='self.focus();'>")
sw.WriteLine(lblOutput.Text)
sw.WriteLine("</body></html>")
sw.Close()

I also set my Print button to visible at this time to assure I had content in my file before I allowed the visitor to print

butPrint.Visible = True

That is a pretty simple way to get only the items you want into a page. You may want to clean up and delete the temp.html page when the visitor leaves.

Make Excel Report from Datagrid

Sat, 25 Sep 2004 07:18:22 GMT

Frequently it is convenient to be able to download a report in an Excel format. This is a quick and easy way to turn your datagrid into an excel report.

Make your subroutine to create your datagrid

Sub pullRecords(ByVal sender As System.Object, ByVal e As System.EventArgs)

'I have eliminated code to build the SQL String but basically create a datagrid in this subroutine like you normally would

Dim dreader As System.Data.SqlClient.SqlDataReader
dreader = cmdReturn.ExecuteReader(CommandBehavior.CloseConnection)
dgOrders.DataSource = dreader
dgOrders.DataBind()

dreader.Close()
conDB.Close()
conDB.Dispose()

End Sub

Okay once you have your datagrid returning to the page to send it to an Excel Doc do something like this:

Sub downloadReport(ByVal sender As System.Object, ByVal e As System.EventArgs)
pullRecords(sender, e)
' Set the content type to Excel.
Response.ContentType = "application/vnd.ms-excel"
' Remove the charset from the Content-Type header.
Response.Charset = ""
' Turn off the view state.
Me.EnableViewState = False

Dim tw As New System.IO.StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)

' Get the HTML for the control.
dgOrders.RenderControl(hw)
' Write the HTML back to the browser.
Response.Write(tw.ToString())
' End the response.
Response.End()
End Sub

I have initiated the creation of my Excel Report with a button on the page but you could incorporate it other ways.

Hiding Panels

Thu, 15 Apr 2004 11:09:54 GMT

I like to design many of my sites in ONE page. Thus I tend to use panels to hide/display various controls that are typically fired off my button events. One of the problems with this type of design is that you need to hide various panels when you show others to keep your page neat. I have a function that I use that will search my page for panels and hide them all. I run this right before I show a new panel to make sure the previous selection is not still displayed in my page.

To call it I pass it the name of my page form - If you don't name your forms the default is typically "Form1":

HidePanels(Page.FindControl("PortalForm"))

The simple little Sub looks like this:

Sub HidePanels(ByVal x As Object)
Dim ctlControl As Control
For Each ctlControl In x.Controls
If ctlControl.ToString() = "System.Web.UI.WebControls.Panel" Then
ctlControl.Visible = False
End If
Next
End Sub

Not much to it really but it negates having to worry about adding and hiding new panels and is a handy little snippet.

Parameterize your SQL

Sun, 11 Apr 2004 00:04:55 GMT

Parameterize your SQL

I use a lot of stored procedures in my programming but occasionally I will bypass the sproc because I know it is rarely going to be used or maybe it is a temporary page and I don't want to clutter up my DB. In those instances when I make an in-page query I thankfully use parameterized SQL wherever I can. Some of the benefits are:

Negates having to fight apostrophes in your data
Much easier to make changes to the Query without worrying about text delimiters
Easier to find your variables for troubleshooting since they are all grouped nicely together.
Offers more protection from injected SQL attacks

The old way we may have done this insert statement may have been:

sql= "Insert into tblGroup (Firstname, Lastname, Email) Values ('" & txtFirstname.text & "', '" & txtLastName.text & "', '" & txtEmail.text & "')"

The way to do it with Parameterized SQL:

Dim fname as string = txtFirstname.text
Dim lname as string = txtLastname.text
Dim email as string = txtEmail.text

With cmd.Parameters:
   .Add(New SQLParameter("@Firstname", fname))
   .Add(New SQLParameter("@LastName", lname))
   .Add(New SQLParameter("@email", email))
end with

sql= "Insert into tlbGroup (Firstname, Lastname, Email) Values (@Firstname, @LastName, @Email)"

Doesn't really look like much in this example. But when you put a query together with 20 or 30 variables, joins, unions etc... your errors will be fewer and easier to spot!

Printable Window with content

Sat, 10 Apr 2004 12:20:37 GMT

I secretly chuckle at programmers who write brilliant code but admit that they don't know HTML. I on the other hand have never taken the time to learn much JavaScript, so this morning when I needed to open a new window - a printable window - that only contained the contents of a label which was built from a dataset, I was at a loss. I found lots of examples of adding code behind script for alert windows but very few that allowed me to add a new window with content from my existing dynamic page.

I ended up creating a temp.html document on the fly and then opening that with Javascript. Here is what I did:

In my subroutine that created my detail view, where I write to my output label. Right after I created my output I went ahead and created a temp.html file.

Dim sw As StreamWriter
sw = File.CreateText("D:\mypath\Management\temp.html")
sw.WriteLine("<html><head></head><body onload='self.focus();'>")
sw.WriteLine(lblOutput.Text)
sw.WriteLine("</body></html>")
sw.Close()
butPrint.Visible = True

In my pageload I went ahead and attached the javascript to the button

butPrint.Attributes.Add("onclick", "window.open('http://www.mypath.com/temp.html',null,'height=400, width=450,status= no, resizable= yes, scrollbars=yes, toolbar=yes,location=no,menubar=yes ');")

It worked out very well for my purposes. The body onload='self.focus();' assured that everytime a different detail view was requested my external printable window had focus. This was a nice addition since that would be the first call from the office "I can't find my window!"

Incidentally you will need the proper references:
Imports System.IO
Imports System.Net

Anyways it was a little challenge but occupied my Saturday morning..... now off to clean the house!