Insert and Update Data to DBF with VB.net

Put this script to General > Declarations

Imports System.Data.OleDb

Script :

Dim FileName As String = IO.Path.Combine(gcPath, “asal.dbf”)
If IO.File.Exists(FileName) Then
Dim Builder As New OleDbConnectionStringBuilder With
{
.DataSource = IO.Path.GetDirectoryName(FileName),
.Provider = “Microsoft.Jet.OLEDB.4.0”
}
Builder.Add(“Extended Properties”, “dBase III”)
Using cn As New OleDb.OleDbConnection With {.ConnectionString = Builder.ConnectionString}
Using dscmd As New OleDbCommand With {.Connection = cn}
cn.Open()
If InsUpdt = “Update” Then
dscmd.CommandText = “update ” & IO.Path.GetFileName(FileName) & ” set nm_asal = ‘” + txtNm_Asal.Text + “‘ where kd_asal = ‘” + cboKd_Asal.Text + “‘”
Else
If InsUpdt = “Insert” Then
Dim query As String
query = “Insert Into ” & IO.Path.GetFileName(FileName) & ” (”
Query = Query + “kd_asal,nm_asal,add_user,tgl_add,jam_add”
Query = Query + “) values (‘”
Query = Query + cboKd_Asal.Text + “‘,'”
Query = Query + txtNm_Asal.Text + “‘,'”
Query = Query + MDIParent1.gcUserID + “‘,'”
Query = Query + Format(Now.Date, “yyyy-MM-dd”) + “‘,'”
Query = Query + DateTime.Now.ToString(“HH:mm:ss”) + “‘)”
dscmd.CommandText = query
End If
End If
dscmd.ExecuteNonQuery()
cn.Close()
End Using
End Using
End If

Show Datagridview from DBF

Put this code to General > Declarations .

Imports System.Data.OleDb

Script :

Dim FileName As String = IO.Path.Combine(gcPath, “stock.dbf”)
If IO.File.Exists(FileName) Then
Dim Builder As New OleDbConnectionStringBuilder With
{
.DataSource = IO.Path.GetDirectoryName(FileName),
.Provider = “Microsoft.Jet.OLEDB.4.0”
}
Builder.Add(“Extended Properties”, “dBase III”)
Using cn As New OleDb.OleDbConnection With {.ConnectionString = Builder.ConnectionString}
Using dscmd As New OleDbCommand With {.Connection = cn}
dscmd.CommandText = “SELECT kd_barang,nm_barang FROM ” & IO.Path.GetFileName(FileName)
Dim oDa As New OleDbDataAdapter(dscmd)
Dim dtable As New DataTable()
oDa.Fill(dtable)

stockView = New DataView(dtable)
End Using
End Using
End If

DataGridView1.DataSource = stockView
        DataGridView1.RowHeadersVisible = False
        DataGridView1.Columns(0).HeaderCell.Value = “Kode Barang”
        DataGridView1.Columns(1).HeaderCell.Value = “Nama Barang”

SelectedIndexChanged

 

Private Sub cboUOMKey_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboUOMKey.SelectedIndexChanged
uomView.RowFilter = “uomkey = ‘” & Trim(cboUOMKey.Text) & “‘”
If uomView.Count > 0 Then
txtUOMDescription.Text = uomView.Item(0).Item(“uomdescription”).ToString
Else
txtUOMDescription.Text = “”
End If
End Sub

Call UOMSelect

Script

Private Sub btnUOM_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUOM.Click
frmUOMSelect.ShowDialog()
If frmUOMSelect.isOK Then
If cboUOMKey.Items.Count = 0 Then
fillUOM()
For i = 0 To uomView.Table.Rows.Count – 1
cboUOMKey.Items.Add(uomView.Item(i).Item(“uomkey”).ToString)
Next
End If
cboUOMKey.Text = frmUOMSelect.selected_uom
End If
End Sub

 

Example for select

It’s example for help search in datagridview.

Capture

Script :

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms
Public Class frmUOMSelect
Dim strCon As String
Dim uomView As DataView

Public isOK As Boolean = False
Public selected_uom As String = “”
Private Sub frmUOMSelect_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
fillUOM()
DataGridView1.DataSource = uomView
DataGridView1.RowHeadersVisible = False
DataGridView1.Columns(0).HeaderCell.Value = “UOM Key”
DataGridView1.Columns(1).HeaderCell.Value = “UOM Description”
End Sub
Private Function fillUOM() As Boolean
strCon = modDataSource.strCon_Online
Try
Dim cn As New SqlConnection(strCon)
cn.Open()

Dim strSelect As String = “select uomkey,uomdescription from uom”
Dim dscmd As New SqlDataAdapter(strSelect, cn)

Dim dtable As New DataTable()
dscmd.Fill(dtable)

uomView = New DataView(dtable)
cn.Close()
fillUOM = True
Catch ex As Exception
MsgBox(“Connection to database online failed.”)
fillUOM = False
End Try
End Function
Private Sub filterUOMView()
Dim fltstr As String = “”
If txtUOMDescription.Text <> “” Then
fltstr = “uomdescription like ‘%” & Trim(txtUOMDescription.Text) & “%'”
End If
If txtUOMKey.Text <> “” Then
If fltstr <> “” Then
fltstr = fltstr & ” and ”
End If
fltstr = fltstr & ” uomkey like ‘%” & Trim(txtUOMKey.Text) & “%'”
End If
uomView.RowFilter = fltstr
End Sub

Private Sub btnSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelect.Click
isOK = True
Dim idx As Integer = DataGridView1.CurrentRow.Index
selected_uom = uomView.Item(idx).Item(“uomkey”).ToString
Me.Close()
End Sub

Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
Me.Close()
End Sub

Private Sub txtUOMKey_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtUOMKey.TextChanged
filterUOMView()
End Sub

Private Sub txtUOMDescription_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtUOMDescription.TextChanged
filterUOMView()
End Sub
End Class

 

Populate combobox from database vb.net

Call to populate

fillUOM()
For i = 0 To uomView.Table.Rows.Count – 1
cboUOMKey.Items.Add(uomView.Item(i).Item(“uomkey”).ToString)
Next

Function to fill

Private Function fillUOM()
strCon = modDataSource.strCon_Online

Try
Dim cn As New SqlConnection(strCon)
cn.Open()

Dim strSelect As String = “select uomkey,uomdescription ” _
& “from uom”
Dim dscmd As New SqlDataAdapter(strSelect, cn)

Dim dtable As New DataTable()
dscmd.Fill(dtable)

uomView = New DataView(dtable)
cn.Close()
fillUOM = True
Catch ex As Exception
MsgBox(“Connection to database online failed.”)
fillUOM = False
End Try

End Function