When I try to update an access database using the commandbuilder and
updatecommand. I get the

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred
in system.data.dll

error message.

The strange thing is that only half of the table's give this error and the
other half of the tables seem to work just fine using the same code (except
other sql string always SELECT * FROM TABLE)

ANy Idea what goes wrong her or what I can do to eliminate this problem.
(Except for remaking all tables)



Here is my code

Dim STARTYPEtable As DataTable

Dim constring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Documents and Settings\Administrator\My
Documents\QUEST.mdb;Persist Security Info=False"

Dim condb As New OleDb.OleDbConnection(constring)

Dim DASTARTYPE As New OleDb.OleDbDataAdapter("SELECT * FROM STAR_TYPE",
condb)

Dim DBSTARTYPE As New OleDb.OleDbCommandBuilder(DASTARTYPE)



Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

DASTARTYPE.InsertCommand = DBSTARTYPE.GetInsertCommand

DASTARTYPE.UpdateCommand = DBSTARTYPE.GetUpdateCommand

DASTARTYPE.DeleteCommand = DBSTARTYPE.GetDeleteCommand

STARTYPEtable = New DataTable

DASTARTYPE.Fill(STARTYPEtable)

DataGrid1.DataSource = STARTYPEtable

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

DASTARTYPE.Update(STARTYPEtable)



End Sub

Hi,

Put the update code in a try catch block to figure out what the
error is.


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Try
DASTARTYPE.Update(STARTYPEtable)
Catch ex as exception
debug.Writeline(ex.tostring)
end try

End Sub

Ken
----------------------
"whaletyr" <(E-Mail Removed)> wrote in message
news:41226887$0$18094$(E-Mail Removed)...
When I try to update an access database using the commandbuilder and
updatecommand. I get the

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred
in system.data.dll

error message.

The strange thing is that only half of the table's give this error and the
other half of the tables seem to work just fine using the same code (except
other sql string always SELECT * FROM TABLE)

ANy Idea what goes wrong her or what I can do to eliminate this problem.
(Except for remaking all tables)



Here is my code

Dim STARTYPEtable As DataTable

Dim constring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Documents and Settings\Administrator\My
Documents\QUEST.mdb;Persist Security Info=False"

Dim condb As New OleDb.OleDbConnection(constring)

Dim DASTARTYPE As New OleDb.OleDbDataAdapter("SELECT * FROM STAR_TYPE",
condb)

Dim DBSTARTYPE As New OleDb.OleDbCommandBuilder(DASTARTYPE)



Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

DASTARTYPE.InsertCommand = DBSTARTYPE.GetInsertCommand

DASTARTYPE.UpdateCommand = DBSTARTYPE.GetUpdateCommand

DASTARTYPE.DeleteCommand = DBSTARTYPE.GetDeleteCommand

STARTYPEtable = New DataTable

DASTARTYPE.Fill(STARTYPEtable)

DataGrid1.DataSource = STARTYPEtable

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

DASTARTYPE.Update(STARTYPEtable)



End Sub

I get the following error then.
Syntax error in UPDATE statement.

at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)

at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)

I also tried it on another Table. but then everything works Fine.. I can
figure out what is wrong with the first table. (Seems only half of the
tables give the error)





