Microsoft ASP .NET Professional Projects - Premier Press

<input name="code_category" type="text" value="604" id="code_category" />

</td>

</tr>

<tr>

<td>

type

</td>

<td>

<input name="type" type="text" value="A" id="type" />

</td>

</tr>

<tr>

<td>

closing

</td>

<td>

<input name="closing" type="text" value="0" id="closing" />

</td>

</tr>

</Table>

<input type="submit" name="ctrl37" value="Update" />

Note that now you don't see the prefix of Gen before the textbox ids. If you make any changes to the input fields and hit "Update" you lose your changes when a post-back occurs (i.e., the state is not maintained now).

Step 4: GenEditAdd Custom Control

In this step, I wrap up the GenEditAdd control. The final code contained is in the file GenEditAdd.vb. You will find the source files for this step in the

...GenEditAdd\Steps\Step4 subfolder on the book's Web site at www.premierpressbooks.com/downloads.asp. Compile the GenEditAdd component by running the bat file mGenEditAdd.bat.

Handling Events

The GenEditAdd control contains a button called AddButton. When this button is clicked, I need to make a call to my stored procedure, sending it the changed/new values so that it can update or insert a new record. I code an event Handler to do this job.

EventHandlers are attached to a button by attaching delegates to the events raised by the child controls. I have done this for the AddButton control as follows:

AddHandler AddButton.Click, AddressOf AddBtn_Click

All this is telling the ASP.NET runtime is that when the AddButton is clicked, fire off the method AddBtn_click. This method contains the code required to build the procedure call. The following is the method:

Private Sub AddBtn_Click(Sender As Object, E As EventArgs)

'Build the procedure call

Dim s As String

Dim r As DataRow

Dim c As DataColumn

Dim cell As TableCell

Dim row As DataRow

Dim column As string

Dim Value As string

Dim Fieldscount As integer

Dim vdisplay As string

vDisplay = Display + "000000000000000000000000000000000000000"

FieldsCount = 0

s = "Execute " + procedure + "" If mode = "update" Then

For Each r in t.Rows

For Each c in t.Columns

If vdisplay.chars(FieldsCount) = "0" or c.ToString = KeyField Then Else

Dim tb As TextBox

tb = me.FindControl(c.ToString) column = c.ToString

Value = tb.text

If c.DataType.ToString = "System.String" Then s = s + " @" + column + "='" + value + "', "

Else

s = s + " @" + column + "=" + value + ", " End If

End If

FieldsCount = FieldsCount + 1

Next c

Next r

s = s + "@" + KeyField + "=" + KeyValue

me.Controls.Add(new LiteralControl(s))

RunSql(s)

Else

For Each c in t.Columns

If vdisplay.chars(FieldsCount) = "0" or c.ToString = KeyField Then Else

Dim tb As TextBox

tb = me.FindControl(c.ToString) column = c.ToString

Value = tb.text

If c.DataType.ToString = "System.String" Then s = s + " @" + column + "='" + value + "', "

Else

s = s + " @" + column + "=" + value + ", " End If

End If

FieldsCount = FieldsCount + 1

Next c

s = s + "@" + KeyField + "=NULL"

me.Controls.Add(new LiteralControl(s))

RunSql(s)

End If

End Sub

The code in this event handler follows similar logic regarding the insert and update modes as discussed in Steps 1 and 2. A procedure call string is built and depending on the mode, the code_value parameter is a null (insert mode) or equals the primary key of the record that needs to be updated (update mode). The string that is built is written out on the screen as a LiteralControl, so that you can see what is being submitted to the database. Finally, the string is passed on to our old friend RunSql, which does the actual job of interacting with the database. The following is the RunSql method:

Sub RunSql(vSql as string)

try

Dim s As string

Dim myConnection As OleDbConnection

myConnection = New OleDbConnection(ConnStr)

Dim mycommand As New OleDbCommand(vsql,myConnection)

myconnection.Open()

myCommand.ExecuteNonQuery()

myconnection.Close()

Catch ex As OleDbException

' SQL error

Dim errItem As OleDbError

Dim errString As String

Dim s As string

For Each errItem In ex.Errors

errString += ex.Message + "<br/>"

Next

s = "<br/><br/>SQL Error.Details follow:<br/>" & errString

me.Controls.Add(new LiteralControl(s))

Catch myException as Exception

me.Controls.Add(new LiteralControl("Exception: " + myException.ToString()))

End try

End Sub

I have included two aspx forms that you can use to test out the control in the insert and update modes. These forms are called GenTestStep4_insert.aspx and GenTestStep4_update.aspx.

This concludes the discussion on the GenEditAdd Custom Control. Here is the complete code listing for your viewing pleasure:

GenEditAdd.vb

Option Strict Off

Imports System

Imports System.Web

Imports System.Web.UI

Imports System.Web.UI.WebControls

Imports System.Data

Imports System.Data.OleDb

Namespace Generic_Chap7

Public Class GenEditAdd_Chap7 : Inherits Control : Implements INamingContainer

Private ls_display as string

Private ls_where as string

Private ls_sql as string

Private ls_ConnStr as string

Private ls_keyField as string

Private ls_keyValue as string

Private ls_procedure as string

Private ls_exitpage as string

Private lt_datatable as datatable

Private ls_mode as string

Protected mytbl as table

Public Property Mode as string

