Finding similar values in dataset

shanwaj · April 15th, 2009, 03:57 AM

Hi to All,
I have two tables(otherbillmaster,receipts).otherbillmaster table is containing "billno" column.Billno column contains invoice nos.In second table(receipts) is containing "otherbillno" column.Here I dont want to repeatation of existed invoice numbers while selecting customer name in DropDownList.I am searching and deleting Existed invoice numbers of receipt in otherbillmaster table in dataset before going to fill dropdownlist
So I coded like below for this.

Code:

 
Dim con As OleDbConnection = New OleDbConnection(strConn)
        con.Open()
        Try
            Dim ds As New DataSet
            Dim adp As OleDbDataAdapter
            adp = New OleDbDataAdapter("select  a.billno & ' ' &  b.workgroup & '/' & a.billdate as invoiceno,a.billno as invno,a.totalamount as totalamount from otherbillmaster a,workorder b where a.orderno=b.workorderno and a.partycode=(select partycode from partycustomer where suppliername='" & ddlFrom.SelectedItem.Text & "')", con)
            adp.Fill(ds, "tblInvoice")
            adp = New OleDbDataAdapter("select otherbillno,recamount,advance,tdsamount,discountamount,recno from receipts where acccode=(select acccode from accmaster where desc1='" & ddlFrom.SelectedItem.Text & "')", con)
            adp.Fill(ds, "tblreceipts")
            Dim valAmount, valNetAmount, valLess, valBalance As Double
            Dim count, i, j, k, l As Integer
            count = ds.Tables("tblinvoice").Rows.Count
            ds.Tables(0).Constraints.Add("rk_pk", ds.Tables(0).Columns("invno"), True)
            'ds.Tables(1).Constraints.Add("ok_pk", ds.Tables(1).Columns("otherbillno"), True) 'It is giving error like column is having duplicate values
/*            Dim uk As UniqueConstraint
            uk = New UniqueConstraint("uk", ds.Tables(1).Columns("otherbillno"))
            'fk.DeleteRule = Rule.None
            ds.Tables(1).Constraints.Add(uk)
            'ds.Tables(1).Constraints.Add("ok_pk", ds.Tables(1).Columns("otherbillno"), True)  */ 'This unique constrain  I added in the morning
            For i = 0 To count
                If i >= count Then
                    Exit For
                Else
                    j = Convert.ToInt32(ds.Tables(0).Rows(i)("invno"))
                    valNetAmount = ds.Tables(0).Rows(i)("totalamount")
                    If ds.Tables(1).Rows.Contains(j) = True Then
                        For l = 0 To ds.Tables(1).Rows.Count
                            If l >= ds.Tables(1).Rows.Count Then
                                Exit For
                            Else
                                Dim amount, temp, adv, tds, discamount As Double
                                If IsDBNull(ds.Tables(1).Rows(l)("recamount")) Then
                                    temp = 0
                                    amount = Convert.ToDouble(temp)
                                Else
                                    amount = ds.Tables(1).Rows(l)("recamount")
                                End If
                                If IsDBNull(ds.Tables(1).Rows(l)("advance")) Then
                                    temp = 0
                                    adv = Convert.ToDouble(temp)
                                Else
                                    adv = ds.Tables(1).Rows(l)("advance")
                                End If
                                If IsDBNull(ds.Tables(1).Rows(l)("tdsamount")) Then
                                    temp = 0
                                    tds = Convert.ToDouble(temp)
                                Else
                                    tds = ds.Tables(1).Rows(l)("tdsamount")
                                End If
                                If IsDBNull(ds.Tables(1).Rows(l)("discountamount")) Then
                                    temp = 0
                                    discamount = Convert.ToDouble(temp)
                                Else
                                    discamount = ds.Tables(1).Rows(l)("discountamount")
                                End If
                                valAmount += amount + adv + tds + discamount
                            End If
                        Next
                        Dim row As DataRow
                        row = ds.Tables(1).Rows.Find(j)
                        k = row("otherbillno")
                        valBalance = valNetAmount - valAmount
                        If k = j And valBalance = 0 Then
                            ds.Tables(0).Rows.Find(k).Delete()
                        Else
                            lstOtherWorksBillNo.DataSource = ds.Tables(0)
                            lstOtherWorksBillNo.DataTextField = "invoiceno"
                            lstOtherWorksBillNo.DataBind()
                        End If
                    Else
                        lstOtherWorksBillNo.DataSource = ds.Tables(0)
                        lstOtherWorksBillNo.DataTextField = "invoiceno"
                        lstOtherWorksBillNo.DataBind()
                    End If
                End If
            Next
            con.Close()
        Catch ex As Exception
            Response.Write(ex.Message)
        Finally
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try

