Imports System.Data
Imports System.Data.SqlClient

Public Class LookupForm
    Dim stable As String
    Dim SelectQuery As String
    Dim cs As String
    Dim htKeys As Hashtable

    Property FKeyWithTable() As Hashtable
        Get
            Return htKeys
        End Get
        Set(ByVal value As Hashtable)
            htKeys = value
        End Set
    End Property

    Public Event LoadRecord(ByVal CurrentRow As Hashtable)
    Private Sub LookupForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        LoadFields()
    End Sub
    Property ConnectionString() As String
        Get
            Return cs
        End Get
        Set(ByVal value As String)
            cs = value
        End Set
    End Property
    Property TableName() As String
        Get
            Return stable
        End Get
        Set(ByVal value As String)
            stable = value
            SelectQuery = "Select * From " & stable
        End Set
    End Property
    Private Sub mbtnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mbtnOK.Click
        Dim ht As New Hashtable
        Dim dgc As DataGridViewCell
        If dgData.CurrentRow IsNot Nothing Then
            For Each dgc In dgData.CurrentRow.Cells
                ht.Add(dgc.ColumnIndex, dgc.Value.ToString)
            Next
        End If
        RaiseEvent LoadRecord(ht)
        Me.Close()
    End Sub

    Private Sub mbtnShowAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mbtnShowAll.Click
        If cboOp.Text.ToUpper = "MASTER DATA" Then
            If htKeys(cboFields.Text) IsNot Nothing Then
                dgData.DataSource = ShowData("Select * From " & htKeys(cboFields.Text))
            Else
                MsgBox("Specify Foreign/PrimaryKey And Its Master Table ")
            End If
        Else
            dgData.DataSource = ShowData(SelectQuery)
        End If
    End Sub

    Function ShowData(ByVal Query As String) As DataTable
        Dim con As New SqlConnection(My.Settings.DatabaseICICIConnectionString)
        Dim cmd As New SqlCommand(Query, con)
        Dim dt As DataTable = Nothing
        Try
            con.Open()
            dt = New DataTable
            dt.Load(cmd.ExecuteReader)
            con.Close()

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return dt
    End Function
    Sub LoadFields()
        con.Open()
        Dim dt As DataTable = ShowData(SelectQuery & " Where 1 <> 1")
        Dim dc As New DataColumn
        For Each dc In dt.Columns
            dt.Load(dc)
            cboFields.Items.Add(dc.ColumnName)
        Next
        con.Close()
    End Sub

    Function IsNumericType(ByVal ColName As String) As Boolean
        Dim dt As DataTable = ShowData(SelectQuery & " Where 1 <> 1")
        If dt.Columns(ColName).DataType Is GetType(Integer) Then
            Return True
        End If
        Return False
    End Function

    Private Sub mbtnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mbtnSearch.Click
        Dim query As String
        If IsNumericType(cboFields.Text) Then
            query = String.Format("{0} Where {1} {2} {3} ", SelectQuery, cboFields.Text, cboOp.Text, txtValue.Text)
        Else
            If cboOp.Text.ToUpper = "LIKE" Or cboOp.Text.ToUpper = "NOT LIKE" Then
                query = String.Format("{0} Where {1} {2} '{3}%' ", SelectQuery, cboFields.Text, cboOp.Text, txtValue.Text)
            Else
                query = String.Format("{0} Where {1} {2} '{3}' ", SelectQuery, cboFields.Text, cboOp.Text, txtValue.Text)
            End If
        End If
        dgData.DataSource = ShowData(query)
    End Sub

    Private Sub mbtnCResult_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mbtnCResult.Click
        dgData.DataSource = Nothing
    End Sub

    Private Sub cboOp_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboOp.SelectedIndexChanged
        If cboOp.Text.ToUpper = "MASTER DATA" Then
            mbtnSearch.Enabled = False
        Else
            mbtnSearch.Enabled = True
        End If
    End Sub

    Private Sub dgData_MouseDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgData.MouseDoubleClick
        If cboOp.Text.ToUpper = "MASTER DATA" Then
            txtValue.Text = dgData.CurrentRow.Cells(cboFields.Text).Value
        End If
    End Sub
    Private Sub mbtnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mbtnCancel.Click
        Me.Close()
    End Sub
End Class

i have attch code plz help me

What error messages are you seeing (if any) and on what line?

Hmm, some strange code you have there. I have commented all what is strange insid changed code bellow:

Public Function ShowData(Query As String) As DataTable
    Dim dt As New DataTable("myTable")
    Using con As New SqlConnection(My.Settings.DatabaseICICIConnectionString)
        Dim da As New SqlDataAdapter(Query, con)
        da.Fill(dt)
        da.Displose()
    End Using
    Return dt
End Function

Public Sub LoadFields()
    Dim dt As DataTable = ShowData("Select ColumnName FROM MyTable Where SOME CONDITION IF NEEDED")
    'if no condition, remove from where on!
    Dim dc As New DataColumn()
    For Each dc As DataColumn In dt.Columns
        'What are you trying to do here?
        'and only columns name s to comoobox? Is so do:         
        cboFields.Items.Add(dc.ColumnName)
    Next
End Sub
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.