By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,534 Members | 1,811 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,534 IT Pros & Developers. It's quick & easy.

reserved word for DB column name crashes data adapter update

P: n/a
I'm working on a project in VB.net connecting to a SQL Server 2000 database
that I can't modify
I created a dataset with a schema identical to the DB.
When trying to update the DB from the dataset using the dataAdapter.update
sub, I get an error.
The problem seems to be that one of the fields was named 'desc' which is a
reserved word for SQL.
I've been able to work around this design flaw 'till now but it looks like
the update sub can't handle it.
I used the command builder to create the Update, Insert and Delete scripts.
The trace is like this:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred
in system.data.dll

Additional information: System error.
Unhandled Exception: System.Data.SqlClient.SqlException: Incorrect syntax
near the keyword 'desc'.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)

Is there a way around this?
Nov 20 '05 #1
Share this Question
Share on Google+
17 Replies


P: n/a
> I'm working on a project in VB.net connecting to a SQL Server 2000
database
that I can't modify
I created a dataset with a schema identical to the DB.
When trying to update the DB from the dataset using the dataAdapter.update
sub, I get an error.
If you cannot modify the database, why bother trying to update it? This
would be my first red flag.
I've been able to work around this design flaw 'till now but it looks like
the update sub can't handle it.


Every once in a while, someone makes a column or other object in a database
whose name is a reserved word. SQL Server will let you refer to this if you
enclose the object's identifier in square brackets:

SELECT [Desc] FROM t_SomeTable

Sometimes, SQL Server will understand what you mean if you qualify the item
you are referring to:

SELECT t_SomeTable.Desc FROM t_SomeTable

Another thing you might consider, though I personally don't like it is this:

SET QUOTED_IDENTIFIER ON
SELECT "Desc" FROM t_SomeTable
SET QUOTED_IDENTIFIER OFF

Generally speaking, the SQL Server Query Analyzer is your best friend for
figuring out correct SQL syntax irrespective of application code. Once you
get the SQL syntax to do the right thing in Query Analyzer, then try it in
your application code. If it then doesn't work, there is something else
going wrong, such as runtime permissions or other screwy application stuff.
--
Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei
Nov 20 '05 #2

P: n/a
when I said that I can't modify the DB, I meant the structure, not the data.

I was aware of the [] work around but it doesn't look like VB.net does it.
The update code is generated by VB.net as I used the command builder (don't
waste your time telling me that I should write my own instead of using the
command builder)
It looks like if you use a reserved word, VB.net will not take any
precautions to avoid an error when dealing with the database.

Is there a way to correct that problem?

"Mike Labosh" <ml*****************@vbsensei.com> wrote in message
news:ur**************@TK2MSFTNGP11.phx.gbl...
I'm working on a project in VB.net connecting to a SQL Server 2000 database
that I can't modify
I created a dataset with a schema identical to the DB.
When trying to update the DB from the dataset using the dataAdapter.update sub, I get an error.


If you cannot modify the database, why bother trying to update it? This
would be my first red flag.
I've been able to work around this design flaw 'till now but it looks like the update sub can't handle it.


Every once in a while, someone makes a column or other object in a

database whose name is a reserved word. SQL Server will let you refer to this if you enclose the object's identifier in square brackets:

SELECT [Desc] FROM t_SomeTable

Sometimes, SQL Server will understand what you mean if you qualify the item you are referring to:

SELECT t_SomeTable.Desc FROM t_SomeTable

Another thing you might consider, though I personally don't like it is this:
SET QUOTED_IDENTIFIER ON
SELECT "Desc" FROM t_SomeTable
SET QUOTED_IDENTIFIER OFF

Generally speaking, the SQL Server Query Analyzer is your best friend for
figuring out correct SQL syntax irrespective of application code. Once you get the SQL syntax to do the right thing in Query Analyzer, then try it in
your application code. If it then doesn't work, there is something else
going wrong, such as runtime permissions or other screwy application stuff. --
Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei

Nov 20 '05 #3

P: n/a
Benoit:

If there's a battle worth fighting, it's Changing column names that are
reserved words. Have you tried changing the Alias on it w/ the []? I've
been told by many that it should work unless you are using access. If you
can't do any of that, why not create a View based on the table and give it
real names. Whoever insists on using Desc and not letting you change it is
being Very shortsighted, but he/she couldn't possibly prohibit you from
creating a view and using it instead.
"Benoit Martin" <bm*********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
when I said that I can't modify the DB, I meant the structure, not the data.
I was aware of the [] work around but it doesn't look like VB.net does it.
The update code is generated by VB.net as I used the command builder (don't waste your time telling me that I should write my own instead of using the
command builder)
It looks like if you use a reserved word, VB.net will not take any
precautions to avoid an error when dealing with the database.

Is there a way to correct that problem?

"Mike Labosh" <ml*****************@vbsensei.com> wrote in message
news:ur**************@TK2MSFTNGP11.phx.gbl...
I'm working on a project in VB.net connecting to a SQL Server 2000

