none
SQL Select Statement not retrieving data RRS feed

  • Question

  • Hi all,

    I am trying to retrieve data from a remote server database. The code as follows:

       <Set rs = cnn.Execute("SELECT [SorDetail].MWarehouse, [SorMaster].OrderStatus, [ArCustomer].CustomerOnHold, [SorMaster].SalesOrder, " _
        & "[SorMaster].CustomerPoNumber, [SorMaster].CustomerName, [SorDetail].MStockCode, [SorDetail].MStockDes, [SorDetail].MOrderQty, " _
        & "[SorDetail].MBackOrderQty, [SorMaster].ShipAddress1, [SorMaster].ShipAddress2, [SorMaster].ShipAddress3, [SorMaster].ShipAddress4, " _
        & "[SorDetail].MShipQty, [SorMaster].OrderDate " _
        & "FROM SorDetail INNER JOIN (ArCustomer INNER JOIN SorMaster ON [ArCustomer].Customer = [SorMaster].Customer) ON " _
        & "[SorDetail].SalesOrder = [SorMaster].SalesOrder " _
        & "WHERE ((([SorDetail].MWarehouse)='49') AND (([SorMaster].OrderStatus)<>'*' And ([SorMaster].OrderStatus)<>'9' And " _
        & "([SorMaster].OrderStatus)<> '\') AND (([SorMaster].CustomerPoNumber) Not Like '*QUOT*') AND " _
        & "(([SorDetail].MBackOrderQty)>0)) OR ((([SorDetail].MShipQty)>0)) " _
        & "ORDER BY [SorMaster].CustomerName")>

    All works 100%, except that the Number and Date fields are not retrieved. This is the MOrderQty, MBacOrderQty, MShipQty and the OrderDate fields are not retrieved.

    Am I doing something wrong?Thanks

    Deon

    Thursday, March 10, 2016 1:52 PM

Answers

  • Hi Stefan,

    I got the problem solved. I omitted the "rs!" part in front of the field name when adding the data to the Combobox control.

    All is working fine now.

    Thanks for the assistance.

    • Marked as answer by Deon SA Friday, March 11, 2016 8:05 AM
    Friday, March 11, 2016 8:05 AM

All replies

  • Please define "Number and Date fields are not retrieved".

    You'll get what you're asking for in the SELECT's column list. Thus those columns are part of the result set.

    So the questions are:

    1) Does the data really exists in the base tables? Are you querying these tables? Or are queries involved, which hide this information?

    2) As it is a ASP site: Do you connect to the correct database file? Does is concurrency an issues?

    3) How do you retrieve the values on your page? Or, how did you recognize, that your don't "retrieve" values? Are they correctly bound to controls?


    p.s. Are you sure that this is not an ASP question? In this case the https://forums.asp.net/ are the better place for your question.
    Thursday, March 10, 2016 3:27 PM
  • >>>All works 100%, except that the Number and Date fields are not retrieved. This is the MOrderQty, MBacOrderQty, MShipQty and the OrderDate fields are not retrieved.

    According to your description, as far as I know that you could join queries in the same way that you join tables, and can also join both. So I suggest that you could create query to simplify your select statement then check query result.

    For more information, click here to refer about Join tables and queries

    Friday, March 11, 2016 4:48 AM
  • Hi Stefan,

    The field type "NUMBER" and "DATE/TIME" does not show any values when the query is run, although the values are in the table.

    If I run the same query in SQL Server Management, I get the correct results. If I create the same query in MS Access' QUERY WIZARD, I get the correct results. It is only when I run the query with the ADODB, that the NUMBER and DATE/TIME fields display empty or null values.

    Thanks

    Friday, March 11, 2016 5:30 AM
  • Hi Stefan,

    I got the problem solved. I omitted the "rs!" part in front of the field name when adding the data to the Combobox control.

    All is working fine now.

    Thanks for the assistance.

    • Marked as answer by Deon SA Friday, March 11, 2016 8:05 AM
    Friday, March 11, 2016 8:05 AM