"Ken Tucker [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> Put the update code in a try catch block to figure out what the
> error is.
>
>
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
>
> Try
> DASTARTYPE.Update(STARTYPEtable)
> Catch ex as exception
> debug.Writeline(ex.tostring)
> end try
>
> End Sub
>
> Ken
> ----------------------
> "whaletyr" <(E-Mail Removed)> wrote in message
> news:41226887$0$18094$(E-Mail Removed)...
> When I try to update an access database using the commandbuilder and
> updatecommand. I get the
>
> An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred
> in system.data.dll
>
> error message.
>
> The strange thing is that only half of the table's give this error and the
> other half of the tables seem to work just fine using the same code
(except
> other sql string always SELECT * FROM TABLE)
>
> ANy Idea what goes wrong her or what I can do to eliminate this problem.
> (Except for remaking all tables)
>
>
>
> Here is my code
>
> Dim STARTYPEtable As DataTable
>
> Dim constring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=C:\Documents and Settings\Administrator\My
> Documents\QUEST.mdb;Persist Security Info=False"
>
> Dim condb As New OleDb.OleDbConnection(constring)
>
> Dim DASTARTYPE As New OleDb.OleDbDataAdapter("SELECT * FROM STAR_TYPE",
> condb)
>
> Dim DBSTARTYPE As New OleDb.OleDbCommandBuilder(DASTARTYPE)
>
>
>
> Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles MyBase.Load
>
> DASTARTYPE.InsertCommand = DBSTARTYPE.GetInsertCommand
>
> DASTARTYPE.UpdateCommand = DBSTARTYPE.GetUpdateCommand
>
> DASTARTYPE.DeleteCommand = DBSTARTYPE.GetDeleteCommand
>
> STARTYPEtable = New DataTable
>
> DASTARTYPE.Fill(STARTYPEtable)
>
> DataGrid1.DataSource = STARTYPEtable
>
> End Sub
>
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
>
> DASTARTYPE.Update(STARTYPEtable)
>
>
>
> End Sub
>
>
>

Hi,

Maybe one of the field names in the table is a keyword.

Ken
--------------
"whaletyr" <(E-Mail Removed)> wrote in message
news:4122f956$0$31047$(E-Mail Removed)...
I get the following error then.
Syntax error in UPDATE statement.

at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)

at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)

I also tried it on another Table. but then everything works Fine.. I can
figure out what is wrong with the first table. (Seems only half of the
tables give the error)





"Ken Tucker [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> Put the update code in a try catch block to figure out what the
> error is.
>
>
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
>
> Try
> DASTARTYPE.Update(STARTYPEtable)
> Catch ex as exception
> debug.Writeline(ex.tostring)
> end try
>
> End Sub
>
> Ken
> ----------------------
> "whaletyr" <(E-Mail Removed)> wrote in message
> news:41226887$0$18094$(E-Mail Removed)...
> When I try to update an access database using the commandbuilder and
> updatecommand. I get the
>
> An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred
> in system.data.dll
>
> error message.
>
> The strange thing is that only half of the table's give this error and the
> other half of the tables seem to work just fine using the same code
(except
> other sql string always SELECT * FROM TABLE)
>
> ANy Idea what goes wrong her or what I can do to eliminate this problem.
> (Except for remaking all tables)
>
>
>
> Here is my code
>
> Dim STARTYPEtable As DataTable
>
> Dim constring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=C:\Documents and Settings\Administrator\My
> Documents\QUEST.mdb;Persist Security Info=False"
>
> Dim condb As New OleDb.OleDbConnection(constring)
>
> Dim DASTARTYPE As New OleDb.OleDbDataAdapter("SELECT * FROM STAR_TYPE",
> condb)
>
> Dim DBSTARTYPE As New OleDb.OleDbCommandBuilder(DASTARTYPE)
>
>
>
> Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles MyBase.Load
>
> DASTARTYPE.InsertCommand = DBSTARTYPE.GetInsertCommand
>
> DASTARTYPE.UpdateCommand = DBSTARTYPE.GetUpdateCommand
>
> DASTARTYPE.DeleteCommand = DBSTARTYPE.GetDeleteCommand
>
> STARTYPEtable = New DataTable
>
> DASTARTYPE.Fill(STARTYPEtable)
>
> DataGrid1.DataSource = STARTYPEtable
>
> End Sub
>
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
>
> DASTARTYPE.Update(STARTYPEtable)
>
>
>
> End Sub
>
>
>

Al the field names are non keyword (eg STAR_COLOR, STAR_SIZE, ect...)
I remade the table with exactly the same parameters except naming it
STAR_TYPE2, and that seems to work fine...
about half of my tables work fine the other tables seem to give the error...