database
that I can't modify
I created a dataset with a schema identical to the DB.
When trying to update the DB from the dataset using the dataAdapter.update sub, I get an error.


If you cannot modify the database, why bother trying to update it? This
would be my first red flag.
I've been able to work around this design flaw 'till now but it looks like the update sub can't handle it.


Every once in a while, someone makes a column or other object in a

database
whose name is a reserved word. SQL Server will let you refer to this if

you
enclose the object's identifier in square brackets:

SELECT [Desc] FROM t_SomeTable

Sometimes, SQL Server will understand what you mean if you qualify the

item
you are referring to:

SELECT t_SomeTable.Desc FROM t_SomeTable

Another thing you might consider, though I personally don't like it is

this:

SET QUOTED_IDENTIFIER ON
SELECT "Desc" FROM t_SomeTable
SET QUOTED_IDENTIFIER OFF

Generally speaking, the SQL Server Query Analyzer is your best friend for figuring out correct SQL syntax irrespective of application code. Once

you
get the SQL syntax to do the right thing in Query Analyzer, then try it in your application code. If it then doesn't work, there is something else
going wrong, such as runtime permissions or other screwy application

stuff.
--
Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei


Nov 20 '05 #4

P: n/a
thanks William,

not being able to change the field name is not too much a problem of being
stuborn but more a problem with the consequences of changing this field
name.

When you are talking about Alia, is it in the dataset? I wasn't aware of
this alias "property". Where is that set?

Thank you

"William Ryan" <do********@nospam.comcast.net> wrote in message
news:%2***************@tk2msftngp13.phx.gbl...
Benoit:

If there's a battle worth fighting, it's Changing column names that are
reserved words. Have you tried changing the Alias on it w/ the []? I've
been told by many that it should work unless you are using access. If you
can't do any of that, why not create a View based on the table and give it
real names. Whoever insists on using Desc and not letting you change it is being Very shortsighted, but he/she couldn't possibly prohibit you from
creating a view and using it instead.
"Benoit Martin" <bm*********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
when I said that I can't modify the DB, I meant the structure, not the data.

I was aware of the [] work around but it doesn't look like VB.net does it.
The update code is generated by VB.net as I used the command builder

(don't
waste your time telling me that I should write my own instead of using the command builder)
It looks like if you use a reserved word, VB.net will not take any
precautions to avoid an error when dealing with the database.

Is there a way to correct that problem?

"Mike Labosh" <ml*****************@vbsensei.com> wrote in message
news:ur**************@TK2MSFTNGP11.phx.gbl...
> I'm working on a project in VB.net connecting to a SQL Server 2000
database
> that I can't modify
> I created a dataset with a schema identical to the DB.
> When trying to update the DB from the dataset using the

dataAdapter.update
> sub, I get an error.

If you cannot modify the database, why bother trying to update it? This would be my first red flag.

> I've been able to work around this design flaw 'till now but it looks
like
> the update sub can't handle it.

Every once in a while, someone makes a column or other object in a

database
whose name is a reserved word. SQL Server will let you refer to this
if you
enclose the object's identifier in square brackets:

SELECT [Desc] FROM t_SomeTable

Sometimes, SQL Server will understand what you mean if you qualify the

item
you are referring to:

SELECT t_SomeTable.Desc FROM t_SomeTable

Another thing you might consider, though I personally don't like it is

this:

SET QUOTED_IDENTIFIER ON
SELECT "Desc" FROM t_SomeTable
SET QUOTED_IDENTIFIER OFF

Generally speaking, the SQL Server Query Analyzer is your best friend for figuring out correct SQL syntax irrespective of application code.
Once you
get the SQL syntax to do the right thing in Query Analyzer, then try

it in your application code. If it then doesn't work, there is something

else going wrong, such as runtime permissions or other screwy application

stuff.
--
Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei



Nov 20 '05 #5

P: n/a
SELECT SomeColumn as AliasName from MyTable
"Benoit Martin" <bm*********@hotmail.com> wrote in message
news:Ob**************@tk2msftngp13.phx.gbl...
thanks William,

not being able to change the field name is not too much a problem of being
stuborn but more a problem with the consequences of changing this field
name.

When you are talking about Alia, is it in the dataset? I wasn't aware of
this alias "property". Where is that set?

Thank you

"William Ryan" <do********@nospam.comcast.net> wrote in message
news:%2***************@tk2msftngp13.phx.gbl...
Benoit:

If there's a battle worth fighting, it's Changing column names that are
reserved words. Have you tried changing the Alias on it w/ the []? I've
been told by many that it should work unless you are using access. If you can't do any of that, why not create a View based on the table and give it real names. Whoever insists on using Desc and not letting you change it is
being Very shortsighted, but he/she couldn't possibly prohibit you from
creating a view and using it instead.
"Benoit Martin" <bm*********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
when I said that I can't modify the DB, I meant the structure, not the

data.

I was aware of the [] work around but it doesn't look like VB.net does it. The update code is generated by VB.net as I used the command builder

(don't
waste your time telling me that I should write my own instead of using the command builder)
It looks like if you use a reserved word, VB.net will not take any
precautions to avoid an error when dealing with the database.

