Can not update a Access database

[Bernie Yaeger]

Hi Yoramo,

Glad you got it to work. I don't know if I can explain what you are
observing. The wizard will add all kinds of mapping statements and xml
statements, but I'm not sure what they do and ultimately how they work.

Bernie

Hi Bernie



Yes, it works!! I guess my problem was that I added a row directly from
the DataGrid and I did not initialize the fields.



I wrote:

DataRow aRow = dataSetGen11.Tables["Stam"].NewRow() ;

dataSetGen11.Tables["Stam"].Rows.Add(aRow) ;



Now I have initialized and it works just fine.

I noticed another thing the wizard generated statements added a longer
statement with all kinds of additional conditions, when I removed all the
conditions it has succeeded.

So I went back to catch the errors and to handle them but hear I found that
the error pointed at the ID that was fine the fields that failed where the
other fields.



Now I'm working with the minimized version of the insert and update
statements.

Can you explain?

Thanks

Yoramo

Hi Yoramo,

OK, I think I know the problem.

I created a table in an access database. I named it stam and gave it 5
cols: id (autonumber), category (text), subcategory (text), numvalue
(number), adate (date/time). I made id the PK (primary key).

I then created a form with textboxes and one datetimepicker for input

and

an
'Add' button, no entry textbox of course for the id column. I used the
oledb data adapter wizard and created a dataset - and here's where your
problem probably is. When I developed the select statement I included ID,
even though it is not part of the form and not part of the insert statement,
because the dataadapter needs to have the PK col when it works with the
database to build the new row with the update statement. I then use this
code for the add button:

Dim rt As DataRow = Stamds1.Tables(0).NewRow
rt("category") = mcategory.Text

rt("subcategory") = msubcategory.Text

rt("numvalue") = mnumvalue.Text

rt("adate") = madate.Value

Stamds1.Tables(0).Rows.Add(rt)

Try

OleDbDataAdapter1.Update(Stamds1)

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

This worked fine - added the row with no problem.

Let me know if this helps.

Bernie

VALUES
(?, built
by to
add

 

[yoramo]

thanks very much for coaching me.

Yoramo

Hi Yoramo,

Glad you got it to work. I don't know if I can explain what you are
observing. The wizard will add all kinds of mapping statements and xml
statements, but I'm not sure what they do and ultimately how they work.

Bernie

Hi Bernie



Yes, it works!! I guess my problem was that I added a row directly from
the DataGrid and I did not initialize the fields.



I wrote:

DataRow aRow = dataSetGen11.Tables["Stam"].NewRow() ;

dataSetGen11.Tables["Stam"].Rows.Add(aRow) ;



Now I have initialized and it works just fine.

I noticed another thing the wizard generated statements added a longer
statement with all kinds of additional conditions, when I removed all the
conditions it has succeeded.

So I went back to catch the errors and to handle them but hear I found that
the error pointed at the ID that was fine the fields that failed where the
other fields.



Now I'm working with the minimized version of the insert and update
statements.

Can you explain?

Thanks

Yoramo

Hi Yoramo,

OK, I think I know the problem.

I created a table in an access database. I named it stam and gave it 5
cols: id (autonumber), category (text), subcategory (text), numvalue
(number), adate (date/time). I made id the PK (primary key).

I then created a form with textboxes and one datetimepicker for input

and

an
'Add' button, no entry textbox of course for the id column. I used the
oledb data adapter wizard and created a dataset - and here's where your
problem probably is. When I developed the select statement I included ID,
even though it is not part of the form and not part of the insert statement,
because the dataadapter needs to have the PK col when it works with the
database to build the new row with the update statement. I then use this
code for the add button:

Dim rt As DataRow = Stamds1.Tables(0).NewRow
rt("category") = mcategory.Text

rt("subcategory") = msubcategory.Text

rt("numvalue") = mnumvalue.Text

rt("adate") = madate.Value

Stamds1.Tables(0).Rows.Add(rt)

Try

OleDbDataAdapter1.Update(Stamds1)

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

This worked fine - added the row with no problem.

Let me know if this helps.

Bernie

Hi Bernie

what is a PK?

I have set the MS Access table to have a field called ID typed
"Autonumber" (this field is also a key for the table.

adding rows in access workes fine but no with my program.

(as you can see I'm new to the database world. thanks for your
patient ).

Yoramo

Hi Yoramo,

OK - that makes sense. Is your id column set as the PK, with datatype
'autonumber'?

If you can, email the database (if it's not too large) to me at
(e-mail address removed). I will write a quick and dirty add

routine
to

see
if I can replicate the error.

Sorry we haven't gotten to the bottom of this yet.

Bernie

Hi Bernie.

I'm getting a message saying that ID can not be null.

yoramo

Hi Yoramo,

Insert statement looks fine. What do the parameters look

like?
And

what
is
the precise error you are getting?

Bernie

Hello again Bernie, and all others that have been very useful.

The insert statment looks like this:
"INSERT INTO Stam(Category, SubCategory, NumValue, aDate) VALUES
(?,
?,
?,
?)"
I have another field that is a "autonumber" named ID but it

is
not

mention
in the insert statement.
Thanks

Yoramo




Hi Yoramo,

OK - making progress.

Please let me see the full insert command. Also, was it built
by
the
wizard
and do you now have all cols in the dataset/datatable?

Bernie

OK now my update works but the insert fails because the ID
field
is
a
"Autonumber" a self incremental field.

my insert command has only 3 fields and is lacking the ID
field.

do you have any suggestions?

Thanks
yoramo

Hi Yoramo,

Value might be a reserved word.

Here's an idea - rebuild the table under a temp button using
the
other
cols
but excluding value and use the wizard. Continue to

do
this

by
ignoring
one
of the cols to see which is causing your error.

HTH,

Bernie Yaeger

Hello



I'm using OleDB to retrieve/update a table from a "MS
Access"
database.



The select works but the update fails and I get the
following
error
"{"Syntax error in UPDATE statement." } I have tried to
add
a
row
and
again
I get an error indicating that the "insert" syntax created
the
error.



Does any one have any Idea why ?



Thanks

Yoramo

 

[Bernie Yaeger]

Hi Yoramo,

Happy to help.

Bernie

thanks very much for coaching me.

Yoramo

Hi Yoramo,

Glad you got it to work. I don't know if I can explain what you are
observing. The wizard will add all kinds of mapping statements and xml
statements, but I'm not sure what they do and ultimately how they work.

Bernie

Hi Bernie



Yes, it works!! I guess my problem was that I added a row

directly
from

the DataGrid and I did not initialize the fields.



I wrote:

DataRow aRow = dataSetGen11.Tables["Stam"].NewRow() ;

dataSetGen11.Tables["Stam"].Rows.Add(aRow) ;



Now I have initialized and it works just fine.

I noticed another thing the wizard generated statements added a longer
statement with all kinds of additional conditions, when I removed all the
conditions it has succeeded.

So I went back to catch the errors and to handle them but hear I found that
the error pointed at the ID that was fine the fields that failed where the
other fields.



Now I'm working with the minimized version of the insert and update
statements.

Can you explain?

Thanks

Yoramo







Hi Yoramo,

OK, I think I know the problem.

I created a table in an access database. I named it stam and gave

it

5 input
and included
ID,

it

is

the

ID tried
to