blackhorse123 blackhorse123 - 7 months ago 25
Vb.net Question

cannot connect to access database using vb.net

I'm trying to connect to my access database which is mdb to vb.net project. I've changed the platform to x86 but it's still not working. I can't really seem to know what the problem is. Hope I can get some help, thank you in advance

Imports System.Data.OleDb 'provides classes to connect to the database

Public Class Form1
Dim conn As New OleDbConnection

Private Sub RefreshData()
If Not conn.State = ConnectionState.Open Then
conn.Open()
End If

Dim da As New OleDbDataAdapter("SELECT ID as [No], " & _
"UserName as [Name], " & _
"FROM Table1 " & _
"ORDER BY ID", conn)
Dim dt As New DataTable
'fill data to dataTable
da.Fill(dt)

'Offer data in dataTable to dataGridView
Me.DataGridView1.DataSource = dt

'close connection
conn.Close()
End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
conn = New OleDbConnection
conn.ConnectionString = "PROVIDER = Microsoft.Jet.OleDB.4.0; Data Source= C:\Users\user\Documents\Access Database\Database2.mdb"

RefreshData()
End Sub
End Class

Answer

You have an error in your SQL statement. The code:

Dim da As New OleDbDataAdapter("SELECT ID as [No], " & _
    "UserName as [Name], " & _
    "FROM Table1 " & _
    "ORDER BY ID", conn)

Should NOT have a comma after as [Name], Instead it should read:

Dim da As New OleDbDataAdapter("SELECT ID as [No], " & _
    "UserName as [Name] " & _ `NO COMMA!
    "FROM Table1 " & _
    "ORDER BY ID", conn)