issue with datatable : Invalid column name ' : '

[graphicsxp]

Hi,
I have a SQL stored procedure which looks like that:

SELECT @QuerySQL = 'SELECT [' + @LookupField + '] as LookupField,
G.GroupDesc + ' + '" : "' + ' + [' + @DescField + '] as DescField
FROM [' + @TableName + ']'
+ 'JOIN ItemGrouping IG on IG.CatID =' + cast(@CatID as varchar(50)) +
'and ItemID = [' + @LookupField + ']
JOIN [Grouping] G on G.GroupID = IG.GroupID
ORDER BY DescField'
EXEC @QuerySQL

Pretty tricky... but it works fine. The SQLServer Query Analyser
returns the expected records and the field DescField looks like :
somestring : someotherstring, so the concatenation works.

However, on the .Net side, when I do ExecuteNonQuery to fill my
Datatable, I get the following exception:

Invalid column name ' : '

I don't understand why it's trying to pick it up as a column, since
DescField should just be a varchar field made up of the concatenation
of @LookupField + ':' + @DescField

Any clue ?

Thanks

[Patrice]

IMO this is because you are using double quotes and depending on the quoted
identifier option double quotes are used to enclose a column name.

If you are using double quotes, try to use '' (two single quotes instead).
--
Patrice

"graphicsxp" <sa*************@googlemail.coma écrit dans le message de
news: 11*********************@i3g2000cwc.googlegroups.co m...

Hi,
I have a SQL stored procedure which looks like that:

SELECT @QuerySQL = 'SELECT [' + @LookupField + '] as LookupField,
G.GroupDesc + ' + '" : "' + ' + [' + @DescField + '] as DescField
FROM [' + @TableName + ']'
+ 'JOIN ItemGrouping IG on IG.CatID =' + cast(@CatID as varchar(50)) +
'and ItemID = [' + @LookupField + ']
JOIN [Grouping] G on G.GroupID = IG.GroupID
ORDER BY DescField'
EXEC @QuerySQL

Pretty tricky... but it works fine. The SQLServer Query Analyser
returns the expected records and the field DescField looks like :
somestring : someotherstring, so the concatenation works.

However, on the .Net side, when I do ExecuteNonQuery to fill my
Datatable, I get the following exception:

Invalid column name ' : '

I don't understand why it's trying to pick it up as a column, since
DescField should just be a varchar field made up of the concatenation
of @LookupField + ':' + @DescField

Any clue ?

Thanks

[graphicsxp]

Hi Patrice,

Great ! Thank you so much. In fact I had to do triple quotes to avoid
SQL error syntax, but you put me on the right track.

Cheers
Sam
Patrice wrote:

IMO this is because you are using double quotes and depending on the quoted
identifier option double quotes are used to enclose a column name.

If you are using double quotes, try to use '' (two single quotes instead).
--
Patrice

"graphicsxp" <sa*************@googlemail.coma écrit dans le message de
news: 11*********************@i3g2000cwc.googlegroups.co m...

Hi,
I have a SQL stored procedure which looks like that:

SELECT @QuerySQL = 'SELECT [' + @LookupField + '] as LookupField,
G.GroupDesc + ' + '" : "' + ' + [' + @DescField + '] as DescField
FROM [' + @TableName + ']'
+ 'JOIN ItemGrouping IG on IG.CatID =' + cast(@CatID as varchar(50)) +
'and ItemID = [' + @LookupField + ']
JOIN [Grouping] G on G.GroupID = IG.GroupID
ORDER BY DescField'
EXEC @QuerySQL

Pretty tricky... but it works fine. The SQLServer Query Analyser
returns the expected records and the field DescField looks like :
somestring : someotherstring, so the concatenation works.

However, on the .Net side, when I do ExecuteNonQuery to fill my
Datatable, I get the following exception:

Invalid column name ' : '

I don't understand why it's trying to pick it up as a column, since
DescField should just be a varchar field made up of the concatenation
of @LookupField + ':' + @DescField

Any clue ?

Thanks

[Patrice]

The external single quote is to start the string literal. Then you double
the single quote inside so that it is taken as a single quote embedded in a
literal string and not as the termination of this literal string.

--
Patrice

"graphicsxp" <sa*************@googlemail.coma écrit dans le message de
news: 11*********************@p79g2000cwp.googlegroups.c om...
Hi Patrice,

Great ! Thank you so much. In fact I had to do triple quotes to avoid
SQL error syntax, but you put me on the right track.

Cheers
Sam
Patrice wrote:

IMO this is because you are using double quotes and depending on the
quoted
identifier option double quotes are used to enclose a column name.

If you are using double quotes, try to use '' (two single quotes instead).
--
Patrice

"graphicsxp" <sa*************@googlemail.coma écrit dans le message de
news: 11*********************@i3g2000cwc.googlegroups.co m...

Hi,
I have a SQL stored procedure which looks like that:

SELECT @QuerySQL = 'SELECT [' + @LookupField + '] as LookupField,
G.GroupDesc + ' + '" : "' + ' + [' + @DescField + '] as DescField
FROM [' + @TableName + ']'
+ 'JOIN ItemGrouping IG on IG.CatID =' + cast(@CatID as varchar(50)) +
'and ItemID = [' + @LookupField + ']
JOIN [Grouping] G on G.GroupID = IG.GroupID
ORDER BY DescField'
EXEC @QuerySQL

Pretty tricky... but it works fine. The SQLServer Query Analyser
returns the expected records and the field DescField looks like :
somestring : someotherstring, so the concatenation works.

However, on the .Net side, when I do ExecuteNonQuery to fill my
Datatable, I get the following exception:

Invalid column name ' : '

I don't understand why it's trying to pick it up as a column, since
DescField should just be a varchar field made up of the concatenation
of @LookupField + ':' + @DescField

Any clue ?

Thanks