Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2004
    Posts
    17

    Unanswered: updating field information

    I've made a database and connected it to VB, I can now succesfully display the database information through VB.

    But when i try to delete any of the information in a field, i get this error message - 'Data type conversion error'

    And when i try to edit any of the information in a field i get this error message - 'Field cannot be updated'

    Can anyone tell me why this is, and how to correct the errors please.

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: updating field information

    Could you please post the code? It may be that you have not chosen the locktype of your recordset and it defaulted to ReadOnly, but I cannot say without seeing any of the code.
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Apr 2004
    Posts
    17
    heres the code: -

    Dim Db As Database
    Dim Rs As Recordset

    Private Sub Command1_Click()
    Set Db = OpenDatabase("C:\Documents and Settings\Mike\Desktop\theatre\theatre")
    Set Rs = Db.OpenRecordset("Booker Details", dbOpenSnapshot)
    Label2.Caption = Rs!Address
    End Sub

    Private Sub Command2_Click()
    Bookings.Hide
    Menu.Show
    End Sub

    Private Sub DBGrid1_Click()

    End Sub

    Private Sub Form_Load()

    End Sub

  4. #4
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    In a Microsoft Jet workspace, a snapshot can't be updated. Try using dbOpenDynamic or dbOpenDynaset instead.
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  5. #5
    Join Date
    Apr 2004
    Posts
    17
    Thanks for the advice, i'm still getting some error messages though.

    Could these errors have anything to do with the fact that the fields i'm trying to change are set as primary keys in the database, or the fact that they have a relationship to another table?

    Because I only seem to get errors with these fields. Should I undo the relationships & primary keys?

    Or do you have any other suggestions?

  6. #6
    Join Date
    Apr 2004
    Posts
    17
    do i need do create an update/edit and delete button on the form?

    Because right now all i'm doing is deleting the information striaght from the fields in the table through VB.

    Or could it be that i'm getting these errors because i'm using the old data control rather than the ADO data control?

    Sorry for all these questions but i'm really stuck with this. And i do appreciate your help.
    Last edited by jimjimany; 04-16-04 at 11:22.

  7. #7
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    Could these errors have anything to do with the fact that the fields i'm trying to change are set as primary keys in the database, or the fact that they have a relationship to another table?

    Yes that would be a reason you cannot delete if you have child records in the other table since if you delete your record in the first table you would have orphaned records. That is one of the strenghths of the relational databases. If you do remove the primary keys and remove the relationships you will be able to delete but is that what you want? Normally you would delete the child records first and then the parent record and then you shouldn't have any problems.

    do i need do create an update/edit and delete button on the form?

    Depending what you are doing with the form, Navigation and Add New, Delete, Refresh, Search, Save etc.. are typical buttons in data access forms. Take a look at the links in my signature for what may be an easy solution.
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  8. #8
    Join Date
    Apr 2004
    Posts
    17
    thanks for the help, I can now change the data in the fields and add new data.

    still one problem left though, when ever i try to delete data in the fields, I get the error message - 'Data Type Conversion Error'

    why is this and can it be fixed?

  9. #9
    Join Date
    Apr 2004
    Posts
    17
    does the error have anything to do with the version of MS Access i'm using and whether i'm using DAO or ADO.

    Because i had to convert my database to 97 format for it to work with DAO.

    But when ever i need to make changes to the database i have to convert it back to access 2000 format then make the changes then convert it back to access 97 format.

    Maybe i'm wrong i'm not sure, but i really need to fix this thing.

    I tried looking at the microsoft support site and found this piece of code -
    ----------------------------------------------------------------------------------
    Private Sub Data1_Validate(Action As Integer, Save As Integer)
    If Text1.DataChanged Then
    Text1.DataChanged = False 'So this data is not saved
    Data1.UpdateRecord ' This saves the data that
    ' may have changed in the other controls

    ' Now clear the numeric field
    Data1.Recordset.Edit
    Data1.Recordset![Year Born] = Null
    Data1.Recordset.Update
    End If
    End Sub
    ----------------------------------------------------------------------------------
    I trid using this but it doesn't seem to make a difference.

    P.S.
    I'm really starting to hate VB!
    Last edited by jimjimany; 04-18-04 at 11:21.

  10. #10
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    Try downloading dao3.6, then you will be able to access the database in 2000/2002 format.

    The problem with clearing the field, is that access probably requires a value to be stored in the field. Check the required property of the field in your table design.

  11. #11
    Join Date
    Apr 2004
    Posts
    17
    I'm using dao3.6 and i still have to convert, otherwise i get a message sayin 'unrecognised database format'.

    And i've checked the fields in access and made sure that they're not set to 'required' but i still get the error.

    In the references 'microsoft active X data objects 2.0 library' is also check, would this have anything to do with the error?

  12. #12
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    yes, make sure you declare the recordset as dao.recordset, not just recordset.

    dim rs as dao.recordset.

    forces vb to use the dao references.

  13. #13
    Join Date
    Apr 2004
    Posts
    17
    would i write that in the general declarations part?

  14. #14
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    probably, it doesn't matter where you place it.

    If you put it in the general declarations, then it is available to the whole form. If you declare it in a module, then it is only available to that module.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •