locked
Query doesn't work the same in an ASP page RRS feed

  • Question

  • User1888948378 posted

    Hi All,

    I have an Access database that has a history table which I'm trying to show all records in each box based on a number input. So if I entered for example 45001 that box number has 14 boxes. 45001-1, 45001-2, 45001-3 etc. So in my search I want to be able to select a dropdown with 45001 and the result would show me the results in chronological order as in:

     

    <tfoot></tfoot>
    CGBoxScanCode
    45001-3
    45001-4
    45001-7
    45001-8
    45001-11
    45001-13
    45001-14

    When I use the following SQL 

    SELECT CGBoxScanCode
    FROM (SELECT DISTINCT CGBoxScanCode FROM CGBoxStatusIn_history
    WHERE CGBoxScanCode Like MMColParam & "*") 
    ORDER BY Left([CGBoxScanCode],InStr([CGBoxScanCode],"-")-1), CLng(Mid([CGBoxScanCode],InStr([CGBoxScanCode],"-")+1));
    

    it works in a query in Access however when I use it in a recordset :

    <%
    Dim Recordset1
    Dim Recordset1_cmd
    Dim Recordset1_numRows
    
    Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
    Recordset1_cmd.ActiveConnection = MM_manpower_STRING
    Recordset1_cmd.CommandText = "SELECT CGBoxScanCode FROM( SELECT DISTINCT CGBoxScanCode FROM CGBoxStatusIn_history) WHERE CGBoxScanCode Like ? & "*" ORDER BY Left([CGBoxScanCode],InStr([CGBoxScanCode],"-")-1), CLng(Mid([CGBoxScanCode],InStr([CGBoxScanCode],"-")+1));" 
    Recordset1_cmd.Prepared = true
    Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter("param1", 200, 1, 255, Recordset1__MMColParam) ' adVarChar
    
    Set Recordset1 = Recordset1_cmd.Execute
    Recordset1_numRows = 0
    %>

    it doesn't work.....can anyone tell me what's wrong here?

    Thanks in Advance,

    Lenny

    Monday, May 20, 2019 6:59 PM

All replies

  • User-848649084 posted

    Hi,

    Could you tell us what is the datatype of CGBoxScanCode field in the table and if you get any error, please share error message with us.

    Regards,

    Jalpa

    Tuesday, May 21, 2019 3:05 AM
  • User1888948378 posted

    Text

    Tuesday, May 21, 2019 6:43 PM
  • User-848649084 posted

    Hi,

    Your query string is right and also working with access database there is some issue when you use like operator in classic asp. so you need to modify query string as below:

    <html>
      <head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
      </head>
    <body> 
    
    <%
    Dim objConn
            Set objConn = Server.CreateObject("ADODB.Connection")
            objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\aspsamplesite\Database1.mdb;"
            objConn.Open
            test = "45001%"
        
            Set str = objConn.execute("SELECT CGBoxScanCode FROM (SELECT DISTINCT CGBoxScanCode FROM CGBoxStatusIn_history WHERE CGBoxScanCode Like '"& test &"') ORDER BY Left([CGBoxScanCode],InStr([CGBoxScanCode],'-')-1), CLng(Mid([CGBoxScanCode],InStr([CGBoxScanCode],'-')+1));")
           
            Response.Write("<table border=1>")
              Response.Write "<tr><td> CGBoxScanCode </td></tr>"
                  If str.BOF And str.EOF Then
                  ' No data
                  Else
                  Do While (Not str.EOF)
                  Response.Write "<tr><td>" & str("CGBoxScanCode") & "</td></tr>"
                  str.MoveNext
                  Loop
                  End If
                  
          Response.Write("</table>")
    %>
    
    </body>
    </html>

    Database table data:

    Thursday, May 30, 2019 8:55 AM
  • User-848649084 posted

    Please check the updated answer.

    Friday, May 31, 2019 5:18 AM