The only columns that are similar between the two tables are site_code. I tried:

query = "SELECT sites_info.site_code, sites_training.site_code, site_info.site_name FROM sites_training, sites_info WHERE"

And got this error:

SELECT sites_info.site_code, sites_training.site_code, site_info.site_name FROM sites_training, sites_info WHERE sites_training.site_ta-pdth LIKE '%Y%' AND sites_info.site_code <> 0 ORDER BY sites_info.site_name ASC
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 3.

/Portal/Portal/Misc/Practicum/practicum2/admin_sites-search_results.asp, line 62

The only other thing I can think to do would be doing a select case from the sites_training that satisfys the search and then doing an additional select from the sites_info table that would allow me to find the records that match. I do not think that this is probably the best way, but it is the only other thing I can think of. Any ideas?

Michael W. Vollmer

I think the easiest way to solve this problem, is to paste the result of the Response.Write statement in a Microsoft Access query designer in your database.

Access is a lot smarter in spotting errors than ADO / ASP. It may hint on where the problem is. If you know the query works correctly , you can put it back in your ASP page.

You may want to consider defining ADO constants for your code, instead of using magic numbers like 3 to indicate the CursorType. Take a look here: http://www.adopenstatic.com/faq/800a0bb9step2.asp for more details.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

If I put it in Access as:

SELECT *
FROM sites_training, sites_info
WHERE [sites_training].[site_ta-macl] Like '%Y%' And [sites_info].[site_code]<>0
ORDER BY [sites_info].[site_name];

it works perfectly. But I had to add the [] around the site_ta-macl. Before I put it in Access asked me to enter a Parameter Value. Is it reading site_ta-macl as site_ta minus macl? and if so is there a way to work around this without changing all of my field names? Thanks again guys!

Michael W. Vollmer

I tried changing site_ta-macl to site_ta_macl in both the database and in the seach form and I am still getting this error:

SELECT * FROM sites_training, sites_info WHERE sites_training.site_ta_macl LIKE '%Y%' AND sites_info.site_code <> 0 ORDER BY sites_info.site_name ASC


Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

/Portal/Portal/Misc/Practicum/practicum2/admin_sites-search_results.asp, line 62


Even though it works just fine in Access. I really appreciate everyone's help on this. I just do not what else could be causing it.

Michael W. Vollmer

Yes, I think that is indeed the problem. Access does allow a minus character in a columname, but it will automatically put brackets around the column name when you design a query. Unfortunately, the driver does not add those, so the query fails. The only way to work around is without changing the column names, is to enclose all offending columns in brackets.

Personally, I always recommend not to use spaces, minus characters, underscores etc, but to use CamelCasing in stead, for the reasons you have now discovered. You may also opt not to repeat the table name in the column name. User.ID is a much more natural way to refer to the ID of a user than User.UserID, although this is a matter of personal preference.

One more thing: now that you are changing some parts of your page anyway, you may want to update your connection string as the one you're using is a bit outdated. Check here for a more recent version.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Okay, I added [] to each of the names and everything seems to be working now. Thanks again everybody for your contributions to this issue. I really appreciate everyone going out of their way to help me. You guys ROCK!!! Thanks again.

Michael W. Vollmer

Hi Michael,

Before you go and change your table and column names in your database, take a look at the following page:

http://support.microsoft.com:80/supp.../Q109/3/12.asp

This page lists all the reserved (key)words in Access, including special characters. This might help in avoiding similar problems in the future.

Regards,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.