I hope to solve this without remaking al the Tables

Thanks




"Ken Tucker [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> Maybe one of the field names in the table is a keyword.
>
> Ken
> --------------
> "whaletyr" <(E-Mail Removed)> wrote in message
> news:4122f956$0$31047$(E-Mail Removed)...
> I get the following error then.
> Syntax error in UPDATE statement.
>
> at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
> DataTableMapping tableMapping)
>
> at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
>
> I also tried it on another Table. but then everything works Fine.. I can
> figure out what is wrong with the first table. (Seems only half of the
> tables give the error)
>
>
>
>
>
> "Ken Tucker [MVP]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi,
> >
> > Put the update code in a try catch block to figure out what the
> > error is.
> >
> >
> > Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> > System.EventArgs) Handles Button1.Click
> >
> > Try
> > DASTARTYPE.Update(STARTYPEtable)
> > Catch ex as exception
> > debug.Writeline(ex.tostring)
> > end try
> >
> > End Sub
> >
> > Ken
> > ----------------------
> > "whaletyr" <(E-Mail Removed)> wrote in message
> > news:41226887$0$18094$(E-Mail Removed)...
> > When I try to update an access database using the commandbuilder and
> > updatecommand. I get the
> >
> > An unhandled exception of type 'System.Data.OleDb.OleDbException'
occurred
> > in system.data.dll
> >
> > error message.
> >
> > The strange thing is that only half of the table's give this error and
the
> > other half of the tables seem to work just fine using the same code
> (except
> > other sql string always SELECT * FROM TABLE)
> >
> > ANy Idea what goes wrong her or what I can do to eliminate this
problem.
> > (Except for remaking all tables)
> >
> >
> >
> > Here is my code
> >
> > Dim STARTYPEtable As DataTable
> >
> > Dim constring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> > Source=C:\Documents and Settings\Administrator\My
> > Documents\QUEST.mdb;Persist Security Info=False"
> >
> > Dim condb As New OleDb.OleDbConnection(constring)
> >
> > Dim DASTARTYPE As New OleDb.OleDbDataAdapter("SELECT * FROM STAR_TYPE",
> > condb)
> >
> > Dim DBSTARTYPE As New OleDb.OleDbCommandBuilder(DASTARTYPE)
> >
> >
> >
> > Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
> > System.EventArgs) Handles MyBase.Load
> >
> > DASTARTYPE.InsertCommand = DBSTARTYPE.GetInsertCommand
> >
> > DASTARTYPE.UpdateCommand = DBSTARTYPE.GetUpdateCommand
> >
> > DASTARTYPE.DeleteCommand = DBSTARTYPE.GetDeleteCommand
> >
> > STARTYPEtable = New DataTable
> >
> > DASTARTYPE.Fill(STARTYPEtable)
> >
> > DataGrid1.DataSource = STARTYPEtable
> >
> > End Sub
> >
> > Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> > System.EventArgs) Handles Button1.Click
> >
> > DASTARTYPE.Update(STARTYPEtable)
> >
> >
> >
> > End Sub
> >
> >
> >
>
>
>

Whaletyr,

I was direct thinking the same as Ken, however now you reply the
stringbuilder can only handle I thought 100 columns

Can it be that?

Cor

I Use a primairy key, I use about 40 colomns' I have no relations on the
table. I use simple SELECT * FROM TABLE.

I made an exact copy of the table with a different name and that works
fine...

I have abvout 30 tables. and about 20 give this error....

I just would like to know what goes wrong and what to do about it, if
nothing works i will have to remake all my tables...


(The generated Update SQL string seems only to be for 1 colomn and not for
all columns)

WWhaletyr


"Cor Ligthert" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Whaletyr,
>
> I was direct thinking the same as Ken, however now you reply the
> stringbuilder can only handle I thought 100 columns
>
> Can it be that?
>
> Cor
>
>

Whaletyr,

I never do it the way as you. Can you try this, it would be strange I admit
when 10 tables work. However now you have nothing,