Is there a way to correct that problem?

"Mike Labosh" <ml*****************@vbsensei.com> wrote in message
news:ur**************@TK2MSFTNGP11.phx.gbl...
> > I'm working on a project in VB.net connecting to a SQL Server 2000
> database
> > that I can't modify
> > I created a dataset with a schema identical to the DB.
> > When trying to update the DB from the dataset using the
dataAdapter.update
> > sub, I get an error.
>
> If you cannot modify the database, why bother trying to update it? This > would be my first red flag.
>
> > I've been able to work around this design flaw 'till now but it looks like
> > the update sub can't handle it.
>
> Every once in a while, someone makes a column or other object in a
database
> whose name is a reserved word. SQL Server will let you refer to this if
you
> enclose the object's identifier in square brackets:
>
> SELECT [Desc] FROM t_SomeTable
>
> Sometimes, SQL Server will understand what you mean if you qualify
the item
> you are referring to:
>
> SELECT t_SomeTable.Desc FROM t_SomeTable
>
> Another thing you might consider, though I personally don't like it is this:
>
> SET QUOTED_IDENTIFIER ON
> SELECT "Desc" FROM t_SomeTable
> SET QUOTED_IDENTIFIER OFF
>
> Generally speaking, the SQL Server Query Analyzer is your best

friend for
> figuring out correct SQL syntax irrespective of application code.

Once you
> get the SQL syntax to do the right thing in Query Analyzer, then try it
in
> your application code. If it then doesn't work, there is something

else > going wrong, such as runtime permissions or other screwy application
stuff.
> --
> Peace & happy computing,
>
> Mike Labosh, MCSD MCT
> Owner, vbSensei.Com
> "Escriba coda ergo sum." -- vbSensei
>
>



Nov 20 '05 #6

P: n/a
oh, that's what you were talking about, sorry...

unfortunately I don't think it's gonna work for me as the code is generated
by VB and I don't write any SELECT statement for my update command.

I guess there is no work around then

"William Ryan" <do********@nospam.comcast.net> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
SELECT SomeColumn as AliasName from MyTable
"Benoit Martin" <bm*********@hotmail.com> wrote in message
news:Ob**************@tk2msftngp13.phx.gbl...
thanks William,

not being able to change the field name is not too much a problem of being
stuborn but more a problem with the consequences of changing this field
name.

When you are talking about Alia, is it in the dataset? I wasn't aware of
this alias "property". Where is that set?

Thank you

"William Ryan" <do********@nospam.comcast.net> wrote in message
news:%2***************@tk2msftngp13.phx.gbl...
Benoit:

If there's a battle worth fighting, it's Changing column names that are reserved words. Have you tried changing the Alias on it w/ the []? I've been told by many that it should work unless you are using access. If you can't do any of that, why not create a View based on the table and give
it
real names. Whoever insists on using Desc and not letting you change
it is
being Very shortsighted, but he/she couldn't possibly prohibit you
from creating a view and using it instead.
"Benoit Martin" <bm*********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
> when I said that I can't modify the DB, I meant the structure, not the data.
>
> I was aware of the [] work around but it doesn't look like VB.net does it.
> The update code is generated by VB.net as I used the command builder
(don't
> waste your time telling me that I should write my own instead of
using the
> command builder)
> It looks like if you use a reserved word, VB.net will not take any
> precautions to avoid an error when dealing with the database.
>
> Is there a way to correct that problem?
>
> "Mike Labosh" <ml*****************@vbsensei.com> wrote in message
> news:ur**************@TK2MSFTNGP11.phx.gbl...
> > > I'm working on a project in VB.net connecting to a SQL Server
2000 > > database
> > > that I can't modify
> > > I created a dataset with a schema identical to the DB.
> > > When trying to update the DB from the dataset using the
> dataAdapter.update
> > > sub, I get an error.
> >
> > If you cannot modify the database, why bother trying to update it? This
> > would be my first red flag.
> >
> > > I've been able to work around this design flaw 'till now but it

looks
> like
> > > the update sub can't handle it.
> >
> > Every once in a while, someone makes a column or other object in a
> database
> > whose name is a reserved word. SQL Server will let you refer to

this
if
> you
> > enclose the object's identifier in square brackets:
> >
> > SELECT [Desc] FROM t_SomeTable
> >
> > Sometimes, SQL Server will understand what you mean if you qualify

the > item
> > you are referring to:
> >
> > SELECT t_SomeTable.Desc FROM t_SomeTable
> >
> > Another thing you might consider, though I personally don't like it is
> this:
> >
> > SET QUOTED_IDENTIFIER ON
> > SELECT "Desc" FROM t_SomeTable
> > SET QUOTED_IDENTIFIER OFF
> >
> > Generally speaking, the SQL Server Query Analyzer is your best friend for
> > figuring out correct SQL syntax irrespective of application code.

Once
> you
> > get the SQL syntax to do the right thing in Query Analyzer, then

try it
in
> > your application code. If it then doesn't work, there is
something else
> > going wrong, such as runtime permissions or other screwy

