Commandbuilder / updatecommand error

W

whaletyr

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
 
K

Ken Tucker [MVP]

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

whaletyr

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

Ken Tucker [MVP]

Hi,

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

Ken
--------------
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)
 
W

whaletyr

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 said:
Hi,

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

Ken
--------------
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 said:
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
----------------------
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
Click to expand...
Click to expand...
 
C

Cor Ligthert

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
 
W

whaletyr

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
 
C

Cor Ligthert

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
 
G

GeoffreyW

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Debugging Error 1
UpdateCommand 5
Help With Custom Paging in DataGridView - VB.Net 2008 0
PrintDocument.Print shows also the little window - "Page 1 of document" 2
how to remove a programmatically created DateTimePicker 38
passing values between sibling forms 3
ToolStrip and MenuStrip do not trigger events like buttons 0
Tutorial, what have I missed ? 6

Top