SQL command in use with MS ACCESS database

[Rajeev R Nair]

Hi friends, after a long time. i am developing a small tool by using VB.Net. I want to display data in comboboxes from a table which is in an access database. I using SQL query. Its a simple problem , I think the problem is in my COMMAND. I want to display data as DISTINCT(No repeating data). I am including the code and design form. If u can pls help me..

My code(for form) is

///

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cn = New Data.OleDb.OleDbConnection

        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\CTCRI-STUDENTS\Rajeev R Nair\Rajeev.Dept.ESS\Variety Finder\Variety Finder\Tuber.accdb"

        
        sql = "SELECT DISTINCT plt_type, mature_stm_clr, emerging_l_c, leaf_type, petiole_color, flower_nature, tuber_shape, tsc, trc, tfc, pre_tuber_neck, Any_other FROM cassava"
        cmd = New Data.OleDb.OleDbCommand(sql, cn)
        ad = New System.Data.OleDb.OleDbDataAdapter(sql, cn)

        Try
            cn.Open()
            cmd = New Data.OleDb.OleDbCommand(cmd.CommandText, cn)
            rd = cmd.ExecuteReader
            ' ad.Fill(ds, "cassava")
            While (rd.Read)
                ComboBox1.Items.Add(rd(0))
                ComboBox2.Items.Add(rd("mature_stm_clr"))
                ComboBox3.Items.Add(rd.Item("emerging_l_c"))
                ComboBox4.Items.Add(rd.Item("leaf_type"))
                ComboBox5.Items.Add(rd.Item("petiole_color"))
                ComboBox6.Items.Add(rd.Item("flower_nature"))
                ComboBox7.Items.Add(rd.Item("tuber_shape"))
                ComboBox8.Items.Add(rd.Item("tsc"))
                ComboBox9.Items.Add(rd.Item("trc"))
                ComboBox10.Items.Add(rd.Item("tfc"))
                ComboBox11.Items.Add(rd.Item("pre_tuber_neck"))
                ComboBox12.Items.Add(rd.Item("Any_other"))
            End While
            rd.Close()
            cmd.Dispose()
            cn.Close()
        Catch ex As Exception
            MsgBox("Cannot open connection")
        End Try
        ComboBox1.SelectedIndex = 0
        ComboBox2.SelectedIndex = 0
        ComboBox3.SelectedIndex = 0
        ComboBox4.SelectedIndex = 0
        ComboBox6.SelectedIndex = 0
        ComboBox5.SelectedIndex = 0
        ComboBox7.SelectedIndex = 0
        ComboBox8.SelectedIndex = 0
        ComboBox9.SelectedIndex = 0
        ComboBox10.SelectedIndex = 0
        ComboBox11.SelectedIndex = 0
        ComboBox12.SelectedIndex = 0

    End Sub

///

I am attaching the image of form in design and my sample database. Please help me immediately.

Yours Truly Rajeev.

[kvprajapati]

Here is for DISTINCT.

[Rajeev R Nair]

it was gud replay, but my problem is not solved yet.

[kvprajapati]

>it was **gud** replay, but my problem is not solved yet.

Tell us your problem please.

[Rajeev R Nair]

Ohh.. Sorry for that. My problem is that even I inserted the DISTINCT command, after running the code the combo boxes displays same values repeatedly. You can see the values on the database that i already included. I know it will not be user friendly.
Regards.

[kvprajapati]

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
       
        ComboBox1.DataSource = GetDataTable("select distinct plt_type from cassava")
        ComboBox1.DisplayMember = "plt_type"

        ComboBox2.DataSource = GetDataTable("select distinct mature_stm_clr from cassava")
        ComboBox2.DisplayMember = "mature_stm_clr"

        ......
    End Sub

    Public Function GetDataTable(ByVal sql As String) As DataTable
        Dim cn As New OleDbConnection
        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\CTCRI-STUDENTS\Rajeev R Nair\Rajeev.Dept.ESS\Variety Finder\Variety Finder\Tuber.accdb"

        Dim adp As New OleDbDataAdapter(sql, cn)
        Dim dt As New DataTable
        adp.Fill(dt)
        Return dt
    End Function

[Rajeev R Nair]

Thanks for your help. I got it. I expects further help from you, thank you forever./B]

[kvprajapati]

You're welcome Rajeev.

I'm glad you got it working. Please mark this thread as solved if you have found an answer to your question and good luck!