Thread: problem in search form

i made the form of search of my project as in screen shot in vb.net the problem is that it shows the record many time of one person i put the sql query this
qury = "SELECT p.per_id AS 'Person ID', " & _ "p.per_name AS 'Name', " & _
"p.f_name AS 'Father Name'," & _
"p.per_address AS 'Address', " & _
"p.per_cell AS 'Cell No', " & _
"p.per_cnic AS 'CNIC #', " & _
"c.chak_name As 'Chak', " & _
"r.rev_name As 'Circle', " & _
"t.teh_name As 'Tehsil' " & _
"FROM RRDBMS.dbo.person_info p, RRDBMS.dbo.chak c , RRDBMS.dbo.rev_circle r, RRDBMS.dbo.tehsil t " & _
"WHERE " & _
"p.chak_id = c.chak_id And " & _
"c.rev_id = r.rev_id And " & _
"r.teh_id = t.teh_id And " & _
"p.per_address LIKE '" & address & "'OR " & _
"p.f_name LIKE '" & fname & "' OR " & _
"p.per_name LIKE '" & name & "' OR " & _
"p.per_cnic LIKE '" & cnic & "'OR " & _
"p.per_cell LIKE '" & cell & "' OR " & _
"p.per_id LIKE '" & id & "' "
please help to solve my problem its urgentsearch.png

I suspect the problem is not the query so much as where in the code you put it. A view of that wil be rather more enlightening than a picture of the result!

Originally Posted by Dunfiddlin

I suspect the problem is not the query so much as where in the code you put it. A view of that wil be rather more enlightening than a picture of the result!

can you please help then finding the problem ? i have tired to see it

Let me see the code and I might have a chance!

Originally Posted by Dunfiddlin

Let me see the code and I might have a chance!

the whole code is that
Imports System.Data.SqlClient
Public Class Search


Dim database As New Database
Dim dt As DataTable
Dim errorProvider As New ErrorProvider
Public Shared idup As String
Public Shared nameup As String
Public Shared fnameup As String
Public Shared addrup As String
Public Shared cellup As String
Public Shared cnicup As String
Public Shared chakup As String
Public Shared cirup As String
Public Shared tehup As String
Dim ds As DataSourceUpdateMode




Private Sub Search_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
dgvSearch.Visible = False
cmbAddress.SelectedIndex = 0
cmbCell.SelectedIndex = 0
cmbCNIC.SelectedIndex = 0
cmbFName.SelectedIndex = 0
cmbID.SelectedIndex = 0
cmbName.SelectedIndex = 0
End Sub


Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
dgvSearch.Visible = True


Dim id, name, fname, address, cnic, cell, update As String


If cmbID.SelectedIndex = 0 Then
id = txtID.Text.ToUpper
ElseIf cmbID.SelectedIndex = 1 Then
id = txtID.Text.ToUpper & "%"
ElseIf cmbID.SelectedIndex = 2 Then
id = "%" & txtID.Text.ToUpper
ElseIf cmbID.SelectedIndex = 3 Then
id = "%" & txtID.Text.ToUpper & "%"
Else
id = "%%"
End If


If cmbName.SelectedIndex = 0 Then
name = txtName.Text.ToUpper
ElseIf cmbName.SelectedIndex = 1 Then
name = txtName.Text.ToUpper & "%"
ElseIf cmbName.SelectedIndex = 2 Then
name = "%" & txtName.Text.ToUpper
ElseIf cmbName.SelectedIndex = 3 Then
name = "%" & txtName.Text.ToUpper & "%"
Else
name = "%%"
End If


If cmbFName.SelectedIndex = 0 Then
fname = txtFName.Text.ToUpper
ElseIf cmbFName.SelectedIndex = 1 Then
fname = txtFName.Text.ToUpper & "%"
ElseIf cmbFName.SelectedIndex = 2 Then
fname = "%" & txtFName.Text.ToUpper
ElseIf cmbFName.SelectedIndex = 3 Then
fname = "%" & txtFName.Text.ToUpper & "%"
Else
fname = "%%"
End If


If cmbAddress.SelectedIndex = 0 Then
address = txtAddress.Text.ToUpper
ElseIf cmbAddress.SelectedIndex = 1 Then
address = txtAddress.Text.ToUpper & "%"
ElseIf cmbAddress.SelectedIndex = 2 Then
address = "%" & txtAddress.Text.ToUpper
ElseIf cmbAddress.SelectedIndex = 3 Then
address = "%" & txtAddress.Text.ToUpper & "%"
Else
address = "%%"
End If


If cmbCell.SelectedIndex = 0 Then
cell = txtCell.Text.ToUpper
ElseIf cmbCell.SelectedIndex = 1 Then
cell = txtCell.Text.ToUpper & "%"
ElseIf cmbCell.SelectedIndex = 2 Then
cell = "%" & txtCell.Text.ToUpper
ElseIf cmbCell.SelectedIndex = 3 Then
cell = "%" & txtCell.Text.ToUpper & "%"
Else
cell = "%%"
End If


If cmbCNIC.SelectedIndex = 0 Then
cnic = txtCNIC.Text.ToUpper
ElseIf cmbCNIC.SelectedIndex = 1 Then
cnic = txtCNIC.Text.ToUpper & "%"
ElseIf cmbCNIC.SelectedIndex = 2 Then
cnic = "%" & txtCNIC.Text.ToUpper
ElseIf cmbCNIC.SelectedIndex = 3 Then
cnic = "%" & txtCNIC.Text.ToUpper & "%"
Else
cnic = "%%"
End If


