Column names with spaces in them giving me trouble

  • Thread starter Thread starter nm
  • Start date Start date
N

nm

I am using .NET to insert a record into an Access Database. The Access
Database is coming from an external source and used by other applications, I
have no control over the column names. It has 3 columns, "First Name", "Last
Name", and "Work Email". As you can see, all 3 use a [space] in the column
title. Shame on whoever designed that database, but nevertheless I have to
deal with it. So anyway in C#, to insert a record, the SQL statement I'm
using is....

sqlStatement = "INSERT INTO MasterTable ('First Name', 'Last Name', 'Work
Email') VALUES ('" + "Bob" + "', '" + "Smith" + "','" +
Regex.Replace(tboxTestEmail.Text, @"'", @"''") + "')";

Which gets me the error...

Exception Details: System.Data.OleDb.OleDbException: The INSERT INTO
statement contains the following unknown field name: ''First Name''. Make
sure you have typed the name correctly, and try the operation again.

I have tried putting escaped " around the column names, to no avail (simliar
error). Leaving off the ' ' around the column names entirely gets me just a
general SQL Syntax error.

Not sure what else to try...
 
nm said:
I am using .NET to insert a record into an Access Database. The Access
Database is coming from an external source and used by other applications, I
have no control over the column names. It has 3 columns, "First Name", "Last
Name", and "Work Email". As you can see, all 3 use a [space] in the column
title. Shame on whoever designed that database, but nevertheless I have to
deal with it. So anyway in C#, to insert a record, the SQL statement I'm
using is....
Click to expand...

Try ([First Name], [Last Name] ...) instead.
 
Works! Thank you!

Jon Skeet said:
nm said:
I am using .NET to insert a record into an Access Database. The Access
Database is coming from an external source and used by other applications, I
have no control over the column names. It has 3 columns, "First Name", "Last
Name", and "Work Email". As you can see, all 3 use a [space] in the column
title. Shame on whoever designed that database, but nevertheless I have to
deal with it. So anyway in C#, to insert a record, the SQL statement I'm
using is....
Click to expand...

Try ([First Name], [Last Name] ...) instead.
Click to expand...
 
NM, just a word of advice on that. Get rid of the spaces. The odds are
pretty strong you or someone else is going to forget to do this and it'll
come up again and again and is probably going to end up being a lot more
headache than it's worth.

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
nm said:
Works! Thank you!

Jon Skeet said:
nm said:
I am using .NET to insert a record into an Access Database. The Access
Database is coming from an external source and used by other applications, I
have no control over the column names. It has 3 columns, "First Name", "Last
Name", and "Work Email". As you can see, all 3 use a [space] in the column
title. Shame on whoever designed that database, but nevertheless I
Click to expand...
Click to expand...
have
to
deal with it. So anyway in C#, to insert a record, the SQL statement I'm
using is....
Click to expand...

Try ([First Name], [Last Name] ...) instead.
Click to expand...
Click to expand...
 
Back
Top