Cor

\\\
Dim STARTYPEtable As new DataSet
Dim constring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Documents and Settings\Administrator\My
Documents\QUEST.mdb;Persist Security Info=False"
Dim condb As New OleDb.OleDbConnection(constring)
Dim DASTARTYPE As New OleDb.OleDbDataAdapter("SELECT * FROM STAR_TYPE",
condb)

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
DASTARTYPE.Fill(STARTYPEtable)
Dim DBSTARTYPE As New OleDb.OleDbCommandBuilder(DASTARTYPE)
DataGrid1.DataSource = STARTYPEtable.tables(0)

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

if STARTTYPEtable.haschanges then
DASTARTYPE.Update(STARTYPEtable)
end if


End Sub

Agreed , but I would like to understand why though


"Cor Ligthert" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Whaletyr,
>
> I never do it the way as you. Can you try this, it would be strange I
admit
> when 10 tables work. However now you have nothing,
>
> Cor
>
> \\\
> Dim STARTYPEtable As new DataSet
> Dim constring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=C:\Documents and Settings\Administrator\My
> Documents\QUEST.mdb;Persist Security Info=False"
> Dim condb As New OleDb.OleDbConnection(constring)
> Dim DASTARTYPE As New OleDb.OleDbDataAdapter("SELECT * FROM STAR_TYPE",
> condb)
>
> Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles MyBase.Load
> DASTARTYPE.Fill(STARTYPEtable)
> Dim DBSTARTYPE As New OleDb.OleDbCommandBuilder(DASTARTYPE)
> DataGrid1.DataSource = STARTYPEtable.tables(0)
>
> End Sub
>
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
>
> if STARTTYPEtable.haschanges then
> DASTARTYPE.Update(STARTYPEtable)
> end if
>
>
> End Sub
>
>

Late reply, but I only just saw your post.

One problem I ran into was having table names and field names that
included spaces. The command builder doesnt handle these correctly by
default. You need to set two additional properties thus:
bld.QuotePrefix = "["
bld.QuoteSuffix = "]"

Do you have some tables with embedded spaces in the table or field
names?

Geoff


On Tue, 17 Aug 2004 22:24:41 +0200, "whaletyr" <(E-Mail Removed)>
wrote:

>When I try to update an access database using the commandbuilder and
>updatecommand. I get the
>
>An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred
>in system.data.dll
>
>error message.
>
>The strange thing is that only half of the table's give this error and the
>other half of the tables seem to work just fine using the same code (except
>other sql string always SELECT * FROM TABLE)
>
>ANy Idea what goes wrong her or what I can do to eliminate this problem.
>(Except for remaking all tables)
>
>
>
>Here is my code
>
>Dim STARTYPEtable As DataTable
>
>Dim constring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
>Source=C:\Documents and Settings\Administrator\My
>Documents\QUEST.mdb;Persist Security Info=False"
>
>Dim condb As New OleDb.OleDbConnection(constring)
>
>Dim DASTARTYPE As New OleDb.OleDbDataAdapter("SELECT * FROM STAR_TYPE",
>condb)
>
>Dim DBSTARTYPE As New OleDb.OleDbCommandBuilder(DASTARTYPE)
>
>
>
>Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
>System.EventArgs) Handles MyBase.Load
>
>DASTARTYPE.InsertCommand = DBSTARTYPE.GetInsertCommand
>
>DASTARTYPE.UpdateCommand = DBSTARTYPE.GetUpdateCommand
>
>DASTARTYPE.DeleteCommand = DBSTARTYPE.GetDeleteCommand
>
>STARTYPEtable = New DataTable
>
>DASTARTYPE.Fill(STARTYPEtable)
>
>DataGrid1.DataSource = STARTYPEtable
>
>End Sub
>
>Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
>System.EventArgs) Handles Button1.Click
>
>DASTARTYPE.Update(STARTYPEtable)
>
>
>
>End Sub
>