Unable to fill OleDbDataAdapter with data

Question

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

score 6 · Answer 1 · answered Mar 12 '11 at 14:29

6

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.

answered Mar 12 '11 at 14:29

Fionnuala

90,370
7
114
152

score 0 · Answer 2 · answered Mar 12 '11 at 13:54

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

I tried the above code it gives the same exception `Syntax error in FROM clause.` at line `adapter.Fill(dataset)` — Student, Mar 12 '11 at 14:01

Unable to fill OleDbDataAdapter with data

2 Answers2