How can this be? CommandBuilder w/ OleDb

[Guest]

try
{
OleDbCommandBuilder bldr = new OleDbCommandBuilder(da);
da.Update(tbl);
}
catch (OleDbException ex)
{
MessageBox.Show(ex.Message);
}

The above code causes the error "Syntax error in UPDATE statement." How can
command builder be building an incorrect UPDATE command?

The SELECT command in the data adapter is:
Select * from [Disc Gear Contents] where Unit=3 order by UnitIndex
and seems to work fine.

The database is an Access db.

 

[William \(Bill\) Vaughn]

Because the CommandBuilder is kinda challenged?
Check what the CB is building for your UpdateCommand. See the CB
GetUpdateMethod.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Guest]

Hmmm... I would rate the result "gobbledegook":
"UPDATE Disc Gear Contents SET Title = ? , CD Info = ? , Comments = ? , Unit
= ? , UnitIndex = ? WHERE ( (CDId = ?) AND ((? = 1 AND Title IS NULL) OR
(Title = ?)) AND ((? = 1 AND CD Info IS NULL) OR (CD Info = ?)) AND ((? = 1
AND Comments IS NULL) OR (Comments = ?)) AND ((? = 1 AND Unit IS NULL) OR
(Unit = ?)) AND ((? = 1 AND UnitIndex IS NULL) OR (UnitIndex = ?)) )"

But it DOES bring up an interesting question: Are parameters in OleDb to and
Access database order based? It appears so from the above. I had thought I
could use named parameters, such as @UnitIndex.

 

[Guest]

GaryFe,

I think that the problem with the Update statement is that the table name
contains spaces but is not surrounded with brackets, [Disc Gear Contents].

Kerry Moorman

 

[William \(Bill\) Vaughn]

You can either get rid of the imbedded spaces in object names (best idea) or
use the QuotePrefix and QuoteSuffix properties to indicate how these long
object names should be framed.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

GaryFe,

I think that the problem with the Update statement is that the table name
contains spaces but is not surrounded with brackets, [Disc Gear Contents].

Kerry Moorman

Hmmm... I would rate the result "gobbledegook":
"UPDATE Disc Gear Contents SET Title = ? , CD Info = ? , Comments = ? ,
Unit
= ? , UnitIndex = ? WHERE ( (CDId = ?) AND ((? = 1 AND Title IS NULL) OR
(Title = ?)) AND ((? = 1 AND CD Info IS NULL) OR (CD Info = ?)) AND ((? =
1
AND Comments IS NULL) OR (Comments = ?)) AND ((? = 1 AND Unit IS NULL) OR
(Unit = ?)) AND ((? = 1 AND UnitIndex IS NULL) OR (UnitIndex = ?)) )"

But it DOES bring up an interesting question: Are parameters in OleDb to
and
Access database order based? It appears so from the above. I had thought
I
could use named parameters, such as @UnitIndex.

 

[Guest]

Ha! QuotePrefix/Suffix did the trick!
Thanks Bill!
Gary

You can either get rid of the imbedded spaces in object names (best idea) or
use the QuotePrefix and QuoteSuffix properties to indicate how these long
object names should be framed.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

GaryFe,

I think that the problem with the Update statement is that the table name
contains spaces but is not surrounded with brackets, [Disc Gear Contents].

Kerry Moorman

Hmmm... I would rate the result "gobbledegook":
"UPDATE Disc Gear Contents SET Title = ? , CD Info = ? , Comments = ? ,
Unit
= ? , UnitIndex = ? WHERE ( (CDId = ?) AND ((? = 1 AND Title IS NULL) OR
(Title = ?)) AND ((? = 1 AND CD Info IS NULL) OR (CD Info = ?)) AND ((? =
1
AND Comments IS NULL) OR (Comments = ?)) AND ((? = 1 AND Unit IS NULL) OR
(Unit = ?)) AND ((? = 1 AND UnitIndex IS NULL) OR (UnitIndex = ?)) )"

But it DOES bring up an interesting question: Are parameters in OleDb to
and
Access database order based? It appears so from the above. I had thought
I
could use named parameters, such as @UnitIndex.