#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    295
    Rep Power
    46

    Issue with query


    Hello all,

    I'm having a problem with an insert query within my code. Quite simply it doesn't insert the data. No error is returned but it just doesn't insert. The actual code with the problem is below:

    Code:
                    ' Create SQL statement and execute
                    strSQL13 = "SELECT stored_value_1, stored_value_2, stored_value_3, stored_value_9, stored_value_10, stored_value_13, stored_value_14 FROM temporary_values WHERE record_type='addrel' AND record_ID=" & tempdebtorID & " ORDER BY temp_ID;"
                    objCommand.CommandText = strSQL13
                    Set rs13 = objCommand.Execute
              
                    ' Loop each row and add to telephone table
                    Do Until rs13.EOF
                        
                        ' Create SQL statement and execute
                        'strSQL14 = "INSERT INTO debtors_add_relationships (address_ID, debtor_name_ID, sending_add, next_let_no, main_add, current, notes) VALUES (" & rs13.Fields(3).Value & "," & rs13.Fields(4).Value & "," & rs13.Fields(5).Value & ",1," & rs13.Fields(6).Value & ",'" & rs13.Fields(2).Value & "','" & rs13.Fields(0).Value & "');"
                        strSQL14 = "INSERT INTO debtors_add_relationships (address_ID, debtor_name_ID, sending_add, next_let_no, main_add, notes, current) VALUES (" & rs13.Fields(3).Value & "," & rs13.Fields(4).Value & "," & rs13.Fields(5).Value & ",1," & rs13.Fields(6).Value & ",'" & rs13.Fields(0).Value & "','" & rs13.Fields(2).Value & "');"
                        objCommand.CommandText = strSQL14
                        Set rs14 = objCommand.Execute
                        MsgBox rs13.Fields(2).Value
    MsgBox strSQL14
                        ' Move to next record in the recordset
                        rs13.MoveNext
                        
                    Loop
    The issue is I can't actually see anything wrong with this. You'll notice that I've used a msgbox to echo the query itself and when doing so it produces this query:

    Code:
    INSERT INTO debtors_add_relationships (address_ID, debtor_name_ID, sending_add, next_let_no, main_add, notes, current) VALUES (21513,21514,True,1,True,'Test','Yes');
    When I paste this directly into my Access (which Im using for my database) it works without any problems and therefore I don't think its a problem with the query itself. The actual offending element seems to be the current field, if I take this out it works perfectly from my script but as soon as I put it back in nothing gets added to my database, regardless of the value.

    There are potentially 3 values, yes, no and unknown but as said any of these stops the data being input. The data type for the current field in my database is text.

    Does anyone have any ideas? This one has truly stumped me. I'm thinking there might be something within VB I'm not aware of but I'm sure you experts would be able to help me out there.

    Thanks very much for your help.
  2. #2
  3. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,453
    Rep Power
    4539
    I haven't used recordset updates in a long time. Don't you need a rs.update statement somewhere? I'm only guessing but the recordset object documentation is available in the msdn library.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    295
    Rep Power
    46
    Originally Posted by Doug G
    I haven't used recordset updates in a long time. Don't you need a rs.update statement somewhere? I'm only guessing but the recordset object documentation is available in the msdn library.
    No you don't. I've used this method on a number of other insert statements, even in the same procedure and it works fine. It must be something to do with the current field as when I take this field out it works perfectly, it's when I put this back in I receive the problem. I also can't see it being a problem with the field in the database itself as the query works when I type it in directly. I'm pretty confused.

IMN logo majestic logo threadwatch logo seochat tools logo