Tuesday, May 17, 2005

How to create an enumeration or position of a child table record

I have seen this question in many user forums. Lets consider pubs database in MS-SQL SERVER. Now see how to generate the position number of each titles of an author in the titleauthor table.

FROM titleauthor a
WHERE a.au_Id = b. au_Id
AND a.title_ID <= b.title_ID
) as pos ,
FROM titleauthor b
GROUP BY au_Id ,title_ID
) T


This is the result we get

pos au_Id title_ID
1 172-32-1176 PS3333
1 213-46-8915 BU1032
2 213-46-8915 BU2075
1 238-95-7766 PC1035
1 267-41-2394 BU1111
2 267-41-2394 TC7777
1 274-80-9391 BU7832
1 409-56-7008 BU1032
1 427-17-2319 PC8888
1 472-27-2349 TC7777
1 486-29-1786 PC9999
2 486-29-1786 PS7777
1 648-92-1872 TC4203
1 672-71-3249 TC7777
1 712-45-1867 MC2222
1 722-51-5454 MC3021
1 724-80-9391 BU1111
2 724-80-9391 PS1372
1 756-30-7391 PS1372
1 807-91-6654 TC3218
1 846-92-7186 PC8888
1 899-46-2035 MC3021
2 899-46-2035 PS2091
1 998-72-3567 PS2091
2 998-72-3567 PS2106


Thursday, May 12, 2005

How User Controls Communicate in ASP.NET1.1

ASP.Net has a good support for component oriented programming. Even the User controls can be excellect self independent components. A user control can not only have public methods & properties, it can also have events. Using this concepts we can easily decompose any complex page to simpler user controls ,ie., I mean to say don't hesitate to create usercontrols to split a complex page, even if the usercontrols is used only once.

Now Let's look at how user controls communicate using events.

First we will see how to add an event to a user control "Header"

  1. Add delegate declaration in the namespace
  2. Add event declaration in the class
  3. Fire the event

namespace TestUserControl
public delegate void ChangeEventHandler(
object sender, string ID);
public class Header : Web.UI.UserControl
protected WebControls.Label lblApp;

public event ChangeEventHandler Change;

public void ChangeAppName(string AppName)
this.lblApp.Text =AppName;
Change (this,"Changed appname to : "
+ AppName.Trim().ToUpper());

Now Lets see how to consume the
event in a page

namespace TestUserControl
public class WebForm1 : System.Web.UI.Page
protected WebControls.Button Button1;
protected WebControls.TextBox TextBox1;
protected Header hdr;

private void Page_Load(object sender,
System.EventArgs e)
hdr.Change+= new
private void Button1_Click(object sender,
System.EventArgs e)
hdr.ChangeAppName("New Application"
+ TextBox1.Text);
private void hdr_ChangeEvent(object sender,
string ID)
TextBox1.Text= ID;

Note: Only relevent code has been kept,
place the code in the appropriate area.

Saturday, May 07, 2005

A complete Editable Data Grid in ASP.NET 1.1

This is good piece of code generated by the Web Matrix,
It had a bug that no records could be added if the table is empty.
This is the fixed version. I feel this could help many beginners.

The Script Part

' TODO: update the ConnectionString and Command values for your application

Dim ConnectionString As String = "server=(local);database=pubs;trusted_connection=true"
Dim SelectCommand As String = "SELECT au_id, au_lname, au_fname from Authors"

Dim isEditing As Boolean = False

Sub Page_Load(Sender As Object, E As EventArgs)

If Not Page.IsPostBack Then

' Databind the data grid on the first request only
' (on postback, bind only in editing, paging and sorting commands)


End If

End Sub

' ---------------------------------------------------------------
' DataGrid Commands: Page, Sort, Edit, Update, Cancel, Delete

Sub DataGrid_ItemCommand(Sender As Object, E As DataGridCommandEventArgs)

' this event fires prior to all of the other commands
' use it to provide a more graceful transition out of edit mode


End Sub

Sub CheckIsEditing(commandName As String)

If DataGrid1.EditItemIndex <> -1 Then

' we are currently editing a row
If commandName <> "Cancel" And commandName <> "Update" Then

