Retreiving Data from MS Access

[witternb]

Hello,

I am trying to retrieve data from my MS Access table but keep getting a "Syntax Error on the FROM statement" error. I was hoping someone could lead me in the correct direction in resolving this error.

Particulars of the VB program: I am trying to create a "Control Panel" to view when a database was last viewed and last updated by my employees. I created one in Access but would like to move to a better platform (VB). I currently have the program attempting to retrieve the data and put it into a list and then when the form loads, I will pull from that list and put it into the appropriate boxes, showing the data I want to view. If others have a better way of pulling data and putting it into text boxes than what I am trying to attempt, I will listen with great intesity. Thanks in advance for your thoughts, opinions, and help!

Here is the coding:

Imports System.Data.OleDb
Public Class CPDB
    Public Shared Function GetViewList() As List(Of fldViewList)
        Dim viewList As New List(Of fldViewList)
        Dim connection As OleDbConnection = FINDB.GetConnection
        Dim selectStatement As String _
            = "SELECT Database, Date, User " _
            & "FROM View " _
            & "ORDER BY Database"
        Dim selectCommand As New OleDbCommand(selectStatement, connection)
        Try
            connection.Open()
            Dim reader As OleDbDataReader = selectCommand.ExecuteReader
            Dim fldviewlist As fldViewList
            Do While reader.Read
                fldviewlist = New fldViewList
                fldviewlist.Database = reader("Database")
                fldviewlist.Date = reader("Date")
                fldviewlist.User = reader("User")
                viewList.Add(fldviewlist)
            Loop
            reader.Close()
        Catch ex As Exception
            Throw ex
        Finally
            connection.Close()
        End Try
        Return viewList
    End Function
End Class

Now, I also have the follwing setup on a seperate class:

Public Class fldViewList
    Private m_Database As String
    Private m_User As String
    Private m_Date As String
    Public Sub New()

    End Sub

    Public Property Database() As String
        Get
            Return m_Database
        End Get
        Set(ByVal value As String)
            m_Database = value
        End Set
    End Property

    Public Property User() As String
        Get
            Return m_User
        End Get
        Set(ByVal value As String)
            m_User = value
        End Set
    End Property

    Public Property [Date]() As String
        Get
            Return m_Date
        End Get
        Set(ByVal value As String)
            m_Date = value
        End Set
    End Property
End Class

Again, thanks in advance for your help!

[cutepinkbunnies]

"SELECT [Database], [Date], [User] FROM [View] ORDER BY [Database]"

Try that and see if it works. I'm assuming you have a table or a view with the name of "View", and columns within this dataset named "Database" and "Date". If you are going to set up your database with objects that are named after reserved words, you will need to place object designators around them when coding later. (Something to keep in mind when you do move away from Access or build another platform)

Let us know how it works out!

[witternb]

Thanks for the reply cutepinkbunnies. I took your advice and changed the table and field names for the access database. the are now Table: tblView - Fields: fldDatabase, fldDate, fldUser to help with not mixing reserved variables with declared variables.

Now that I have completed that, I am getting an error when trying to retrieve data from the list. The error states that the "Conversion from type 'fldViewList' to type 'String' is not valid". Any ideas why I am getting that error? I have reviewed the code and it appears that I have put the variable classifications in correctly.

Your help is greatly appreciate!

Code on opening of control panel to populate the "test" variable:

'Load ViewList for access
        Dim viewList As List(Of fldViewList)
        Try
            viewList = CPDB.GetViewList
            Dim test = viewList(0)
            MessageBox.Show(test)
        Catch ex As Exception
            MessageBox.Show(ex.Message, ex.GetType.ToString)
        End Try
        'End Load ViewList for access

[cutepinkbunnies]

Ok you're getting an error with this statement
"viewList = CPDB.GetViewList"

You're trying to convert something that isn't a string into one implicitly here. If you can't do so explicitly (like using the cstr() method etc) you need to find out what the .Getviewlist() method returns and setup a variable to accommodate it :) I wouldn't be surprised if the .Getviewlist() method returned an array of some sort, so you will have to fix your viewlist declaration to make it happy :)

Keep us posted.