CommandBuilder generating flawed command for single table "select *"

  • Thread starter Thread starter Alan Baljeu
  • Start date Start date
A

Alan Baljeu

I had a database for tracking development tasks. It worked fine, until I introduced a
column called 'Use Case'. Below is part of the generated sql reported by SQL Profiler. I
was using a command builder with the command "select * from tasklist". It gives me an
error about the keyword Use. I could rename the column, or stop using CommandBuilder.

exec sp_executesql N'UPDATE tasklist SET Name = @p1 , Use Case = @p2 , Function Code = @p3
, Description = @p4 , Location of Details = @p5 , Assigned to = @p6 , Status = @p7 WHERE
( (ObjectID = @p8) AND (Name = @p9) AND ((@p10 = 1 AND Use Case IS NULL) OR (Use Case =
@p11)) ...

Any thoughts?

Alan
 
Alan Baljeu said:
I had a database for tracking development tasks. It worked fine, until I introduced a
column called 'Use Case'. Below is part of the generated sql reported by SQL Profiler. I
was using a command builder with the command "select * from tasklist". It gives me an
error about the keyword Use. I could rename the column, or stop using CommandBuilder.

exec sp_executesql N'UPDATE tasklist SET Name = @p1 , Use Case = @p2 , Function Code = @p3
, Description = @p4 , Location of Details = @p5 , Assigned to = @p6 , Status = @p7 WHERE
( (ObjectID = @p8) AND (Name = @p9) AND ((@p10 = 1 AND Use Case IS NULL) OR (Use Case =
@p11)) ...

Any thoughts?
Click to expand...

Change your column name.

Never put a space in a column name, use an underscore intead.
And Never use a SQL reserved word (Case) as a column name.

You will constantly be working around this. It's just a hastle.



If you must use this column name, the CommandBuilder.QuotePrefix and
CommandBuilder.QuoteSuffix can help.


dim cb as System.Data.SqlClient.SqlCommandBuilder

cb.QuotePrefix = "["
cb.QuoteSuffix = "]"

David
 
Back
Top