reserved word for DB column name crashes data adapter update

[Benoit Martin]

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?

 

[Mike Labosh]

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

 

[Benoit Martin]

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?

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

 

[William Ryan]

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.

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?

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

 

[Benoit Martin]

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

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.

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?

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

 

[William Ryan]

SELECT SomeColumn as AliasName from MyTable

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

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.

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?

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

 

[Benoit Martin]

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

SELECT SomeColumn as AliasName from MyTable

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

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

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 try
it something
else

 

[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

 

[One Handed Man [ OHM# ]]

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#

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

SELECT SomeColumn as AliasName from MyTable

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

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"
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?

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# (e-mail address removed)

 

[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

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#

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

SELECT SomeColumn as AliasName from MyTable
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

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"
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?

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# (e-mail address removed)

 

[One Handed Man [ OHM# ]]

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

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# ]"

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#

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

SELECT SomeColumn as AliasName from MyTable
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

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"
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?

message 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# (e-mail address removed)

Regards - OHM# (e-mail address removed)

 

[Benoit Martin]

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

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#

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

SELECT SomeColumn as AliasName from MyTable
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

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"
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?

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# (e-mail address removed)

 

[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

 

[Benoit Martin]

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]

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

 

[One Handed Man [ OHM# ]]

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

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 ???

Regards - OHM# (e-mail address removed)

 

[Ron Allen]

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]

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