i am getting repetitive elements in my combobox from the database, can some one help me out

Question

Here's the code

Imports System.Data.OleDb

Public Class Purchase

Inherits System.Windows.Forms.Form

Dim newCompID As String = 1

#Region " Windows Form Designer generated code "

Public Sub New()

MyBase.New()

'This call is required by the Windows Form Designer.

InitializeComponent()

'Add any initialization after the InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component list.

Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)

If disposing Then

If Not (components Is Nothing) Then

components.Dispose()

End If

MyBase.Dispose(disposing)

End Sub

'Required by the Windows Form Designer

Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form Designer

'It can be modified using the Windows Form Designer.

'Do not modify it using the code editor.

Friend WithEvents Label1 As System.Windows.Forms.Label

Friend WithEvents Label2 As System.Windows.Forms.Label

Friend WithEvents Label3 As System.Windows.Forms.Label

Friend WithEvents Label4 As System.Windows.Forms.Label

Friend WithEvents Label5 As System.Windows.Forms.Label

Friend WithEvents Label6 As System.Windows.Forms.Label

Friend WithEvents Label7 As System.Windows.Forms.Label

Friend WithEvents Label8 As System.Windows.Forms.Label

Friend WithEvents txtpurid As System.Windows.Forms.TextBox

Friend WithEvents cbomanufacturer As System.Windows.Forms.ComboBox

Friend WithEvents cbomodel As System.Windows.Forms.ComboBox

Friend WithEvents txtquant As System.Windows.Forms.TextBox

Friend WithEvents txtamnt As System.Windows.Forms.TextBox

Friend WithEvents btnadd As System.Windows.Forms.Button

Friend WithEvents btndel As System.Windows.Forms.Button

Friend WithEvents btncancel As System.Windows.Forms.Button

Friend WithEvents btnupdate As System.Windows.Forms.Button

Friend WithEvents txtpurprice As System.Windows.Forms.TextBox

Friend WithEvents btncalc As System.Windows.Forms.Button

Friend WithEvents purdate As System.Windows.Forms.Label

Friend WithEvents Label9 As System.Windows.Forms.Label

Friend WithEvents Label11 As System.Windows.Forms.Label

Friend WithEvents cbodealid As System.Windows.Forms.ComboBox

Friend WithEvents cbocompntid As System.Windows.Forms.ComboBox

Friend WithEvents txtdealname As System.Windows.Forms.TextBox

Friend WithEvents txtcompnt As System.Windows.Forms.TextBox

Friend WithEvents txtdate As System.Windows.Forms.TextBox

<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

Me.Label1 = New System.Windows.Forms.Label

Me.Label2 = New System.Windows.Forms.Label

Me.Label3 = New System.Windows.Forms.Label

Me.Label4 = New System.Windows.Forms.Label

Me.Label5 = New System.Windows.Forms.Label

Me.Label6 = New System.Windows.Forms.Label

Me.Label7 = New System.Windows.Forms.Label

Me.Label8 = New System.Windows.Forms.Label

Me.txtpurid = New System.Windows.Forms.TextBox

Me.cbomanufacturer = New System.Windows.Forms.ComboBox

Me.cbomodel = New System.Windows.Forms.ComboBox

Me.txtpurprice = New System.Windows.Forms.TextBox

Me.txtquant = New System.Windows.Forms.TextBox

Me.txtamnt = New System.Windows.Forms.TextBox

Me.btnadd = New System.Windows.Forms.Button

Me.btndel = New System.Windows.Forms.Button

Me.btncancel = New System.Windows.Forms.Button

Me.btnupdate = New System.Windows.Forms.Button

Me.btncalc = New System.Windows.Forms.Button

Me.purdate = New System.Windows.Forms.Label

Me.Label9 = New System.Windows.Forms.Label

Me.Label11 = New System.Windows.Forms.Label

Me.cbodealid = New System.Windows.Forms.ComboBox

Me.cbocompntid = New System.Windows.Forms.ComboBox

Me.txtdealname = New System.Windows.Forms.TextBox

Me.txtcompnt = New System.Windows.Forms.TextBox

Me.txtdate = New System.Windows.Forms.TextBox

Me.SuspendLayout()

'

'Label1

'

Me.Label1.Location = New System.Drawing.Point(24, 24)

Me.Label1.Name = "Label1"

