?
Solved

ADOdc control failing to update database

Posted on 2005-04-23
12
Medium Priority
?
183 Views
Last Modified: 2010-05-02
I am using adodc object to connet to database vie odbc, and it works fine till
the fields get changed(fields are mapped through the adodc), then it cannot update the database.

I get all kind of errors.
I have tried all kind of settings for the adodc, like CursorLocation, CursorType, LockType and Mode.
I've also tried forcing the save, then it fails few times and then saves it, when run from the editor.
Same code when compiled into .exe crashes of course.

My question is whether it's something with the code or it may have to do with the database settings
on the database server?! Any ideas what it should be like?

And I know I can use DAO too but I really want to make it work with the ADO controls if possible.

Thank You!
0
Comment
Question by:Berhan Karagoez
  • 6
  • 4
  • 2
12 Comments
 

Author Comment

by:Berhan Karagoez
ID: 13851360
btw:

the mysql database version  is :  4.0.23-standard
and the odbc connector version is: 3.51.11.00

0
 
LVL 19

Assisted Solution

by:Stephen Manderson
Stephen Manderson earned 1050 total points
ID: 13861454
you could do this to hard code the update


Private Sub cmdUpdate_Click
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database.mdb;"
conn.Open

sqlstr = "Table"

rs.Open sqlstr, conn, adOpenKeyset, adLockOptimistic

With rs
          .fields("Fields1") = Text1.text
          .fields("Fields2") = Text2.text
          .Update
End With

rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
End Sub
0
 

Author Comment

by:Berhan Karagoez
ID: 13861544
Thank you for your response but that's something I really don't ant to do.

Just because the specs are to use adodc. Same program/code works with another database (older mysql database).
I am using odbc driver 3.51.11.00 for both. It fails only on the new mysql version/server.

Either odbc or setting I say!
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 19

Assisted Solution

by:Stephen Manderson
Stephen Manderson earned 1050 total points
ID: 13861708
Sounds like you have a problem with the connection string, what is your connection string to your older db ?
0
 

Author Comment

by:Berhan Karagoez
ID: 13862011
nope: everything is same:


only difference is :

working mysql db version:  MySQL 3.23.58
failing to update db version: 4.0.23-standard


that's it.
0
 
LVL 29

Accepted Solution

by:
leonstryker earned 450 total points
ID: 13862290
ADODC is the most unstable, bug prone control ever released by M$.  You are shooting yourself in the foot just by trying to use it.  I would suggest using the method given by MrManderson.

>specs are to use adodc

Whose specs?  This could not possibly be anyone who know anything about VB development.

You should also look here for a MySQL connection string:

http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForMySQL

Leon
0
 

Author Comment

by:Berhan Karagoez
ID: 13862345
Right, but if you read again what I've written, I wrote that it works with one db and it doesn't with the other.
I interpret it as either a driver/odbc or mysql seting somehow.

About the specs, it's from a client that knows some VB, and I did a solution with code instead of the
MS controls which was too hard for him to work with, so that's why we've gone back to adodc, which
by the way again works fine with the old db. A solution is to move the database tables to the old database
and have it work there.

I don't think I can get an answer that will solve this for me.
Perhaps I should mention that the new database does update (when run from the vb editor) and press
F5 when error is triggered, it also complains about "E_Fail" then it actually upldates the field. But
having an exe file will just crash after the first error message.
0
 
LVL 19

Assisted Solution

by:Stephen Manderson
Stephen Manderson earned 1050 total points
ID: 13862455
you have different connection strings for different db. try this as your connection string, make sure to change the user name and pw and server name

Provider=SQLOLEDB.1;pwd=zzzzz;uid=ZZZZZ;Initial Catalog=sdmdb;Data Source=Server
0
 
LVL 29

Assisted Solution

by:leonstryker
leonstryker earned 450 total points
ID: 13866324
>, I wrote that it works with one db and it doesn't with the other.

But, you are saying that one of the databases is old and the other is new.  does this mean they are diferent versions of MySQL or just when they there installed.


>client that knows some VB

Nothing is more dangereous than someone with a little knowledge.  From my experience you will continue to have problems with ADODC in strange ways.

Leon
0
 

Author Comment

by:Berhan Karagoez
ID: 13873323
that's why I read before I post! ;)

Look above it says:

only difference is :
working mysql db version:  MySQL 3.23.58
failing to update db version: 4.0.23-standard

what I ment was they are different versions, incase it looks like it says something else.

Nothing is more dangerous than people giving up!!
Actually it's works with the old db, and he is perfectly capable of working with adodc.
Also adodc has never had a problems with the older db version.
0
 
LVL 19

Assisted Solution

by:Stephen Manderson
Stephen Manderson earned 1050 total points
ID: 13873768
Can you supple the code you are using to update ?
0
 

Author Comment

by:Berhan Karagoez
ID: 13905218
no, because there is no code, since the adodc does the updating itself automatically!

I did only this:

Private Sub Adodc2_WillChangeField(ByVal cFields As Long, Fields As Variant, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
On Error Resume Next
Adodc2.Recordset.Update
End Sub
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question