Enforce SQL DISTINCT in VB.NET

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.

Thanks
TerraceAsked:
Who is Participating?
 
Christopher KileConnect With a Mentor Commented:
>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.

0
 
Ashish PatelCommented:
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.
0
 
Christopher KileCommented:
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?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
TerraceAuthor Commented:
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?
0
 
Christopher KileCommented:
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???
0
 
TerraceAuthor Commented:
OK.
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

???
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.