none
Fill Dataset - stumped RRS feed

  • Question

  • I can't figure out how to fill a dataset.  It must be so simple but I am the end of my wits.
    Whatever I tried it always creates an error.
    The database is an Access 2000 database.
    Imports System
    Imports System.Math
    Imports System.Data
    Imports System.Data.OleDb
    Imports ADODB

           cn.Open()
            Dim dsLadderID As New DataSet
            Dim DA As New OleDbDataAdapter(strSQL, cn)
            DA.Fill(dsLadderID, "Ladder") 'error occurs here
     
    The error message is unspecified error.

    I have tried this (amongst others) because at one point I got an error message that there was no SelectCommand.
            Dim dsLadder As New DataSet
            Dim adapter As New OleDbDataAdapter
            cn.Open()
            cmd.CommandText = strSQL
            cmd.Connection = cn
            adapter.SelectCommand = cmd
            adapter.Fill(dsLadder, "Ladder") 'error occurs here


    BraafPaard

    Sunday, March 5, 2017 8:50 PM

Answers

  • First off saying "it did not work" tells me nothing. The rule of thumb is if something does not work and there is an error then indicate what it is.

    Second thing, you are trying things without understanding. For example, when adding a parameter ? you must use the same parameter in the SQL statement that is used in the parameter e.g. ? if you use @1 then use it in both places MS-Access could careless what it's called and most use ? but in a larger SQL statement that makes it difficult to track things while SQL-Server uses named parameters such as what I showed which works also in MS-Access. Next up, there is zero reasons to use Parameter.Add over Parameter.AddWithValue, there is no gain here.

    What I have supplied works as I wrote the code and ran it to ensure it works and even so I could write the code in NotePad and would know it works so with that I have nothing else to offer.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by BraafPaard Monday, March 6, 2017 2:14 PM
    Monday, March 6, 2017 2:34 AM
    Moderator

