| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
11-29-06, 10:25
|
|
Registered User
|
|
Join Date: Nov 2006
Posts: 18
|
|
|
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")
Conn.Open DBConn
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.
Any assistance would be very much appreciated!
Thanks,
Katie
|
|
11-29-06, 13:36
|
|
Registered User
|
|
Join Date: May 2005
Location: Nevada, USA
Posts: 2,475
|
|
The value for a numeric field should not be surrounded by single quotes. If that's the actual number 104, try:
sql = "select * from AppData where (Project_ID = 104)"
__________________
Paul
|
|
11-29-06, 13:47
|
|
Registered User
|
|
Join Date: Nov 2006
Posts: 18
|
|
|
|
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?
|
|
11-29-06, 14:28
|
|
Registered User
|
|
Join Date: May 2005
Location: Nevada, USA
Posts: 2,475
|
|
I'm not clear on exactly what you've got, but you were on the right track in your original code. You just needed to drop the single quotes. If your original code was returning the correct ID, try:
sql = "select * from AppData where (Project_ID = " & CStr(104) & ")"
or perhaps you need a reference to the field selected by the user.
__________________
Paul
|
|
11-29-06, 14:46
|
|
Registered User
|
|
Join Date: Nov 2006
Posts: 18
|
|
|
Yeeeee-haah!!
It works!! It works!! Thanks so much!!!!
|
|
12-04-06, 07:57
|
|
Registered User
|
|
Join Date: Nov 2006
Posts: 18
|
|
|
Next step...
Now that this is working the next step is to pull in data from two tables in Access. The code currently reads thus:
set Conn= Server.CreateObject("ADODB.Connection")
DBConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("ITCDB.mdb")
Conn.Open DBConn
Project_ID = request.querystring("project_nbr")
sql = "select * from AppData, Comments where (Project_ID = " & CStr(Project_ID) & ") and (Project_ID = " & CStr(Project_ID) & ")"
set rst = Conn.Execute(SQL)
do While not rst.eof
Project_ID = rst("Project_ID")
projName = rst("projName")
Primary_ITC_Resource = rst("Primary_ITC_Resource")
Secondary_ITC_Resource = rst("Secondary_ITC_Resource")
Project_Contacts = rst("Project_Contacts")
Assist_Type = rst("Assist_Type")
Change_Requests = rst("Change_Requests")
Date1 = rst("Comments.Date1")
Comment1 = rst("Comments.Comment1")
Date2 = rst("Comments.Date2")
Comment2 = rst("Comments.Comment2")
Date3 = rst("Comments.Date3")
Comment3 = rst("Comments.Comment3")
Date4 = rst("Comments.Comments.Date4")
Comment4 = rst("Comments.Comment4")
Date5 = rst("Comments.Date5")
Comment5 = rst("Comment5")
Date6 = rst("Date6")
Comment6 = rst("Comment6")
rst.movenext
loop
However, the date and comment fields are actually in a second table in the same Access db. How can I alter this code so that it pulls the date and comment fields in from the second table?
Thanks in advance!
|
|
12-04-06, 08:37
|
|
Registered User
|
|
Join Date: Nov 2006
Posts: 18
|
|
|
Addendum
Tried this, but it doesn't work:  Any suggestions?
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 projName
dim Primary_ITC_Resource
dim Secondary_ITC_Resource
dim Project_Contacts
dim Assist_Type
dim Change_Requests
dim Date1,Date2,Date3,Date4,Date5,Date6
dim Comment1,Comment2,Comment3,Comment4,Comment5,Comme nt6
set Conn= Server.CreateObject("ADODB.Connection")
DBConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("ITCDB.mdb")
Conn.Open DBConn
Project_ID = request.querystring("project_nbr")
sql = "select * from AppData, Comments where (Project_ID = " & CStr(Project_ID) & ") and (Project_ID = " & CStr(Project_ID) & ")"
set rst = Conn.Execute(SQL)
do While not rst.eof
Project_ID = rst("AppData.Project_ID")
projName = rst("AppData.projName")
Primary_ITC_Resource = rst("AppData.Primary_ITC_Resource")
Secondary_ITC_Resource = rst("AppData.Secondary_ITC_Resource")
Project_Contacts = rst("AppData.Project_Contacts")
Assist_Type = rst("AppData.Assist_Type")
Change_Requests = rst("AppData.Change_Requests")
Date1 = rst("Comments.Date1")
Comment1 = rst("Comments.Comment1")
Date2 = rst("Comments.Date2")
Comment2 = rst("Comments.Comment2")
Date3 = rst("Comments.Date3")
Comment3 = rst("Comments.Comment3")
Date4 = rst("Comments.Date4")
Comment4 = rst("Comments.Comment4")
Date5 = rst("Comments.Date5")
Comment5 = rst("Comments.Comment5")
Date6 = rst("Comments.Date6")
Comment6 = rst("Comments.Comment6")
rst.movenext
loop
Set rst = Nothing
Conn.close
Set Conn= Nothing
%>
|
|
12-04-06, 11:58
|
|
Registered User
|
|
Join Date: May 2005
Location: Nevada, USA
Posts: 2,475
|
|
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.
__________________
Paul
|
|
12-04-06, 12:02
|
|
Registered User
|
|
Join Date: May 2005
Location: Nevada, USA
Posts: 2,475
|
|
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.
__________________
Paul
|
|
12-04-06, 12:27
|
|
Registered User
|
|
Join Date: Nov 2006
Posts: 18
|
|
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!)
|
|
12-04-06, 13:29
|
|
Registered User
|
|
Join Date: Nov 2006
Posts: 18
|
|
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.
<%
dim projName
dim Primary_ITC_Resource
dim Secondary_ITC_Resource
dim Project_Contacts
dim Assist_Type
dim Change_Requests
dim Date
dim Comment
set Conn= Server.CreateObject("ADODB.Connection")
DBConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("ITCDB.mdb")
Conn.Open DBConn
Project_ID = request.querystring("project_nbr")
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.Date, Comments.Comment _
from AppData INNER JOIN Comments ON AppData.Project_ID where (Project_ID = " & CStr(Project_ID) & ")
set rst = Conn.Execute(SQL)
do While not rst.eof
Project_ID = rst("Project_ID")
projName = rst("projName")
Primary_ITC_Resource = rst("Primary_ITC_Resource")
Secondary_ITC_Resource = rst("Secondary_ITC_Resource")
Project_Contacts = rst("Project_Contacts")
Assist_Type = rst("Assist_Type")
Change_Requests = rst("Change_Requests")
Date = rst("Date")
Comment = rst("Comment")
rst.movenext
loop
Set rst = Nothing
Conn.close
Set Conn= Nothing
%>
|
|
12-04-06, 13:42
|
|
Registered User
|
|
Join Date: May 2005
Location: Nevada, USA
Posts: 2,475
|
|
I don't recognize that as valid SQL. Did you copy it from a working query? It would normally be something like:
from AppData INNER JOIN Comments ON AppData.Project_ID = Comments.Project_ID where (Project_ID = " & CStr(Project_ID) & ")
Note the extra field after ON. And from the sound of it, I'd change the "INNER JOIN" to "LEFT JOIN"
__________________
Paul
|
|
12-04-06, 14:22
|
|
Registered User
|
|
Join Date: Nov 2006
Posts: 18
|
|
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.
|
|
12-04-06, 14:23
|
|
Registered User
|
|
Join Date: Nov 2006
Posts: 18
|
|
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) & ")"
|
|
12-04-06, 14:28
|
|
Registered User
|
|
Join Date: May 2005
Location: Nevada, USA
Posts: 2,475
|
|
Try a
Response.Write sql
and see if the completed string looks correct. Is that all on one line?
__________________
Paul
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Data type mismatch in criteria expression
