Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I jus have tipical ADO conn opened with rs1 and a table named FIDI

    an have my_var1=123456789, var_prova07=1, var_prova08=5, var_prova09=7

    I want with a query this controll:

    first condition:
    based my_var1 find in prova02 the value of my_var1 (i have indexed this filed) and compare
    var_prova07=1, var_prova08=5, var_prova09=5 with fileds prova07,prova08,prova09

    No one or more avalue of prova07,prova08,prova09=5 are changed no action.

    Instead if one or more value of var_prova07,var_prova08, var_prova09 are change update prova07,prova08,prova09 with new value.

    second condition:
    my_var1=87988787 not exitsis in prova02 and var_prova07=7, var_prova08=11, var_prova09=48 add new rset prova02, prova07,prova08,prova09 with the related value of var in table.

    in the case of my mdb attached add new rset based my_var1=87988787 in prova02 , var_prova07=7, var_prova08=11, var_prova09=48 in prova07,prova08,prova09

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    About your first question, it seems that you want to ensure that all records with prova02=my_var1 have prova07=var_prova07, prova08=var_prova08 and prova09=var_prova09. Is that correct?

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='796997' date='08-Oct-2009 21:32']About your first question, it seems that you want to ensure that all records with prova02=my_var1 have prova07=var_prova07, prova08=var_prova08 and prova09=var_prova09. Is that correct?[/quote]
    perfect!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You don't need to open a recordset for this question. You can execute an SQL statement:

    Code:
    cn1.Execute "UPDATE FIDI SET PROVA07='" & var_prova07 & _
      "', PROVA08='" & var_prova08 & "', PROVA09='" & _
      var_prova09 & "' WHERE PROVA02='" & my_var1 & "'"
    where cn1 is your ADO connection variable.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    About the second question: if I understand it correctly, you can use the following:

    Code:
    ' Open a recordset that selects the records with PROVA02=my_var1
    rs1.Open "SELECT PROVA02 FROM FIDI WHERE PROVA02='" & my_var1 & "'", _
      cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    
    ' If there are no such records, then...
    If rs1.EOF Then
      ' ... insert a new record
      cn1.Execute "INSERT INTO FIDI (PROVA02, PROVA07, PROVA08, PROVA09) " & _
    	"VALUES(" '" & my_var1 & "', '" & var_prova07 & "', '" & _
    	var_prova07 & "', '" & var_prova07 & "')"
    End If
    
    rs1.Close
    Set rs1 = Nothing

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797001' date='08-Oct-2009 21:43']You don't need to open a recordset for this question. You can execute an SQL statement:

    Code:
    cn1.Execute "UPDATE FIDI SET PROVA07='" & var_prova07 & _
      "', PROVA08='" & var_prova08 & "', PROVA09='" & _
      var_prova09 & "' WHERE PROVA02='" & my_var1 & "'"
    where cn1 is your ADO connection variable.[/quote]

    but i think your code not compare the value of var with thew value in fileds, or not?

    sorry i dont have see the last your post not consider my note;-(

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797001' date='08-Oct-2009 21:43']You don't need to open a recordset for this question. You can execute an SQL statement:

    Code:
    cn1.Execute "UPDATE FIDI SET PROVA07='" & var_prova07 & _
      "', PROVA08='" & var_prova08 & "', PROVA09='" & _
      var_prova09 & "' WHERE PROVA02='" & my_var1 & "'"
    where cn1 is your ADO connection variable.[/quote]

    ummmmmm... but this code update the rset in other cae and bot compre the value of the 3 var in rset?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The UPDATE statement will also update the records that already had the correct values but that doesn't matter - they will remain the same.

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797007' date='08-Oct-2009 22:07']The UPDATE statement will also update the records that already had the correct values but that doesn't matter - they will remain the same.[/quote]

    but the query update only the value in prova07,prova08 and prova09 and remain inaltered the valu in other fileds for example in prova01, prova03, prova04 remain the same?

    in other case is this correct:

    ' Open a recordset that selects the records with PROVA02=my_var1
    rs1.Open "SELECT PROVA02 FROM FIDI WHERE PROVA02='" & my_var1 & "'", _
    cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

    ' If there are no such records, then...
    If rs1.EOF Then
    ' ... insert a new record
    cn1.Execute "INSERT INTO FIDI (PROVA02, PROVA07, PROVA08, PROVA09) " & _
    "VALUES(" '" & my_var1 & "', '" & var_prova07 & "', '" & _
    var_prova07 & "', '" & var_prova07 & "')"

    else

    cn1.Execute "UPDATE FIDI SET PROVA07='" & var_prova07 & _
    "', PROVA08='" & var_prova08 & "', PROVA09='" & _
    var_prova09 & "' WHERE PROVA02='" & my_var1 & "'"

    End If

    rs1.Close
    Set rs1 = Nothing

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='sal21' post='797013' date='08-Oct-2009 22:31']but the query update only the value in prova07,prova08 and prova09 and remain inaltered the valu in other fileds for example in prova01, prova03, prova04 remain the same?[/quote]
    Yes. The other fields will not be updated since they are not mentioned in the SQL statement.

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797014' date='08-Oct-2009 22:33']Yes. The other fields will not be updated since they are not mentioned in the SQL statement.[/quote]

    ahhhhhhhh.. the satement if rs1.EOF Then... ecc is thr best in order of fasted time then .seek .find or other method?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Unless your table is very large it won't make much difference, but if you have many records, I think that opening a recordset with just the records for which PROVA02=my_var1 and testing EOF will be faster.

  13. #13
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797016' date='08-Oct-2009 22:45']Unless your table is very large it won't make much difference, but if you have many records, I think that opening a recordset with just the records for which PROVA02=my_var1 and testing EOF will be faster.[/quote]
    but dont understand if the ELSE condition in the my reply is corect?

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I hadn't seen that you had edited your reply.
    It should work OK.

  15. #15
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797025' date='08-Oct-2009 23:19']I hadn't seen that you had edited your reply.
    It should work OK.[/quote]

    Hi Hans...

    About find method peraphs i am wrong in this piece of code!!!!

    Code:
     RS3.MoveFirst
    				 Do While RS3.EOF = False
    				 If RS3("AG") = AGENZIA Then
    				  MsgBox ("TROVATO OK")
    				  Exit Do
    				  Else
    				  MsgBox ("NON TROVATO KO")
    				 End If
    			RS3.MoveNext
    		Loop

Page 1 of 2 12 LastLast

Posting Permissions

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