Option Explicit On
Imports System.Data.OleDb
Imports System.Data
Public Class frmSupplier
Dim AddMode As Boolean
Dim Ctrl As Integer
Dim myConn As New OleDbConnection
Dim myCmd As New OleDbCommand
Dim myDA As New OleDbDataAdapter
Dim myDR As OleDbDataReader
Dim strSQL As String
Dim ds As DataSet
'"Declaration (Join Club)"
Dim Ctrl2 As Integer
'Dim myConn As New OleDbConnection
Dim myCmd2 As New OleDbCommand
Dim myDA2 As New OleDbDataAdapter
Dim myDR2 As OleDbDataReader
Dim strSQL2 As String
Dim ds2 As DataSet
Function IsConnected() As Boolean
Try
'Checks first if already connected to database,if connected, it will be disconnected.
If myConn.State = ConnectionState.Open Then myConn.Close()
myConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\salesInventory.mdb;Persist Security Info=False;Jet OLEDB:Database Password = sales"
myConn.Open()
IsConnected = True
Catch ex As Exception
MsgBox(ex.Message, , "Connection")
End Try
End Function
Function IsExists() As Boolean
SearchSM(txtboxID.Text)
If myDR.HasRows = True Then
Return True
Else
Return False
End If
End Function
Sub SearchSM(ByVal SearchWord As String)
Try
If IsConnected() = True Then
'Queries to database
If tsbtnSave.Enabled = True Then
strSQL = "SELECT * FROM SUPPLIER WHERE S_ID LIKE '" & SearchWord & "%'"
Else
strSQL = "SELECT * FROM SUPPLIER WHERE S_Name LIKE '" _
& SearchWord & "%' OR S_Address LIKE '" _
& SearchWord & "%' OR S_Contactno LIKE '" _
& SearchWord & "%' ORDER BY S_Name"
End If
myCmd.CommandText = strSQL
myCmd.Connection = myConn
myDA.SelectCommand = myCmd
myDR = myCmd.ExecuteReader()
'Display results to table
'lvwRecipients.Items.Clear()
While (myDR.Read())
With lvwSupplier.Items.Add(myDR("S_ID").ToString)
.SubItems.Add(myDR("S_Name"))
.SubItems.Add(myDR("S_Address"))
.SubItems.Add(myDR("S_Contactno"))
End With
End While
End If
Catch ex As Exception
MsgBox(ex.Message, , "SearchSM")
End Try
'---------inserted code to count the number of columns
ds = New DataSet
myDA = New OleDbDataAdapter(strSQL, myConn)
myDA.Fill(ds, "MyData")
Dim kRow1 As Integer = ds.Tables("MyData").Rows.Count 'TOTAL ROW
lblTotal.Text = "Total No. of Recipients : " + kRow1.ToString
'------------------------end
lvwSupplier.Items.Clear()
Dim i As Integer 'COLUMNS
Dim iCol As Integer = ds.Tables("MyData").Columns.Count 'TOTAL COLUMN
Dim k As Integer 'ROWS
Dim kRow As Integer = ds.Tables("MyData").Rows.Count 'TOTAL ROW
Dim ThisRow(4) As String '//=For each Loop of ROW
For k = 0 To kRow - 1
For i = 0 To iCol - 1
ThisRow(i) = ds.Tables("MyData").Rows(k).Item(i).ToString
Next
Dim lsvi As New ListViewItem(ThisRow, 2)
'//=Start Alternate Color
If k / 2 <> Int(k / 2) Then '//=Determine Alternate Row(odd or not)
lsvi.BackColor = Color.SkyBlue
Else
lsvi.BackColor = Color.LightCyan
End If
'//=End Alternate Color
lvwSupplier.Items.Add(lsvi)
Next
End Sub
Sub SaveEntry(ByVal S_ID As Integer, ByVal S_Name As String, _
ByVal S_Address As String, ByVal S_Contactno As String)
Try
If IsExists() = False Then
'Adds new record to the table
strSQL = "INSERT INTO SUPPLIER(S_ID, S_Name, S_Address, S_Contactno) VALUES('" _
& S_ID & "' ,'" _
& S_Name.ToUpper & "' ,'" _
& S_Address.ToUpper & "' ,'" _
& S_Contactno & "')"
Else
'Updates record from the table
strSQL = "UPDATE SUPPLIER SET S_ID = '" & S_ID & "'," _
& "S_Name = '" & S_Name & "'," _
& "S_Address = '" & S_Address & "'," _
& "S_Contactno = '" & S_Contactno & "'"
' & "WHERE S_ID = " & Ctrl & ""
End If
Dim myCmd As New OleDb.OleDbCommand
myCmd.CommandText = strSQL
myCmd.Connection = myConn
myCmd.ExecuteNonQuery()
tsbtnAdd.Enabled = True
tsbtnSave.Enabled = False
' LockTextboxes()
txtboxID.Text = String.Empty
txtBoxName.Text = String.Empty
txtBoxAddress.Text = String.Empty
txtBoxContact.Text = String.Empty
Catch ex As Exception
MsgBox(ex.Message, , "Save Recipient")
End Try
End Sub
Sub DeleteEntry(ByVal S_ID As Integer, ByVal S_Name As String, _
ByVal S_Address As String, ByVal S_Contactno As String)
Dim confirmDelete As DialogResult
confirmDelete = MessageBox.Show("Are you sure you want to delete this Supplier?", _
"SMS Sytem", MessageBoxButtons.YesNoCancel, _
MessageBoxIcon.Warning)
If confirmDelete = Windows.Forms.DialogResult.Yes Then
Try
If IsExists() = True Then
'Updates record from the table
strSQL = "DELETE * FROM SUPPLIER WHERE S_ID = " & txtboxID.Text & ""
End If
Dim myCmd As New OleDb.OleDbCommand
myCmd.CommandText = strSQL
myCmd.Connection = myConn
myCmd.ExecuteNonQuery()
tsbtnCancel.PerformClick()
Catch ex As Exception
MsgBox(ex.Message, , "Delete Supplier")
End Try
ElseIf confirmDelete = Windows.Forms.DialogResult.Cancel Then
tsbtnCancel.PerformClick()
End If
End Sub
Sub Locked(ByVal Mode As Boolean)
For Each Ctrl As Control In Me.Controls
If TypeOf Ctrl Is TextBox Then
Ctrl.Enabled = Not Mode
Ctrl.Text = ""
If Ctrl.Name = "txtBoxID" Then
Ctrl.Focus()
End If
' ElseIf TypeOf Ctrl Is ComboBox Then
'Ctrl.Enabled = Not Mode
' If Ctrl.Name = "cboClubName" Then
' Ctrl.Focus()
' End If
'ElseIf TypeOf Ctrl Is Button Then
'Ctrl.Enabled = Not Mode
'If Ctrl.Name = "btnAddClub" Then
' Ctrl.Focus()
' Else
' Ctrl.Enabled = True
' End If
End If
Next Ctrl
End Sub
Sub SetSearchBox()
If tstbSearchSupplier.Text = "" Then
tstbSearchSupplier.Text = "Type here to search"
tstbSearchSupplier.ForeColor = Color.Gray
End If
End Sub
Private Sub frmSupplier_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'DsClub.CLUB' table. You can move, or remove it, as needed.
SearchSM("")
Locked(True)
SetSearchBox()
End Sub
Private Sub tsbtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsbtnAdd.Click
tsbtnAdd.Enabled = False
tsbtnSave.Enabled = True
tsbtnCancel.Enabled = True
AddMode = True
Locked(False)
End Sub
Private Sub tsbtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsbtnSave.Click
'If txtBoxName.Text = "" Or txtBoxAddress.Text = _
' "" Or txtBoxContact.Text = "" Then
'MessageBox.Show("Please fill up all the data.", "Sales and Inventory Sytem", _
' MessageBoxButtons.OK, MessageBoxIcon.Information)
If IsConnected() = True Then
SaveEntry(txtboxID.Text, txtBoxName.Text, txtBoxAddress.Text, txtBoxContact.Text)
SearchSM("")
'End If
tsbtnCancel.Enabled = False
tsbtnDelete.Enabled = False
Locked(True)
End If
End Sub
Private Sub lvwSupplier_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles lvwSupplier.MouseClick
lvwSupplier_MouseDoubleClick(sender, e)
End Sub
Private Sub lvwSupplier_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles lvwSupplier.MouseDoubleClick
tsbtnSave.Enabled = True
tsbtnDelete.Enabled = True
tsbtnAdd.Enabled = True
tsbtnCancel.Enabled = True
Locked(False)
With txtboxID
.BackColor = Color.White
.ForeColor = Color.Red
.Enabled = True
End With
With lvwSupplier.SelectedItems(0)
Ctrl = .SubItems(0).Text
txtBoxName.Text = .SubItems(1).Text
txtBoxAddress.Text = .SubItems(2).Text
txtBoxContact.Text = .SubItems(3).Text
'txtYear_Level.Text = .SubItems(4).Text
End With
txtboxID.Text = Ctrl 'gets the Ctrl
' for the family name
'Dim strFamilyName As String = txtDummyName.Text
'Dim strArrFamilyName() As String
'Dim countFamilyName As Integer
'strArrFamilyName = strFamilyName.Split(",")
'For countFamilyName = 0 To strArrFamilyName.Length - 1
'txtFamilyName.Text = (strArrFamilyName(countFamilyName))
'Exit For
'Next
'for the family name
'for the first name
'Dim strFirstName As String = txtDummyName.Text
'Dim strArrFirstName() As String
'Dim countFirstName As Integer
'Dim test As String
'strArrFirstName = strFirstName.Split(", ")
' For countFirstName = 1 To strArrFirstName.Length - 1
'test = (strArrFirstName(countFirstName)).TrimStart.TrimEnd
'Dim myString As String = test
'Dim myChar As Char() = {"A"c, "B"c, "C"c, "D"c, "E"c, "F"c, "G"c, "H"c, _
' "I"c, "J"c, "K"c, "L"c, "M"c, "N"c, "O"c, "P"c, "Q"c, "R"c, _
' "S"c, "T"c, "U"c, "V"c, "W"c, "X"c, "Y"c, "Z"c, "."c}
'Dim NewString As String = myString.TrimEnd(myChar)
'txtFirstName.Text = NewString.TrimEnd
'Exit For
'Next
' for the first name
'for the middle name
'Dim strMiddleInitial As String = Microsoft.VisualBasic.Right(txtDummyName.Text, 2)
'txtMiddleInitial.Text = strMiddleInitial.TrimStart
'for the middle name
End Sub
Private Sub tsbtnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsbtnDelete.Click
If IsConnected() = True Then
DeleteEntry(txtboxID.Text, txtBoxName.Text, txtBoxAddress.Text, txtBoxContact.Text)
SearchSM("")
End If
tsbtnAdd.Enabled = True
tsbtnEdit.Enabled = True
tsbtnSave.Enabled = False
tsbtnCancel.Enabled = False
tsbtnDelete.Enabled = False
End Sub
Private Sub tsbtnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsbtnCancel.Click
Locked(True)
tsbtnCancel.Enabled = False
tsbtnDelete.Enabled = False
tsbtnSave.Enabled = False
tsbtnAdd.Enabled = True
End Sub
Private Sub tstbSearchSupplier_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles tstbSearchSupplier.Click
If tstbSearchSupplier.Text = "Type here to search" Then
tstbSearchSupplier.Text = ""
tstbSearchSupplier.ForeColor = Color.Black
ElseIf tstbSearchSupplier.Text <> "" Then
tstbSearchSupplier.SelectAll()
End If
End Sub
Private Sub tstbSearchSupplier_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles tstbSearchSupplier.KeyDown
End Sub
Private Sub tstbSearchSupplier_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles tstbSearchSupplier.KeyPress
If e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return) Then
'Checks first if system is connected then if true, continues to search
If IsConnected() = True Then Call SearchSM(tstbSearchSupplier.Text)
e.Handled = True
ElseIf e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Back) Then
SearchSM("")
'e.Handled = True
End If
End Sub
Private Sub txtboxID_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtboxID.KeyPress
Dim strAllowableChars As String
strAllowableChars = "0123456789"
If InStr(strAllowableChars, e.KeyChar) = 0 And Asc(e.KeyChar) <> 8 Then
e.Handled = True
End If
If e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return) Then
txtBoxName.Focus()
e.Handled = True
End If
End Sub
Private Sub txtBoxName_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtBoxName.KeyPress
End Sub
Private Sub txtBoxAddress_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtBoxAddress.KeyPress
End Sub
Private Sub txtBoxContact_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtBoxContact.KeyPress
End Sub
Private Sub tstbSearchSupplier_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles tstbSearchSupplier.LostFocus
SetSearchBox()
End Sub
Private Sub tstbSearchSupplier_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles tstbSearchSupplier.TextChanged
Try
Dim sItem As ListViewItem = lvwSupplier.FindItemWithText(tstbSearchSupplier.Text, True, 0)
If Not sItem Is Nothing Then
With lvwSupplier.Items(sItem.Index)
.EnsureVisible()
.Selected = True
End With
End If
Catch ex As Exception
End Try
End Sub
Private Sub lvwSupplier_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lvwSupplier.SelectedIndexChanged
End Sub
Private Sub ToolStrip1_ItemClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ToolStripItemClickedEventArgs) Handles ToolStrip1.ItemClicked
End Sub
End Class
No comments:
Post a Comment