' user's edit changes (If any) will not be committed
Message.Text = "Your changes have not been saved yet. Please press update to save your changes, or cancel to discard your changes, before selecting another item."
isEditing = True

End If

End If

End Sub

Sub DataGrid_Edit(Sender As Object, E As DataGridCommandEventArgs)

' turn on editing for the selected row

If Not isEditing Then

DataGrid1.EditItemIndex = e.Item.ItemIndex

End If

End Sub

Sub DataGrid_Update(Sender As Object, E As DataGridCommandEventArgs)

' update the database with the new values

' get the edit text boxes
Dim id As String = CType(e.Item.Cells(2).Controls(0), TextBox).Text
Dim lname As String = CType(e.Item.Cells(3).Controls(0), TextBox).Text
Dim fname As String = CType(e.Item.Cells(4).Controls(0), TextBox).Text

' TODO: update the Command value for your application
Dim myConnection As New SqlConnection(ConnectionString)
Dim UpdateCommand As SqlCommand = new SqlCommand()
UpdateCommand.Connection = myConnection

If AddingNew = True Then
UpdateCommand.CommandText = "INSERT INTO authors(au_id, au_lname, au_fname, contract) VALUES (@au_id, @au_lname, @au_fname, 0)"
UpdateCommand.CommandText = "UPDATE authors SET au_lname = @au_lname, au_fname = @au_fname WHERE au_id = @au_id"
End If

UpdateCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11).Value = id
UpdateCommand.Parameters.Add("@au_lname", SqlDbType.VarChar, 40).Value = lname
UpdateCommand.Parameters.Add("@au_fname", SqlDbType.VarChar, 20).Value = fname

' execute the command

Catch ex as Exception
Message.Text = ex.ToString()


End Try

' Resort the grid for new records
If AddingNew = True Then
DataGrid1.CurrentPageIndex = 0
AddingNew = false
End If

' rebind the grid
DataGrid1.EditItemIndex = -1

End Sub

Sub DataGrid_Cancel(Sender As Object, E As DataGridCommandEventArgs)

' cancel editing

DataGrid1.EditItemIndex = -1

AddingNew = False

End Sub

Sub DataGrid_Delete(Sender As Object, E As DataGridCommandEventArgs)

' delete the selected row

If Not isEditing Then

' the key value for this row is in the DataKeys collection
Dim keyValue As String = CStr(DataGrid1.DataKeys(e.Item.ItemIndex))

' TODO: update the Command value for your application
Dim myConnection As New SqlConnection(ConnectionString)
Dim DeleteCommand As New SqlCommand("DELETE from authors where au_id='" & keyValue & "'", myConnection)

' execute the command

' rebind the grid
DataGrid1.CurrentPageIndex = 0
DataGrid1.EditItemIndex = -1

End If

End Sub

Sub DataGrid_Page(Sender As Object, E As DataGridPageChangedEventArgs)

' display a new page of data

If Not isEditing Then

DataGrid1.EditItemIndex = -1
DataGrid1.CurrentPageIndex = e.NewPageIndex

End If

End Sub

Sub AddNew_Click(Sender As Object, E As EventArgs)

' add a new row to the end of the data, and set editing mode 'on'


If Not isEditing = True Then

' set the flag so we know to do an insert at Update time
AddingNew = True

' add new row to the end of the dataset after binding

' first get the data
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(SelectCommand, myConnection)

Dim ds As New DataSet()

' add a new blank row to the end of the data
Dim rowValues As Object() = {"", "", ""}

' figure out the EditItemIndex, last record on last page
Dim recordCount As Integer = ds.Tables(0).Rows.Count

If recordCount > 1 Then

recordCount -= 1
DataGrid1.CurrentPageIndex = recordCount \ DataGrid1.PageSize
DataGrid1.EditItemIndex = recordCount Mod DataGrid1.PageSize
DataGrid1.EditItemIndex = 0
End If

' databind
DataGrid1.DataSource = ds

End If

End Sub

' ---------------------------------------------------------------
' Helpers Methods:

' property to keep track of whether we are adding a new record,
' and save it in viewstate between postbacks

Property AddingNew() As Boolean