Me.Label1.TabIndex = 0

Me.Label1.Text = "Purchase ID"

'

'Label2

'

Me.Label2.Location = New System.Drawing.Point(304, 72)

Me.Label2.Name = "Label2"

Me.Label2.TabIndex = 1

Me.Label2.Text = "Dealer Name"

'

'Label3

'

Me.Label3.Location = New System.Drawing.Point(304, 120)

Me.Label3.Name = "Label3"

Me.Label3.TabIndex = 2

Me.Label3.Text = "Component"

'

'Label4

'

Me.Label4.Location = New System.Drawing.Point(24, 168)

Me.Label4.Name = "Label4"

Me.Label4.TabIndex = 3

Me.Label4.Text = "Manufacturer"

'

'Label5

'

Me.Label5.Location = New System.Drawing.Point(24, 216)

Me.Label5.Name = "Label5"

Me.Label5.TabIndex = 4

Me.Label5.Text = "Component Model"

'

'Label6

'

Me.Label6.Location = New System.Drawing.Point(24, 264)

Me.Label6.Name = "Label6"

Me.Label6.TabIndex = 5

Me.Label6.Text = "Unit Price"

'

'Label7

'

Me.Label7.Location = New System.Drawing.Point(24, 312)

Me.Label7.Name = "Label7"

Me.Label7.TabIndex = 6

Me.Label7.Text = "Quantity"

'

'Label8

'

Me.Label8.Location = New System.Drawing.Point(24, 360)

Me.Label8.Name = "Label8"

Me.Label8.TabIndex = 7

Me.Label8.Text = "Amount"

'

'txtpurid

'

Me.txtpurid.Enabled = False

Me.txtpurid.Location = New System.Drawing.Point(160, 24)

Me.txtpurid.Name = "txtpurid"

Me.txtpurid.Size = New System.Drawing.Size(120, 20)

Me.txtpurid.TabIndex = 8

Me.txtpurid.Text = ""

'

'cbomanufacturer

'

Me.cbomanufacturer.Location = New System.Drawing.Point(160, 160)

Me.cbomanufacturer.Name = "cbomanufacturer"

Me.cbomanufacturer.Size = New System.Drawing.Size(121, 21)

Me.cbomanufacturer.TabIndex = 11

'

'cbomodel

'

Me.cbomodel.Location = New System.Drawing.Point(160, 216)

Me.cbomodel.Name = "cbomodel"

Me.cbomodel.Size = New System.Drawing.Size(121, 21)

Me.cbomodel.TabIndex = 12

'

'txtpurprice

'

Me.txtpurprice.Location = New System.Drawing.Point(160, 264)

Me.txtpurprice.Name = "txtpurprice"

Me.txtpurprice.Size = New System.Drawing.Size(120, 20)

Me.txtpurprice.TabIndex = 13

Me.txtpurprice.Text = ""

'

'txtquant

'

Me.txtquant.Location = New System.Drawing.Point(160, 312)

Me.txtquant.Name = "txtquant"

Me.txtquant.Size = New System.Drawing.Size(120, 20)

Me.txtquant.TabIndex = 14

Me.txtquant.Text = ""

'

'txtamnt

'

Me.txtamnt.Location = New System.Drawing.Point(160, 360)

Me.txtamnt.Name = "txtamnt"

Me.txtamnt.Size = New System.Drawing.Size(120, 20)

Me.txtamnt.TabIndex = 15

Me.txtamnt.Text = ""

'

'btnadd

'

Me.btnadd.Location = New System.Drawing.Point(24, 432)

Me.btnadd.Name = "btnadd"

Me.btnadd.TabIndex = 16

Me.btnadd.Text = "&Add"

'

'btndel

'

Me.btndel.Location = New System.Drawing.Point(112, 432)

Me.btndel.Name = "btndel"

Me.btndel.TabIndex = 17

Me.btndel.Text = "&Delete"

'

'btncancel

'

Me.btncancel.Location = New System.Drawing.Point(208, 432)

Me.btncancel.Name = "btncancel"

Me.btncancel.TabIndex = 18

Me.btncancel.Text = "&Cancel"

'

'btnupdate

'

Me.btnupdate.Location = New System.Drawing.Point(304, 432)

Me.btnupdate.Name = "btnupdate"

