ASP (Classic) Question Regarding SQL Queries

[clamum]

I'm writing classic ASP code and often have to do SQL queries of an Access database. I took this job over from another guy and the way the queries are written are as follows:

Set rsQuery = Server.CreateObject("ADODB.RecordSet")
strQuerySQL = "SELECT ot FROM atot"
rsQuery.Open strQuerySQL, Connection

Then you use the RecordSet object's MoveNext method and whatnot to go through the resulting records.

Then I came across another way of doing SQL queries like this:

strQuerySQL = "DELETE * FROM atot"
Connection.Execute(strQuerySQL)

Now, I am wondering which one is recommended? Neither one is harder to code or requires much more/less typing. I did notice that using the RecordSet.Open method allows you to use other methods like AddNew and such, where otherwise you would have to write an INSERT query. But, I am used to writing normal SQL queries and executing them as I've done a lot of that in PHP.

So, any thoughts? I suppose I'm leaning towards the RecordSet.Open method just because I can keep continuity in coding style, but I'm the only one who deals with the code and it doesn't matter to me. But, it might to the next person to have this job.

 

[Cerebus451]

If you are issuing a SELECT query, then you are likely expecting a record set with the results of the SELECT query, hence you use RecordSet.Open (if your query is a SELECT INTO then there would not be a result set). If you are issuing an INSERT/UPDATE/DELETE statement, you are merely asking the database to perform the selected operation and there typically will be no results returned, hence you just need to use Connection.Execute.

 

[Zontor]

RecordSet.Open has a few more options / parameters that can be assigned. Also note that the .Execute method always retuns a read-only, forward-only cursor if objects are returned.

Cerebus also makes some very good points.

A few more points since you're interested in style:

--Use stored procedures instead of inline SQL code.
--Look at using GetRows as you cycle through the recordsets instead of a WHILE type loop.
--Be sure you close your objects when done.