Ado controller SQL

[mihereteab]

Hi I am doing a college project on VB6 and I am having problems with using an adocontroller to take data from two different tables from a database which are linked together. I have tried this SQL query under the ADO controller recordsource but it dosent seem to work, help please.

SELECT Event.eventID, Event.performers, Event.event_date, Event.start_time, Event.finish_time, Event.genre, Event.VenueID, Venue.venueID, Venue.venue_name, Venue.city, Venue.seat_number
FROM Venue INNER JOIN Event ON Venue.venueID = Event.VenueID
WHERE (([where Venue].[venueID]=[Event].[VenueID]));

[dglienna]

Take a look at my sample here.

To solve your problem, you need 2 connection strings.

[mihereteab]

thanks for the help but I am still a little confused?

[dglienna]

Download the program, then read the article.

Then, install the program, and test it. Go back and read thru the article and follow along. Add breakpoints, to see WHAT happens, and WHEN it happens.

Inspect variables.

SQL isn't the same as VB6...

[mihereteab]

I still havent got it to work and by the way how can you get 2 connection strings

[DataMiser]

I am not sure what you mean when you say ADOControler. ?? Do you mean the ADO Control? If so I never saw a need to use it better to code it yourself.

That said ADO code allows you to join 2 or more tables and return data without a problem, only one connection is requried.

Also the where clause you have is not needed as the inner join takes care of matching the records in this case returning only records where the venueid exists in both tables.

[DataMiser]

A quick sample from a project I did using MYsql as a backend

Dim Cn As New Connection
Cn.CursorLocation = adUseClient
Cn.Open ConnectionString
Dim RS As New Recordset
RS.Open "select PackQty from FGInventory inner Join Products on Products.idPartSku=FGInventory.Products_idPartSku where FGInventory.idPackSN=" & MatSN, Cn, adOpenDynamic, adLockOptimistic

If RS.RecordCount > 0 Then
     MatQty = RS(0)
     RS.Close                       
Else
     'not found
     RS.Close
End If

Must include a reference to the ADO libary.
ConnectionString is a variable that contains the actual connection string for the database in question

[d.paulson]

This part looks weird and You shouldn't need it.

WHERE (([where Venue].[venueID]=[Event].[VenueID]));

[DataMiser]

I did not notice the [where venue] in there.

Is that actually the name of a table in the database?

If not then that is a problem for sure.

[mihereteab]

yup thanks for the replies Venue and Event are the names of the tables and I have tried to run the program without the line WHERE (([where Venue].[venueID]=[Event].[VenueID]));
but its linked but not properley but someone has suggested making an SQL query within the form rather than in the recordsource property of the ADO control, which i will try

[DataMiser]

I am not sure but could be that Event is a reserved word

This may be a problem

FROM Venue INNER JOIN Event

In some areas you are using the [] around the table name. I would suggest you do that on the portion above as well.

[DataMiser]

Try it like this and see what happens

SELECT [Event].eventID, [Event].performers, [Event].event_date, [Event].start_time, [Event].finish_time, [Event].genre, [Event].VenueID, Venue.venueID, Venue.venue_name, Venue.city, Venue.seat_number
FROM [Event] INNER JOIN [Venue] ON [Venue].venueID = [Event].VenueID

[dglienna]

Change the name of the field(s). Besides being reserved, it doesn't make a lot of sense

[mikesaga]

Another solution that makes me feel comfortable using two or more tables.
Make a view, it will integrate your database, it's very simple and fast performance unlike inner join technique.

-----------
Mike
http://visualbasic-source-code.com/

[mikesaga]

Here's the sample View in MS Access Database

'---Code
SELECT CATrans.RecordNo, CATrans.EmployeeNo, view_employeeInfo.Name, CATrans.DateCA, CATrans.Amount, CATrans.Reason
FROM CATrans LEFT JOIN view_employeeInfo ON CATrans.EmployeeNo = view_employeeInfo.EmployeeNo;

Please find the picture file attached.