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

Commandbuilder / updatecommand error

P: n/a
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
Nov 21 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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" <wh******@wanadoo.nl> wrote in message
news:41***********************@news.euronet.nl...
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

Nov 21 '05 #2

P: n/a
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]" <vb***@bellsouth.net> wrote in message
news:ui**************@TK2MSFTNGP10.phx.gbl...
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" <wh******@wanadoo.nl> wrote in message
news:41***********************@news.euronet.nl...
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

Nov 21 '05 #3

P: n/a
Hi,

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

Ken
--------------
"whaletyr" <wh******@wanadoo.nl> wrote in message
news:41***********************@news.wanadoo.nl...
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]" <vb***@bellsouth.net> wrote in message
news:ui**************@TK2MSFTNGP10.phx.gbl...
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" <wh******@wanadoo.nl> wrote in message
news:41***********************@news.euronet.nl...
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


Nov 21 '05 #4

P: n/a
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]" <vb***@bellsouth.net> wrote in message
news:ee**************@TK2MSFTNGP12.phx.gbl...
Hi,

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

Ken
--------------
"whaletyr" <wh******@wanadoo.nl> wrote in message
news:41***********************@news.wanadoo.nl...
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]" <vb***@bellsouth.net> wrote in message
news:ui**************@TK2MSFTNGP10.phx.gbl...
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" <wh******@wanadoo.nl> wrote in message
news:41***********************@news.euronet.nl...
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


Nov 21 '05 #5

P: n/a
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
Nov 21 '05 #6

P: n/a
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" <no**********@planet.nl> wrote in message
news:uj**************@tk2msftngp13.phx.gbl...
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

Nov 21 '05 #7

P: n/a
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
Nov 21 '05 #8

P: n/a
Agreed , but I would like to understand why though :)
"Cor Ligthert" <no**********@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
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

Nov 21 '05 #9

P: n/a
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" <wh******@wanadoo.nl>
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


Nov 21 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.