DataAdapter.Fill & case sensitivity

  • Thread starter Thread starter Koi
  • Start date Start date
K

Koi

Hi,

I have table in SQL Server with column names "a", "b", "B", "d", "D",
....

When I use the SqlDataAdapter .Fill method to create & fill a DataTable
with data from that table, the columnnames have changed to "a", "b",
"B1", "d", "D1", ...

I suppose this has something to do with case sensitivity, but I don't
know how to fix it (the CaseSensitive property of the DataTable changes
nothing, as this apparently only applies to string comparisons within
the table, and not to column names)

I also tried the .FillSchema with SchemaType.Source prior to .Fill,
because that mentioned "without applying any transformations", but no
avail.

Any ideas?
 
Hi,

In your case column name b and B would be the same for the provider and it
tries to create alias for the second instance of the field name. I would
suggest to create aliases explicitly to avoid this issue. For example, your
SELECT statement should look like

SELECT a, b, B as MyAnotherB, d, D MyAnotherD FROM ...

In this case you will be able to reference B and D columns in a code using
assigned aliases
 
Val said:
Hi,

In your case column name b and B would be the same for the provider and it
tries to create alias for the second instance of the field name. I would
suggest to create aliases explicitly to avoid this issue. For example, your
SELECT statement should look like

SELECT a, b, B as MyAnotherB, d, D MyAnotherD FROM ...

In this case you will be able to reference B and D columns in a code using
assigned aliases
Click to expand...

Hi,

That won't work, because the function where I use the .Fill is generic.
I don't know what columns I will retrieve, but the output must contain
the same column names as in the database.

SQL server allows these column names, so surely there must be a way to
keep the same column names as in the database? Or is this a bug?
 
Koi,

There are people who can give even worser variablenames than you did.

I i 1 l L

However they are qualified directly when somebode else who has a little
knowledge about datanames sees that.

Therefore I think it is better to give your database fields more proper
names.

Just my thought,

Cor
 
Cor,

I couldn't agree more, the column names are a pain in the (lower back).
Unfortunately those names aren't up to me.

We have to write the software that converts the data from customer A to
an XML document for customer B. There are dozens of tables with all
different columns, and customer B expects the exact same column names
as in customer A's schema...

Since most column names are only 1 letter, I thought about changing the
columnnames afterwards by removing all the 1's. But no luck there:
some columns are named "R1", "X1", ...
Yes, the world can be a scary place sometimes...
 
Hi,

I do not think SQL Server allows to have same name for the column several
times in a same table. You might have same column names in a select, but to
work properly with them, provider has to have some way to distinct them and
here it requires unique name.
 
Koi,

Reading the message from Val and you do not succeed at all, I got the idea
why not try to access the columns at there index number.

You would have to write your own arrays of fieldnames, however that cannot
be that hard.

Cor
 
Back
Top