Re: Column_Name with periods in name
- From: pb <philbrierley@xxxxxxxxxxx>
- Date: 22 May 2007 01:36:19 -0700
Thanks for your comments, but as a software developer I can't control
what users call fields in their databases, and there are other
databases being hooked into apart from SQL Server. It does actually
happen that periods are used in field names, which is how a user
discovered the error in my software. I don't see why rsSchema!
Column_Name returns an interpretation of the column name rather than
the real column name.
I'll try your suggestion to see what happens.
Pb.
On May 22, 10:46 am, "Bob Barrows [MVP]" <reb01...@xxxxxxxxxxxxxxx>
wrote:
pb wrote:
Hi,
I am trying to retreive the column names from a database table using
something like
Dim aRestrictions As Variant
aRestrictions = Array(Empty, Empty, strTableName, Empty)
Set rsSchema = cnSim.OpenSchema(adSchemaColumns, aRestrictions)
Do Until rsSchema.EOF = True
i = i + 1
colnames(i) = rsSchema!Column_Name
rsSchema.MoveNext
Loop
What I am finding is that if the real column name contains a period,
<gasp>
Seriously???? Periods?!?!?
You do realize that in SQL Server, periods are used as delimiters in the
naming convention ...
what is returned from ADO is the period replace with a hash.
eg.
my.fieldname >> my#fieldname
I'm amazed it handles it at all ...
This is not a problem when running queries derived from the extracted
field names, as it deals with them quite well. For example, two
similar names such as A#A and A.A become A#A and A#A1
My issue is that I want the REAL field name, as I then use it to go
and generate some SQL. If I currently run the SQL on the field names
returned by ADO then it spits the dummy as some of the field names
don't exist in the database.
I could replace and '#' that is returned with '.', but I'd be guessing
that it was not suppose to be a # in the first place.
Has anyone come accross this
No, of course not.
and is there a solution?
Stop using periods in column names.
Try using ADOX to retrieve the column names.
Try opening a recordset using where 1=2 so you don't retrieve any data, and
loop through the Fields collection to see if the real names are retrieved
this way.
But seriously .... stop using periods in column names!
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"- Hide quoted text -
- Show quoted text -
.
- Follow-Ups:
- Re: Column_Name with periods in name
- From: Bob Barrows [MVP]
- Re: Column_Name with periods in name
- References:
- Column_Name with periods in name
- From: pb
- Re: Column_Name with periods in name
- From: Bob Barrows [MVP]
- Column_Name with periods in name
- Prev by Date: Re: Column_Name with periods in name
- Next by Date: Re: snapshot isolation level
- Previous by thread: Re: Column_Name with periods in name
- Next by thread: Re: Column_Name with periods in name
- Index(es):
Relevant Pages
|
|
