0

In the following code, when I close the Emails RecordSet I get an error saying

Operation not allowed when the object is closed

... why do I need to close the Leagues RecordSet (after a SELECT) but not the Emails RecordSet (after an UPDATE) ?!?

Call ConnectToDatabase

SQLstr = "SELECT [Id],[Name],[Session] From [Leagues] "
SQLstr = SQLstr & "WHERE Leagues.[Name] = '" & UCase(KAleague) & "';"

KA_RS_Leagues.Open SQLstr, KA_DB, adOpenDynamic, adLockOptimistic

SQLstr = "UPDATE Emails SET FixtureList = 1 "
SQLstr = SQLstr & "WHERE Emails.League = '" & KA_RS_Leagues!ID & "';"

KA_RS_Emails.Open SQLstr, KA_DB, adOpenDynamic, adLockOptimistic

KA_RS_Leagues.Close
KA_RS_Emails.Close <<< Error occurs here
braX
  • 11,506
  • 5
  • 20
  • 33
Gary Heath
  • 313
  • 1
  • 3
  • 15
  • Please DO NOT build up strings like this and execute them. This is wide open to sql injection. You need to read about, understand and start using parameterized queries before bobby tables comes to visit. http://bobby-tables.com/ – Sean Lange Feb 06 '18 at 14:48
  • This is only for my own use on my own PC, so I'm not worried about attacks, BUT, I do have a possible job coming up that will require me to write "proper" SQL in a production environment, so I will take a look at this, thanks you ... – Gary Heath Feb 06 '18 at 17:06
  • I can't get my head around this at all ... I am trying to manipulate the code found here (https://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i) but when I type Using cn as new ADODB.Connection(KADB_String) I get a Syntax Error on the "As" ... – Gary Heath Feb 06 '18 at 19:32
  • That's because you are looking at an example in vb.net but your are in VBA. They are different animals. The syntax is quite different. Haven't done this in VBA in a LONG time but google should provide plenty of examples. Make sure it is VBA though and not VB.NET. – Sean Lange Feb 06 '18 at 19:53
  • Thank you Sean, I googled VBA as part of my search and this came up, I've just noticed the VB.net Tag now you've mentioned it, that would account for the Syntax error then, DOH !!! – Gary Heath Feb 07 '18 at 07:42

1 Answers1

1

An UPDATE does not return any result set, and per the documentation:

It is not a good idea to use the Source argument of the Open method to perform an action query that does not return records because there is no easy way to determine whether the call succeeded. The Recordset returned by such a query will be closed. To perform a query that does not return records, such as a SQL INSERT statement, call the Execute method of a Command object or the Execute method of a Connection object instead.

Jeroen Mostert
  • 27,176
  • 2
  • 52
  • 85
  • As above, I need to get into some good habits in case I get this job, so thank you too :-) Now, I tried using the EXECUTE method but was getting an error, I can't remember exactly what it was but it was basically saying my SQL string was wrong, but I couldn't see why ... UPDATE Emails SET FixtureList = 1 WHERE Emails.League = '5'; – Gary Heath Feb 06 '18 at 16:54