Click here to Skip to main content
15,042,336 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi everyone,
I'm moving on from VB.Net to Visual Studio C# and recoding a previous project from VB.Net to C#. I'm extremely new to C# and only ever used single SELECT SQL Queries in my VB.Net code. I'm trying to make some changes to the way I implement my design and have read that it should be possible to use SQL to SELECT multiple tables from an Access db into a single dataset; however, I'm getting a 'characters found after end of SQL statement' error.

What I have tried:

Looking at examples online I thought I was using the correct SQL syntax, but clearly not! I've probably made several errors given my lack of familiarity with C# and limited use of SQL. This is the code I'm using:

public partial class MainWindow : Window
    {
        private readonly string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:/Users/Me/Documents/My Games/Race/Race.accdb";
        private OleDbConnection accessConnection = null;
        private OleDbDataAdapter daShipData;
        private OleDbCommandBuilder dbCommandBuilder;
        public DataSet dsShips = new DataSet();

        private readonly string shipSelectionSQL = "SELECT * FROM Race2Ships ORDER BY ID; SELECT * FROM Race1Ships ORDER BY ID;";
        
        public MainWindow()
        {
            InitializeComponent();
            LoadData();
        }
       private void LoadData ()
        {
            try
            {
                accessConnection = new OleDbConnection(connectionString);
                accessConnection.Open();
                MessageBox.Show("Connection Opened");
                OleDbDataAdapter daShipData = new OleDbDataAdapter(shipSelectionSQL , connectionString );
                daShipData.Fill(dsShips, "ShipData");
                accessConnection.Close();
                MessageBox.Show("Connection Closed");
            }
            catch (Exception ex ){ MessageBox.Show(ex.Message); }
        }

    }
Posted
Updated 28-Apr-21 10:06am
Comments
Gerry Schmitz 28-Apr-21 15:07pm
   
You do a straight conversion ... then start thinking about doing it differently (based on being "extremely new").

1 solution

Access only allows one SQL statement per query: you cannot "chain them together" with semicolons.

As a result, you can only SELECT a single DataTable at a time - you can't fill two tables with one query.
You can Fill one DT followed by a second Command object to fill a second.
   
Comments
Welchbloke 28-Apr-21 16:27pm
   
Thanks, that explains my issue then. Back to single queries for me.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900