Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Professional
|
ASP.NET 2.0 Professional If you are an experienced ASP.NET programmer, this is the forum for your 2.0 questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 15th, 2009, 03:57 AM
Authorized User
 
Join Date: Dec 2007
Posts: 38
Thanks: 2
Thanked 0 Times in 0 Posts
Default Finding similar values in dataset

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.
__________________
shanwaj
 
Old April 15th, 2009, 07:41 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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...
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
 
Old April 15th, 2009, 08:31 AM
Authorized User
 
Join Date: Dec 2007
Posts: 38
Thanks: 2
Thanked 0 Times in 0 Posts
Default

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.
__________________
shanwaj
 
Old April 15th, 2009, 09:19 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
 
Old April 15th, 2009, 09:37 AM
Authorized User
 
Join Date: Dec 2007
Posts: 38
Thanks: 2
Thanked 0 Times in 0 Posts
Default

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
__________________
shanwaj
 
Old April 15th, 2009, 09:40 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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....
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
 
Old April 16th, 2009, 01:38 AM
Authorized User
 
Join Date: Dec 2007
Posts: 38
Thanks: 2
Thanked 0 Times in 0 Posts
Default

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
__________________
shanwaj
 
Old April 16th, 2009, 08:10 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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.
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
The Following User Says Thank You to gbianchi For This Useful Post:
shanwaj (April 17th, 2009)
 
Old April 17th, 2009, 12:18 AM
Authorized User
 
Join Date: Dec 2007
Posts: 38
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Hi gbianchi,
Thanks for your reply,I will do it.
__________________
shanwaj
 
Old April 17th, 2009, 06:03 AM
Authorized User
 
Join Date: Dec 2007
Posts: 38
Thanks: 2
Thanked 0 Times in 0 Posts
Default

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
__________________
shanwaj





Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding identical values voskoue Access VBA 7 January 30th, 2007 08:43 AM
Finding Values and counting scattered through data Scootterp Access VBA 13 March 20th, 2006 03:54 PM
Finding values from duplicated nodes swwallace XSLT 1 March 2nd, 2006 05:21 AM
Finding records containing similar text salman Classic ASP Databases 3 November 30th, 2004 10:17 AM
Finding Field Type & string length From dataset? Jackie VB.NET 2002/2003 Basics 4 July 9th, 2003 04:53 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.