I can't update a Access 2000 Database, the sourcecode you can find below causes a error message "syntax error in update statement", "unhandled OleDbException" and no data is added to the database
I even copy and past a working example from MSDN into my VisualStudio, I got the same error message again .
An other problem I have is that I can not add a OLEDB DataSource to my Application with the help of the IDE, I can add a ODBC or SQL DataSource
but if I click on Add DataSource in the Main Menu of Visual Studio 2005 and choose add Datasource and then I click on
the Database Icon I can click on New Connection, but then I dont get a Menu where I can chose a DataSource and set the password.
I know how to do this with Visual Basic 6 and Visual Studio 2003, but this doesnot work with my Visual Studio 2005. Because of this Bug and the problems with updating a Access Database I think my Visual Studio Installation is corrupted,
I reinstalled it 3 times but it did not help.
This is the source code:
Private Sub DBTest2()
''''''''''''''''''''''''''
'Create Connection String
''''''''''''''''''''''''''
Dim stringProvider As String = "provider=Microsoft.JET.OLEDB.4.0;"
Dim stringDataSource As String = "data source = C:\" + "BLS II" + "\" + "BLS2000Vollversion.mdb;"
Dim stringSecurity As String = "Persist Security Info=False;Jet OLEDB:Database Password=st1811ma"
Dim connectionString As String
connectionString = stringProvider + stringDataSource + stringSecurity
'''''''''''''''''''''''''
'Create Connection Object
'''''''''''''''''''''''''
Dim cn As New OleDbConnection(connectionString)
'''''''''''''''''''''''''''''''
'Create DataAdapter and DataSet
''''''''''''''''''''''''''''''''
Dim da As New OleDbDataAdapter("Select * FROM Profil", cn)
Dim ds As New DataSet()
da.FillSchema(ds, SchemaType.Source, "Profil")
da.Fill(ds, "Profil")
Debug.Print(ds.Tables("Profil").Rows(0)("Alter"))
''''''''''''''''''''''
'Create Command Object
''''''''''''''''''''''
Dim cmdCommand As OleDbCommandBuilder = New OleDbCommandBuilder(da)
''''''''''''''''''''''
'Change DataSet values
''''''''''''''''''''''
ds.Tables("Profil").Rows(0)("Alter") = 500
''''''''''''''''''''''
'Open connection and execute command
''''''''''''''''''''''
cn.Open()
da.Update(ds, "Profil")
cn.Close()
End Sub
I have reporteted a bug to Microsoft with the Bug ID FDBK49997
I would be thankfull if someone could take a look at this bug report and give it a
vote, because Microsoft will not consider a bug reprort unless it gets at leas 2 votes .
Here is the Link for the Produkt Feedback Center where you can find my bug report
http://lab.msdn.microsoft.com/productfeedback/Default.aspx
Can't update Access 2000, unhandled OleDbException BugID FDBK49997
DineshB
Thank you for your advise, I changed "Alter" to "mAlter" and suddenly
everything works, the database gets updated without any error.
I think that I would never have solved this problem without your help.
Best Regards
Gerald Lorentzen
Andreas Pollak
Do you have primary key on that table and how it looks like (you must have it in order to use CommandBuilder) Can you print out update command generated by CommandBuilder Can you print our errors from exception error collection (sample below)
http://msdn.microsoft.com/library/default.asp url=/library/en-us/cpref/html/frlrfSystemDataOleDbOleDbExceptionClassTopic.asp
pnp
Alright, I saw there is a bug in there...hopefully it is gonna be wiped out as soon as possible!
This is the source code:
Private Sub DBTest2()
''''''''''''''''''''''''''
'Create Connection String
''''''''''''''''''''''''''
Dim stringProvider As String = "provider=Microsoft.JET.OLEDB.4.0;"
Dim stringDataSource As String = "data source = C:\" + "BLS II" + "\" + "BLS2000Vollversion.mdb;"
Dim stringSecurity As String = "Persist Security Info=False;Jet OLEDB:Database Password=st1811ma"
Dim connectionString As String
connectionString = stringProvider + stringDataSource + stringSecurity
'''''''''''''''''''''''''
'Create Connection Object
'''''''''''''''''''''''''
Dim cn As New OleDbConnection(connectionString)
'''''''''''''''''''''''''''''''
'Create DataAdapter and DataSet
''''''''''''''''''''''''''''''''
Dim da As New OleDbDataAdapter("Select * FROM Profil", cn)
Dim ds As New DataSet()
da.FillSchema(ds, SchemaType.Source, "Profil")
da.Fill(ds, "Profil")
Debug.Print(ds.Tables("Profil").Rows(0)("Alter"))
''''''''''''''''''''''
'Create Command Object
''''''''''''''''''''''
Dim cmdCommand As OleDbCommandBuilder = New OleDbCommandBuilder(da)
''''''''''''''''''''''
'Change DataSet values
''''''''''''''''''''''
ds.Tables("Profil").Rows(0)("Alter") = 500
''''''''''''''''''''''
'Open connection and execute command
''''''''''''''''''''''
cn.Open()
da.Update(ds, "Profil")
cn.Close()
Best Regards to you and all the team of Microsoft!:)
Kanchev!
Sepp Obermayer
I was actually suggesting adding some code to catch the exception so it won't be unhandled as well as adding code to print out messages from error collection (not to be confused with exception's error message). Link above contains a sample on how to do that.
Anyway, it appears you're using reserved keyword "Alter" as a column name without appropriately setting QuotePrefix and QuoteSuffix properties of CommandBuilder. That leads to incorrect command been generated.
Debil
Thanks for your reply
Yes I have a primary key, a integer field and it's not empy.
Here is the CommandBuilder Text:
UPDATE Profil SET key = , User = , ProfilVorhanden = , stringProfilVorhanden = , KalorienGrundumsatz = , KalorienLeistungsumsatz = , KalorienGesamtumsatz = , KohlehydrateKalorien = , FettKalorien = , ProteinKalorien = , BMIIst = , BMISoll = , GewichtAnfang = , GewichtIst = , GewichtSoll = , Groesse = , Sex = , Alter = , Datum = WHERE ((key = ) AND (( = 1 AND User IS NULL) OR (User = )) AND (( = 1 AND ProfilVorhanden IS NULL) OR (ProfilVorhanden = )) AND (( = 1 AND stringProfilVorhanden IS NULL) OR (stringProfilVorhanden = )) AND (( = 1 AND KalorienGrundumsatz IS NULL) OR (KalorienGrundumsatz = )) AND (( = 1 AND KalorienLeistungsumsatz IS NULL) OR (KalorienLeistungsumsatz = )) AND (( = 1 AND KalorienGesamtumsatz IS NULL) OR (KalorienGesamtumsatz = )) AND (( = 1 AND KohlehydrateKalorien IS NULL) OR (KohlehydrateKalorien = )) AND (( = 1 AND FettKalorien IS NULL) OR (FettKalorien = )) AND (( = 1 AND ProteinKalorien IS NULL) OR (ProteinKalorien = )) AND (( = 1 AND BMIIst IS NULL) OR (BMIIst = )) AND (( = 1 AND BMISoll IS NULL) OR (BMISoll = )) AND (( = 1 AND GewichtAnfang IS NULL) OR (GewichtAnfang = )) AND (( = 1 AND GewichtIst IS NULL) OR (GewichtIst = )) AND (( = 1 AND GewichtSoll IS NULL) OR (GewichtSoll = )) AND (( = 1 AND Groesse IS NULL) OR (Groesse = )) AND (( = 1 AND Sex IS NULL) OR (Sex = )) AND (( = 1 AND Alter IS NULL) OR (Alter = )) AND (( = 1 AND Datum IS NULL) OR (Datum = )))
And here is the Error Message from the Error Message Collection:
Eine Ausnahme (erste Chance) des Typs "System.Data.OleDb.OleDbException" ist in System.Data.dll aufgetreten.
An exception occurred. Please contact your system administrator.
This error message doesn't say much because it's an unhandled exception.
__________________
Somehow it has to be easier to update a Access Database.
Maybe the German Version of Visual Studio Standard ist corrupt.
Thanks for your help
Gerald Lorentzen