|
|
|
|
| ||
|
Loading the table and displaying data in the form's
controls Private Sub btnLoadTable_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnLoadTable.Click
Me.Cursor = Cursors.WaitCursor
Dim connectionString As String = "server=P-III; database=programmersheaven;" + _
"uid=phuser; pwd=nicecoding;" conn = New SqlConnection(connectionString)
Dim commandString As String = "SELECT * from article"
dataAdapter = New SqlDataAdapter(commandString, conn)
ds = New DataSet() dataAdapter.Fill(ds, "article")
dataTable = ds.Tables("article")
currRec = 0
totalRec = dataTable.Rows.Count
FillControls()
' show current record on the form
InitializeCommands() ' prepare commands
ToggleControls(True) ' enable corresponding controls
Me.Cursor = Cursors.Default
End SubWe have changed the cursor to WaitCursor at the start of the
method, and changed it to Default at the end of the method. Later we have
called the InitializeCommands() method after filling the controls with the
first record.
Initialing Commands Private Sub InitializeCommands()
' Preparing Insert SQL Command
dataAdapter.InsertCommand = conn.CreateCommand()
dataAdapter.InsertCommand.CommandText = _
"INSERT INTO article " + _
"(artId, title, topic, authorId, lines, dateOfPublishing) " + _
"VALUES(@artId, @title, @topic, @authorId, @lines, @dateOfPublishing)"
AddParams(dataAdapter.InsertCommand, "artId", "title", "topic", _
"authorId", "lines", "dateOfPublishing")
' Preparing Update SQL Command
dataAdapter.UpdateCommand = conn.CreateCommand()
dataAdapter.UpdateCommand.CommandText = _
"UPDATE article SET " + _
"title = @title, topic = @topic, authorId = @authorId, " + _
"lines = @lines, dateOfPublishing = @dateOfPublishing " + _
"WHERE artId = @artId"
AddParams(dataAdapter.UpdateCommand, "artId", "title", "topic", _
"authorId", "lines", "dateOfPublishing")
' Preparing Delete SQL Command
dataAdapter.DeleteCommand = conn.CreateCommand()
dataAdapter.DeleteCommand.CommandText = "DELETE FROM article WHERE artId = @artId"
AddParams(dataAdapter.DeleteCommand, "artId")
End SubThe SqlDataAdapter (and OleDbDataAdapter) class has
properties for each of the Insert, Update and Delete commands. The type of
these properties is SqlCommand (and OleDbCommand respectively). We have
created the Commands using the connection (SqlConnection) object's
CreateCommand() method. We then set the CommandText property of these
commands to the respective SQL queries in string format. The thing to note
here is that the above commands are very general and we have used the name
of the fields with an '@' sign wherever the specific field value is
required. For example, we have written the DeleteCommand's CommandText as:
"DELETE FROM article WHERE artId = @artId"Here we have used @artId instead of a physical value. In fact, this value will be replaced by the specific value when we delete a particular record. Adding Parameters to the commands Private Sub AddParams(ByVal cmd As SqlCommand, ByVal ParamArray cols() As String)
' Adding Hectice parameters in SQL Commands
Dim col As String
For Each col In cols
cmd.Parameters.Add("@" + col, SqlDbType.Char, 0, col)
Next
End SubThe very first thing to note in the AddParams method is
that the type of the second parameter is ' ParamArray cols() As String '
Private Sub AddParams(ByVal cmd As SqlCommand, ByVal ParamArray cols() As String)The ParamArray keyword is used to tell the compiler that the method will take a variable number of string elements to be stored in the string array named 'cols'. If the method is called like this: AddParams(someCommand, "one")The size of the cols array would be one, and if the method is called like this: AddParams(someCommand, "one", "two", "numbers")The size of the cols array would be three. Isn't it useful and extremely simple at the same time? VB.NET rules! So let's get back to the method. What it does is simply adds the supplied column name prefixed with an '@' in the Parameters collection of the SqlCommand class. The other parameters of the Add() method are the type of the parameter, the size of the parameter and the corresponding column name. After following the above steps, we have defined different commands for the record update. Now we can update records of the table. The ToggleControls() method of our application In the Load Table buttons event handler, we called the ToggleControls() method after initializing the commands Private Sub btnLoadTable_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnLoadTable.Click
Me.Cursor = Cursors.WaitCursor
...
FillControls()
' show current record on the form
InitializeCommands() ' prepare commands
ToggleControls(True) ' enable corresponding controls
Me.Cursor = Cursors.Default
End Sub We have defined the ToggleControls() method in our
application to change the Enabled and ReadOnly properties of buttons and
text boxes in our form at the respective times. If the ToggleControls()
method is called with a false boolean value it will take the form to the
edit mode and if called with a true value it will take the form back to
normal mode. The method is defined as:
Private Sub ToggleControls(ByVal val As Boolean)
txtArticleTitle.ReadOnly = val
txtArticleTopic.ReadOnly = val
txtAuthorId.ReadOnly = val
txtNumOfLines.ReadOnly = val
txtDateOfPublishing.ReadOnly = val
btnLoadTable.Enabled = val
btnNext.Enabled = val
btnPrevious.Enabled = val
btnEditRecord.Enabled = val
btnInsertRecord.Enabled = val
btnDeleteRecord.Enabled = val
btnSave.Enabled = Not val
btnCancel.Enabled = Not val
End SubEditing (or Updating) RecordsFor editing the current record, we have provided an Edit Record button on the form. The event for this button is surprisingly very simple and is: Private Sub btnEditRecord_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnEditRecord.Click
ToggleControls(False)
End SubThis event simply takes the form and the controls to the
edit mode by passing a false value to the ToggleControls() method
presented above. When a user presses the Edit Record button, the form is
changed, so it looks like:
 |
|
 |
 |
 |
|
 |
 |
 |
