Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    105

    Unhappy Unanswered: problems on writing data in a oracle db via recordset

    hi everybody i have a problem
    i have a programme in vb6 that gets data from a oracle database and also should write data into the database
    now i get the datas with a recordset like that

    GetData SQLUserData, adOpenForwardOnly, recData

    function GetData()
    'setting the recordset
    Set GetData1 = New ADODB.Recordset

    'execute the SQL-Query
    GetData1.Open strSQL, conDB, strCursortype, adLockBatchOptimistic

    Set rsOutput = GetData1
    -----
    The strSQL contains the sql-query
    that works so far but if i want to write data into that recordset with

    recData.Fields(4) = Date
    recData.UpdateBatch
    recData.Close

    then i get this message:

    "Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype"

    the code works when the used database is a access database is there a thing in oracle that i need to tell the tables that they can be editet?
    does somebody know what is the problem here and how i can solve that?
    thanks a real lot

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Sorry, I don't know, but just a suggestion : if you only select data to update some fields, then instead of a SELECT, just execute an UPDATE query : it should be much faster especially if the select return many rows, since almost all of the work would be done on the server, and very few data would transit on the network. Now if you have to get data for other processing, then keep your select, but you could still do an update afterwards though, if you can't get your current method to work..

    Regards,

    RBARAER.

  3. #3
    Join Date
    Oct 2004
    Posts
    105
    yes the problem is that i need the recordset with the select anyways
    its also that i have this problem very often in my programme if i have to do a update too then that will be lots of extra code
    but if its not working other way then i will have to use the update
    how do i do that though?
    just like update tablename.colum
    set "inserteddata"
    does it work like that?
    thanks alot

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Right; you can't update an Oracle table (column) using SELECT statement; you'll have to use the UPDATE statement which, in a simple way, looks like this:
    UPDATE tablename SET
    column = column_value
    WHERE condition;

    For example:
    UPDATE emp SET
    ename = 'Just Me'
    WHERE empno = 1234;

  5. #5
    Join Date
    Oct 2004
    Posts
    105
    I have it like that now

    Update tblUser set tblUser.LastConnect = '02/02/2005' WHERE tblUser.UserID = '1242345'

    but i get the errormessage:

    ORA - 01843: not a valid month

    does oracle has a certain structure for dates?
    thanks
    Last edited by justme:-); 02-03-05 at 08:26.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'd say that it depends on NLS settings ... however, you could do this:
    UPDATE tblUser set
    tblUser.LastConnect = to_date('28.02.2005.', 'dd.mm.yyyy.')
    where tblUser.UserID = 'uid11196';

  7. #7
    Join Date
    Oct 2004
    Posts
    105
    that worked well
    thank you lots

Posting Permissions

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