[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Enforce SQL DISTINCT in VB.NET

Posted on 2007-10-08
6
Medium Priority
?
668 Views
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.

Thanks
0
Comment
Question by:Terrace
  • 3
  • 2
6 Comments
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20035247
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
 
LVL 23

Expert Comment

by:Christopher Kile
ID: 20035296
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
 

Author Comment

by:Terrace
ID: 20035916
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 23

Expert Comment

by:Christopher Kile
ID: 20036557
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
 

Author Comment

by:Terrace
ID: 20038176
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
 
LVL 23

Accepted Solution

by:
Christopher Kile earned 1000 total points
ID: 20041289
>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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Loops Section Overview
Suggested Courses

872 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