Follow these easy steps to create your AJAX solution.
1.
Start your Visual Studio 2005 IDE.
2.
Choose Create Web Site from the menu.
3.
In this sample application we will use the Visual Basic language for the
sample application.
4. Name
the solution AS400Dept.
5.
Choose default Web Site.
6.
Choose File System in the location box.
7.
Click ok to create the project.
8.
Visual Studio will create your project with a Default.aspx
page and most probably a readme.txt. Go ahead and get rid of the latter file.
9.
Open Default.aspx page in design view.
10. Right
click the solution name, select Add Reference and
select IBM DB2 for ISeries .NET Provider. Click ok.
11. Drag
two panels from Toolbox, name them PnlMain and PnlDetail and make the PnlDetail
invisible by default.
12. Inside
the panel, main drag a GridView and name it GrdMain and make its style as
professional. Please note Listing 12 for HTML addition for the GridView in the
first line (section datakeynames to allow for edit and delete option
afterwards).
13. In
the details panel add the text Dept Id followed by a text box named TxtDeptId.
14. Add
the text Dept Name followed by a textbox named TxtDeptName.
15. Also
add two buttons with the following specifications:
16. Id:
BtnSave and Text: Save
17. Id:
BtnCancel and Text: Cancel
18. Above
the Main Panel create a new button with id BtnCreate and Text Create Department.
19. In
the GridView add Edit Command as well as the delete command.
20. For
The Create Button use Listing 6.
21. For
the cancel button code use Listing 7.
22. There
is some Code that should be common to all functions that should be created.
This code should go after the class directly. One is to prepare connection and
the other is to convert anything that has been returned as a dataset. You can
find the code in Listing 8. (Remember to replace the required parameters in the
connection string.)
23. For
the save button, check listing 9.
24. Now
we turn to loading the grid with data selected from AS400 stored procedure. You
can find the code in listing 10.
25. The
delete event should be handled now inside the grid view. Listing 11 shows the
code needed.
26. Lastly,
we have the edit part. You can find the code in Listing 13. The save button
command code would change to accommodate update. The new listing for the save
button is 14.
Listing 6 - Create Button Code
Protected Sub BtnCreate_Click(ByVal sender As Object, ByVal e As System.EventArgs)_
Handles BtnCreate.Click
PnlDetail.Visible = True
PnlMain.Visible = False
TxtDeptId.Text = ""
TxtDeptName.Text = ""
TxtDeptId.ReadOnly = False
End Sub
Listing 7 - Cancel Button Code
Protected Sub BtnCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs)_
Handles BtnCancel.Click
PnlDetail.Visible = False
PnlMain.Visible = True
End Sub
Listing 8 - Common Shared Code Section
#Region "GeneralFunctions"
Dim WSConn As New IBM.Data.DB2.iSeries.iDB2Connection
Dim WSCommand As New IBM.Data.DB2.iSeries.iDB2Command
Dim WSDatareader As IBM.Data.DB2.iSeries.iDB2DataReader
Dim WSDataSet As New Data.DataSet
Public Sub ConvertReaderToDataset(ByVal ETableName As String)
Dim SchemaTable As New Data.DataTable, MyDataTable As New Data.DataTable
SchemaTable = WSDatareader.GetSchemaTable()
MyDataTable.TableName = ETableName
Dim intcounter As Integer
For intcounter = 0 To SchemaTable.Rows.Count - 1
Dim dataRow As DataRow = SchemaTable.Rows(intcounter)
Dim columnname As String = CType(dataRow("ColumnName"), String)
Dim column As DataColumn = New DataColumn(columnname, _
CType(dataRow("DataType"), Type))
MyDataTable.Columns.Add(column)
Next
WSDataSet.Tables.Add(MyDataTable)
While WSDatareader.Read
Dim dataRow As DataRow = MyDataTable.NewRow()
For intcounter = 0 To WSDatareader.FieldCount - 1
dataRow(intcounter) = WSDatareader.GetValue(intcounter)
Next
MyDataTable.Rows.Add(datarow)
End While
End Sub
Public Function Prepare_Env() As Boolean
WSConn.ConnectionString = _
"datasource=AS400Name;userid=MyUser;password=MyPassword;"
WSCommand.Parameters.Clear()
Wsdataset.Tables.Clear()
WSCommand.Connection = WSConn
WSCommand.CommandType = CommandType.StoredProcedure
Return True
End Function
#End Region
Listing 9 - The Save Button Code
Protected Sub BtnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles BtnSave.Click
Prepare_Env()
WSConn.Open()
WSCommand.CommandText = "TESTNET.INSDEPT"
WSCommand.CommandType = CommandType.StoredProcedure
WSCommand.Parameters.Add("P_ID", TxtDeptId.Text)
WSCommand.Parameters.Add("P_DESC", TxtDeptName.Text)
WSCommand.ExecuteNonQuery()
WSConn.Close()
End Sub
Listing 10 - The Form Load Event
Protected Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles form1.Load
Prepare_Env()
WSConn.Open()
WSCommand.CommandText = "TESTNET.SELDEPT"
WSCommand.CommandType = CommandType.StoredProcedure
WSDatareader = WSCommand.ExecuteReader()
ConvertReaderToDataset("TblDept")
WSConn.Close()
GrdMain.DataSource = WSDataSet
GrdMain.DataMember = "TblDept"
If Not Me.IsPostBack Then
GrdMain.DataBind()
End If
Listing 11 - The Delete Event Handling
Protected Sub GrdMain_RowDeleting(ByVal sender As Object, ByVal e As _
System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles GrdMain.RowDeleting
Prepare_Env()
WSConn.Open()
WSCommand.CommandText = "TESTNET.DELDEPT"
WSCommand.CommandType = CommandType.StoredProcedure
WSCommand.Parameters.Add("P_ID", GrdMain.DataKeys(e.RowIndex).Value)
WSCommand.ExecuteNonQuery()
WSConn.Close()
Response.Redirect(Me.Request.Url.ToString())
End Sub
Listing 12 - The Extension of GridView
<asp:GridView ID="GrdMain" runat="server" CellPadding="4" ForeColor="#333333"
GridLines="None" DataKeyNames="DEPTID">
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:CommandField ShowDeleteButton="True" />
</Columns>
Listing 13 - Row Editing Listing
Protected Sub GrdMain_RowEditing(ByVal sender As Object, ByVal e As _
System.Web.UI.WebControls.GridViewEditEventArgs) Handles GrdMain.RowEditing
Dim P_ID As Integer
P_ID = GrdMain.DataKeys(e.NewEditIndex).Value
PnlMain.Visible = False
PnlDetail.Visible = True
TxtDeptId.Text = P_ID
TxtDeptId.ReadOnly = True
Prepare_Env()
WSConn.Open()
WSCommand.CommandText = "TESTNET.SDEPTID"
WSCommand.Parameters.Add("P_ID", P_ID)
WSCommand.CommandType = CommandType.StoredProcedure
WSDatareader = WSCommand.ExecuteReader()
ConvertReaderToDataset("TblDept")
WSConn.Close()
TxtDeptName.Text = WSDataSet.Tables("TblDept").Rows(0).Item("DEPTNM")
End Sub
Listing 14 - The New Save Button
Protected Sub BtnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles BtnSave.Click
Prepare_Env()
WSConn.Open()
If TxtDeptId.ReadOnly Then
WSCommand.CommandText = "TESTNET.UPDDEPT"
Else
WSCommand.CommandText = "TESTNET.INSDEPT"
End If
WSCommand.CommandType = CommandType.StoredProcedure
WSCommand.Parameters.Add("P_ID", TxtDeptId.Text)
WSCommand.Parameters.Add("P_DESC", TxtDeptName.Text)
WSCommand.ExecuteNonQuery()
WSConn.Close()
Response.Redirect(Me.Request.Url.ToString())
End Sub