I dont have any primary key or foreign keys in database,So I assigning it to dataset.I want to search values in tblreceipts ,if I find any then delete in tblinvoice.Please help me where I am missing.

gbianchi · April 15th, 2009, 07:41 AM

Hi.. Can you please post an example of what you need???

Even if you don't have keys, you can still maybe use a Delete to do this...

shanwaj · April 15th, 2009, 08:31 AM

Hi,
Thanks for your reply.I have one dropdownlist which is named as ddlFrom.It was filled by Clients on page load and I have one listbox(lstOtherbillno) which is filled Inovice numbers(billno)by selected client from ddlFrom.Here I want to generate receipts depend on bill number of clients.when the clients outstanding bill comes to zero then that client bill number not to fill in listbox.Here I dont want to repeat the existed billno in listbox,it should be search in dataset and delete there only and rest of the billnumbers will fill to list box.Sorry for my lengthy explanation.Please help me in this.

gbianchi · April 15th, 2009, 09:19 AM

Sorry I still don't understand you.. maybe a example with data???

shanwaj · April 15th, 2009, 09:37 AM

Hi
Sorry for my bad explanation.
I am giving data here
OtherBillMaster(Invoice Table)
=================
billno totalamount orderno advance
2 Rs.133,393.00 2 Rs.45,000.00

receipts Table
=========
recno recdate acccode amount billdate recamount advance balance less tdsamount discountamount
1 09/04/2009 ACC19 Rs.133,393.00 4/6/2009 Rs.20,000.00 Rs.65,000.00 Rs.45,000.00 Rs.88,393.00 Rs.3,000.00 Rs.393.00
2 09/04/2009 ACC19 Rs.133,393.00 4/6/2009 Rs.20,000.00 Rs.25,000.00 Rs.108,393.00 Rs.0.00 Rs.0.00
3 09/04/2009 ACC19 Rs.133,393.00 4/6/2009 Rs.25,000.00 Rs.0.00 Rs.133,393.00 Rs.0.00 Rs.0.00

please help me

gbianchi · April 15th, 2009, 09:40 AM

Now, can you put that data into an example of what you need??? so we can understand what you are wanting to delete and from where...

Thanks....

shanwaj · April 16th, 2009, 01:38 AM

Hi gbianchi,
Thanks for your reply,yester day my job hours were over so couldn't reply you. Defenantly I will explain my problem in detail.I have two clients(Samson,Leo) in my table.I generated invoice to Samson the amount of $50000 and billno is 1 and for Leo the amount of $45000 and billno is 2.This data will come from Invoice table while selecting customer name from ddlFrom .By selecting Samson in dropdown the billno 1 will fill in listbox(lstOtherbillno). The Samson has paid me $50000 in three terms.So the receipt generated 3 times in receipt table with name of Samson and billno 1.In receipt table Samson Invoice billno(1) repeated 3 times because Samson has paid the amount in terms.Upto here everything is fine.

Here I want to do is when I select Samson from dropdown it wil search for existed billno
in receipt table ,if found any existed bill for samson it wont be refill the same no in listbox.
So , I need to delete existed bill no(one) in dataset only and rest of the billnos of Samson will fill into listbox.
I hope above details will help you to understand my problem.Thanks in advance

gbianchi · April 16th, 2009, 08:10 AM

And thanks. No, you don't need to delete anything, you need a group by (or select distinct) query to only show one bill in the second combo.
Your query to the second combo should be something like:

SELECT DISTINCT field FROM table.

Try with that, or if that doesn't work a group by will sure make the work.

shanwaj · April 17th, 2009, 12:18 AM

Hi gbianchi,
Thanks for your reply,I will do it.

shanwaj · April 17th, 2009, 06:03 AM

Hi gbianchi,
Thank you very much for your valuble suggestion.I am trying to do it nearly from one week .I did not get any solution.I followed your suggestion and I changed SQLQUEERY for receipts as per my requirement.Its working fine.I reduced half part of code in above code.Thank you once again