My main problem is connection between controls and database. First I tried using SQL only but can't - I typed the SQL statement in the "data source" property of each control and it seems not working. Later I use SQL in ADO (I already do the Dim conn as new ADODB.Connection... thing) but still the controls didn't detect the ADO - the data source property list is still empty. Only when I put an ADODC (even when the ADODC is blank) to the form the controls can detect it and allow me to choose it in the data source property list.
This is one of the problems with bound controls. They only work in design mode if you have a DataControl (ADODC or similar). And the datasource is NOT a SQL statement, but a data provider (in most cases).
Furthermore some controls will only allow the datasource to be a DataControl but most still allow the source to be any recordset provider.
The datasource of any bound control object is normally a recordset provider.
Example:
one form with a ADODC control, a ADODB reference (code wise) and any bound control.
In design mode if you wish to "see" the fields you need to assign ADOCD to the datasource, and then you can select the field.
If you use direct coding instead you could do like this.
Set adoSelectComm = New ADODB.Command
adoSelectComm.ActiveConnection = DBConn
adoSelectComm.CommandType = adCmdStoredProc
adoSelectComm.CommandText = "SP_SELECT_ALL_cities"
RS.Open adoSelectComm, , adOpenForwardOnly, adLockReadOnly
set myboundcontrol.datasource = RS
So the above code will create a new adodb command object, will execute it (on this case it will execute a stored procedure called "SP_SELECT_ALL_cities") and then it will tell the bound control that it's datasource object is the recordsetobject "RS"
where RS = ADODB.recordset, dbconn = ADODB.connection.
"I personally don't use ADODC unless absolutelly required to do so... ADOCD... has plenty of problems and is also because is requires bound controls(in fact I only use them on listboxes)."
Does this mean there is something that ADO cannot handle by itself? "Unless absolutely required" implies that ADODC MUST be involved when dealing with controls?
ADO can handle anything, the problem is that some controls need a Data control being it ADO/RDO/DAO, to work.
"SQL is not a connection method, but it is/can be used with any of the other methods." One of my books said SQL can work by itself replacing ADO and is even better, since ADO deals with recordset only one by one but SQL is not... but it did not talk about it any further. I wish to know if what it said is true.
If you use a ADO recordset you will be restricted somehow on how you deal with the recordset. e.g. you can only update one at the time. But with plain SQL you can do "update my_table set fld1=a, fld2=b where my_fild = "abc" and this can potentially update thousands of rows in one statement.
But you can use the same SQL with ADO e.g. you could execute the above with either the connection or the command object.
e.g. my_adodb_connection.open or my_adodb_command.execute
Regards
Frederico Fonseca
SysSoft Integrated Ltd