Me.btnupdate.TabIndex = 19

Me.btnupdate.Text = "&Update"

'

'btncalc

'

Me.btncalc.Location = New System.Drawing.Point(160, 400)

Me.btncalc.Name = "btncalc"

Me.btncalc.TabIndex = 20

Me.btncalc.Text = "Calculate"

'

'purdate

'

Me.purdate.Location = New System.Drawing.Point(304, 24)

Me.purdate.Name = "purdate"

Me.purdate.TabIndex = 22

Me.purdate.Text = "Date:"

'

'Label9

'

Me.Label9.Location = New System.Drawing.Point(24, 64)

Me.Label9.Name = "Label9"

Me.Label9.TabIndex = 23

Me.Label9.Text = "Deal ID"

'

'Label11

'

Me.Label11.Location = New System.Drawing.Point(24, 112)

Me.Label11.Name = "Label11"

Me.Label11.TabIndex = 25

Me.Label11.Text = "Component ID"

'

'cbodealid

'

Me.cbodealid.ItemHeight = 13

Me.cbodealid.Location = New System.Drawing.Point(160, 64)

Me.cbodealid.Name = "cbodealid"

Me.cbodealid.Size = New System.Drawing.Size(104, 21)

Me.cbodealid.TabIndex = 27

'

'cbocompntid

'

Me.cbocompntid.Location = New System.Drawing.Point(168, 112)

Me.cbocompntid.Name = "cbocompntid"

Me.cbocompntid.Size = New System.Drawing.Size(72, 21)

Me.cbocompntid.TabIndex = 28

'

'txtdealname

'

Me.txtdealname.Location = New System.Drawing.Point(408, 72)

Me.txtdealname.Name = "txtdealname"

Me.txtdealname.Size = New System.Drawing.Size(136, 20)

Me.txtdealname.TabIndex = 29

Me.txtdealname.Text = ""

'

'txtcompnt

'

Me.txtcompnt.Location = New System.Drawing.Point(408, 120)

Me.txtcompnt.Name = "txtcompnt"

Me.txtcompnt.Size = New System.Drawing.Size(136, 20)

Me.txtcompnt.TabIndex = 30

Me.txtcompnt.Text = ""

'

'txtdate

'

Me.txtdate.Location = New System.Drawing.Point(408, 16)

Me.txtdate.Name = "txtdate"

Me.txtdate.TabIndex = 31

Me.txtdate.Text = ""

'

'Purchase

'

Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)

Me.ClientSize = New System.Drawing.Size(632, 502)

Me.Controls.Add(Me.txtdate)

Me.Controls.Add(Me.txtcompnt)

Me.Controls.Add(Me.txtdealname)

Me.Controls.Add(Me.cbocompntid)

Me.Controls.Add(Me.cbodealid)

Me.Controls.Add(Me.Label11)

Me.Controls.Add(Me.Label9)

Me.Controls.Add(Me.purdate)

Me.Controls.Add(Me.btncalc)

Me.Controls.Add(Me.btnupdate)

Me.Controls.Add(Me.btncancel)

Me.Controls.Add(Me.btndel)

Me.Controls.Add(Me.btnadd)

Me.Controls.Add(Me.txtamnt)

Me.Controls.Add(Me.txtquant)

Me.Controls.Add(Me.txtpurprice)

Me.Controls.Add(Me.cbomodel)

Me.Controls.Add(Me.cbomanufacturer)

Me.Controls.Add(Me.txtpurid)

Me.Controls.Add(Me.Label8)

Me.Controls.Add(Me.Label7)

Me.Controls.Add(Me.Label6)

Me.Controls.Add(Me.Label5)

Me.Controls.Add(Me.Label4)

Me.Controls.Add(Me.Label3)

Me.Controls.Add(Me.Label2)

Me.Controls.Add(Me.Label1)

Me.Name = "Purchase"

Me.Text = "Purchase"

Me.ResumeLayout(False)

End Sub

#End Region

Private Sub btncancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncancel.Click

Me.Close()

End Sub

Private Sub btncalc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncalc.Click

txtamnt.Text = Val(txtpurprice.Text) * Val(txtquant.Text)

End Sub

Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click

Dim saveSuccess As Boolean = False

Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Database.mdb")

