Query a table that has spaces in its name
Solution 1
Surround the spaced out item with square brackets:
[Common station]
Then slap the guy who designed the database.
Solution 2
SELECT * FROM [My Crazy Table With Spaces and Other Chars!]
Use brackets to "quote" table and field names.
Solution 3
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)"
Solution 4
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!
LaDante Riley
Very new to programming, but I am a mechanical engineer so I am used to seeing some C++ and C# code. My job is what has gotten me into programming.
Updated on July 21, 2022Comments
-
LaDante Riley almost 2 years
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?
-
LaDante Riley about 13 yearsThanks, 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
-
Gregory A Beamer almost 13 yearsI 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?).
-
LaDante Riley almost 13 yearsNot 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.
-
exrezzo about 3 yearsI laughed really hard at this.