Get

Return Cstr(ViewState("ls_mode"))

End Get

Set

ViewState("ls_mode") = value

End Set

End Property

Public Property ExitPage as string

Get

Return Cstr(ViewState("ls_exitpage"))

End Get

Set

ViewState("ls_exitpage") = value End Set

End Property

Public Property t as datatable

Get

Return lt_datatable

End Get

Set

lt_datatable = value End Set

End Property

Public Property KeyField as string Get

Return Cstr(ViewState("ls_keyfield"))

End Get

Set

ViewState("ls_keyfield") = value End Set

End Property

Public Property KeyValue as string Get

Return Cstr(ViewState("ls_keyvalue"))

End Get

Set

ViewState("ls_keyvalue") = value End Set

End Property

Public Property Procedure as string Get

Return Cstr(ViewState("ls_procedure"))

End Get

Set

ViewState("ls_procedure") = value End Set

End Property

Public Property display as string Get

Return Cstr(ViewState("ls_display"))

End Get

Set

ViewState("ls_display") = value End Set

End Property

Public Property Where as string Get

Return Cstr(ViewState("ls_where"))

End Get

Set

ViewState("ls_where") = value End Set

End Property

Public Property SQL as string

Get

Return Cstr(ViewState("ls_sql"))

End Get

Set

ViewState("ls_sql") = value

End Set

End Property

Public Property ConnStr as string

Get

Return Cstr(ViewState("ls_ConnStr") )

End Get

Set

ViewState("ls_ConnStr") = value

End Set

End Property

Protected Overrides Sub CreateChildControls()

Dim dv As DataView

Dim myConnection As OleDbConnection

Dim myCommand As OleDbDataAdapter

Dim ds As New DataSet

Dim vSql As string

If Where.Length < 1 then vSql = SQL

mode = "insert" Else

vSql = SQL + Where mode = "update"

End If

myConnection = New OleDbConnection(ConnStr) myCommand = New OleDbDataAdapter(vSql, myConnection) myCommand.Fill(ds, "vtable")

dv = new DataView(ds.Tables("vtable")) Dim Fields As Integer

'Dim t As DataTable t = dv.Table

Dim r As DataRow

Dim c As DataColumn

Dim cell As TableCell

Dim row As DataRow

Dim Fieldscount As integer

Dim s As string

Dim vdisplay as string

vDisplay = Display + "000000000000000000000000000000000000000000"

FieldsCount = 0

s = "<A HREF=" + ExitPage + ">Back</A>"

me.Controls.Add(new LiteralControl(s))

me.Controls.Add(new LiteralControl("<table bgcolor ='antiquewhite' style='font: 8pt verdana'>"))

me.Controls.Add(new LiteralControl("<tr>")) If mode = "insert" then

me.Controls.Add(new LiteralControl("<td colspan='2' bgcolor='#aaaadd' style='font:10pt verdana'>Add a New

Record:</td>"))

Else

me.Controls.Add(new LiteralControl("<td colspan='2' bgcolor='#aaaadd' style='font:10pt verdana'>Edit

Record:</td>"))

End if

me.Controls.Add(new LiteralControl("</tr>")) If mode = "update" then

For Each r in t.Rows

For Each c in t.Columns 'Don't show this field

IF vdisplay.chars(FieldsCount) = "0" or c.ToString = KeyField then Else

me.Controls.Add(new LiteralControl("<tr>"))

'label

me.Controls.Add(new LiteralControl("<td>")) me.Controls.Add(new LiteralControl(c.ToString)) me.Controls.Add(new LiteralControl("</td>")) 'value

me.Controls.Add(new LiteralControl("<td>")) Dim Box As New TextBox

Box.Text = r(c).ToString Box.ID = c.ToString me.Controls.Add(box)

me.Controls.Add(new LiteralControl("</td>")) end if

FieldsCount = FieldsCount + 1

Next c

Next r

Else ' Insert Mode

For Each c in t.Columns

IF vdisplay.chars(FieldsCount) = "0" or c.ToString = KeyField then Else

me.Controls.Add(new LiteralControl("<tr>")) 'label

me.Controls.Add(new LiteralControl("<td>")) me.Controls.Add(new LiteralControl(c.ToString)) me.Controls.Add(new LiteralControl("</td>")) 'value

me.Controls.Add(new LiteralControl("<td>")) Dim Box As New TextBox

Box.ID = c.ToString me.Controls.Add(box)

me.Controls.Add(new LiteralControl("</td>"))

End if

FieldsCount = FieldsCount + 1

Next c

End If

me.Controls.Add(new LiteralControl("</tr>")) me.Controls.Add(new LiteralControl("</Table>")) '------add button

Dim AddButton As New Button if mode = "insert" then

AddButton.Text = "Add" else

AddButton.Text = "Update" end if

AddHandler AddButton.Click, AddressOf AddBtn_Click

Me.Controls.Add(AddButton)

Dim cancel As New Button cancel.Text = "Cancel" Me.Controls.Add(cancel)

End Sub

Private Sub AddBtn_Click(Sender As Object, E As EventArgs) 'Build the procedure call

Dim s As String

Dim r As DataRow

Dim c As DataColumn

Dim cell As TableCell

Dim row As DataRow

Dim column As string

Dim Value As string

Dim Fieldscount As integer

Dim vdisplay As string