Dim o As Object = ViewState("AddingNew")
If o Is Nothing Then
Return False
End If
Return CBool(o)
End Get

Set(ByVal Value As Boolean)
ViewState("AddingNew") = Value
End Set

End Property

Sub BindGrid()

Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(SelectCommand, myConnection)

Dim ds As New DataSet()

DataGrid1.DataSource = ds

End Sub

The HTML part

<H2>Editable Data Grid </H2>
<FORM runat="server"><?xml:namespace prefix = asp /><asp:datagrid id=DataGrid1 runat="server" DataKeyField="au_id" OnItemCommand="DataGrid_ItemCommand" OnEditCommand="DataGrid_Edit" OnUpdateCommand="DataGrid_Update" OnCancelCommand="DataGrid_Cancel" OnDeleteCommand="DataGrid_Delete" AllowPaging="true" PageSize="6" OnPageIndexChanged="DataGrid_Page" ForeColor="Black" BackColor="White" CellPadding="3" GridLines="None" CellSpacing="1" width="80%"><HEADERSTYLE backcolor="#4A3C8C" forecolor="white" font-bold="True"></HEADERSTYLE><PAGERSTYLE backcolor="#C6C3C6" font-size="smaller" mode="NumericPages" horizontalalign="Right"></PAGERSTYLE><ITEMSTYLE backcolor="#DEDFDE"></ITEMSTYLE><FOOTERSTYLE backcolor="#C6C3C6"></FOOTERSTYLE><COLUMNS><asp:EditCommandColumn ItemStyle-Width="10%" ItemStyle-Font-Size="smaller" EditText="Edit" CancelText="Cancel" UpdateText="Update" ButtonType="LinkButton"></asp:EditCommandColumn><asp:ButtonColumn ItemStyle-Width="10%" ItemStyle-Font-Size="smaller" CommandName="Delete" Text="Delete"></asp:ButtonColumn></COLUMNS></asp:datagrid><BR><asp:LinkButton id=LinkButton1 onclick=AddNew_Click runat="server" Text="Add new item" Font-Size="smaller"></asp:LinkButton><BR><BR><asp:Label id=Message runat="server" width="80%" forecolor="red" enableviewstate="false"></asp:Label></FORM>

Thursday, May 05, 2005

Updating using a gridview (ASP.NET 2.0)

This Example will show how Updating using a gridview is done in ASP.NET 2.0 with the SqlDatasource command.



<FORM runat="server">

"<%$ ConnectionStrings:MyNorthwind%>"
SelectCommand="SELECT EmployeeID,FirstName,
LastName,Title FROM Employees"
UpdateCommand="Update Employees
SET FirstName=@FirstName,
LastName=@LastName, Title=@Title
WHERE EmployeeID=@EmployeeID">

<asp:BoundField HeaderText="First Name"
DataField="FirstName" />
<asp:BoundField HeaderText="Last Name"
DataField="LastName" />
<asp:BoundField HeaderText="Title"
DataField="Title" />



Look at 
 "<%$ ConnectionStrings:MyNorthwind%>"

"<%$   %>"
is used to databind the Key in web.config.
Its another new feature of asp.net 2.0.

Monday, May 02, 2005

How to find the Primary key of table

While creating some generic database related userinterfaces we always have find the primary key of the table in runtime. Previously in my Code generation wizards I used to search for the first field with "Id" as suffix and give the user chance to change. But now a senario came when i had create an update command of an sqldatasource in runtime. So i had get the primary key to for specifiying the where clause.

Now I could find the "sp_pKeys" in MS-SQlServer to get the primary key

use pubs
sp_pKeys 'authors'

How to insert multiple values using single Query

Today i happend to answer this question in a users group, I felt to share the method I used
in many occassions.
EG: If you have a user Service selection interface where customer can subscribe for more than
one Service at a time. You may be using a set of a check boxes or a multi-selection listbox. The best way is to can create a coma seperated list from the check list(say ServiceID),so that you can use a Query something like this :

"INSERT INTO Customer-Services "
& " SELECT " & CustmerID & " , ServicesID "
& " FROM Services WHERE ServicesID IN (" & ComaSerpatedList & ")"