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).

 

[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

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

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" <[email protected]> a écrit dans le message de
news: (e-mail address removed)...
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

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

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