1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Very Ugly SQL statement. Help Needed.

Discussion in 'C#' started by devmonk, Sep 29, 2006.

  1. #1
    I'll try to make this as brief as possible. I am having trouble getting this SQL statement to work. Any suggestions would be appriciated. I am using Classic ASP with an Access Backend.

    The object:
    The user will submit a productid and stateid through a form to find a list of stores in the given state that sells the given product.

    The DB schema:
    wtbProducts (table)
    productID
    Alias

    wtbProductWarehouses (table)
    productID
    warehouseID

    wtbWarehouseStores (table)
    warehouseID
    storeID

    wtbStoreStates (table)
    storeID
    stateID

    wtbStores (table)
    storeID
    Alias (name of store)
    WebAddress

    Other tables which I don't think are needed in the SQL
    wtbWarehouses (table)
    warehouseID
    Alias
    CustomerNumber

    wtbStates (table)
    stateID
    Code
    Alias
    Show

    The data I want returned:
    Alias and WebAddress from wtbStores

    The code that I have written:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
    <!--#include  virtual="/Connections/Conn2.asp" -->
    <%
        Dim conn, rs
        Set conn = Server.CreateObject("ADODB.Connection")
        Set rs = Server.CreateObject("ADODB.Recordset")
        conn.Open strConnect
       
        pid = Request.Form("productid")
        sid = Request.Form("stateid")
       
        SQL = " SELECT * FROM wtbStores " _
            & " WHERE storeID IN ( SELECT * " _
            & "                    FROM wtbStoreStates " _
            & "                    WHERE stateID = " & sid & ") " _
            & " AND storeID IN   ( SELECT * FROM wtbWarehouseStores " _
            & "                    WHERE warehouseID IN ( SELECT * " _
            & "                                              FROM wtbProductWarehouses " _
            & "                                              WHERE productID = " & pid & ")) "
           
        Set rs = conn.Execute(SQL)
    %>
    Code (markup):

    The Error that I am getting:
    Microsoft JET Database Engine error '80040e14'

    Syntax error. in query expression 'storeID IN ( SELECT * FROM wtbStoreStates WHERE stateID = ) AND storeID IN ( SELECT * FROM wtbWarehouseStores WHERE warehouseID IN ( SELECT * FROM wtbProductWarehouses WHERE productID = ))'.

    /getStores.asp, line 21


    Any suggestions?

    Thanks,

    Jason
     
    devmonk, Sep 29, 2006 IP
  2. devmonk

    devmonk Peon

    Messages:
    9
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Sorry. I forgot to explain the product/warehouse/store relationship...

    Warehouses order different products. This relationship is set in wtbProductWarehouses. Stores then can order any of the products that a Warehouse carries. If a Warehouse does not carry a product the store can not order it. The relationship of stores to Warehouses is set in wtbWarehouseStores. Thus in my thinking we have to use the productid submitted from the form to find warehouses which carry that product. Then we find which stores subscribe to those warehouses and then finally figure out which stores are in the stateid provided from the form.

    I hope this explains the mess.

    Thanks
     
    devmonk, Sep 29, 2006 IP
  3. vectorgraphx

    vectorgraphx Guest

    Messages:
    545
    Likes Received:
    16
    Best Answers:
    0
    Trophy Points:
    0
    #3
    try adding a response.write line just before your " Set rs = conn.Execute(SQL)" line to display your SQL statement to the browser. this should give you an idea as to what's going on. Your logic looks pretty sound to me on your sql statement, however if it were me, i'd think about using joins. Join statements, to me, are FAR less complicated than where... in statements.

    for example, try something like:

    sql = "select * from wtbStores inner join"
    sql = sql & " (wtbStoreStates inner join"
    sql = sql & " (wtbWarehouseStores inner join wtbproductWarehouses"
    sql = sql & " on wtbWarehouseStores.WarehouseID = wtbproductWarehouses.WarehouseID)"
    sql = sql & " on wtbStoreStates.StoreID = wtbWarehouseStores.StoreID)"
    sql = sql & " on wtbStores.StoreID = wtbStoreStates.StoreID"
    sql = sql & " where wtbStoreStates.stateID = " & sID
    sql = sql & " and wtbProductWarehouses.productID = " & pID

    should do pretty much what you're trying to accomplish here, only in a bit more orderly fashion with one query instead of several embedded queries.

    let me know how it turns out.

    EDIT:
    i edited my query to make it a little bit prettier.

    VG
     
    vectorgraphx, Sep 29, 2006 IP
  4. pushkar

    pushkar Peon

    Messages:
    125
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #4
    I think ur Request can't be done.
    I mean
    pid = Request.Form("productid")
    sid = Request.Form("stateid")

    are empty.

    Your current page can't get requested value of PID,SID.It shows Blank."stateID= & productID =".

    So ur post data can't be Requested on this page.

    So that u r getting this ERROR.

    Just check that the variables value u r requesting is typed proper OR not.

    Hope u can get Solution.
     
    pushkar, Sep 30, 2006 IP