Query a table that has spaces in its name

Question

I have a situation, I have a Access table named Gas Flow Rates that I want to add records. When I try to run my insert query for a similar table Common Station, I get the following error:

"error hy000: syntax error, in query incomplete query clause"

Code is:

using System;
using System.Data.Odbc;

class MainClass
{
static void Main(string[] args)
{
    string connectionString = "Dsn=Gas_meter";
    string sqlins = "";
    OdbcConnection conn = new OdbcConnection(connectionString);

    OdbcCommand cmdnon = new OdbcCommand(sqlins, conn);
    conn.Open();

    try
    {
       cmdnon.CommandText = "INSERT INTO 'Common station' ( S1Flow, S2Flow, S3Flow, S4Flow) VALUES (9999,999, 999, 999)";
        //Once the above line works replace it with cmdnon.CommandText= "INSERT INTO Gas Flow Rates ( S1Flow, S2Flow, S3Flow, S4Flow) VALUES (9999,999, 999, 999)"
        int rowsAffected = cmdnon.ExecuteNonQuery();
        Console.WriteLine(rowsAffected);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
    }
    finally
    {
        conn.Close();
    }
}
}

How do I overcome that error?

score 53 · Accepted Answer · answered Jun 27 '11 at 20:03

53

Surround the spaced out item with square brackets:

[Common station]

Then slap the guy who designed the database.

answered Jun 27 '11 at 20:03

Gregory A Beamer

16,870
3
25
32

2

Thanks, I absolutely would if he didn't live Arizona. I think that I will send him a clip of the Chappelle show Rick James skit where Rick James tell him what did the five fingers say to the face joke. LMBO – LaDante Riley Jun 27 '11 at 20:20
I have an issue with a guy using keywords as column names. Same solution (i.e. [] - and perhaps the slap?). There are reasons we don't do this (does the word maintainability mean anything?). – Gregory A Beamer Jun 28 '11 at 16:12
Not really, it doesn't hold any great importance that cant be derived from either Gas_Flow_Rates or GasFlowRates. This is just the way that he created the table as far as I know. – LaDante Riley Jun 28 '11 at 18:28
I laughed really hard at this. – exrezzo May 19 '21 at 10:25

score 7 · Answer 2 · answered Jun 27 '11 at 20:03

7

SELECT * FROM [My Crazy Table With Spaces and Other Chars!]

Use brackets to "quote" table and field names.

answered Jun 27 '11 at 20:03

Mike Caron

14,351
4
49
77

score 4 · Answer 3 · answered Jun 27 '11 at 20:03

  cmdnon.CommandText = "INSERT INTO '[Common station]' ( S1Flow, S2Flow, S3Flow, S4Flow) VALUES (9999,999, 999, 999)";
    //Once the above line works replace it with cmdnon.CommandText= "INSERT INTO Gas Flow Rates ( S1Flow, S2Flow, S3Flow, S4Flow) VALUES (9999,999, 999, 999)"

score 2 · Answer 4 · answered Oct 27 '17 at 15:35

Late to the party I know, but have just solved my own issue here... Playing in access 2007 using ODBC connection to an SQL Db.

Table name is Employee_Appointment Extra Detail Custom Syntax to select is as follows SQlRecordSet.Open "Select * from [Employee].[Appointment Extra Detail Custom]", Conn, adOpenStatic, adLockOptimistic

Hope this saves someone else a few hours of playing!

Query a table that has spaces in its name

4 Answers4

Linked

Related