Can not update a Access database

  • Thread starter Thread starter yoramo
  • Start date Start date
Y

yoramo

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
 
Is the table Keyed? Not sure if you're using the Configuration
wizard/commandbuilder or wrote your own, but make sure your update logic is
clean (ie can run in Access).

HTH,

Bill
 
I have tried using both the wizard (created a OleDbDataAdapter and let the
wizard to create the insert,update and delete commands).
I have aslo tried the commandBuilder. nothing seems to help I'm getting the
same result all the time.

I'm using a very simple table
Yoramo
 
Yoramo,
Make sure one of your field names isn't a reserved word. Just because
the query works in Access doesn't mean that the SQL parser for .NET won't
think the statement is invalid. If you are using the CommandBuilder set the
QuotePrefix and QuoteSuffix properties so that the Insert/Delete/Update
commands have all the fields quoted.

Ron Allen
 
my update code looks like this:

oleDbDataAdapter3.Update(dataSetGen11) ;

where my command that was generated by the wizard is:

"UPDATE Stam
SET Category = ?,
SubCategory = ?,
Value = ?
WHERE (ID = ?)
AND (Category = ? OR ? IS NULL AND Category IS NULL)
AND (SubCategory = ? OR ? IS NULL AND SubCategory IS NULL)
AND (Value = ? OR ? IS NULL AND Value IS NULL)"
 
the names I use are:
ID, Category, SubCategory and Value

is one of them a reserved word?
 
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
 
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,

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

Insert statement looks fine. What do the parameters look like? And what is
the precise error you are getting?

Bernie
 
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

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
 
I think there is a problem with ID too. In debugging one of my Access
problems, that is the one I ran into; one of my columns was named ID. I
renamed it and then it worked fine.

Brad
 
Hi Brad,

I duplicated his table and built an add routine - name id did not cause a
problem. I think I know what the problem is - I will tell him directly.

Bernie
 
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



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
 
Back
Top