3 Replies - 391 Views - Last Post: 29 February 2012 - 04:13 PM Rate Topic: -----

#1 Criel  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 128
  • Joined: 11-March 11

Specifically order a recordset/DB results

Posted 29 February 2012 - 12:09 PM

Hello,

I'm currently stuck on how to sort/organize the results from an Oracle database, here's my issue.

While Not rs.EOF

                reason_number = rs.Fields(0).Value

                adju_type = rs.Fields(5).Value
                reason_type = rs.Fields(6).Value
                ID = reason_number & "|" & adju_type & reason_type
                cmb.Items.Add(New PopulateCombo(rs.Fields(1).Value, ID))

                rs.MoveNext()
            End While



reason_number is getting pulled as '7', when it should be pulled as '4', I need the combobox to be populated as

4
5
6
7
8
9
10
11
1
2
3

But it's currently getting populated as

7
8
9
10
11
1
2
3
4
5
6

Is there a way to manually sort the results so I can get the index to start at 4 instead of 7?

Is This A Good Question/Topic? 0
  • +

Replies To: Specifically order a recordset/DB results

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4332
  • View blog
  • Posts: 12,127
  • Joined: 18-April 07

Re: Specifically order a recordset/DB results

Posted 29 February 2012 - 12:36 PM

what type of recordset are you creating here? Show us the code where you create this rs object.

Secondly, what is the rule that makes 7 appear first over 4? I assume the set of values you are seeing that start with 4 is the order in the database right? What business logic tells you it should start with 7 instead of 4 or instead of 10?

Usually if you want to get into a custom sort and you are using something like a recordset from VB6... you will have to pull out the records based on your business rules for ordering them and put the values into a structure like an array. This is because the recordset sort method usually sorts it based on a field in ascending/descending order.

More information is going to be needed. :)

P.S. If this is VB6 you are in the wrong forum.
Was This Post Helpful? 0
  • +
  • -

#3 Criel  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 128
  • Joined: 11-March 11

Re: Specifically order a recordset/DB results

Posted 29 February 2012 - 12:52 PM

Hello Marty2,

Thanks for your reply. Here is where I declare the recordset,

Dim rs As ADODB.Recordset


And actually 4 is required to go first since it's a business requirement, this component is actually converted from vb6 to .net which it why it looks similar to vb6 code, in the vb6 component it starts at 4 however in dotnet it starts at 7 which completely changes the look of the combobox and how it populates.

I've tried the sort thing and as you said, it only sorts asc/desc, I've researched on possible ways to start at 4 but haven't come up with anything. I might have to give your suggestion a shot.
Was This Post Helpful? 0
  • +
  • -

#4 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4332
  • View blog
  • Posts: 12,127
  • Joined: 18-April 07

Re: Specifically order a recordset/DB results

Posted 29 February 2012 - 04:13 PM

Well creating a variable as ADODB.Recordset is not the proper VB.NET way of doing it and that is why I asked because this looks like VB6 stuff which even though is not going to cause many errors, you should be instead using things like DataSet/DataTable objects through DataAdapters.

I also told you that it sorts only asc/desc and that I needed to know what the business rule was that governs how they are to be sorted. This is because you are going to have to tell VB.NET why 4 needs to be above 7 and why 2 should be below 10 etc. It sounds like you are wanting them sorted based on the name order that was shown on the form originally?

One quick and dirty way you could do this is to simply pull out the records from the recordset and build an array or some other type, putting the items you pulled out into the order based on your business rules. All in all, you are going to have to write a custom sort.

That or loop through the recordset starting from 4 and when you hit the end, start from 1 and go to 3. That is, manipulate the record pointer on the recordset and loop accordingly.

This post has been edited by Martyr2: 29 February 2012 - 04:15 PM

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1