-
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.
-
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
