Unable to fill OleDbDataAdapter with data

I am unable to fill data in the OleDbDataAdapter

I get an OleDbException with the following message Syntax error in FROM clause.

at this line adapter.Fill(dataset, "User")

Here is the code

Imports System.Data.OleDb

Public Class Form1

Public connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\MyDatabase.mdb;Persist Security Info=False")

Private Sub button_display_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button_display.Click
    connection.Close()
    connection.Open()
    Dim adapter As New OleDbDataAdapter
    adapter.SelectCommand = New OleDbCommand
    adapter.SelectCommand.Connection = connection
    adapter.SelectCommand.CommandText = "select * from user;"

    Dim dataset As New DataSet
    adapter.Fill(dataset, "User")
    connection.Close()
    Dim datarow = dataset.Tables("User").Rows(0)
    Dim fname, lname As String
    fname = datarow(0)
    lname = datarow(1)
    txtfirstname.Text = fname
    txtlastname.Text = lname

End Sub
End Class

I have tried replacing

    Dim adapter As New OleDbDataAdapter
    adapter.SelectCommand = New OleDbCommand
    adapter.SelectCommand.Connection = connection
    adapter.SelectCommand.CommandText = "select * from user;"

    Dim dataset As New DataSet
    adapter.Fill(dataset, "User")

with

    Dim adapter As New OleDbDataAdapter("select * from user;", connection)
    Dim dataset As New DataSet
    adapter.Fill(dataset, "User")

and also I have tried

    Dim adapter As New OleDbDataAdapter
    adapter.SelectCommand = New OleDbCommand("select * from user;", connection)
    Dim dataset As New DataSet
    adapter.Fill(dataset)

But I get the same exception Syntax error in FROM clause

Answers


User is a reserved word. It should be written like so:

"select * from [user];"

Note the square brackets. It would be better to avoid reserved words altogether by renaming the table.


I'm not certain adapter.SelectCommand = New OleDbCommand is correct (I don't do much VB so I could be wrong) In any event why are you closing the connection and then opening it and then not closing it? Move your close to the end.

Try something like this:

Dim conn As New OleDbConnection(connection)
Dim adapter As New OleDbDataAdapter()
conn.Open()
adapter.SelectCommand = new OleDbCommand("Select * From User", conn)
DataSet dataset As New DataSet
adapter.Fill(dataset)
conn.Close()

'Rest of your code


Need Your Help

Using OLEDB parameters in .NET when connecting to an AS400/DB2

.net sql db2 oledb ibm-midrange

I have been pulling my hair out trying to figure out what I can't get parameters to work in my query. I have the code written in VB.NET trying to do a query to an AS/400. I have IBM Access for Wind...

Simple sound wave generator with SDL in c++

c++ audio sdl

i am having problems understanding how the audio part of the sdl library works