application > stuff.
> > --
> > Peace & happy computing,
> >
> > Mike Labosh, MCSD MCT
> > Owner, vbSensei.Com
> > "Escriba coda ergo sum." -- vbSensei
> >
> >
>
>



Nov 20 '05 #7

P: n/a
Cor
Hi Benoit,

Take a look on your form and see if you see there below the OleDBdatadapter
or the SQLdataadapter.

Probably it is there rightclick on it and choose configure adapter, there
you can change your select statement.

I hope this helps

Cor

unfortunately I don't think it's gonna work for me as the code is generated by VB and I don't write any SELECT statement for my update command.

Nov 20 '05 #8

P: n/a
Sorry, but I have to step in here. I've been watching this thread with
dubious interest.

You seem to have this idea that command builder is king, well let me tell
you that it is not.
Command builder can only handle simple queries, sooner or later you are
going
to have to roll up your sleeves and get your hands dirty.

Bill' suggestion is a good one, simply modify the code already produced by
the
command builer to suit your needs.

OHM#

Benoit Martin wrote:
oh, that's what you were talking about, sorry...

unfortunately I don't think it's gonna work for me as the code is
generated by VB and I don't write any SELECT statement for my update
command.

I guess there is no work around then

"William Ryan" <do********@nospam.comcast.net> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
SELECT SomeColumn as AliasName from MyTable
"Benoit Martin" <bm*********@hotmail.com> wrote in message
news:Ob**************@tk2msftngp13.phx.gbl...
thanks William,

not being able to change the field name is not too much a problem
of being stuborn but more a problem with the consequences of
changing this field name.

When you are talking about Alia, is it in the dataset? I wasn't
aware of this alias "property". Where is that set?

Thank you

"William Ryan" <do********@nospam.comcast.net> wrote in message
news:%2***************@tk2msftngp13.phx.gbl...
Benoit:

If there's a battle worth fighting, it's Changing column names
that are reserved words. Have you tried changing the Alias on it
w/ the []? I've been told by many that it should work unless you
are using access. If you can't do any of that, why not create a
View based on the table and give it real names. Whoever insists
on using Desc and not letting you change it is being Very
shortsighted, but he/she couldn't possibly prohibit you from
creating a view and using it instead. "Benoit Martin"
<bm*********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
> when I said that I can't modify the DB, I meant the structure,
> not the data.
>
> I was aware of the [] work around but it doesn't look like VB.net
> does it. The update code is generated by VB.net as I used the
> command builder (don't waste your time telling me that I should
> write my own instead of using the command builder)
> It looks like if you use a reserved word, VB.net will not take any
> precautions to avoid an error when dealing with the database.
>
> Is there a way to correct that problem?
>
> "Mike Labosh" <ml*****************@vbsensei.com> wrote in message
> news:ur**************@TK2MSFTNGP11.phx.gbl...
>>> I'm working on a project in VB.net connecting to a SQL Server
>>> 2000 database that I can't modify
>>> I created a dataset with a schema identical to the DB.
>>> When trying to update the DB from the dataset using the
>>> dataAdapter.update sub, I get an error.
>>
>> If you cannot modify the database, why bother trying to update
>> it? This would be my first red flag.
>>
>>> I've been able to work around this design flaw 'till now but it
>>> looks like the update sub can't handle it.
>>
>> Every once in a while, someone makes a column or other object in
>> a database whose name is a reserved word. SQL Server will let
>> you refer to

this
if
> you
>> enclose the object's identifier in square brackets:
>>
>> SELECT [Desc] FROM t_SomeTable
>>
>> Sometimes, SQL Server will understand what you mean if you
>> qualify the item you are referring to:
>>
>> SELECT t_SomeTable.Desc FROM t_SomeTable
>>
>> Another thing you might consider, though I personally don't like it
is
> this:
>>
>> SET QUOTED_IDENTIFIER ON
>> SELECT "Desc" FROM t_SomeTable
>> SET QUOTED_IDENTIFIER OFF
>>
>> Generally speaking, the SQL Server Query Analyzer is your best
>> friend for figuring out correct SQL syntax irrespective of
>> application code. Once you get the SQL syntax to do the right
>> thing in Query Analyzer, then

try it
in
>> your application code. If it then doesn't work, there is
>> something else going wrong, such as runtime permissions or other
>> screwy application stuff. --
>> Peace & happy computing,
>>
>> Mike Labosh, MCSD MCT
>> Owner, vbSensei.Com
>> "Escriba coda ergo sum." -- vbSensei


Regards - OHM# On**********@BTInternet.com
Nov 20 '05 #9

P: n/a
Cor
Hi OHM are you sure the OP is using the commandbuilder, this looks like drag
and drop, but I can be wrong.

He says "I dont write any SELECT statement"

Not "I dont write any UPDATE statement"

But maybe I am wrong, and maybe he answer us about that after this message.

Cor

"One Handed Man [ OHM# ]" <te***************************@BTOpenworld.com>
schreef in bericht news:eq**************@TK2MSFTNGP09.phx.gbl...
Sorry, but I have to step in here. I've been watching this thread with
dubious interest.

