I have a Database named "Store" and tables are orders and firm. The firm table fields are firmname, firmcode. The fields orderno, orderdate orderqty, qtyrcvd and balqty are stroed in another table. When selecting three ComboBox values (fetched from another(invoice) table) ie firm name, orderno and orderdate respectively when clicking a button check that it matches with the current value (current row values) in the order table. If so fetch the result. 1.add the records in another table result will be present qtyrcvd is qtyrcvd balqty is orderqty-qtyrcvd Please help me writing the code. I am new in VB.NET.
Imports system.data Imports System.Data.OleDb Public Class IR1 'Dim cos As New OleDbConnection
Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\HNCopy\Store.mdb")
Dim com As New OleDbCommand
Dim sql, sqs, connstring, path As String
Dim g As String
Dim dr As OleDbDataReader
Dim dr1 As OleDbDataReader
Dim dr2, dr9, dr8 As OleDbDataReader
Dim ds, dss, dsss As New DataSet
Dim da, da1, da2, da3 As New OleDb.OleDbDataAdapter
Dim dt, dt1, dt2, dt3, dt4, dt5, dt6 As New DataTable
Dim INVQTYEXCESS As Integer
Dim publictable As New DataTable
Private Sub IR1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'StoreDataSet3.ORDERNO' table. You can move, or remove it, as needed.
con.Open()
Dim da As New OleDbDataAdapter("select * from INV02 ORDER BY INVNO,SLNO", con)
Dim da1 As New OleDbDataAdapter("select * from INV02 ORDER BY INVNO,SLNO", con)
Dim da2 As New OleDbDataAdapter("select * from csmt ORDER BY ID", con)
Dim da3 As New OleDbDataAdapter("select * from CSMT ORDER BY ID DESC", con)
da.Fill(dt)
da1.Fill(dt1)
da2.Fill(dt2)
da3.Fill(dt3)
ComboBox24.DataSource = dt3
ComboBox1.DataSource = dt
ComboBox4.DataSource = dt
ComboBox2.DataSource = dt
ComboBox3.DataSource = dt
ComboBox36.DataSource = dt
ComboBox19.DataSource = dt
ComboBox5.DataSource = dt
ComboBox6.DataSource = dt
ComboBox7.DataSource = dt
ComboBox8.DataSource = dt
ComboBox10.DataSource = dt
ComboBox11.DataSource = dt
ComboBox41.DataSource = dt
ComboBox35.DataSource = dt1
ComboBox22.DataSource = dt1
ComboBox21.DataSource = dt1
ComboBox16.DataSource = dt1
ComboBox15.DataSource = dt1
ComboBox9.DataSource = dt1
ComboBox14.DataSource = dt1
ComboBox13.DataSource = dt1
ComboBox12.DataSource = dt1
ComboBox24.DisplayMember = "ID"
ComboBox24.ValueMember = "ID"
ComboBox1.DisplayMember = "fIRM"
ComboBox1.ValueMember = "fIRM"
ComboBox4.DisplayMember = "fcode"
ComboBox4.ValueMember = "fcode"
ComboBox2.DisplayMember = "ORDERNO"
ComboBox2.ValueMember = "ORDERNO"
ComboBox3.DisplayMember = "ORDERDT"
ComboBox3.ValueMember = "ORDERDT"
ComboBox36.DisplayMember = "INVNO"
ComboBox36.ValueMember = "INVNO"
ComboBox19.DisplayMember = "INVDATE"
ComboBox19.ValueMember = "INVDATE"
ComboBox35.DisplayMember = "INVNO"
ComboBox35.ValueMember = "INVNO"
ComboBox22.DisplayMember = "INVDATE"
ComboBox22.ValueMember = "INVDATE"
ComboBox5.DisplayMember = "SLNO"
ComboBox5.ValueMember = "SLNO"
ComboBox6.DisplayMember = "ITEMCODE"
ComboBox6.ValueMember = "ITEMCODE"
ComboBox7.DisplayMember = "ITEMNAME"
ComboBox7.ValueMember = "ITEMNAME"
ComboBox8.DisplayMember = "PARTNO"
ComboBox8.ValueMember = "PARTNO"
ComboBox10.DisplayMember = "STORE"
ComboBox10.ValueMember = "STORE"
ComboBox11.DisplayMember = "UNIT"
ComboBox11.ValueMember = "UNIT"
ComboBox41.DisplayMember = "QTY"
ComboBox41.ValueMember = "QTY"
ComboBox21.DisplayMember = "SLNO"
ComboBox21.ValueMember = "SLNO"
ComboBox16.DisplayMember = "ITEMCODE"
ComboBox16.ValueMember = "ITEMCODE"
ComboBox15.DisplayMember = "ITEMNAME"
ComboBox15.ValueMember = "ITEMNAME"
ComboBox9.DisplayMember = "PARTNO"
ComboBox9.ValueMember = "PARTNO"
ComboBox14.DisplayMember = "STORE"
ComboBox14.ValueMember = "STORE"
ComboBox13.DisplayMember = "UNIT"
ComboBox13.ValueMember = "UNIT"
ComboBox12.DisplayMember = "QTY"
ComboBox12.ValueMember = "QTY"
con.Close()
End Sub
Private Sub Button19_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button19.Click
Dim d As New Date(DateTimePicker1.Value.Year, DateTimePicker1.Value.Month, DateTimePicker1.Value.Day)
'****************************************************************************************************************************
If CInt(Me.ComboBox41.Text) > CInt(Me.TextBox9.Text) And CInt(Me.TextBox9.Text) = CInt(Me.TextBox31.Text) Then
Me.TextBox11.Text = 0
Me.TextBox16.Text = CInt(Me.ComboBox41.Text) - CInt(Me.TextBox9.Text)
Me.TextBox26.Text = 0
Me.TextBox22.Text = 0
ElseIf CInt(Me.ComboBox41.Text) < CInt(Me.TextBox9.Text) And CInt(Me.TextBox9.Text) > CInt(Me.TextBox31.Text) Then
Me.TextBox11.Text = CInt(Me.TextBox9.Text) - CInt(Me.ComboBox41.Text)
Me.TextBox16.Text = 0
Me.TextBox26.Text = CInt(Me.TextBox9.Text) - CInt(Me.TextBox31.Text)
Me.TextBox22.Text = CInt(Me.TextBox26.Text)
ElseIf CInt(Me.ComboBox41.Text) > CInt(Me.TextBox9.Text) And CInt(Me.TextBox9.Text) > CInt(Me.TextBox31.Text) Then
Me.TextBox11.Text = 0
Me.TextBox16.Text = CInt(Me.ComboBox41.Text) - CInt(Me.TextBox9.Text)
Me.TextBox26.Text = CInt(Me.TextBox9.Text) - CInt(Me.TextBox31.Text)
Me.TextBox22.Text = CInt(Me.TextBox26.Text)
ElseIf CInt(Me.ComboBox41.Text) = CInt(Me.TextBox9.Text) And CInt(Me.TextBox9.Text) = CInt(Me.TextBox31.Text) Then
Me.TextBox11.Text = 0
Me.TextBox16.Text = 0
Me.TextBox26.Text = 0
Me.TextBox22.Text = 0
ElseIf CInt(Me.ComboBox41.Text) = CInt(Me.TextBox9.Text) And CInt(Me.TextBox9.Text) > CInt(Me.TextBox31.Text) Then
Me.TextBox11.Text = 0
Me.TextBox16.Text = 0
Me.TextBox26.Text = CInt(Me.TextBox9.Text) - CInt(Me.TextBox31.Text)
Me.TextBox22.Text = CInt(Me.TextBox26.Text)
ElseIf CInt(Me.ComboBox41.Text) < CInt(Me.TextBox9.Text) And CInt(Me.TextBox9.Text) = CInt(Me.TextBox31.Text) Then
Me.TextBox11.Text = CInt(Me.TextBox9.Text) - CInt(ComboBox41.Text) 'text16-qtyshort
Me.TextBox16.Text = 0
Me.TextBox26.Text = 0
Me.TextBox22.Text = 0
End If
'****************************************************************************************************************************
Dim con As New OleDb.OleDbConnection
Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\HNCopy\Store.mdb;Persist Security Info=False"
Dim com As New OleDb.OleDbCommand
Try
con.ConnectionString = connStr
con.Open()
com.Connection = con
com.CommandText = "insert into CSMT([MTDATE],[FIRM],[FCODE],[ORDERNO],[ORDERDT],[INVNO1],[INVDATE1],[SLNO],[ITEMCODE],[ITEMNAME],[PARTNO],[STORE],[UNIT],[QTY],[QTYRCVD],[QTYACCPTD],[INVQTYEXCESS],[INVQTYSHORT],[INVQTYREJN],[INVQTYBAL])values(@MTDATE,@FIRM,@FCODE,@ORDERNO,@ORDERDT,@INVNO1,@INVDATE1,@SLNO,@ITEMCODE,@ITEMNAME,@PARTNO,@STORE,@UNIT,@QTY,@QTYRCVD,@QTYACCPTD,@INVQTYEXCESS,@INVQTYSHORT,@INVQTYREJN,@INVQTYBAL)"
com.Parameters.AddWithValue("@MTDATE", d)
com.Parameters.AddWithValue("@FIRM", ComboBox1.Text)
com.Parameters.AddWithValue("@FCODE", ComboBox4.Text)
com.Parameters.AddWithValue("@ORDERNO", ComboBox2.Text)
com.Parameters.AddWithValue("@ORDERDT", ComboBox3.Text)
com.Parameters.AddWithValue("@INVNO1", ComboBox36.Text)
com.Parameters.AddWithValue("@INVDATE1", ComboBox19.Text)
com.Parameters.AddWithValue("@SLNO", ComboBox5.Text)
com.Parameters.AddWithValue("@ITEMCODE", ComboBox6.Text)
com.Parameters.AddWithValue("@ITEMNAME", ComboBox7.Text)
com.Parameters.AddWithValue("@PARTNO", ComboBox8.Text)
com.Parameters.AddWithValue("@STORE", ComboBox10.Text)
com.Parameters.AddWithValue("@UNIT", ComboBox11.Text)
com.Parameters.AddWithValue("@QTY", ComboBox41.Text)
com.Parameters.AddWithValue("@QTYRCVD", TextBox9.Text)
com.Parameters.AddWithValue("@QTYACCPTD", TextBox31.Text)
com.Parameters.AddWithValue("@INVQTYEXCESS", TextBox11.Text)
com.Parameters.AddWithValue("@INVQTYSHORT", TextBox16.Text)
com.Parameters.AddWithValue("@INVQTYREJN", TextBox26.Text)
com.Parameters.AddWithValue("@INVQTYBAL", TextBox22.Text)
com.ExecuteNonQuery()
con.Close()
MsgBox("Record Appended", MsgBoxStyle.Information, "Successfully Added!")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
Private Sub Button20_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button20.Click
Dim d As New Date(DateTimePicker1.Value.Year, DateTimePicker1.Value.Month, DateTimePicker1.Value.Day)
'****************************************************************************************************************************
If CInt(Me.ComboBox12.Text) > CInt(Me.TextBox29.Text) And CInt(Me.TextBox29.Text) = CInt(Me.TextBox28.Text) Then
Me.TextBox32.Text = 0.0
Me.TextBox30.Text = CInt(Me.ComboBox12.Text) - CInt(Me.TextBox29.Text)
Me.TextBox25.Text = 0.0
Me.TextBox27.Text = 0.0 + CInt(Me.TextBox32.Text)
ElseIf CInt(Me.ComboBox12.Text) < CInt(Me.TextBox29.Text) And CInt(Me.TextBox29.Text) > CInt(Me.TextBox28.Text) Then
Me.TextBox32.Text = CInt(Me.TextBox29.Text) - CInt(Me.ComboBox12.Text)
Me.TextBox30.Text = 0.0
Me.TextBox25.Text = CInt(Me.TextBox29.Text) - CInt(Me.TextBox28.Text)
Me.TextBox27.Text = CInt(Me.TextBox25.Text)
ElseIf CInt(Me.ComboBox12.Text) > CInt(Me.TextBox29.Text) And CInt(Me.TextBox29.Text) > CInt(Me.TextBox28.Text) Then
Me.TextBox32.Text = 0.0
Me.TextBox30.Text = CInt(Me.ComboBox12.Text) - CInt(Me.TextBox29.Text)
Me.TextBox25.Text = CInt(Me.TextBox29.Text) - CInt(Me.TextBox28.Text)
Me.TextBox27.Text = CInt(Me.TextBox25.Text)
ElseIf CInt(Me.ComboBox12.Text) = CInt(Me.TextBox29.Text) And CInt(Me.TextBox29.Text) = CInt(Me.TextBox28.Text) Then
Me.TextBox32.Text = 0.0
Me.TextBox30.Text = 0.0
Me.TextBox25.Text = 0.0
Me.TextBox27.Text = 0.0
ElseIf CInt(Me.ComboBox12.Text) = CInt(Me.TextBox29.Text) And CInt(Me.TextBox29.Text) > CInt(Me.TextBox28.Text) Then
Me.TextBox32.Text = 0.0
Me.TextBox30.Text = 0.0
Me.TextBox25.Text = CInt(Me.TextBox29.Text) - CInt(Me.TextBox28.Text)
Me.TextBox27.Text = CInt(Me.TextBox25.Text)
ElseIf CInt(Me.ComboBox12.Text) < CInt(Me.TextBox29.Text) And CInt(Me.TextBox29.Text) = CInt(Me.TextBox28.Text) Then
Me.TextBox32.Text = CInt(Me.TextBox29.Text) - CInt(ComboBox12.Text) 'text16-qtyshort
Me.TextBox30.Text = 0.0
Me.TextBox25.Text = 0.0
Me.TextBox27.Text = 0.0
End If
'****************************************************************************************************************************
Dim con As New OleDb.OleDbConnection
Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\HNCopy\Store.mdb;Persist Security Info=False"
Dim com As New OleDb.OleDbCommand
Try
con.ConnectionString = connStr
con.Open()
com.Connection = con
com.CommandText = "insert into CSMT([MTDATE],[FIRM],[FCODE],[ORDERNO],[ORDERDT],[INVNO2],[INVDATE2],[SLNO],[ITEMCODE],[ITEMNAME],[PARTNO],[STORE],[UNIT],[QTY],[QTYRCVD],[QTYACCPTD],[INVQTYEXCESS],[INVQTYSHORT],[INVQTYREJN],[INVQTYBAL],[POQTYEXCESS],[POQTYKEPTOFF],[POQTYRJTD],[POQTYBAL],[UNITRATE],[TOTAMOUNT])values(@MTDATE,@FIRM,@FCODE,@ORDERNO,@ORDERDT,@INVNO2,@INVDATE2,@SLNO,@ITEMCODE,@ITEMNAME,@PARTNO,@STORE,@UNIT,@QTY,@QTYRCVD,@QTYACCPTD,@INVQTYEXCESS,@INVQTYSHORT,@INVQTYREJN,@INVQTYBAL,@POQTYEXCESS,@POQTYKEPTOFF,@POQTYRJTD,@POQTYBAL,@UNITRATE,@TOTAMOUNT)"
com.Parameters.AddWithValue("@MTDATE", d)
com.Parameters.AddWithValue("@FIRM", ComboBox1.Text)
com.Parameters.AddWithValue("@FCODE", ComboBox4.Text)
com.Parameters.AddWithValue("@ORDERNO", ComboBox2.Text)
com.Parameters.AddWithValue("@ORDERDT", ComboBox3.Text)
com.Parameters.AddWithValue("@INVNO2", ComboBox35.Text)
com.Parameters.AddWithValue("@INVDATE2", ComboBox22.Text)
com.Parameters.AddWithValue("@SLNO", ComboBox21.Text)
com.Parameters.AddWithValue("@ITEMCODE", ComboBox16.Text)
com.Parameters.AddWithValue("@ITEMNAME", ComboBox15.Text)
com.Parameters.AddWithValue("@PARTNO", ComboBox9.Text)
com.Parameters.AddWithValue("@STORE", ComboBox14.Text)
com.Parameters.AddWithValue("@UNIT", ComboBox13.Text)
com.Parameters.AddWithValue("@QTY", ComboBox12.Text)
com.Parameters.AddWithValue("@QTYRCVD", TextBox29.Text)
com.Parameters.AddWithValue("@QTYACCPTD", TextBox28.Text)
com.Parameters.AddWithValue("@INVQTYEXCESS", TextBox32.Text)
com.Parameters.AddWithValue("@INVQTYSHORT", TextBox30.Text)
com.Parameters.AddWithValue("@INVQTYREJN", TextBox25.Text)
com.Parameters.AddWithValue("@INVQTYBAL", TextBox27.Text)
com.Parameters.AddWithValue("@POQTEXCESS", TextBox24.Text)
com.Parameters.AddWithValue("@POQTYKEPTOFF", TextBox23.Text)
com.Parameters.AddWithValue("@POQTYRJTD", TextBox20.Text)
com.Parameters.AddWithValue("@POQTYBAL", TextBox21.Text)
com.Parameters.AddWithValue("@unitrate", TextBox19.Text)
com.Parameters.AddWithValue("@totamount", TextBox33.Text)
com.ExecuteNonQuery()
con.Close()
MsgBox("Record Appended", MsgBoxStyle.Information, "Successfully Added!")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim unitRATE, TOTAMOUNT As Double
unitRATE = 0.0
TOTAMOUNT = 0.0
If RadioButton1.Checked Then
TextBox33.Text = TextBox28.Text * TextBox19.Text
ElseIf RadioButton2.Checked Then
MsgBox("Please enter Total Price")
End If
End Sub
Hashik 0 Newbie Poster
aldeene 0 Junior Poster in Training
do a loop and then there you can compare the values between two combo boxes.
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.