All replies

  • Hello,

    Change the connection string to suit your database along with the SELECT statement.

    Imports System.Data.OleDb
    Public Class Sample
        Private Builder As New OleDb.OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.ACE.OLEDB.12.0",
                .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "names.accdb")
            }
        Public Property DataSet As New DataSet
        Public Property Adapter As New OleDbDataAdapter
        Public Sub New()
            Adapter.SelectCommand = New OleDbCommand With {.CommandText = "SELECT id,UserName,UserPassword FROM Users"}
        End Sub
        Public Sub FillDataSet()
            Dim dt As New DataTable
            Using cn As New OleDbConnection(Builder.ConnectionString)
                Adapter.SelectCommand.Connection = cn
                cn.Open()
                Adapter.Fill(DataSet, "Users")
            End Using
        End Sub
    End Class
    

    Then in a button click event or perhaps form load, in other words where you want it. You might like to declare the DataSet at form level, that's up to you.

    Dim ops As New Sample
    ops.FillDataSet()
    Dim ds As DataSet = ops.DataSet


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, March 5, 2017 11:09 PM
    Moderator
  • Everything which is shown is declaration until you do..... adapter.fill 

    Then there is really something done. 

    At that moment is told that the data adapter has no select command.

    There are many ways to set that, but the easiest way is by the constructor (your new OleDBDataAdapter(strSQL,cn)

    It seems that the strSQL does not contain a select statement (Which is a piece of SQL transaction code starting with "Select........................"


    Success
    Cor

    Sunday, March 5, 2017 11:42 PM
  • Thank you very much.  Before I try your code I let you know that I have a clue :)

    I found that it might be a problem related to strSQL.

    I tried to create a dataset in another sub and to my surprise the code worked there.  I then copied to strSQL from the sub where it did not work ito the sub where it worked.  Now the code generated n error. So:

    The following strSQL works

            strSQL = "SELECT LadderID, LadderName, clubName FROM Ladders ORDER BY LadderName ASC;"

    The next one does not work although there is no SQL problem.  I copied the stringfrom my vb 6.0 program that I am migrating from to vb 2010.
    It works there.  It gives a recordcount of 3 which is correct.

    strSQL = "SELECT points, ranking FROM names WHERE ladderID = " & g_LadderID & " ORDER BY points desc;"

    Points and ranking are both integers and the numbers are less than 10.


    BraafPaard

    Monday, March 6, 2017 12:23 AM
  • I have replied Karen. Please check that.

    BraafPaard

    Monday, March 6, 2017 12:24 AM
  • When using variables in the WHERE you should use parameters e.g.

    Imports System.Data.OleDb
    Public Class Sample
        Private Builder As New OleDb.OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.ACE.OLEDB.12.0",
                .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "names.accdb")
            }
        Public Property DataSet As New DataSet
        Public Property Adapter As New OleDbDataAdapter
        Public Sub New()
            Adapter.SelectCommand = New OleDbCommand With {.CommandText = "SELECT id,UserName,UserPassword FROM Users WHERE id = @id"}
        End Sub
        Public Sub FillDataSet(ByVal Identifier As Integer)
            Adapter.SelectCommand.Parameters.Clear()
    
            Using cn As New OleDbConnection(Builder.ConnectionString)
                Adapter.SelectCommand.Connection = cn
                Adapter.SelectCommand.Parameters.AddWithValue("@id", Identifier)
                cn.Open()
                Adapter.Fill(DataSet, "Users")
            End Using
        End Sub
    End Class
    

    Usage

    Dim ops As New Sample
    ops.FillDataSet(2)
    Dim ds As DataSet = ops.DataSe


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, March 6, 2017 12:42 AM
    Moderator
  • I don't fully understand your code so I tried a "shortcut" now I learned about the WHERE issue.

    It did not work but maybe it only needs a little tweaking.

          Dim da As New OleDbDataAdapter, ds As New DataSet
            cn.Open()
            cmd.Connection = cn
            strSQL = "SELECT points, ranking FROM names WHERE ladderID = ? ORDER BY points desc;"
            cmd.Parameters.Add("@1", OleDbType.Char, 1, g_LadderID)
            cmd.CommandText = strSQL
            da.SelectCommand = cmd
            da.Fill(ds, "Ladde

    I also tried

    cmd.Parameters.AddWithValue("@1", g_ladderID)    'the value of g_ladderID is 1

    cmd.Parameters.AddWithValue("@1", 1)


    BraafPaard

    Monday, March 6, 2017 2:14 AM
  • First off saying "it did not work" tells me nothing. The rule of thumb is if something does not work and there is an error then indicate what it is.

    Second thing, you are trying things without understanding. For example, when adding a parameter ? you must use the same parameter in the SQL statement that is used in the parameter e.g. ? if you use @1 then use it in both places MS-Access could careless what it's called and most use ? but in a larger SQL statement that makes it difficult to track things while SQL-Server uses named parameters such as what I showed which works also in MS-Access. Next up, there is zero reasons to use Parameter.Add over Parameter.AddWithValue, there is no gain here.

    What I have supplied works as I wrote the code and ran it to ensure it works and even so I could write the code in NotePad and would know it works so with that I have nothing else to offer.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by BraafPaard Monday, March 6, 2017 2:14 PM
    Monday, March 6, 2017 2:34 AM
    Moderator
  • I don't fully understand your code so I tried a "shortcut" now I learned about the WHERE issue.

    It did not work but maybe it only needs a little tweaking.

          Dim da As New OleDbDataAdapter, ds As New DataSet
            cn.Open()
            cmd.Connection = cn
            strSQL = "SELECT points, ranking FROM names WHERE ladderID = ? ORDER BY points desc;"
            cmd.Parameters.Add("@1", OleDbType.Char, 1, g_LadderID)
            cmd.CommandText = strSQL
            da.SelectCommand = cmd
            da.Fill(ds, "Ladde

    I also tried

    cmd.Parameters.AddWithValue("@1", g_ladderID)    'the value of g_ladderID is 1

    cmd.Parameters.AddWithValue("@1", 1)


    BraafPaard


    One of the problems could be that "Names" is a reserved word. Try enclosing it within brackets (e.g. [Names]) to see if it resolves your issue.

    Paul ~~~~ Microsoft MVP (Visual Basic)


    Monday, March 6, 2017 1:50 PM
  • Be aware that what Paul wrote is true. 

    However, you also have probably a misunderstanding about parameters. OleDB does not have named parameters. The use of @ is just a kind of convention (it does nothing) with parameters. 

    Therefore with OleDB you simply can do 

    cmd.Parameters.AddWithValue("?", 1)

    And before Paul tells me this, it does not chance the real problem of the reserved word. 


    Success
    Cor


    Monday, March 6, 2017 1:55 PM
  • The following code did work.

         cn.Open()
            cmd.Connection = cn
            strSQL = "SELECT points, ranking FROM [names] WHERE ladderID =? ORDER BY points desc;"
            Dim adapter As New OleDbDataAdapter(strSQL, cn)
            cmd.Parameters.AddWithValue("?", g_LadderID)
            cmd.CommandText = strSQL
            adapter.SelectCommand = cmd
            adapter.Fill(ds, "Ladder")

    Imagine my elation when after days of trying and reading and getting help I finally could fill a dataset :)

    I learned a lot in the meantime.  Thanks for helping.


    BraafPaard

    Monday, March 6, 2017 2:11 PM
  • I am sorry to get on your wrong side.

    Thanks to your help I learned a lot though.

    The code that I finally got to work is below:

         cn.Open()
            cmd.Connection = cn
            strSQL = "SELECT points, ranking FROM [names] WHERE ladderID =? ORDER BY points desc;"
            Dim adapter As New OleDbDataAdapter(strSQL, cn)
            cmd.Parameters.AddWithValue("?", g_LadderID)
            cmd.CommandText = strSQL
            adapter.SelectCommand = cmd
            adapter.Fill(ds, "Ladder")


    BraafPaard

    Monday, March 6, 2017 2:13 PM
  • You can be glad, it is the last time I give any attention to your messages here. 

    You always marks the answer from persons who did not give the answer and mislead other persons. In what you marked as answer is not included the real problem of the reserved word as Paul wrote.

    For me you are not a BraafPaard (obedient horse) but a GedesoriënteerdeKoe 


    Success
    Cor


    Monday, March 6, 2017 2:23 PM
  • I am sorry to get on your wrong side.

    Thanks to your help I learned a lot though.

    The code that I finally got to work is below:

         cn.Open()
            cmd.Connection = cn
            strSQL = "SELECT points, ranking FROM [names] WHERE ladderID =? ORDER BY points desc;"
            Dim adapter As New OleDbDataAdapter(strSQL, cn)
            cmd.Parameters.AddWithValue("?", g_LadderID)
            cmd.CommandText = strSQL
            adapter.SelectCommand = cmd
            adapter.Fill(ds, "Ladder")


    BraafPaard


    Good to hear you are working now :-)

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, March 6, 2017 2:44 PM
    Moderator
  • Yea but your intention seems not to be to create helpful replies in this forum. 

    The current marked answer gives points to you, but for all others it is complete wrong. 

    If you were a good moderator you changed the answer from yours to the answer from Paul because that is the code which the OP showed he used. 

    How you name parameters is in OleDB complete not important.


    Success
    Cor


    Monday, March 6, 2017 2:59 PM
  • The following code did work.

         cn.Open()
            cmd.Connection = cn
            strSQL = "SELECT points, ranking FROM [names] WHERE ladderID =? ORDER BY points desc;"
            Dim adapter As New OleDbDataAdapter(strSQL, cn)
            cmd.Parameters.AddWithValue("?", g_LadderID)
            cmd.CommandText = strSQL
            adapter.SelectCommand = cmd
            adapter.Fill(ds, "Ladder")

    Imagine my elation when after days of trying and reading and getting help I finally could fill a dataset :)

    I learned a lot in the meantime.  Thanks for helping.


    BraafPaard

    So did changing "names" to "[names]", as I suggested, resolve the issue?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, March 6, 2017 4:07 PM