Handling column names with parenthesis...

[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

 

[W.G. Ryan eMVP]

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

 

[Miha Markic [MVP C#]]

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

 

[Vagabond Software]

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.

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

 

[Sahil Malik]

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

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

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 eMVP]

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

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

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 eMVP]

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

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.

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

 

[Sahil Malik]

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

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.

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