You seem to have this idea that command builder is king, well let me tell
you that it is not.
Command builder can only handle simple queries, sooner or later you are
going
to have to roll up your sleeves and get your hands dirty.

Bill' suggestion is a good one, simply modify the code already produced by
the
command builer to suit your needs.

OHM#

Benoit Martin wrote:
oh, that's what you were talking about, sorry...

unfortunately I don't think it's gonna work for me as the code is
generated by VB and I don't write any SELECT statement for my update
command.

I guess there is no work around then

"William Ryan" <do********@nospam.comcast.net> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
SELECT SomeColumn as AliasName from MyTable
"Benoit Martin" <bm*********@hotmail.com> wrote in message
news:Ob**************@tk2msftngp13.phx.gbl...
thanks William,

not being able to change the field name is not too much a problem
of being stuborn but more a problem with the consequences of
changing this field name.

When you are talking about Alia, is it in the dataset? I wasn't
aware of this alias "property". Where is that set?

Thank you

"William Ryan" <do********@nospam.comcast.net> wrote in message
news:%2***************@tk2msftngp13.phx.gbl...
> Benoit:
>
> If there's a battle worth fighting, it's Changing column names
> that are reserved words. Have you tried changing the Alias on it
> w/ the []? I've been told by many that it should work unless you
> are using access. If you can't do any of that, why not create a
> View based on the table and give it real names. Whoever insists
> on using Desc and not letting you change it is being Very
> shortsighted, but he/she couldn't possibly prohibit you from
> creating a view and using it instead. "Benoit Martin"
> <bm*********@hotmail.com> wrote in message
> news:%2****************@TK2MSFTNGP10.phx.gbl...
>> when I said that I can't modify the DB, I meant the structure,
>> not the data.
>>
>> I was aware of the [] work around but it doesn't look like VB.net
>> does it. The update code is generated by VB.net as I used the
>> command builder (don't waste your time telling me that I should
>> write my own instead of using the command builder)
>> It looks like if you use a reserved word, VB.net will not take any
>> precautions to avoid an error when dealing with the database.
>>
>> Is there a way to correct that problem?
>>
>> "Mike Labosh" <ml*****************@vbsensei.com> wrote in message
>> news:ur**************@TK2MSFTNGP11.phx.gbl...
>>>> I'm working on a project in VB.net connecting to a SQL Server
>>>> 2000 database that I can't modify
>>>> I created a dataset with a schema identical to the DB.
>>>> When trying to update the DB from the dataset using the
>>>> dataAdapter.update sub, I get an error.
>>>
>>> If you cannot modify the database, why bother trying to update
>>> it? This would be my first red flag.
>>>
>>>> I've been able to work around this design flaw 'till now but it
>>>> looks like the update sub can't handle it.
>>>
>>> Every once in a while, someone makes a column or other object in
>>> a database whose name is a reserved word. SQL Server will let
>>> you refer to
this
if
>> you
>>> enclose the object's identifier in square brackets:
>>>
>>> SELECT [Desc] FROM t_SomeTable
>>>
>>> Sometimes, SQL Server will understand what you mean if you
>>> qualify the item you are referring to:
>>>
>>> SELECT t_SomeTable.Desc FROM t_SomeTable
>>>
>>> Another thing you might consider, though I personally don't like

it
is
>> this:
>>>
>>> SET QUOTED_IDENTIFIER ON
>>> SELECT "Desc" FROM t_SomeTable
>>> SET QUOTED_IDENTIFIER OFF
>>>
>>> Generally speaking, the SQL Server Query Analyzer is your best
>>> friend for figuring out correct SQL syntax irrespective of
>>> application code. Once you get the SQL syntax to do the right
>>> thing in Query Analyzer, then

try
it
> in
>>> your application code. If it then doesn't work, there is
>>> something else going wrong, such as runtime permissions or other
>>> screwy application stuff. --
>>> Peace & happy computing,
>>>
>>> Mike Labosh, MCSD MCT
>>> Owner, vbSensei.Com
>>> "Escriba coda ergo sum." -- vbSensei


Regards - OHM# On**********@BTInternet.com

Nov 20 '05 #10

P: n/a
OP wrote
\\
I was aware of the [] work around but it doesn't look like VB.net does it.
The update code is generated by VB.net as I used the command builder (don't
waste your time telling me that I should write my own instead of using the
command builder)
//
Cor wrote:
Hi OHM are you sure the OP is using the commandbuilder, this looks
like drag and drop, but I can be wrong.

He says "I dont write any SELECT statement"

Not "I dont write any UPDATE statement"

But maybe I am wrong, and maybe he answer us about that after this
message.

Cor

"One Handed Man [ OHM# ]"
<te***************************@BTOpenworld.com> schreef in bericht
news:eq**************@TK2MSFTNGP09.phx.gbl...
Sorry, but I have to step in here. I've been watching this thread
with dubious interest.

You seem to have this idea that command builder is king, well let me
tell you that it is not.
Command builder can only handle simple queries, sooner or later you
are going
to have to roll up your sleeves and get your hands dirty.

Bill' suggestion is a good one, simply modify the code already
produced by the
command builer to suit your needs.

OHM#

Benoit Martin wrote:
oh, that's what you were talking about, sorry...

unfortunately I don't think it's gonna work for me as the code is
generated by VB and I don't write any SELECT statement for my update
command.

I guess there is no work around then

"William Ryan" <do********@nospam.comcast.net> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
SELECT SomeColumn as AliasName from MyTable
"Benoit Martin" <bm*********@hotmail.com> wrote in message
news:Ob**************@tk2msftngp13.phx.gbl...
> thanks William,
>
> not being able to change the field name is not too much a problem
> of being stuborn but more a problem with the consequences of
> changing this field name.
>
> When you are talking about Alia, is it in the dataset? I wasn't
> aware of this alias "property". Where is that set?
>
> Thank you
>
> "William Ryan" <do********@nospam.comcast.net> wrote in message
> news:%2***************@tk2msftngp13.phx.gbl...
>> Benoit:
>>
>> If there's a battle worth fighting, it's Changing column names
>> that are reserved words. Have you tried changing the Alias on it
>> w/ the []? I've been told by many that it should work unless you
>> are using access. If you can't do any of that, why not create a
>> View based on the table and give it real names. Whoever insists
>> on using Desc and not letting you change it is being Very
>> shortsighted, but he/she couldn't possibly prohibit you from
>> creating a view and using it instead. "Benoit Martin"
>> <bm*********@hotmail.com> wrote in message
>> news:%2****************@TK2MSFTNGP10.phx.gbl...
>>> when I said that I can't modify the DB, I meant the structure,
>>> not the data.
>>>
>>> I was aware of the [] work around but it doesn't look like
>>> VB.net does it. The update code is generated by VB.net as I
>>> used the command builder (don't waste your time telling me that
>>> I should write my own instead of using the command builder)
>>> It looks like if you use a reserved word, VB.net will not take
>>> any precautions to avoid an error when dealing with the
>>> database.
>>>
>>> Is there a way to correct that problem?
>>>
>>> "Mike Labosh" <ml*****************@vbsensei.com> wrote in
>>> message news:ur**************@TK2MSFTNGP11.phx.gbl...
>>>>> I'm working on a project in VB.net connecting to a SQL Server
>>>>> 2000 database that I can't modify
>>>>> I created a dataset with a schema identical to the DB.
>>>>> When trying to update the DB from the dataset using the
>>>>> dataAdapter.update sub, I get an error.
>>>>
>>>> If you cannot modify the database, why bother trying to update
>>>> it? This would be my first red flag.
>>>>
>>>>> I've been able to work around this design flaw 'till now but
>>>>> it looks like the update sub can't handle it.
>>>>
>>>> Every once in a while, someone makes a column or other object
>>>> in a database whose name is a reserved word. SQL Server will
>>>> let you refer to
this
> if
>>> you
>>>> enclose the object's identifier in square brackets:
>>>>
>>>> SELECT [Desc] FROM t_SomeTable
>>>>
>>>> Sometimes, SQL Server will understand what you mean if you
>>>> qualify the item you are referring to:
>>>>
>>>> SELECT t_SomeTable.Desc FROM t_SomeTable
>>>>
>>>> Another thing you might consider, though I personally don't
>>>> like
it
is
>>> this:
>>>>
>>>> SET QUOTED_IDENTIFIER ON
>>>> SELECT "Desc" FROM t_SomeTable
>>>> SET QUOTED_IDENTIFIER OFF
>>>>
>>>> Generally speaking, the SQL Server Query Analyzer is your best
>>>> friend for figuring out correct SQL syntax irrespective of
>>>> application code. Once you get the SQL syntax to do the right
>>>> thing in Query Analyzer, then
try
> it
>> in
>>>> your application code. If it then doesn't work, there is
>>>> something else going wrong, such as runtime permissions or
>>>> other screwy application stuff. --
>>>> Peace & happy computing,
>>>>
>>>> Mike Labosh, MCSD MCT
>>>> Owner, vbSensei.Com
>>>> "Escriba coda ergo sum." -- vbSensei


Regards - OHM# On**********@BTInternet.com


Regards - OHM# On**********@BTInternet.com
Nov 20 '05 #11

P: n/a
I don't think that the command builder is king. It's just that my experience
of news groups and forums taught me to make that kind of things clear.
Anytime I ask help about a wizard, or builder or anything generating code, I
don't get an answer about a fix but people telling me to write it myself. If
I use the command builder it's because my queries are supposed to be simple
and can be handled by the commandBuilder. Why write myself something that a
simple line of code can write for me and do it good enough for my needs.

This said, being no way to get that commandBuilder to work around this
problem, I decided to write the commands myself. In this case, it happens to
save me time

Thank you all for your input

"One Handed Man [ OHM# ]" <te***************************@BTOpenworld.com>
wrote in message news:eq**************@TK2MSFTNGP09.phx.gbl...
Sorry, but I have to step in here. I've been watching this thread with
dubious interest.

