dcsimg
Results 1 to 7 of 7

Thread: sorting or order by on a recordset with no column names

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Posts
    207

    sorting or order by on a recordset with no column names

    Hello:

    I am tying to either do something line this using ADO.NET on CSV files:
    Code:
    Dim sql As String = "SELECT * FROM [" & _strNewCSVFile & "] ORDER BY col5"
    Or without an order by, sort later:
    Code:
    rs.Sort = rs.Fields.Item(5)

    I somehow have to refer to these things by column number, as I have no line 1 with field names defined.

    Thanks in advance!

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,737

    Re: sorting or order by on a recordset with no column names

    I think you can just name the col by it's ordinal value:
    Code:
    Dim sql As String = "SELECT * FROM [" & _strNewCSVFile & "] ORDER BY 5"
    I think...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Posts
    207

    Re: sorting or order by on a recordset with no column names

    Thats it! Thanks!!!

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,479

    Re: sorting or order by on a recordset with no column names

    If you're using VB.NET, why are you using a Recordset at all? Why are you not using a DataTable, or perhaps a list of objects of a custom type?
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Posts
    207

    Re: sorting or order by on a recordset with no column names

    Because it is not a database. It is a csv file, kind of old school, and I am bouncing all over the place withing the recordsets, pulling different rows for different columns, etc.

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,479

    Re: sorting or order by on a recordset with no column names

    Quote Originally Posted by ssabc View Post
    Because it is not a database. It is a csv file
    That's irrelevant. The only reason to use a Recordset in VB.NET is if you're upgrading existing VB6 code and you don't want to rewrite the data access code just yet. If you're writing new code then you should absolutely not be using a Recordset under any circumstances. If you're using ADO.NET then you should be using a DataTable. Even if you're not using ADO.NET, a DataTable is still a better option than a Recordset.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  7. #7
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,150

    Re: sorting or order by on a recordset with no column names

    Quote Originally Posted by ssabc View Post
    Because it is not a database. It is a csv file, kind of old school, and I am bouncing all over the place withing the recordsets, pulling different rows for different columns, etc.
    Hi,
    try it like this

    I set the HDR=No
    if you open the CSV you will see the Headers like F1 F2 etc....

    here a sample..
    Code:
     
    Imports System.Data.OleDb
    
    
    
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim _tb As New DataTable
    
            Me.DataGridView1.DataSource = _tb
    
            Dim SrcDir As String = "C:\"
            Dim sConn As String = String.Join(";", New String() { _
               "Provider=Microsoft.Jet.OLEDB.4.0", _
               "Data Source=" & SrcDir, _
               "Extended Properties=""Text; HDR=No; FMT=Delimited"""})
    
     
            Dim SQL As String = "Select * From [Test2.csv] ORDER BY F2;"
    
            Using Cn As New OleDbConnection(sConn), ta As New OleDbDataAdapter(SQL, Cn)
    
                Cn.Open()
    
                ta.Fill(_tb)
    
    
            End Using
        End Sub
    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width