473,322 Members | 1,778 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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

Similar topics

16
by: noah | last post by:
Does PHP have a feature to associate Cookie sessions with a persistent database connection that will allow a single transaction across multiple HTTP requests? Here is how I imagine my process: I...
0
by: Patrick | last post by:
I'm working on a contact management application, and need a hand with one aspect... Here's what I want to create: ------------------------------------ A form split into two parts. There is a...
9
by: Mourad | last post by:
Hi All, Is it possible to create a Make Table query in access (2.0 and 2003) that creates the table into a SQL Server database? Following the steps: 1- Create New Query 2- Set Query Type as...
4
by: psyvanz | last post by:
Im a college student, my program is a computerized enrollment system, Im using MS ACCESS database in connecting my VB6 program form. Inside i'd created two tables, named "tbstudentinfo" and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.