You seem to have this idea that command builder is king, well let me tell
you that it is not.
Command builder can only handle simple queries, sooner or later you are
going
to have to roll up your sleeves and get your hands dirty.

Bill' suggestion is a good one, simply modify the code already produced by
the
command builer to suit your needs.

OHM#

Benoit Martin wrote:
oh, that's what you were talking about, sorry...

unfortunately I don't think it's gonna work for me as the code is
generated by VB and I don't write any SELECT statement for my update
command.

I guess there is no work around then

"William Ryan" <do********@nospam.comcast.net> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
SELECT SomeColumn as AliasName from MyTable
"Benoit Martin" <bm*********@hotmail.com> wrote in message
news:Ob**************@tk2msftngp13.phx.gbl...
thanks William,

not being able to change the field name is not too much a problem
of being stuborn but more a problem with the consequences of
changing this field name.

When you are talking about Alia, is it in the dataset? I wasn't
aware of this alias "property". Where is that set?

Thank you

"William Ryan" <do********@nospam.comcast.net> wrote in message
news:%2***************@tk2msftngp13.phx.gbl...
> Benoit:
>
> If there's a battle worth fighting, it's Changing column names
> that are reserved words. Have you tried changing the Alias on it
> w/ the []? I've been told by many that it should work unless you
> are using access. If you can't do any of that, why not create a
> View based on the table and give it real names. Whoever insists
> on using Desc and not letting you change it is being Very
> shortsighted, but he/she couldn't possibly prohibit you from
> creating a view and using it instead. "Benoit Martin"
> <bm*********@hotmail.com> wrote in message
> news:%2****************@TK2MSFTNGP10.phx.gbl...
>> when I said that I can't modify the DB, I meant the structure,
>> not the data.
>>
>> I was aware of the [] work around but it doesn't look like VB.net
>> does it. The update code is generated by VB.net as I used the
>> command builder (don't waste your time telling me that I should
>> write my own instead of using the command builder)
>> It looks like if you use a reserved word, VB.net will not take any
>> precautions to avoid an error when dealing with the database.
>>
>> Is there a way to correct that problem?
>>
>> "Mike Labosh" <ml*****************@vbsensei.com> wrote in message
>> news:ur**************@TK2MSFTNGP11.phx.gbl...
>>>> I'm working on a project in VB.net connecting to a SQL Server
>>>> 2000 database that I can't modify
>>>> I created a dataset with a schema identical to the DB.
>>>> When trying to update the DB from the dataset using the
>>>> dataAdapter.update sub, I get an error.
>>>
>>> If you cannot modify the database, why bother trying to update
>>> it? This would be my first red flag.
>>>
>>>> I've been able to work around this design flaw 'till now but it
>>>> looks like the update sub can't handle it.
>>>
>>> Every once in a while, someone makes a column or other object in
>>> a database whose name is a reserved word. SQL Server will let
>>> you refer to
this
if
>> you
>>> enclose the object's identifier in square brackets:
>>>
>>> SELECT [Desc] FROM t_SomeTable
>>>
>>> Sometimes, SQL Server will understand what you mean if you
>>> qualify the item you are referring to:
>>>
>>> SELECT t_SomeTable.Desc FROM t_SomeTable
>>>
>>> Another thing you might consider, though I personally don't like

it
is
>> this:
>>>
>>> SET QUOTED_IDENTIFIER ON
>>> SELECT "Desc" FROM t_SomeTable
>>> SET QUOTED_IDENTIFIER OFF
>>>
>>> Generally speaking, the SQL Server Query Analyzer is your best
>>> friend for figuring out correct SQL syntax irrespective of
>>> application code. Once you get the SQL syntax to do the right
>>> thing in Query Analyzer, then

try
it
> in
>>> your application code. If it then doesn't work, there is
>>> something else going wrong, such as runtime permissions or other
>>> screwy application stuff. --
>>> Peace & happy computing,
>>>
>>> Mike Labosh, MCSD MCT
>>> Owner, vbSensei.Com
>>> "Escriba coda ergo sum." -- vbSensei


Regards - OHM# On**********@BTInternet.com

Nov 20 '05 #12

P: n/a
Cor
Hi Benoit,

I use the commandbuilder frequently and till now I am very happy with it
because I hate everything that has to do with SQL.

Now looking furter in your problem I think that I see what is the problem.

The commandbuilder uses the Select statement, that you did use when you did
filled the dataset.

The sequence is:

connection using the connection string
create the command using the select and connection
create the adapter using the command
dataAdapter.Fill the dataset
let the commandbuilde do it work
dataAdapter.update the dataset.

So when Bill is talking about the select you has to change, than is that
that select statement that you did use with the command

Maybe we are something furter now.

Cor
Nov 20 '05 #13

P: n/a
I changed the select to include the brackets around the desc field name but
when the command builder creates the Insert command, it removes the
brackets... maybe this is a bug in VS2002 that was corrected in VS2003 ???

