MFI Suite.

Sun, 10 Jun 2012 01:59:26 -0700

The program has two tables, 'tblclients and tblgroup' a number of clients in tblclients belong to a particular group in tblgroup. I have difficulties trying to tie both tables in specific.
Here is my code:
<code>
Imports System.Data.OleDb

Public Class frmSearchClient
Dim CNString As String
Dim CN As New OleDbConnection
Dim cmd As New OleDbCommand
Dim dsNewRow As DataRow
Dim sql As String
Dim inc As Integer
Dim ds As New DataSet
Dim da As New OleDbDataAdapter

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Dim dbProvider As String
Dim dbSource As String
dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
dbSource = "Data Source = C:\LOCUS MICROFINANCE SUITES\AddressBookTrial.mdb"
CN.ConnectionString = dbProvider & dbSource
Dim cmd As New OleDbCommand("SELECT * FROM Clients where ID like '%" + txtSearch.Text + "%'OR Surname like '%" + txtSearch.Text + "%' OR Firstname like '%" + txtSearch.Text + "%'", CN)
Dim cmd2 As New OleDbCommand("Select Group_Name FROM Groups", CN)
Dim Adpt As New OleDbDataAdapter(cmd)
Dim adpt1 As New OleDbDataAdapter(cmd2)
Dim ds As New DataSet()
If (Adpt.Fill(ds, "Clients")) And (adpt1.Fill(ds, "Groups")) Then
dgvSearch.DataSource = ds.Tables(0)
Else
MessageBox.Show("match not found")
End If
Dim sql1 As String
sql1 = "SELECT *, Group_Name FROM Clients, Groups"
Dim adapter1 As New OleDbDataAdapter(sql1, CN)
Dim cmd1 As New OleDbCommand(sql1, CN)
CN.Open()
Dim myreader As OleDbDataReader = cmd1.ExecuteReader
myreader.Read()

CN.Close()

End Sub

Private Sub dgvSearch_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvSearch.CellContentClick
frmNewClient.Show()
' Same thing as btnUpdate_Click except the user can actually DoubleClick
' a record. You want to be sure you get the field names and cell numbers
' correct or the information will be misconstrued.
frmNewClient.txtid.Text = dgvSearch.Rows(e.RowIndex).Cells(0).Value.ToString
frmNewClient.dtDate.Value = dgvSearch.Rows(e.RowIndex).Cells(1).Value.ToString
frmNewClient.cmbTitle.Text = dgvSearch.Rows(e.RowIndex).Cells(2).Value.ToString
frmNewClient.txtSurname.Text = dgvSearch.Rows(e.RowIndex).Cells(13).Value.ToStrin g
frmNewClient.txtMiddleName.Text = dgvSearch.Rows(e.RowIndex).Cells(14).Value.ToStrin g
frmNewClient.txtFirstname.Text = dgvSearch.Rows(e.RowIndex).Cells(15).Value.ToStrin g
frmNewClient.cmbSex.Text = dgvSearch.Rows(e.RowIndex).Cells(3).Value.ToString
frmNewClient.dtDate_Of_Birth.Value = dgvSearch.Rows(e.RowIndex).Cells(4).Value.ToString
frmNewClient.txtHome_Address.Text = dgvSearch.Rows(e.RowIndex).Cells(5).Value.ToString
frmNewClient.txtCity.Text = dgvSearch.Rows(e.RowIndex).Cells(6).Value.ToString
frmNewClient.txtStateOfOrigin.Text = dgvSearch.Rows(e.RowIndex).Cells(7).Value.ToString
frmNewClient.cmbNationality.Text = dgvSearch.Rows(e.RowIndex).Cells(8).Value.ToString
frmNewClient.txtTelephone.Text = dgvSearch.Rows(e.RowIndex).Cells(9).Value.ToString
frmNewClient.cmbClientType.Text = dgvSearch.Rows(e.RowIndex).Cells(10).Value.ToStrin g
frmNewClient.cmbBranch.Text = dgvSearch.Rows(e.RowIndex).Cells(11).Value.ToStrin g
frmNewClient.btnUpdate.Visible = True
frmNewClient.SaveToolStripButton.Enabled = False

End Sub
End Class

<code/>

'MFI Suite.' Thread RSS Feed

MFI Suite.