command strings - best meyhods

[lenin]

Hi,
Can someone give me a definitive set of rules as regards what is the best strategy when using ADO. i.e. Do I use:

1. Connection strings to execute
2. command strings to execute.
3. Recordset object to execute.

I presumed the first two were relevant for doing things that did not return a recordset, and vice versa the third. however I always seem to get the syntax wrong, and have a headache searching for exactly what I want. for example to simply insert a record using a DSN, I thought the following should work, but I get a "Two Few Parameters" error, and CANNOT see the issue. Can some please advise.


Dim lcon_conn As ADODB.Connection
Dim lc_command As ADODB.Command
Dim ls_SQL As String

Set lcon_conn = New ADODB.Connection

lcon_conn.Open "DSN=test1"

ls_SQL = "Insert into customer(name) values(""gerry's"")"
Set lc_command = New ADODB.Command
lc_command.ActiveConnection = lcon_conn
lc_command.CommandText = ls_SQL
lc_command.CommandType = adCmdText

lc_command.Execute


Also the same error with the connection string.

[Clunietp]

Change this:

Insert into customer(name) values(""gerry's"")

To this:

Insert into customer (name) values ("gerry''s")

You have to replace the 1 single quote to 2 single quotes

=========================================================

My personal rules for the ADO objects:
----------------------------------------

Use the Connection.Execute method when:
- I have to execute an SQL statement 1 time (like an update or delete)
- I want to return a read only, forward only recordset

Use the Command.Execute method when:
-I have a boatload of parameters to a query or stored procedure, and using the parameters collection keeps it organized
-I have to retrieve a return value(s) or output parameter(s) from a stored procedure
-I am executing an SQL statement multiple times (like in a loop) -- If I set the PREPARED property to TRUE, the DBMS (SQL Server) will create a temporary stored procedure to speed up the batch

Use the Recordset.Open method when:
-I need more control over the recordset (scrolling, searching, client or server cursor, recordcount, add/update/delete, etc)

If anyone has anything to add or change, please let us know!

Tom