Dim com As New OleDbCommand("INSERT INTO Purchase(DealID,DealerName,CompntID,Component,Company,CompntModel,UnitPrice,Qty,Amount,PurDate) VALUES(", con)

Dim dt As New Date(0)

dt = CType(txtdate.Text, Date)

com.CommandText = com.CommandText & "'" & cbodealid.Text & "','" & txtdealname.Text & "','" & cbocompntid.Text & "','" & txtcompnt.Text & "','" & cbomanufacturer.Text & "','" & cbomodel.Text & "'," & txtpurprice.Text & "," & txtquant.Text & "," & txtamnt.Text & ",'" & dt & "')"

Try

con.Open()

If com.ExecuteNonQuery <> 1 Then

saveSuccess = False

Else

saveSuccess = True

End If

Catch ex As Exception

saveSuccess = False

MsgBox(ex.Message)

End Try

con.Close()

If saveSuccess = False Then

MsgBox("Purchase cannot be added", MsgBoxStyle.Critical, "Error in Saving")

Else

MsgBox("purchase was successfully added", MsgBoxStyle.Information, "Customer Added")

btncancel.PerformClick()

End If

End Sub

Private Sub Purchase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

txtdate.Text = Now.ToShortDateString

Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Database.mdb")

'Dim com As New OleDbCommand("SELECT DealerName FROM Dealer", con)

'Dim com1 As New OleDbCommand("SELECT CompntName,Company,CompntModel FROM Component", con)

Dim com As New OleDbCommand("SELECT DealID,CompntID FROM Dealer,Component", con)

Dim com1 As New OleDbCommand("SELECT MAX(PurchaseID) From Purchase", con)

Dim r As OleDbDataReader

Dim k As OleDbDataReader

Try

con.Open()

r = com.ExecuteReader

Do While (r.Read)

cbodealid.Items.Add(r("DealID"))

cbocompntid.Items.Add(r("CompntID"))

txtcompnt.Text = ""

cbomanufacturer.Text = ""

cbomodel.Text = ""

txtdealname.Text = ""

Loop

Catch ex As Exception

MsgBox(ex.Message)

End Try

r = Nothing

con.Close()

cbodealid.SelectedIndex = 0

cbocompntid.SelectedIndex = 0

Try

con.Open()

k = com1.ExecuteReader

k.Read()

If k.HasRows Then

newCompID = Val(k(0) + 1).ToString

End If

Catch ex As Exception

MsgBox(ex.Message)

End Try

k = Nothing

con.Close()

txtpurid.Text = newCompID

End Sub

Private Sub cbodealid_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbodealid.SelectedIndexChanged

Dim filCon As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Database.mdb")

Dim filCom As New OleDbCommand("SELECT * FROM Dealer WHERE DealID=" & cbodealid.Text & "", filCon)

Dim filR As OleDbDataReader

txtdealname.Text = ""

Try

filCon.Open()

filR = filCom.ExecuteReader

filR.Read()

txtdealname.Text = filR("DealerName")

Catch ex As Exception

MsgBox(ex.Message)

End Try

filR = Nothing

filCon.Close()

End Sub

Private Sub cbocompntid_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbocompntid.SelectedIndexChanged

Dim filCon As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Database.mdb")

Dim filCom As New OleDbCommand("SELECT * FROM Component WHERE CompntID='" & cbocompntid.Text & "'", filCon)

Dim filR As OleDbDataReader

txtcompnt.Text = ""

cbomanufacturer.Text = ""

cbomodel.Text = ""

Try

filCon.Open()

filR = filCom.ExecuteReader

filR.Read()

txtcompnt.Text = filR("CompntName")

cbomanufacturer.Text = filR("Company")

cbomodel.Text = filR("CompntModel")

Catch ex As Exception

MsgBox(ex.Message)

End Try

filR = Nothing

filCon.Close()

End Sub

End Class

Karan · Answer

yes scott is abosultely right u should use Distinct clause in the query . because it doesn't allow the repetitve values in the qoutput e.g. select distinct ename from tblemp;

Guest User · Answer

Also, instead of "SELECT *", use the specific field names that you need to populate your combo box. It may not matter when your table only has 2 or 3 columns, but if it has more than that, then your query is returning a lot of extraneous data that you don't need just to populate a combo box.

Scott Lysle · Answer

Try SELECT DISTINCT in lieu of SELECT where you don't want repetitive values.