如何将两个Access表关联至DataGridView？FROM语句格式报错

Hey there! Let's get that DataGridView displaying your joined table data sorted out. The error you're seeing is because your INNER JOIN syntax is incomplete—you must include an ON clause to define how the two tables relate to each other. Without it, the database has no idea how to link records from tblOrders and tblTickets.

What's Wrong with the Original SQL? #

Your current command is missing the critical ON condition:

Dim cmd As New OleDbCommand("Select * from [tblOrders] inner join [tblTickets] where Username = @username", cn)

INNER JOIN requires you to specify which columns connect the two tables (e.g., a shared ID like OrderID or TicketID). You also need to make sure the WHERE clause comes after the join condition.

Step-by-Step Fix #

1. Add the ON Clause: Replace the placeholder YourSharedColumn with the actual column that exists in both tblOrders and tblTickets (this is how the database matches related records).
2. Clean Up Resource Management: Use Using statements to ensure database connections and commands are properly disposed of after use.
3. Simplify Data Binding: Make sure your BindingSource and DataGridView are set up correctly.

Corrected Code #

Here's the revised version of your btnDisplayDataGrid_Click method:

Imports System.Data.OleDb
Public Class frmViewTables
    ' Reuse the connection string to avoid duplication
    Private connString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "\SAC1 Database.mdb"

    Private Sub btnDisplayDataGrid_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDisplayDataGrid.Click
        Dim ds As New DataSet()
        Dim source1 As New BindingSource()

        ' Use Using statements to auto-dispose database objects
        Using cn As New OleDbConnection(connString)
            ' Replace YourSharedColumn with the actual matching column (e.g., OrderID, TicketID)
            Dim sql As String = "SELECT * FROM [tblOrders] INNER JOIN [tblTickets] ON tblOrders.YourSharedColumn = tblTickets.YourSharedColumn WHERE Username = @username"
            
            Using cmd As New OleDbCommand(sql, cn)
                ' Add parameter to prevent SQL injection
                cmd.Parameters.Add("@username", OleDbType.VarChar, 255).Value = frmLogin.SuccessfulLoginUsername
                
                Using da As New OleDbDataAdapter(cmd)
                    da.Fill(ds, "JoinedTicketsOrders")
                End Using
            End Using
        End Using

        ' Bind the data to the DataGridView
        source1.DataSource = ds.Tables("JoinedTicketsOrders")
        dgvDynamic.DataSource = source1
    End Sub
End Class

Key Notes #

• Replace YourSharedColumn: You need to swap this with the actual column that links tblOrders and tblTickets. For example, if both tables have an OrderID column that connects them, use tblOrders.OrderID = tblTickets.OrderID.
• Using Statements: These ensure that database connections, commands, and adapters are closed and disposed properly, preventing resource leaks.
• Parameterized Query: Your original code already uses this, which is great—it protects against SQL injection attacks.

Once you update the ON clause with your actual shared column, clicking the button should load the joined records into your dgvDynamic DataGridView without syntax errors.

内容的提问来源于stack exchange，提问作者Ethan