"Cor" <no*@non.com> wrote in message
news:eS**************@TK2MSFTNGP12.phx.gbl...
Hi Benoit,

I use the commandbuilder frequently and till now I am very happy with it
because I hate everything that has to do with SQL.

Now looking furter in your problem I think that I see what is the problem.

The commandbuilder uses the Select statement, that you did use when you did filled the dataset.

The sequence is:

connection using the connection string
create the command using the select and connection
create the adapter using the command
dataAdapter.Fill the dataset
let the commandbuilde do it work
dataAdapter.update the dataset.

So when Bill is talking about the select you has to change, than is that
that select statement that you did use with the command

Maybe we are something furter now.

Cor

Nov 20 '05 #14

P: n/a
Cor
Hi Benoit,

Although probably I am not the best one to help you with SQL strings, send
your SELECT string maybe OHM can help you or maybe even I.

Cor
Nov 20 '05 #15

P: n/a
Benoit,

Command builder has these nuances unfortunately. Provided that your select
statements don't contain joins then its OK to use it. If putting the
brackets around the desc word fixes the problem then I suggest you do a
search an replace after each time you run command builder.

This is why I never use command builder and do it all manually. It's a pain
in the neck, but at least 'You' stay in control of your code and understand
each and every line of it.

Regards - OHM

Benoit Martin wrote:
I changed the select to include the brackets around the desc field
name but when the command builder creates the Insert command, it
removes the brackets... maybe this is a bug in VS2002 that was
corrected in VS2003 ???

"Cor" <no*@non.com> wrote in message
news:eS**************@TK2MSFTNGP12.phx.gbl...
Hi Benoit,

I use the commandbuilder frequently and till now I am very happy
with it because I hate everything that has to do with SQL.

Now looking furter in your problem I think that I see what is the
problem.

The commandbuilder uses the Select statement, that you did use when
you did filled the dataset.

The sequence is:

connection using the connection string
create the command using the select and connection
create the adapter using the command
dataAdapter.Fill the dataset
let the commandbuilde do it work
dataAdapter.update the dataset.

So when Bill is talking about the select you has to change, than is
that that select statement that you did use with the command

Maybe we are something furter now.

Cor


Regards - OHM# On**********@BTInternet.com
Nov 20 '05 #16

P: n/a
Benoit,
You can set QuotePrefix and QuoteSuffix on the CommandBuilder to get it
to quote all of your fields for you which should work around the problem of
reserved words.

Ron Allen
"Benoit Martin" <bm*********@hotmail.com> wrote in message
news:en**************@TK2MSFTNGP09.phx.gbl...
I changed the select to include the brackets around the desc field name but when the command builder creates the Insert command, it removes the
brackets... maybe this is a bug in VS2002 that was corrected in VS2003 ???

"Cor" <no*@non.com> wrote in message
news:eS**************@TK2MSFTNGP12.phx.gbl...
Hi Benoit,

I use the commandbuilder frequently and till now I am very happy with it
because I hate everything that has to do with SQL.

Now looking furter in your problem I think that I see what is the problem.
The commandbuilder uses the Select statement, that you did use when you

did
filled the dataset.

The sequence is:

connection using the connection string
create the command using the select and connection
create the adapter using the command
dataAdapter.Fill the dataset
let the commandbuilde do it work
dataAdapter.update the dataset.

So when Bill is talking about the select you has to change, than is that
that select statement that you did use with the command

Maybe we are something furter now.

Cor


Nov 20 '05 #17

P: n/a
here is the answer I was looking for !!!

Thanks Ron!

unfortunately I won't test it this time as I couldn't wait anymore for a
work around and decided to code it myself.
Hope this thread will help other people

Thank you all for your help

"Ron Allen" <rallen@_nospam_src-us.com> wrote in message
news:uF**************@tk2msftngp13.phx.gbl...
Benoit,
You can set QuotePrefix and QuoteSuffix on the CommandBuilder to get it to quote all of your fields for you which should work around the problem of reserved words.

Ron Allen
"Benoit Martin" <bm*********@hotmail.com> wrote in message
news:en**************@TK2MSFTNGP09.phx.gbl...
I changed the select to include the brackets around the desc field name

but
when the command builder creates the Insert command, it removes the
brackets... maybe this is a bug in VS2002 that was corrected in VS2003 ???

"Cor" <no*@non.com> wrote in message
news:eS**************@TK2MSFTNGP12.phx.gbl...
Hi Benoit,

I use the commandbuilder frequently and till now I am very happy with it because I hate everything that has to do with SQL.

Now looking furter in your problem I think that I see what is the problem.
The commandbuilder uses the Select statement, that you did use when you did
filled the dataset.

The sequence is:

connection using the connection string
create the command using the select and connection
create the adapter using the command
dataAdapter.Fill the dataset
let the commandbuilde do it work
dataAdapter.update the dataset.

So when Bill is talking about the select you has to change, than is

that that select statement that you did use with the command

Maybe we are something furter now.

Cor



Nov 20 '05 #18

This discussion thread is closed

Replies have been disabled for this discussion.