Searching the database of adodc control joint by various Tables

[nkechifesie]

I have an adodc control that is build with several tables using the select command. I need to search for a record on the adodc, is it possibles? If yes, how?

[Andrew Thackray]

Without seeing your code it is difficult to reply.

However in general there are two methods to do this.

The first is to include a WHERE clause in the ADODC select statement that only causes it to select the records you want. You can do this programatically by buiding the SQL code and then setting the ADODC command to it. This an example of buiding the SQL given that you have two tables, TableA and TableB joined on a field 'RELATED' and TableA has a field called 'COLOR' containing the value you want to look for.

[code]

dim SQL as string
dim Col as string

Col = inputbox("Enter the color you want to find")

SQL = "Select * from TableA inner join TableB on TableA.RELATED = TableB.RELATED "
SQL = SQL & "WHERE TableA.COLOR = '" & Col & "' "


[code]

in this code the variable SQL will contain the select statement that you can set in your ADODC control to return a specific recordset. Note the use of single & double quotes in the code when building the SQL statement

The other technique is to open the recordset and then use the SEEK method on the recordset. However this is cumbersome as you have to retrieve a full table and then search through it

[nkechifesie]

Thank you very much, I have done that but am stuck on how to connect the sql to the adodc for it to open it.I didnt code my Adodc, so am not familiar with coding it.
this is the code i need to connect to my adodc1

 
DSearch = "SELECT DISTINCT H_DUTY_PER.H_DUTY_PER_ISST, H_DUTY_PER.H_DUTY_PER_EXPT, H_PER.H_PER_ISST, H_PER.H_PER_EXPT, INS_CERT.INS_CERT_ISST, INS_CERT.INS_CERT_EXPT, MOB_ADV.MOB_ADV_ISST, MOB_ADV.MOB_ADV_EXPT, RD_WORTH.RD_WORTH_ISST, RD_WORTH.RD_WORTH_EXPT, VEH_DESCRIPTION.VEHICLE_NO, VEH_DESCRIPTION.ENGINE_NO, VEH_DESCRIPTION.CHASIS_NO, VEH_LIC.VEH_LIC_ISST, VEH_LIC.VEH_LIC_EXPT, VEH_TYPE.VEHICLE_TYPE From H_DUTY_PER, H_PER, INS_CERT, MOB_ADV, RD_WORTH, VEH_DESCRIPTION, VEH_LIC, VEH_TYPE WHERE VEH_DESCRIPTION.VEHICLE_ID=VEH_TYPE.VEHICLE_ID And VEH_DESCRIPTION.VEHICLE_NO=VEH_LIC.VEHICLE_NO AND VEH_DESCRIPTION.VEHICLE_NO=H_DUTY_PER.VEHICLE_NO AND VEH_DESCRIPTION.VEHICLE_NO=H_PER.VEHICLE_NO AND VEH_DESCRIPTION.VEHICLE_NO=INS_CERT.VEHICLE_NO AND VEH_DESCRIPTION.VEHICLE_NO=MOB_ADV.VEHICLE_NO AND VEH_DESCRIPTION.VEHICLE_NO=RD_WORTH.VEHICLE_NO"
        DSearch = DSearch & " WHERE VEH_DESCRIPTION.VEHICLE_NO = '" & VehNo & "'"
 

[nkechifesie]

I have made the code better for your understanding below

 
DSearch = "SELECT DISTINCT H_DUTY_PER.H_DUTY_PER_ISST, 
H_DUTY_PER.H_DUTY_PER_EXPT, H_PER.H_PER_ISST, 
H_PER.H_PER_EXPT, INS_CERT.INS_CERT_ISST, 
INS_CERT.INS_CERT_EXPT, MOB_ADV.MOB_ADV_ISST, 
MOB_ADV.MOB_ADV_EXPT, RD_WORTH.RD_WORTH_ISST,
RD_WORTH.RD_WORTH_EXPT, VEH_DESCRIPTION.VEHICLE_NO,
VEH_DESCRIPTION.ENGINE_NO, VEH_DESCRIPTION.CHASIS_NO, VEH_LIC.VEH_LIC_ISST, VEH_LIC.VEH_LIC_EXPT, 
VEH_TYPE.VEHICLE_TYPE From H_DUTY_PER, H_PER, INS_CERT, 
MOB_ADV, RD_WORTH, VEH_DESCRIPTION, VEH_LIC, VEH_TYPE 
WHERE VEH_DESCRIPTION.VEHICLE_ID=VEH_TYPE.VEHICLE_ID And VEH_DESCRIPTION.VEHICLE_NO=VEH_LIC.VEHICLE_NO AND 
VEH_DESCRIPTION.VEHICLE_NO=H_DUTY_PER.VEHICLE_NO AND 
VEH_DESCRIPTION.VEHICLE_NO=H_PER.VEHICLE_NO AND 
VEH_DESCRIPTION.VEHICLE_NO=INS_CERT.VEHICLE_NO AND 
VEH_DESCRIPTION.VEHICLE_NO=MOB_ADV.VEHICLE_NO AND 
VEH_DESCRIPTION.VEHICLE_NO=RD_WORTH.VEHICLE_NO"
DSearch = DSearch & " WHERE VEH_DESCRIPTION.VEHICLE_NO = '" & VehNo & "'"
 

[Andrew Thackray]

The type of code you need to retrieve records using ADODB data objects is as follows

 
Dim Conn as ADODB.connection
Dim Rs as ADODB.Recordset
DIim SQL as string
conn.connectstring = "The string needed to connect to your database"
conn.connect
 
' put the code to build your SQL string in the variable SQL here
 
rs.open SQL,conn,adopendynamic
if rs.bof = true then
 'Put your code for handling no records returned here
end if
 
rs.movefirst
 
 ' Put your code for handling a record here. Fields in the record are referencedwith the syntax rs!Fieldname
 
 
 

use the vb help to look up the syntax & examples for ADODB.Connectio n and ADODB.Recordset objects

good luck