'Dim query = "SELECT * FROM person_info WHERE per_name LIKE '%" & txtName.Text & "'" & _
' "OR per_name LIKE '%" & txtName.Text & "'" & _
' "OR per_name LIKE '" & txtName.Text & "%'"


Dim query As String
query = "SELECT p.per_id AS 'Person ID', " & _
"p.per_name AS 'Name', " & _
"p.f_name AS 'Father Name'," & _
"p.per_address AS 'Address', " & _
"p.per_cell AS 'Cell No', " & _
"p.per_cnic AS 'CNIC #', " & _
"c.chak_name As 'Chak', " & _
"r.rev_name As 'Circle', " & _
"t.teh_name As 'Tehsil' " & _
"FROM RRDBMS.dbo.person_info p, RRDBMS.dbo.chak c , RRDBMS.dbo.rev_circle r, RRDBMS.dbo.tehsil t " & _
"WHERE " & _
"p.chak_id = c.chak_id And " & _
"c.rev_id = r.rev_id And " & _
"r.teh_id = t.teh_id And " & _
"p.per_address LIKE '" & address & "'OR " & _
"p.f_name LIKE '" & fname & "' OR " & _
"p.per_name LIKE '" & name & "' OR " & _
"p.per_cnic LIKE '" & cnic & "'OR " & _
"p.per_cell LIKEfb '" & cell & "' OR " & _
"p.per_id LIKE '" & id & "' "


Dim qury As String


qury = "SELECT p.per_id AS 'Person ID', " & _
"p.per_name AS 'Name', " & _
"p.f_name AS 'Father Name'," & _
"p.per_address AS 'Address', " & _
"p.per_cell AS 'Cell No', " & _
"p.per_cnic AS 'CNIC #', " & _
"c.chak_name As 'Chak', " & _
"r.rev_name As 'Circle', " & _
"t.teh_name As 'Tehsil' " & _
"FROM RRDBMS.dbo.person_info p, RRDBMS.dbo.chak c , RRDBMS.dbo.rev_circle r, RRDBMS.dbo.tehsil t " & _
"WHERE " & _
"p.chak_id = c.chak_id And " & _
"c.rev_id = r.rev_id And " & _
"r.teh_id = t.teh_id And " & _
"p.per_address LIKE '" & address & "'OR " & _
"p.f_name LIKE '" & fname & "' OR " & _
"p.per_name LIKE '" & name & "' OR " & _
"p.per_cnic LIKE '" & cnic & "'OR " & _
"p.per_cell LIKE '" & cell & "' OR " & _
"p.per_id LIKE '" & id & "' "


database.creatConn()
dt = database.getDS(qury, "tehsil")
dgvSearch.DataSource = dt
database.endConn()




'dgvSearch.Columns("edit").DisplayIndex = dgvSearch.ColumnCount - 1
dgvSearch.DataSource = ds.Tables(1)
clear()


End Sub
Private Sub clear()
cmbAddress.SelectedIndex = 0
cmbCell.SelectedIndex = 0
cmbCNIC.SelectedIndex = 0
cmbFName.SelectedIndex = 0
cmbID.SelectedIndex = 0
cmbName.SelectedIndex = 0
txtAddress.Text = ""
txtCell.Text = ""
txtCNIC.Text = ""
txtFName.Text = ""
txtID.Text = ""
txtName.Text = ""
End Sub


Private Sub DataGridView1_CellContentClick_1(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs)
If e.ColumnIndex = 0 Then
MessageBox.Show(dgvSearch.Item(1, e.RowIndex).Value)
End If
End Sub


Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click


Me.Close()
End Sub


Private Sub dgvSearch_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvSearch.CellContentClick


If e.ColumnIndex = 9 Then
idup = dgvSearch.Item(1, e.RowIndex).Value
nameup = dgvSearch.Item(2, e.RowIndex).Value
fnameup = dgvSearch.Item(3, e.RowIndex).Value
addrup = dgvSearch.Item(4, e.RowIndex).Value
cellup = dgvSearch.Item(5, e.RowIndex).Value
cnicup = dgvSearch.Item(6, e.RowIndex).Value
chakup = dgvSearch.Item(7, e.RowIndex).Value
cirup = dgvSearch.Item(8, e.RowIndex).Value
tehup = dgvSearch.Item(9, e.RowIndex).Value
Me.Hide()
' UpdateData.ShowDialog()


End If
End Sub

Well the good news is you don't have any hidden loops. However, you do seem to have 2 queries only one of which is used. And the query apparently searches four tables for identical information. As it returns a result from each of them, if the record is in all four tables you get four identical results.

the 2 query are same but in different ways one is on comments and four table have primary keys which are shifted as a forign key other tables

It really doesn't matter what the justification is, if you search 4 tables you'll get 4 results.

Originally Posted by Dunfiddlin

It really doesn't matter what the justification is, if you search 4 tables you'll get 4 results.

now what i have to do now ?

If the tables are identical except for their primary keys then only search one of them. If not you'll have to find a way to filter out duplicates in the grid.