Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Nov 2006
    Posts
    18

    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")
    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

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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

  3. #3
    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?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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

  5. #5
    Join Date
    Nov 2006
    Posts
    18

    Yeeeee-haah!!

    It works!! It works!! Thanks so much!!!!


  6. #6
    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!

  7. #7
    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
    %>

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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

  10. #10
    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!)

  11. #11
    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. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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

  13. #13
    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.

  14. #14
    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) & ")"

  15. #15
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try a

    Response.Write sql

    and see if the completed string looks correct. Is that all on one line?
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •