Dear Friends first of all, you will design a vb.net form for customers or vendors. In this form you can fill the entry of customers details and you can see the all records of you customer in the datagridview as you can see left side in you form. and bottom of the form you will get the search option like search GSTIN wise and search Name wise after that you will find three button. with first (Getdata) you can find all records of Customers or Vendors. with second (Reset) you can remove all record from datagridview. and last is (Export Excel) you can do the Export in Excel formate all record witch you can see in the datagridview. Now I will give image along with source code.
Imports System.Data.OleDb
Imports System.Security.Cryptography
Imports System.Text
Imports Excel = Microsoft.Office.Interop.Excel
Public Class frmCustomer
Dim rdr As OleDbDataReader = Nothing
Dim con As OleDbConnection = Nothing
Dim cmd As OleDbCommand = Nothing
Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\SI_DB.accdb;Persist Security Info=False;"
Public Sub Clear()
txtCode.Text = ""
txtName.Text = ""
txtAddress.Text = ""
txtMobile.Text = ""
txtGSTIN.Text = ""
End Sub
Private Sub NewRecord_Click(sender As Object, e As EventArgs) Handles NewRecord.Click
Clear()
Save.Enabled = True
txtName.Focus()
End Sub
Private Sub auto()
txtCode.Text = "C-" & GetUniqueKey(4)
End Sub
Public Shared Function GetUniqueKey(ByVal maxSize As Integer) As String
Dim chars As Char() = New Char(61) {}
chars = "123456789".ToCharArray()
Dim data As Byte() = New Byte(0) {}
Dim crypto As New RNGCryptoServiceProvider()
crypto.GetNonZeroBytes(data)
data = New Byte(maxSize - 1) {}
crypto.GetNonZeroBytes(data)
Dim result As New StringBuilder(maxSize)
For Each b As Byte In data
result.Append(chars(b Mod (chars.Length)))
Next
Return result.ToString()
End Function
Private Sub Save_Click(sender As Object, e As EventArgs) Handles Save.Click
If txtName.Text = "" Then
MsgBox("Enter the Name")
txtName.Focus()
Exit Sub
End If
Try
auto()
con = New OleDbConnection(cs)
con.Open()
Dim cb As String = "insert into CustomerProfile(C_CODE,C_NAME,C_ADDRESS,C_Mobile,GSTIN,Company) VALUES (@d1,@d2,@d3,@d4,@d5,@d6)"
cmd = New OleDbCommand(cb)
cmd.Connection = con
cmd.Parameters.Add(New OleDbParameter("@d1", System.Data.OleDb.OleDbType.VarChar, 100, "(C_CODE"))
cmd.Parameters.Add(New OleDbParameter("@d2", System.Data.OleDb.OleDbType.VarChar, 250, "C_NAME"))
cmd.Parameters.Add(New OleDbParameter("@d3", System.Data.OleDb.OleDbType.VarChar, 250, "C_ADDRESS"))
cmd.Parameters.Add(New OleDbParameter("@d4", System.Data.OleDb.OleDbType.VarChar, 50, "C_Mobile"))
cmd.Parameters.Add(New OleDbParameter("@d5", System.Data.OleDb.OleDbType.VarChar, 50, "GSTIN"))
cmd.Parameters.Add(New OleDbParameter("@d6", System.Data.OleDb.OleDbType.VarChar, 50, "Company"))
cmd.Parameters("@d1").Value = txtCode.Text
cmd.Parameters("@d2").Value = txtName.Text
cmd.Parameters("@d3").Value = txtAddress.Text
cmd.Parameters("@d4").Value = txtMobile.Text
cmd.Parameters("@d5").Value = txtGSTIN.Text
cmd.Parameters("@d6").Value = frmMain.Label6.Text
cmd.ExecuteReader()
MessageBox.Show("Successfully saved", "Customer Details", MessageBoxButtons.OK, MessageBoxIcon.Information)
Save.Enabled = False
DataGridView1.DataSource = GetData()
If con.State = ConnectionState.Open Then
con.Close()
End If
con.Close()
'End If
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Private ReadOnly Property Connection() As OleDbConnection
Get
Dim ConnectionToFetch As New OleDbConnection(cs)
ConnectionToFetch.Open()
Return ConnectionToFetch
End Get
End Property
Public Function GetData() As DataView
Dim SelectQry = "SELECT (C_CODE) as [CustomerID],(C_NAME) as [Name],(C_ADDRESS) as [Address],(C_Mobile) as [MOBILE],(GSTIN) as [GSTIN] from CustomerProfile where Company = '" & frmMain.Label6.Text & "' order by C_NAME"
Dim SampleSource As New DataSet
Dim TableView As DataView
Try
Dim SampleCommand As New OleDbCommand()
Dim SampleDataAdapter = New OleDbDataAdapter()
SampleCommand.CommandText = SelectQry
SampleCommand.Connection = Connection
SampleDataAdapter.SelectCommand = SampleCommand
SampleDataAdapter.Fill(SampleSource)
TableView = SampleSource.Tables(0).DefaultView
Catch ex As Exception
Throw ex
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Return TableView
End Function
Private Sub frmCustomer_Activated(sender As Object, e As EventArgs) Handles Me.Activated
DataGridView1.Rows(0).DefaultCellStyle.Font = New Font("Arial", 9, FontStyle.Bold)
End Sub
Private Sub frmCustomer_KeyDown(sender As Object, e As KeyEventArgs) Handles Me.KeyDown
If e.KeyCode = Keys.Escape Then
Me.Close()
End If
End Sub
Private Sub frmCustomer_Load(sender As Object, e As EventArgs) Handles MyBase.Load
DataGridView1.DataSource = GetData()
DataGridView1.Rows(0).DefaultCellStyle.Font = New Font("Arial", 9, FontStyle.Bold)
End Sub
Private Sub BT_GetData_Click(sender As Object, e As EventArgs) Handles BT_GetData.Click
DataGridView1.DataSource = GetData()
DataGridView1.Rows(0).DefaultCellStyle.Font = New Font("Arial", 9, FontStyle.Bold)
End Sub
Private Sub Update_Record_Click(sender As Object, e As EventArgs) Handles Update_Record.Click
If txtName.Text = "" Then
MsgBox("Name field is Empty")
txtName.Focus()
End If
Try
con = New OleDbConnection(cs)
con.Open()
Dim cb As String = "update CustomerProfile set C_NAME='" & txtName.Text & "',C_ADDRESS='" & txtAddress.Text & "',C_Mobile='" & txtMobile.Text & "',GSTIN='" & txtGSTIN.Text & "' where C_CODE ='" & txtCode.Text & "'"
cmd = New OleDbCommand(cb)
cmd.Connection = con
cmd.ExecuteReader()
MessageBox.Show("Successfully updated", "Customer Details", MessageBoxButtons.OK, MessageBoxIcon.Information)
Update_Record.Enabled = False
Delete.Enabled = False
DataGridView1.DataSource = GetData()
Clear()
If con.State = ConnectionState.Open Then
con.Close()
End If
con.Close()
'End If
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Private Sub delete_records()
Try
Dim RowsAffected As Integer = 0
con = New OleDbConnection(cs)
con.Open()
Dim cq1 As String = "delete from CustomerProfile where C_CODE=@DELETE1;"
cmd = New OleDbCommand(cq1)
cmd.Connection = con
cmd.Parameters.Add(New OleDbParameter("@DELETE1", System.Data.OleDb.OleDbType.VarChar, 20, "C_CODE"))
cmd.Parameters("@DELETE1").Value = Trim(txtcode.Text)
RowsAffected = cmd.ExecuteNonQuery()
If RowsAffected > 0 Then
MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
'Button1.PerformClick()
Else
MessageBox.Show("No record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
'Button1.PerformClick()
If con.State = ConnectionState.Open Then
con.Close()
End If
con.Close()
End If
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Private Sub Delete_Click(sender As Object, e As EventArgs) Handles Delete.Click
Try
If MessageBox.Show("Do you really want to delete the record?", "Customer Profile Record", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
delete_records()
Clear()
Update_Record.Enabled = False
Delete.Enabled = False
DataGridView1.DataSource = GetData()
End If
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Private Sub DataGridView1_RowHeaderMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles DataGridView1.RowHeaderMouseClick
If Label8.Text = "SaleReturn" Then
Try
Dim dr As DataGridViewRow = DataGridView1.SelectedRows(0)
Me.Close()
frmSaleReturn.txtCustomerID.Text = dr.Cells(0).Value.ToString()
frmSaleReturn.txtCustomerName.Text = dr.Cells(1).Value.ToString()
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Exit Sub
End If
If Label8.Text = "Sale" Then
Try
Dim dr As DataGridViewRow = DataGridView1.SelectedRows(0)
Me.Close()
frmSale.txtCustomerID.Text = dr.Cells(0).Value.ToString()
frmSale.txtCustomerName.Text = dr.Cells(1).Value.ToString()
frmSale.txtAddress.Text = dr.Cells(2).Value.ToString()
frmSale.txtMobile.Text = dr.Cells(3).Value.ToString()
frmSale.txtGSTIN.Text = dr.Cells(4).Value.ToString()
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End If
Try
Dim dr As DataGridViewRow = DataGridView1.SelectedRows(0)
'Me.Hide()
txtCode.Text = dr.Cells(0).Value.ToString()
txtName.Text = dr.Cells(1).Value.ToString()
txtAddress.Text = dr.Cells(2).Value.ToString()
txtMobile.Text = dr.Cells(3).Value.ToString()
txtGSTIN.Text = dr.Cells(4).Value.ToString()
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
'Me.Close()
Save.Enabled = False
Update_Record.Enabled = True
Delete.Enabled = True
End Sub
Public Function SearchGSTIN() As DataView
Dim SelectQry = "SELECT (C_CODE) as [CustomerID],(C_NAME) as [Name],(C_ADDRESS) as [Address],(C_Mobile) as [MOBILE],(GSTIN) as [GSTIN] from CustomerProfile where Company = '" & frmMain.Label6.Text & "' and GSTIN like '" & txtGSTINSearch.Text & "%' order by C_NAME"
Dim SampleSource As New DataSet
Dim TableView As DataView
Try
Dim SampleCommand As New OleDbCommand()
Dim SampleDataAdapter = New OleDbDataAdapter()
SampleCommand.CommandText = SelectQry
SampleCommand.Connection = Connection
SampleDataAdapter.SelectCommand = SampleCommand
SampleDataAdapter.Fill(SampleSource)
TableView = SampleSource.Tables(0).DefaultView
Catch ex As Exception
Throw ex
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Return TableView
End Function
Private Sub txtGSTINSearch_TextChanged(sender As Object, e As EventArgs) Handles txtGSTINSearch.TextChanged
DataGridView1.DataSource = SearchGSTIN()
DataGridView1.Rows(0).DefaultCellStyle.Font = New Font("Arial", 9, FontStyle.Bold)
End Sub
Public Function SearchName() As DataView
Dim SelectQry = "SELECT (C_CODE) as [CustomerID],(C_NAME) as [Name],(C_ADDRESS) as [Address],(C_Mobile) as [MOBILE],(GSTIN) as [GSTIN] from CustomerProfile where Company = '" & frmMain.Label6.Text & "' and C_Name like '" & txtSearchName.Text & "%' order by C_NAME"
Dim SampleSource As New DataSet
Dim TableView As DataView
Try
Dim SampleCommand As New OleDbCommand()
Dim SampleDataAdapter = New OleDbDataAdapter()
SampleCommand.CommandText = SelectQry
SampleCommand.Connection = Connection
SampleDataAdapter.SelectCommand = SampleCommand
SampleDataAdapter.Fill(SampleSource)
TableView = SampleSource.Tables(0).DefaultView
Catch ex As Exception
Throw ex
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Return TableView
End Function
Private Sub txtSearchName_TextChanged(sender As Object, e As EventArgs) Handles txtSearchName.TextChanged
DataGridView1.DataSource = SearchName()
DataGridView1.Rows(0).DefaultCellStyle.Font = New Font("Arial", 9, FontStyle.Bold)
End Sub
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
DataGridView1.DataSource = Nothing
End Sub
Private Sub BT_Export_Click(sender As Object, e As EventArgs) Handles BT_Export.Click
If DataGridView1.RowCount = Nothing Then
MessageBox.Show("Sorry nothing to export into excel sheet.." & vbCrLf & "Please retrieve data in datagridview", "", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End If
Dim rowsTotal, colsTotal As Short
Dim I, j, iC As Short
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
Dim xlApp As New Excel.Application
Try
Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
xlApp.Visible = True
rowsTotal = DataGridView1.RowCount - 1
colsTotal = DataGridView1.Columns.Count - 1
With excelWorksheet
.Cells.Select()
.Cells.Delete()
For iC = 0 To colsTotal
.Cells(1, iC + 1).Value = DataGridView1.Columns(iC).HeaderText
Next
For I = 0 To rowsTotal - 1
For j = 0 To colsTotal
.Cells(I + 2, j + 1).value = DataGridView1.Rows(I).Cells(j).Value
Next j
Next I
.Rows("1:1").Font.FontStyle = "Bold"
.Rows("1:1").Font.Size = 12
.Cells.Columns.AutoFit()
.Cells.Select()
.Cells.EntireColumn.AutoFit()
.Cells(1, 1).Select()
End With
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
'RELEASE ALLOACTED RESOURCES
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
xlApp = Nothing
End Try
End Sub
Private Sub txtName_KeyDown(sender As Object, e As KeyEventArgs) Handles txtName.KeyDown
If e.KeyCode = Keys.Enter Then
txtAddress.Focus()
End If
End Sub
Private Sub txtName_TextChanged(sender As Object, e As EventArgs) Handles txtName.TextChanged
End Sub
Private Sub txtAddress_KeyDown(sender As Object, e As KeyEventArgs) Handles txtAddress.KeyDown
If e.KeyCode = Keys.Enter Then
txtMobile.Focus()
End If
End Sub
Private Sub txtAddress_TextChanged(sender As Object, e As EventArgs) Handles txtAddress.TextChanged
End Sub
Private Sub txtMobile_KeyDown(sender As Object, e As KeyEventArgs) Handles txtMobile.KeyDown
If e.KeyCode = Keys.Enter Then
txtGSTIN.Focus()
End If
End Sub
Private Sub txtMobile_TextChanged(sender As Object, e As EventArgs) Handles txtMobile.TextChanged
End Sub
Private Sub txtGSTIN_KeyDown(sender As Object, e As KeyEventArgs) Handles txtGSTIN.KeyDown
If e.KeyCode = Keys.Enter Then
Save.Focus()
End If
End Sub
Private Sub txtGSTIN_TextChanged(sender As Object, e As EventArgs) Handles txtGSTIN.TextChanged
End Sub
Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
End Sub
End Class
No comments:
Post a Comment