Issue with query

Dev Shed Forums Sponsor:

April 8th, 2012, 02:36 PM

Gavin83

Contributing User

Join Date: Jan 2004

Posts: 295

Time spent in forums: 2 Days 8 h 19 m 58 sec
Reputation Power: 45

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.

April 8th, 2012, 08:10 PM

Doug G

Grumpier Old Moderator

Dev Shed God 19th Plane (14000 - 14499 posts)

Join Date: Jun 2003

Posts: 14,237

Time spent in forums: 1 Month 4 Weeks 14 h 43 m 55 sec
Reputation Power: 4445

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
======
It is a truism of American politics that no man who can win an election deserves to. --Trevanian, from the novel Shibumi

April 9th, 2012, 07:22 AM

Gavin83

Contributing User

Join Date: Jan 2004

Posts: 295

Time spent in forums: 2 Days 8 h 19 m 58 sec
Reputation Power: 45

Quote:

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.

Developer Shed Advertisers and Affiliates