473,383 Members | 1,859 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,383 software developers and data experts.

reserved word for DB column name crashes data adapter update

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
17 3778
> 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Ms.net | last post by:
I am writing application using Access Database.... Everything work on my PC. update, add,or delete. Since the application is multiuser, i put the database on network..... Application start...
0
by: Webgour | last post by:
So basically, I have an autoincremented primary key, so basically I set the selectcommand, and then used SqlCommandBuilder to build the rest. The problem is that when I do dataadapter's update to...
1
by: !!bogus | last post by:
Hi, This is partial code. I am trying to update the table, but it is not working and I am not sure why. Can you please help? I don't get any errors, but the table is not updated. I am sure I am...
8
by: rriness | last post by:
I'm getting an inconsistent failure when trying to save data in ADO.Net. I'm using an Access database with a simple query - SELECT StudentID, FirstName, LastName FROM Students - and have no...
2
by: Parveen | last post by:
I have a data grid that's bound to a table in my dataset. After inserting a new row into the grid and populating it with data, I go to save my changes. My data adapter update command returns an...
1
by: Adam | last post by:
This is kind of a tough question, but, I'm working on a program now and using sql data adapters with sql server to generate data tables. I'm updating the tables and then using the...
6
by: pooba53 | last post by:
I have a VB .NET application that is communicating properly with an Access DB. I have a slew of textbox controls bound to a dataset and when the application launches, the fields are correctly...
3
by: Mustaf Kazi | last post by:
hi, I tried to update record using data table and dataadapter but I get error for line (in edit case) daadapter1.update(dtTable1) the error is "Dynamic SQL generation for the...
0
by: mwenz | last post by:
I am trying to update an Access table using OLEDB in VB.Net 2005. I can add rows but I cannot update them. Code to instantiate the Access database and table... Dim conn As New...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.