Unanswered: Data type mismatch in criteria expression
ARGHHH!! I've read through many, many posts with this error, but still can't seem to get my code working, continually getting the "Data type mismatch in criteria expression" error. My ASP page has the following code:
set Conn= Server.CreateObject("ADODB.Connection")
DBConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("ITCDB.mdb")
Project_ID = request.querystring("project_nbr")
sql = "select * from AppData where (Project_ID = '" & CStr(104) & "')"
set rst = Conn.Execute(SQL)
If the error message is to be believed, the last line is the offending piece.
Also, note that the field/column "Project_ID" is an auto-number field.
Hi Paul...that actually works...sort of. My fault for not mentioning that the Project_ID number is actually an array, if that's the right word. User selects a project from a list on the website. Then a window pops up, with a form. It has worked correctly to this point, but I've been unable to get the form to populate...and of course it should populate from the table row associated with the project the user selected. So, I can't hard code the Project_ID. But this is the first time I've seen the form populate with anything, so at least I know I'm on the right track.
How can I write that line so that it selects the appropriate data?
Note that the tables are linked by "Project_ID", an auto-number field; the Primary Key in table AppData; Foreign Key in table Comments.
dim Comment1,Comment2,Comment3,Comment4,Comment5,Comme nt6
I would think that pulls the data from both tables, but without a join it's probably pulling a greater number of records than expected. You need a join on Project_ID. The simplest way to see how to do it is to create a query in the Access db that pulls the records as you want to see them, then view the SQL of that query and use the same structure here.
Plus I just noticed you had Project_ID in the WHERE clause twice, which I assume you were doing because it's in both tables. You only need it once, which combined with the proper join on the tables should return the correct records.
Plus I should mention that a table with repeating fields (date1, date2...) probably violates normalization rules.
Your first message: Good idea...I'm about to give that a try.
Your second message: Yes, I've pulled out the second reference to Project_ID in the WHERE clause.
Thanks for the info...I'll let you know if it works out (or be back w/ more questions!)
Nope, doesn't work. Let me give you a better idea of what I'm trying to accomplish. We have a web site that lists the projects/applications we are currently working on. Users can double-click a project, which opens a new page that displays data about the project (from the "AppData" table) as well as status updates/comments (from the "Comments" table). There may be one, none or several updates (or comments), each of which has a date.
The Comments table has just four columns; Project_ID (which is the foreign key to the AppData table's column of the same name), proj_Name (the AppData table has the same column...I'm not sure why), Date, and Comment.
Here's the code as it appears now, adapting the SQL from a query in Access that did what I wanted. The data from the AppData table is coming in fine, but I'm not getting anything from the Comments table.
I've changed the last piece of the Select statement as you suggested; tried as INNER JOIN and LEFT JOIN...to no avail. Also, I said earlier that I was getting the data from the AppData table, but not the Comments table. Not true...I was pointing to the wrong thing. I'm not getting any data, or the page, for that matter. The error message I'm getting is "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."
I originally had a column called "Date", and I thought that might be a reserved word and causing the problem, so I changed it to "Comment_Date". Still getting the same error.
Here's the statement as it now appears: sql = "select AppData.Project_ID AS AppData_Project_ID, AppData.projNum, AppData.projName AS AppData.projName, AppData.Primary_ITC_Resource, AppData.Secondary_ITC_Resource, AppData.Project_Contacts, AppData.Assist_Type, AppData.Change_Requests, Comments.Project_ID AS Comments_Project_ID, Comments.projName AS Comments_projName, Comments.Comment_Date, Comments.Comment from AppData INNER JOIN Comments ON AppData.Project_ID =Comments.Project_ID where (Project_ID = " & CStr(Project_ID) & ")"