Lessons on Program

The next example does the following:

Inserts the Record
Edits the Record
Deletes the Record
Shows the Record in the Grid
Go to the First Record
Go to the Last Record
Go to the Next Record
Go to the Previous Record

The screen shot showing the layout of the design

In this application a separate module is added which will have the code for the connection string and to get connected to the database.
Imports System.Data.SqlClient
Module Module1
Public cnnstr As New SqlConnection
Public issueCommand As New SqlCommand
Public Sub connect()
cnnstr.ConnectionString = "Data Source=USER;Initial
Catalog=BCA;Integrated Security=True"
cnnstr.Open()
issueCommand.Connection = cnnstr
End Sub
End Module

Following is the complete code for the application:
Public Class Form1
Dim adp As New SqlClient.SqlDataAdapter
Dim ds As New DataSet
Dim bds As New BindingSource
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
connect()
Dim qury As String
qury = "insert into BCAStdInfo(RegNum,Name,Semester)values(" & CInt(Me.TextBox1.Text) & ",'" & Me.TextBox2.Text & "'," & CInt(Me.TextBox3.Text) & ")"
issueCommand.CommandText = qury
issueCommand.CommandType = CommandType.Text
issueCommand.ExecuteNonQuery()
MsgBox("One Record Inserted")
Me.Button10.Visible = True
Me.Button1.Visible = False
Catch ex As Exceptio
n MsgBox("Error Inserting Row" & ex.ToString)
End Try
cnnstr.Close()
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Try
connect()
issueCommand.CommandText = "update BCAStdInfo set
Name = '" & Me.TextBox2.Text & "',Semester=" & CInt(Me.TextBox3.Text) & " where RegNum=" & Me.TextBox1.Text
issueCommand.ExecuteNonQuery()
MsgBox("Record Updated Successfully")
cnnstr.Close()
Me.Button2.Visible = False
Me.Button9.Visible = True
Catch ex As Exception
MsgBox(ex.ToString)
End Try

End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Try
connect()

issueCommand.CommandText = "select * from BCAStdInfo" adp.SelectCommand = issueCommand
ds.Clear()
adp.Fill(ds, "BCAStdInfo")
cnnstr.Close()
Me.DataGridView1.DataSource = ds
Me.DataGridView1.DataMember = "BCAStdInfo"
bds.DataSource = d
s bds.DataMember = "BCAStdInfo"
Me.TextBox1.DataBindings.Add("text", bds, "RegNum")
Me.TextBox2.DataBindings.Add("text", bds, "Name")
Me.TextBox3.DataBindings.Add("text", bds, "Semester")
Me.Button3.Enabled = True
Me.Button9.Enabled = True
Me.Button5.Enabled = True
Me.Button6.Enabled = True
Me.Button7.Enabled = True
Me.Button8.Enabled = True
Me.Button1.Visible = False
Me.Button10.Visible = True
Catch ex As Exception
End Try
End Sub

Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
bds.MoveFirst()
End Sub

Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
bds.Mo
veLast()
End Sub
Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
bds.MovePrevious()
End Sub

Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
bds.MoveNext()
End Sub

Private Sub Button9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button9.Click
Me.TextBox1.Enabled = False
Me.TextBox2.Focus()
Me.Button9.Visible = False
Me.Button2.Visible = True
End Sub

Private Sub Button10_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button10.Click
Me.Button1.Visible = True
Form1_Load(sender, e)
Me.TextBox2.Focus()
Try
connect()
issueCommand.CommandText = "select MAX(RegNum) from BCAStdInfo"
Dim i = issueCommand.ExecuteScalar
If IsDBNull(i) Then
i = 100
Else
i += 1
End If
Me.TextBox1.Text = i
Me.Button10.Visible = False
cnnstr.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Try
connect()
Dim s As Integer = InputBox("Enter the Registration Number U want to Delete :")
issueCommand.CommandText = "Delete from BCAStdInfo where RegNum=" & s
issueCommand.ExecuteNonQuery()
cnnstr.Close()
MsgBox("Record Deleted Successfully")
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.Button3.Enabled = False
Me.Button9.Enabled = False
Me.Button5.Enabled = False
Me.Button6.Enabled = False
Me.Button7.Enabled = False
Me.Button8.Enabled = False
Me.TextBox2.Text = ""
Me.TextBox3.Text = ""
Me.Button10.Focus()
End Sub

Private Sub Button11_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button11.Click
End
End Sub
End Class

Screen shot of the application when you clicked on to the Display button. The records of the table is shown in the Datagrid (Fig. 34).

Note:
ExexuteScalar: This executes the query and returns the first column of the first row in the result set returned by the query as a .NET Framework data type. Extra Columns or rows are ignored.
CommandText: Gets or Sets the SQL statement or stored procedure to execute against the database.
CommandType: Gets or Sets a value indicating how the CommandText property is interpreted.
ExecuteNonQuery(): This Executes an SQL statement against the connection and returns the number of rows affected.
Parameters: Gets the command parameters.

Previous Example<< Previous
Next >>Next Example

Our aim is to provide information to the knowledge seekers.

About us

Disclaimer