Posted on 2007-10-08
Last Modified: 2008-01-09
I have a simple SQL query that is written and executed in VB.NET.

Dim strBrokerSQL As String = "SELECT DISTINCT autonumber, (rateMonth + '_'+ rateDay+ '_'+rateYear+ '_'+rateversion+ '_'+brok_code) As RateSheetName, risk_adj, sales_adj FROM tblRates where rateMonth = '" & rateMonth & "' and rateDay = '" & rateDay"

Query pulls data from database including duplicate rows, so DISTINCT is not playing its role. I am not sure why it does not work?

I could probably achieve the wanted result, by writing stored procedure at SQL side and just execute it from VB.NET, but I was wondwering if there is a way to force DISTINCT from VB.NET code.

Question by:Terrace
    LVL 23

    Expert Comment

    by:Ashish Patel
    What i see here is the Distinct AutoNumber column, and i guess the Autonumber shouldn't be duplicating. so just try removing the autonumber from your select statement. Or please show us some values so that we can help further.
    LVL 23

    Expert Comment

    by:Christopher Kile
    Distinct queries do not pull duplicate rows.  It never fails to "play its role."  But I am very suspicious of the autonumber field, as autonumber fields are supposed to be unique for every row - including this field insures that duplicates can appear in the other fields.  Why don't you try dropping this field from your query and see what you get?

    Author Comment

    You are probably right about the autonumber field. I did not test it without it yet, but I must have that field in resulting dataset.

    I am filling a datagrid with this dataset and one of the hidden fields in datagrid is autonumber value. I need it for further editing of a particular row from the datagrid.

    I could formulate query as
    Select DISTINCT * from ........

    but then I will lose that (rateMonth + '_'+ rateDay+ '_'+rateYear+ '_'+rateversion+ '_'+brok_code) As RateSheetName string creation.

    Is there are way to get DISTINCT dataset that will have autonumber and RateSheetName string at the same time?
    LVL 23

    Expert Comment

    by:Christopher Kile
    I admit to being very puzzled now.  Having a SELECT DISTINCT in this situation does not seem in any way appropriate, as you must edit a particular record, and with a SELECT DISTINCT there is no way of knowing what original record to edit.

    Why do you need SELECT DISTINCT at all???

    Author Comment

    The way it work is: I query a DB and get a dataset consisting two or more records. This dataset will fill my datagrid and data is displayed. One of the columns in datagrid is filled with autonumber values that are retrived from DB for each record (row). So it looks something like this:

    autonumber   RateSheetName           Action
    12                   09_12_2007_000       Click to Edit
    14                   09_23_2007_000       Click to Edit
    16                   09_12_2007_000       Click to Edit

    So, when user cliks on Click to Edit, I am using value of autonumber for that particular row of data grid to grab data for that specific row of DB. When I make some edits to that row, and I want to save changes, knowing row's autonumber, I simply update that row in DB and editing is done. I hope now it is more clear why I need autonumber values for each row in my grid.

    As above example ilustrates, row 12 and 14 are DUPLICATE based on RateSheetName value.

    So, ideally I would have a Select DISTINCT ..... statement that would ensure that my dataset has only unique RateSheetNames, not duplicates.
    Maybe I should try something like
    SELECT DISTINCT  (rateMonth + '_'+ rateDay+ '_'+rateYear+ '_'+rateversion+ '_'+brok_code) As RateSheetName, autonumber, risk_adj, sales_adj FROM tblRates where rateMonth = '" & rateMonth & "' and rateDay = '" & rateDay

    LVL 23

    Accepted Solution

    >Maybe I should try something like
    >SELECT DISTINCT  (rateMonth + '_'+ >rateDay+ '_'+rateYear+ '_'+rateversion+ '_'+brok_code) As RateSheetName, >autonumber, risk_adj, sales_adj FROM tblRates where rateMonth = '" & >rateMonth & "' and rateDay = '" & rateDay

    I'll repeat this...IT WON'T WORK.   The problem isn't autonumber, it's your wish to have a RateSheetName appear only once.  

    >As above example ilustrates, row 12 and 14 are DUPLICATE based on >RateSheetName value.
    >So, ideally I would have a Select DISTINCT ..... statement that would ensure
    >that my dataset has only unique RateSheetNames, not duplicates.

    This is the assumption that makes no sense to me.  Are you editing the RateSheet, or are you editing tblRates?  It seems to me that you've vaporlocked on two assumptions, each one of which is good for editing one of these items but makes sensible edit of the other item impossible.


    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    In my previous article ( we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
    Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now