469,646 Members | 1,248 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,646 developers. It's quick & easy.

Searching the database of adodc control joint by various Tables

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?
Dec 5 '06 #1
4 1768
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
Dec 5 '06 #2
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. 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"
  3.         DSearch = DSearch & " WHERE VEH_DESCRIPTION.VEHICLE_NO = '" & VehNo & "'"
  4.  
Dec 5 '06 #3
I have made the code better for your understanding below

Expand|Select|Wrap|Line Numbers
  1.  
  2. DSearch = "SELECT DISTINCT H_DUTY_PER.H_DUTY_PER_ISST, 
  3. H_DUTY_PER.H_DUTY_PER_EXPT, H_PER.H_PER_ISST, 
  4. H_PER.H_PER_EXPT, INS_CERT.INS_CERT_ISST, 
  5. INS_CERT.INS_CERT_EXPT, MOB_ADV.MOB_ADV_ISST, 
  6. MOB_ADV.MOB_ADV_EXPT, RD_WORTH.RD_WORTH_ISST,
  7. RD_WORTH.RD_WORTH_EXPT, VEH_DESCRIPTION.VEHICLE_NO,
  8. VEH_DESCRIPTION.ENGINE_NO, VEH_DESCRIPTION.CHASIS_NO, VEH_LIC.VEH_LIC_ISST, VEH_LIC.VEH_LIC_EXPT, 
  9. VEH_TYPE.VEHICLE_TYPE From H_DUTY_PER, H_PER, INS_CERT, 
  10. MOB_ADV, RD_WORTH, VEH_DESCRIPTION, VEH_LIC, VEH_TYPE 
  11. WHERE VEH_DESCRIPTION.VEHICLE_ID=VEH_TYPE.VEHICLE_ID And VEH_DESCRIPTION.VEHICLE_NO=VEH_LIC.VEHICLE_NO AND 
  12. VEH_DESCRIPTION.VEHICLE_NO=H_DUTY_PER.VEHICLE_NO AND 
  13. VEH_DESCRIPTION.VEHICLE_NO=H_PER.VEHICLE_NO AND 
  14. VEH_DESCRIPTION.VEHICLE_NO=INS_CERT.VEHICLE_NO AND 
  15. VEH_DESCRIPTION.VEHICLE_NO=MOB_ADV.VEHICLE_NO AND 
  16. VEH_DESCRIPTION.VEHICLE_NO=RD_WORTH.VEHICLE_NO"
  17. DSearch = DSearch & " WHERE VEH_DESCRIPTION.VEHICLE_NO = '" & VehNo & "'"
  18.  
Dec 5 '06 #4
The type of code you need to retrieve records using ADODB data objects is as follows

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim Conn as ADODB.connection
  3. Dim Rs as ADODB.Recordset
  4. DIim SQL as string
  5. conn.connectstring = "The string needed to connect to your database"
  6. conn.connect
  7.  
  8. ' put the code to build your SQL string in the variable SQL here
  9.  
  10. rs.open SQL,conn,adopendynamic
  11. if rs.bof = true then
  12.  'Put your code for handling no records returned here
  13. end if
  14.  
  15. rs.movefirst
  16.  
  17.  ' Put your code for handling a record here. Fields in the record are referencedwith the syntax rs!Fieldname
  18.  
  19.  
  20.  
use the vb help to look up the syntax & examples for ADODB.Connection and ADODB.Recordset objects

good luck
Dec 5 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.