Results 1 to 12 of 12
  1. #1
    Join Date
    May 2004
    Location
    Dubai
    Posts
    40

    Unanswered: selecting 2 table at same time / please help

    Hi
    I want select 2 table at same time
    this is my code and my first table
    Code:
      strSQL = "SELECT TOP "&Cint(intTopCount)& " * "
      strSQL = strSQL & "FROM T_WEBLOG "
      strSQL = strSQL & "WHERE b_published = true "
      strSQL = strSQL & "ORDER BY b_date DESC, b_time DESC;"
    the above code work very good
    but I want select second table

    I added this code to above code
    Code:
      strSQL = strSQL & "SELECT T_IMAGES.* "
      strSQL = strSQL & "FROM T_IMAGES "
    so this is my code
    Code:
     strSQL = "SELECT TOP "&Cint(intTopCount)&" * "
     strSQL = strSQL & "FROM T_WEBLOG "
     strSQL = strSQL & "WHERE b_published = true "
     strSQL = strSQL & "ORDER BY b_date DESC, b_time DESC;"
     strSQL = strSQL & "SELECT T_IMAGES.* "
     strSQL = strSQL & "FROM T_IMAGES "
    but after I added second code to it
    I get this error
    ============
    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [Microsoft][ODBC Microsoft Access Driver] Characters found after end of SQL statement.

    /functions.asp, line 71

    ===========


    whats the problem
    Please help me I need it

    Thanks
    Last edited by farshad; 10-23-04 at 13:52.

  2. #2
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    A ; marks the end of a SQL statement, so if Access finds characters after a ; it can't run the SQL. Don't concatenate this portion:

    Code:
    strSQL = strSQL & "SELECT T_IMAGES.* "
    strSQL = strSQL & "FROM T_IMAGES "
    after the first SQL statement. If it's a separate query, use a different variable to store the string:

    Code:
    strSQL2 = strSQL2 & "SELECT T_IMAGES.* "
    strSQL2 = strSQL2 & "FROM T_IMAGES "

  3. #3
    Join Date
    May 2004
    Location
    Dubai
    Posts
    40
    Hi
    But No this is possible to add two query in with one variable
    look at this , this is a portion of one of pages in WEBWIZ FORUM
    I want some thing like this

    Look there is two SELECT

    ========================
    strSQL = "SELECT " & strDbTable & "Forum.Forum_name, " & strDbTable & "Forum.Password, " & strDbTable & "Forum.Forum_code, " & strDbTable & "Topic.* "
    strSQL = strSQL & "FROM " & strDbTable & "Category, " & strDbTable & "Forum, " & strDbTable & "Topic "
    strSQL = strSQL & "WHERE ((" & strDbTable & "Category.Cat_ID = " & strDbTable & "Forum.Cat_ID AND " & strDbTable & "Forum.Forum_ID = " & strDbTable & "Topic.Forum_ID) AND (" & strDbTable & "Topic.Last_entry_date > " & strDatabaseDateFunction & " - " & dblActiveFrom & "))"
    strSQL = strSQL & " AND (" & strDbTable & "Forum.[Read] <= " & intForumGroupPermission & " OR (" & strDbTable & "Topic.Forum_ID IN ("
    strSQL = strSQL & " SELECT " & strDbTable & "Permissions.Forum_ID "
    strSQL = strSQL & " FROM " & strDbTable & "Permissions "
    strSQL = strSQL & " WHERE " & strDbTable & "Permissions.Author_ID=" & lngLoggedInUserID & " OR " & strDbTable & "Permissions.Group_ID = " & intGroupID & " AND " & strDbTable & "Permissions.[Read]=TRUE))"
    strSQL = strSQL & " )"
    strSQL = strSQL & "ORDER BY " & strDbTable & "Category.Cat_order ASC, " & strDbTable & "Forum.Forum_Order ASC, " & strDbTable & "Topic.Last_entry_date DESC;"

    ==========================

  4. #4
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    you can´t put two queries in one string.

    if you look good at the webwiz forum example, you will see 2x select, but actually the whole example is 1 query.

    the second select is related to a IN (select .......), and as mentioned by BummerJeff the ; that indicates the end of the query and there is only one at the end of the webwiz query example.

    the error ... characters after end indicates that the ; is inside the string and not at the end.

  5. #5
    Join Date
    May 2004
    Location
    Dubai
    Posts
    40
    Hi

    Thanks
    anyway I want Use two table in one query
    with this code it work fine

    strSQL = "SELECT T_WEBLOG.*, T_IMAGES.* "
    strSQL = strSQL & "FROM T_WEBLOG, T_IMAGES "
    strSQL = strSQL & "WHERE b_published = true "
    strSQL = strSQL & "ORDER BY b_date DESC, b_time DESC;"

    but I want select top 5 records in one of tables for example
    T_WEBLOG how can I add SELECT TOP 5 to above code?

    I have to use one variable

    Set Rs = Server.CreateObject("ADODB.Recordset")

    because I want to use LOOP function and I have to use one variable

    NOTICE : I want it select top 5 records Just From First Table
    ==

    ?????

  6. #6
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    Perhaps you could tell us the structure of your 2 tables, the relationship between the two and what data you want to retrieve with your query, because the SQL in your last post doesn't join the two tables on a field...a little clarification might allow us to better understand what you're trying to do.

    If you want to select the top 5 records from just the first table, then you'd probably need a separate query on just that table.

  7. #7
    Join Date
    May 2004
    Location
    Dubai
    Posts
    40
    Hi
    Thanks

    Yes I want it Just select top 5 from first table I can`t use 2 query because I want use

    Do
    Loop
    function and I can`t use 2 query
    I must Use 1 query , what do you think
    is there any way to do this?

    This is fields from table 1 T_WEBLOG

    T_WEBLOG.id, T_WEBLOG.b_headline, T_WEBLOG.b_content, T_WEBLOG.b_date, T_WEBLOG.b_year, T_WEBLOG.b_month, T_WEBLOG.b_time, T_WEBLOG.b_published, T_WEBLOG.b_author, T_WEBLOG.b_link, T_WEBLOG.image, T_WEBLOG.DownloadURL, T_WEBLOG.DownloadText, T_WEBLOG.WebsiteURL, T_WEBLOG.WebsiteText
    FROM T_WEBLOG;


    and this is fields from table 2 T_IMAGES

    T_IMAGES.imageid, T_IMAGES.b_imagename, T_IMAGES.b_imageurl
    FROM T_IMAGES;


    Thanks

    Thanks

  8. #8
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    To query the two together, you need a common field to join on. From the structure of T_WEBLOG, I would guess it's T_WEBLOG.image. What field in T_IMAGES is this equivalent to (imageid, b_imagename or b_imageurl?) and what fields do you want in your query?

  9. #9
    Join Date
    May 2004
    Location
    Dubai
    Posts
    40
    Hi
    Yes its T_WEBLOG.image and imageid is equivalent for it,
    I want add this code

    If Rs("image") = Rs("imageid") Then

    and I want add this between a Loop function
    and also I want it select top 5 record in Table T_WEBLOG

    thanks

  10. #10
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    Try this:

    SELECT TOP 5 T_WEBLOG.*, T_IMAGES.* FROM T_WEBLOG INNER JOIN T_IMAGES ON T_WEBLOG.image = T_IMAGES.imageid WHERE T_WEBLOG.b_published=True ORDER BY T_WEBLOG.b_date DESC, T_WEBLOG.b_time DESC;

  11. #11
    Join Date
    May 2004
    Location
    Dubai
    Posts
    40
    Hi

    I added that code but I get error!!!

    ??

  12. #12
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    Can you post the entire code you are using? Also, what exact error are you getting?

Posting Permissions

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