Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Posts
    2

    Unanswered: Help PLZ, novice in VB need advice

    When I execute this:

    Private Sub getCEE()


    On Error GoTo Err_getCEE


    sqlPrim = "SELECT CodeEOCs, CodeEOCd, QteIndEOCsd, IndexEOCsd FROM " & ICEE & " ORDER BY CodeEOCs, CodeEOCd "
    If Not adoPrimaryRS.State = adStateClosed Then adoPrimaryRS.Close
    adoPrimaryRS.Open sqlPrim, db, adOpenStatic, adLockOptimistic

    While Not (adoPrimaryRS.BOF Or adoPrimaryRS.EOF)
    sqlTemp = "SELECT eo_key FROM ElementObjet WHERE cod_eoc = '" & Replace(adoPrimaryRS.Fields("CodeEOCs").Value, "'", "''")
    sqlTemp = sqlTemp & "' AND periode = " & PERIODE & " AND exercice = " & EXERCICE & " AND typ_donnee = " & TYPE_D
    If Not adoTempRS.State = adStateClosed Then adoTempRS.Close
    adoTempRS.Open sqlTemp, db, adOpenStatic, adLockOptimistic

    sqlTemp1 = "SELECT eo_key FROM ElementObjet WHERE cod_eoc = '" & Replace(adoPrimaryRS.Fields("CodeEOCd").Value, "'", "''")
    sqlTemp1 = sqlTemp1 & "' AND periode = " & PERIODE & " AND exercice = " & EXERCICE & " AND typ_donnee = " & TYPE_D
    If Not adoTempRS1.State = adStateClosed Then adoTempRS1.Close
    adoTempRS1.Open sqlTemp1, db, adOpenStatic, adLockOptimistic

    sqlPrimC = "UPDATE EOConsEO SET qte_eoc_eoc = " & Val(adoPrimaryRS!QteIndEOCsd)
    sqlPrimC = sqlPrimC & " WHERE eo_key_src = '" & Replace(adoTempRS.Fields("eo_key").Value, "'", "''") & "' AND eo_key_dst = '" & Replace(adoTempRS1.Fields("eo_key").Value, "'", "''") & "' AND alpha_eoc_eoc = '" & Replace(adoPrimaryRS.Fields("IndexEOCsd").Value, "'", "''") & "' AND periode = " & PERIODE & " AND exercice = " & EXERCICE & " AND typ_donnee = " & TYPE_D
    If Not adoPrimaryCRS.State = adStateClosed Then adoPrimaryCRS.Close
    adoPrimaryCRS.Open sqlPrimC, db, adOpenStatic, adLockOptimistic


    adoPrimaryRS.MoveNext
    Wend
    Exit Sub

    Err_getCEE:
    MsgBox err.Description
    End Sub


    I got this:

    [Microsoft] [ODBC Microsoft Access Driver] Data type mismatchi in criteria expression

    How to Solve this????????????????

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If the code is "stolen" it probably works on the original machine.
    Sounds like one or more of the column datatypes is not the same as the SQL is suggesting
    Most probably you have defined the column in the database as type text/string where as the SQL is suggesting its a numeric type, OR this error can occur when you are trying to do a join or where criteira with mismatched data types

    failing that.....
    how have you declared the ado objects
    Its reccommended that you explicitly declare all objects (although VB does a reasonable job it isn't always bang on)
    -suggest
    dim adotemprs = adodb.recordset ' either as a global or static or dim if only used inside the function
    -add before the .open' or before each new invoication of the recordset.
    set adotemprs=new adodb.recordset
    HTH

  3. #3
    Join Date
    Dec 2004
    Posts
    2
    The code is pasted from another part of the app
    Thanks, I'll try to reset the recordset

Posting Permissions

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