none
Questions Regarding Data Reader Between Microsoft Access and Visual Basic.NET RRS feed

  • Question

  • Hi, May I know how do I can read all the data in my Microsoft Access to my Visual Basic.NET? I only can read one row of data but another row of data I can't read it. Please solve for me please... Thank you!

    Below are my code and my databas

    Search2.CommandText = "Select * from 1231232 Where date = '" & ListBox1.Text & "';" Search2.Connection = cnnOleDb Dim dr As OleDbDataReader = Search2.ExecuteReader If dr.Read = True Then Button2.BackColor = Color.Red End If dr.Close()


    Tuesday, April 3, 2018 11:00 AM

All replies

  • Hello,

    Here is a basic pattern, note the square brackets around the field date as I believe is this a reserve token. Also note the use of a parameter for the WHERE condition value, you should never use string concatenation as this can lead to a) unescapted apostrophes b) sql injection.

        Public Function Demo(pSearchValue As string) As Boolean
            Using cn As New OleDbConnection With {.ConnectionString = "TODO"}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT [date] FROM SomeTable WHERE [Date] = ?"
                    cmd.Parameters.AddWithValue("?",pSearchValue)
                    cn.Open()
                    Dim reader = cmd.ExecuteReader()
                    Return reader.HasRows
                End Using
            End Using
        End Function


    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

    Tuesday, April 3, 2018 11:30 AM
    Moderator
  • If you want to read more than one row you need call the Read method again:

            While dr.Read
                'Columns in each Row
                Console.WriteLine(dr.Item(0).ToString)
                Console.WriteLine(dr.Item(1).ToString)
            End While


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Proposed as answer by Cor Ligthert Wednesday, April 4, 2018 7:16 AM
    Tuesday, April 3, 2018 11:42 AM
  • Hi ben10chia,

    You can use OleDbDatareader OleDbDataReader.Read Method () to do this, like this:

     Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "c:\Data\Northwind.mdb;User Id=admin;Password=;"
            Dim queryString As String = "SELECT ProductID, UnitPrice, ProductName from products " & "WHERE UnitPrice > ? " & "ORDER BY UnitPrice DESC;"
            Dim paramValue As Integer = 5
            Using connection As New OleDbConnection(connectionString)
                Dim command As New OleDbCommand(queryString, connection)
                command.Parameters.AddWithValue("@pricePoint", paramValue)
                Try
                    connection.Open()
                    Dim reader As OleDbDataReader = command.ExecuteReader()
                    While reader.Read()
                        Console.WriteLine(vbTab & "{0}" & vbTab & "{1}" & vbTab & "{2}", reader(0), reader(1), reader(2))
                    End While
    
                    reader.Close()
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                End Try
    
                Console.ReadLine()
            End Using

    More detailed info about OleDbDatareader, please refer to :

    https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdatareader(v=vs.110).aspx

    Best Rgeards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, April 4, 2018 2:33 AM
    Moderator
  • Cherry, 

    What is this bad converted C# into .Net VB.

    In .Net VB the Shared Sub is a module and VB knows since version 7.1 the statement 

     Using connection As  New OleDbConnection(connectionString)

    Just to let you know 
    Cor


    Wednesday, April 4, 2018 7:15 AM