Imports System.Data.SqlClient
Imports System.Data
Public Class Batches
Dim com As SqlCommand
Dim dr As SqlDataReader
Dim da As SqlDataAdapter
Dim dv As DataView
Dim str As String = ""
Public Function CheckEmptyTextbox(ByVal groupbox As GroupBox) As Boolean
Dim txt As Control
For Each txt In groupbox.Controls
If TypeOf txt Is TextBox Then
If txt.Text = "" Then
MsgBox("Fill Details To Proceed", MsgBoxStyle.Exclamation)
CheckEmptyTextbox = False
Exit Function
End If
End If
Next
CheckEmptyTextbox = True
End Function
Sub clear()
batchidcmb.SelectedIndex = -1
timetxt.Text = ""
coursenamecmb.SelectedIndex = -1
daytxt.SelectedIndex = -1
staffidcmb.Text = ""
stfnametxt.Text = ""
stflnametxt.Text = ""
End Sub
Sub Dis()
Dim str As String
str = "SELECT btbatchid, bttime, btday, ccoursename, stfstaffid, stfname, stfsurname from Batches"
Dim com As New SqlCommand(str, con)
Dim da As New SqlDataAdapter(com)
Dim dt As New DataTable
Dim ds As New DataSet
Try
str = "SELECT btbatchid, bttime, btday, ccoursename, stfstaffid, from Batches"
da.SelectCommand = com
da.Fill(dt)
batchidcmb.DisplayMember = "btbatchid"
batchidcmb.DataSource = dt
da.Dispose()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Sub DisplayDGV()
Dim str As String
str = "SELECT btbatchid, bttime, btday, ccoursename, stfstaffid, stfname, stfsurname from Batches"
Dim com As New SqlCommand(str, con)
Dim da As New SqlDataAdapter(com)
Dim dt As New DataTable
Dim ds As New DataSet
Try
str = "SELECT btbatchid, bttime, btday, ccoursename, stfstaffid, stfname, stfsurname from Batches"
da.SelectCommand = com
da.Fill(dt)
DataGridView1.DataSource = dt
da.Dispose()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Sub insertbtn_Click(sender As System.Object, e As System.EventArgs) Handles insertbtn.Click
If Not CheckEmptyTextbox(GroupBox1) Then
Exit Sub
End If
Dim com As SqlCommand
Dim str As String
Dim s = CInt(staffidcmb.Text)
Try
con.Open()
str = "Insert into Batches values(@btbatchid, @bttime, @btday , @ccoursename, @stfstaffid, @stfname, @stfsurname)"
com = New SqlCommand(str, con)
com.Parameters.AddWithValue("@btbatchid", batchidcmb.Text)
com.Parameters.AddWithValue("@bttime", timetxt.Text)
com.Parameters.AddWithValue("@ccoursename", coursenamecmb.Text)
com.Parameters.AddWithValue("@btday", daytxt.Text)
com.Parameters.AddWithValue("@stfstaffid", s)
com.Parameters.AddWithValue("@stfname", stfnametxt.Text)
com.Parameters.AddWithValue("@stfsurname", stflnametxt.Text)
com.CommandText = str
com.ExecuteNonQuery()
con.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
MsgBox("Record Inserted Successfully!", MsgBoxStyle.Information)
DisplayDGV()
Dis()
clear()
End Sub
Private Sub editbtn_Click(sender As System.Object, e As System.EventArgs) Handles editbtn.Click
If Not CheckEmptyTextbox(GroupBox1) Then
Exit Sub
End If
Dim com As SqlCommand
Dim str As String
Dim s = CInt(staffidcmb.Text)
Try
con.Open()
str = " Update Batches set btbatchid = @btbatchid, bttime = @bttime, btday = @btday , ccoursename = @ccoursename, stfstaffid = @stfstaffid, stfname = @stfname, stfsurname = @stfsurname where btbatchid = @btbatchid"
com = New SqlCommand(str, con)
com.Parameters.AddWithValue("@btbatchid", batchidcmb.Text)
com.Parameters.AddWithValue("@bttime", timetxt.Text)
com.Parameters.AddWithValue("@ccoursename", coursenamecmb.Text)
com.Parameters.AddWithValue("@btday", daytxt.Text)
com.Parameters.AddWithValue("@stfstaffid", s)
com.Parameters.AddWithValue("@stfname", stfnametxt.Text)
com.Parameters.AddWithValue("@stfsurname", stflnametxt.Text)
com.CommandText = str
com.ExecuteNonQuery()
con.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
MsgBox("Record Edited Successfully!", MsgBoxStyle.Information)
DisplayDGV()
Dis()
clear()
End Sub
Private Sub removebtn_Click(sender As System.Object, e As System.EventArgs) Handles removebtn.Click
Dim str As String
Dim ans As Integer
ans = MsgBox("Are You Sure Want To Remove The Data?", vbYesNo + vbQuestion, "Warning!")
If (ans = MsgBoxResult.Yes) Then
Try
con.Open()
str = "delete from Batches where btbatchid like '" & batchidcmb.Text & "'"
com = New SqlCommand(str, con)
com.CommandText = str
com.ExecuteNonQuery()
con.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
MsgBox("Record Removed Successfully!", MsgBoxStyle.Exclamation)
DisplayDGV()
Dis()
clear()
End If
End Sub
Private Sub bckbtn_Click(sender As System.Object, e As System.EventArgs) Handles bckbtn.Click
Me.Dispose()
HomePage.Show()
End Sub
Private Sub batchidcmb_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles batchidcmb.SelectedIndexChanged
Dim com As SqlCommand
Dim dr As SqlDataReader
Try
con.Open()
Dim str As String
str = "select btbatchid, bttime, btday, ccoursename, stfstaffid, stfname, stfsurname from Batches where btbatchid like '" & batchidcmb.Text & "'"
com = New SqlCommand(str, con)
dr = com.ExecuteReader
While dr.Read
batchidcmb.Text = dr.GetString(0)
timetxt.Text = dr.GetString(1)
daytxt.Text = dr.GetString(2)
coursenamecmb.Text = dr.GetString(3)
staffidcmb.Text = dr.GetValue(4)
stfnametxt.Text = dr.GetString(5)
stflnametxt.Text = dr.GetString(6)
End While
con.Close()
Catch ex As SqlException
MessageBox.Show(ex.Message)
End Try
End Sub
Private Sub coursenamecmb_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles coursenamecmb.SelectedIndexChanged
Dim com As SqlCommand
Dim dr As SqlDataReader
Try
con.Open() // Here the error is
Dim str As String
str = "Select ccoursename, stfstaffid, stfname, stfsurname from CoursesStaff where ccoursename like '" & coursenamecmb.Text & "'"
com = New SqlCommand(str, con)
dr = com.ExecuteReader
While dr.Read
coursenamecmb.Text = dr.GetString(0)
staffidcmb.Text = dr.GetValue(1)
stfnametxt.Text = dr.GetValue(2)
stflnametxt.Text = dr.GetValue(3)
End While
con.Close()
Catch ex As SqlException
MessageBox.Show(ex.Message)
End Try
End Sub
Private Sub StaffRegister()
Dim str As String
str = "SELECT stfstaffid,stfname,stfmiddlename,stfsurname,stfdob,stfqualification,stfspecialarea,stfexperience,stfage,stfgender,stfcontact,stfaddress,stfemailid,stfdatereg from StaffRegister"
Dim com As New SqlCommand(str, con)
Dim da As New SqlDataAdapter(com)
Dim dt As New DataTable
Dim ds As New DataSet
Try
str = "SELECT stfstaffid,stfname,stfmiddlename,stfsurname,stfdob,stfqualification,stfspecialarea,stfexperience,stfage,stfgender,stfcontact,stfaddress,stfemailid,stfdatereg from StaffRegister"
da.SelectCommand = com
da.Fill(dt)
staffidcmb.DisplayMember = "stfstaffid"
staffidcmb.DataSource = dt
da.Dispose()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Sub Coursename()
Dim str As String
str = "SELECT ccoursename, cfees from Course"
Dim dt As New DataTable
Dim ds As New DataSet
Try
str = "SELECT ccoursename, cfees from Course"
Dim com As New SqlCommand(str, con)
Dim da As New SqlDataAdapter(com)
da.SelectCommand = com
da.Fill(dt)
coursenamecmb.DisplayMember = "ccoursename"
coursenamecmb.DataSource = dt
da.Dispose()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Sub Batches()
Dim str As String
'str = "SELECT btbatchid, bttime, btday, ccoursename, stfstaffid, stfname, stfsurname from Batches"
Dim dt As New DataTable
Dim ds As New DataSet
Try
str = "SELECT btbatchid, bttime, btday, ccoursename, stfstaffid, stfname, stfsurname from Batches"
Dim com As New SqlCommand(str, con)
Dim da As New SqlDataAdapter(com)
da.SelectCommand = com
da.Fill(dt)
DataGridView1.DataSource = dt
da.Dispose()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Sub Batches_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
StaffRegister()
Coursename()
Batches()
Dis()
clear()
End Sub
End Class</pre>
What I have tried:
I have made Connection module in which the connection string is their. I don't know why the connection is remaining open. Also the update code is not working which is on "editbtn_click" Everything is getting update but the BatchID is not getting update.
Submit an article or tip