Handling column names with parenthesis...

  • Thread starter Thread starter Vagabond Software
  • Start date Start date
V

Vagabond Software

I am parsing column names from text files and building CREATE TABLE and INSERT statements at run-time using these names. I have no control over the data written to the text files.

When building my SQL statements, I bracket all column names (e.g. "[" + name + "]"). However, this doesn't seem to be enough for column names that contain parenthesis. I receive an "Invalid bracketing of name..." error when attempting an ExecuteNonQuery using CommandText with column names containing parenthesis.

Any help or suggestions would be greatly appreciated. Here are the three lines of code that build the part of the command around the column names using StringBuilder:

sbprefix.Append("[");
sbprefix.Append(columnName);
sbprefix.Append("]");

Thanks in advance.

- carl
 
I would highly recommend against using () in column names unless there is no
other way around it. I'm not sure how/if you can handle this directly in
ADO.NET, but it's still going to be an awkward construct and will, in all
likelihood come back to haunt you in the future.

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
I am parsing column names from text files and building CREATE TABLE and
INSERT statements at run-time using these names. I have no control over the
data written to the text files.

When building my SQL statements, I bracket all column names (e.g. "[" + name
+ "]"). However, this doesn't seem to be enough for column names that
contain parenthesis. I receive an "Invalid bracketing of name..." error
when attempting an ExecuteNonQuery using CommandText with column names
containing parenthesis.

Any help or suggestions would be greatly appreciated. Here are the three
lines of code that build the part of the command around the column names
using StringBuilder:

sbprefix.Append("[");
sbprefix.Append(columnName);
sbprefix.Append("]");

Thanks in advance.

- carl
 
Ditto.
Why do databases allow setting non-alphanumeric characters for metadata is
beyond me.
 
W.G. Ryan eMVP said:
I would highly recommend against using () in column names unless there is no
other way around it. I'm not sure how/if you can handle this directly in
ADO.NET, but it's still going to be an awkward construct and will, in all
likelihood come back to haunt you in the future.
Click to expand...

Unfortunately, I have absolutely no control over what the client's LabView developers decide to use as Column names. I have communicated something very close to your own comments to the engineering manager at the client company, but the current task remains before me.

I suppose I can see how the idea of me unilaterally substituting parenthesis with more acceptable characters flies by the client. I have a feeling the idea will not be popular.

- carl
 
I have not to date seen one developer say .. MAN I'M GLAD I WAS ABLE TO USE
SPACES, OR {} OR () CHARACTERS IN MY COLUMNNAME.

- Sahil Malik
You can reach me thru my blog at
http://www.dotnetjunkies.com/weblog/sahilmalik


Miha Markic said:
Ditto.
Why do databases allow setting non-alphanumeric characters for metadata is
beyond me.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

W.G. Ryan eMVP said:
I would highly recommend against using () in column names unless there is
no
other way around it. I'm not sure how/if you can handle this directly in
ADO.NET, but it's still going to be an awkward construct and will, in all
likelihood come back to haunt you in the future.
Click to expand...
Click to expand...
 
:-)

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
Miha Markic said:
Ditto.
Why do databases allow setting non-alphanumeric characters for metadata is
beyond me.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

W.G. Ryan eMVP said:
I would highly recommend against using () in column names unless there is
no
other way around it. I'm not sure how/if you can handle this directly in
ADO.NET, but it's still going to be an awkward construct and will, in all
likelihood come back to haunt you in the future.
Click to expand...
Click to expand...
 
going to have to punt on this one - sorry for that. Hopefully one of the
guys in the product group can help.

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
W.G. Ryan eMVP said:
I would highly recommend against using () in column names unless there is no
other way around it. I'm not sure how/if you can handle this directly in
ADO.NET, but it's still going to be an awkward construct and will, in all
likelihood come back to haunt you in the future.
Click to expand...

Unfortunately, I have absolutely no control over what the client's LabView
developers decide to use as Column names. I have communicated something
very close to your own comments to the engineering manager at the client
company, but the current task remains before me.

I suppose I can see how the idea of me unilaterally substituting parenthesis
with more acceptable characters flies by the client. I have a feeling the
idea will not be popular.

- carl
 
Carl,

I think your error is really something else.

Look at the following code --

Imports System.Data
Imports System.Data.SqlClient

Module Module1
Sub Main()
Dim conn As SqlConnection = New
SqlClient.SqlConnection("Server=SahilMalik;Database=Northwind;user=sa;password=sa")
Dim cmd As SqlCommand = conn.CreateCommand()
Try
cmd.CommandText = "Create Table MyName ([Sahil(Malik)]
varchar(50) NULL)"
conn.Open()
cmd.ExecuteNonQuery()
Console.Write("Done !!")
Catch ex As Exception
Console.Write(ex.ToString())
Finally
If (conn.State = ConnectionState.Open) Then
conn.Close()
End If
Console.Read()
End Try
End Sub
End Module


.... This code works perfectly :-/

- Sahil Malik
You can reach me thru my blog http://www.dotnetjunkies.com/weblog/sahilmalik




W.G. Ryan eMVP said:
I would highly recommend against using () in column names unless there is
no
other way around it. I'm not sure how/if you can handle this directly in
ADO.NET, but it's still going to be an awkward construct and will, in all
likelihood come back to haunt you in the future.
Click to expand...

Unfortunately, I have absolutely no control over what the client's LabView
developers decide to use as Column names. I have communicated something
very close to your own comments to the engineering manager at the client
company, but the current task remains before me.

I suppose I can see how the idea of me unilaterally substituting parenthesis
with more acceptable characters flies by the client. I have a feeling the
idea will not be popular.

- carl
 
Back
Top