Guys here;s the code of
VB.NET where i am adding data from database to the combobox... it shows me duplicate entries.. pls help me out
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
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.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList
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,Comp any,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 Dealer.DealID,Component.CompntID FROM Dealer,Component", con)
Dim com1 As New OleDbCommand("SELECT MAX(PurchaseID) From Purchase", con)
Dim r As OleDbDataReader
Try
con.Open()
r = com.ExecuteReader
txtcompnt.Text = ""
cbomanufacturer.Text = ""
cbomodel.Text = ""
txtdealname.Text = ""
With r
If r.HasRows Then
While (r.Read)
cbodealid.Items.Add(r("DealID"))
cbodealid.EndUpdate()
cbocompntid.Items.Add(r("CompntID"))
End While
End If
End With
txtcompnt.Text = ""
cbomanufacturer.Text = ""
cbomodel.Text = ""
txtdealname.Text = ""
Catch ex As Exception
MsgBox(ex.Message)
End Try
r = Nothing
con.Close()
cbodealid.SelectedIndex = 0
cbocompntid.SelectedIndex = 0
Dim k As OleDbDataReader
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
If filR.HasRows Then
filR.Read()
txtcompnt.Text = filR("CompntName")
cbomanufacturer.Text = filR("Company")
cbomodel.Text = filR("CompntModel")
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
filR = Nothing
filCon.Close()
End Sub
End Class
Combobox shows me repetition of entries...
