Professional Web Applications Themes

SELECT statement works with query yzer but not in ASP. Help? - ASP Database

Hi, The following statement works in query yzer but does not work in asp. Specifically, what does not work is that the rfc822 field from mailbox is returned properly in query yzer but it is returned null in the asp code. This rfc822 field contains the body of an email message which is linked to conthist via the recid field. Can someone help point out what is wrong here? You can respond to this post or email me directly at tim123(removethisfortheemailtowork)omnitronix.com This statement works: select c.accountno, c.ref, c.ondate, c.ontime, c.userid, c.rectype, c.notes AS histnotes, c.recid, m.rfc822, ct.company, ct.contact FROM ( ...

  1. #1

    Default SELECT statement works with query yzer but not in ASP. Help?

    Hi,

    The following statement works in query yzer but does not work in asp.
    Specifically, what does not work is that the rfc822 field from mailbox is
    returned properly in query yzer but it is returned null in the asp code.
    This rfc822 field contains the body of an email message which is linked to
    conthist via the recid field.

    Can someone help point out what is wrong here? You can respond to this post
    or email me directly at tim123(removethisfortheemailtowork)omnitronix.com

    This statement works:

    select
    c.accountno,
    c.ref,
    c.ondate,
    c.ontime,
    c.userid,
    c.rectype,
    c.notes AS histnotes,
    c.recid,
    m.rfc822,
    ct.company,
    ct.contact
    FROM
    ( common.dbo.conthist c
    inner join common.dbo.contact1 ct on c.accountno = ct.accountno)
    left outer join common.dbo.mailbox m on c.recid = m.linkrecid
    where c.ondate BETWEEN '11/01/2004' and '11/02/2004'
    ORDER BY c.userid,c.ondate,c.ontime

    but when translated into asp like this, the rfc822 field is returned as
    null:

    SqlStr = "select " & _
    " c.accountno," & _
    " c.ref," & _
    " c.ondate," & _
    " c.ontime," & _
    " c.userid," & _
    " c.rectype," & _
    " c.notes AS histnotes," & _
    " c.recid," & _
    " m.rfc822," & _
    " ct.company," & _
    " ct.contact " & _
    "FROM " & _
    "common.dbo.conthist c inner join common.dbo.contact1 ct on c.accountno =
    ct.accountno " & _
    "left outer join common.dbo.mailbox m on c.recid = m.linkrecid " & _
    "where c.ondate BETWEEN '11/01/2004' and '11/02/2004' " & _
    "ORDER BY c.userid,c.ondate,c.ontime"

    RS.open sqlstr
    rs.movefirst
    do while not RS.EOF
    vcompany=trim(rs("company"))
    vref=trimref(rs("ref"))
    vemail=trim(rs("rfc822"))

    response.write vemail & "<br>"

    RS.MoveNext
    loop

    Any suggestions, anyone?

    Tim Stoner



    Tim Guest

  2. #2

    Default Re: SELECT statement works with query yzer but not in ASP. Help?

    "Tim Stoner" <com> wrote in message
    news:#phx.gbl... 
    code. 
    post 

    Try changing
    BETWEEN '11/01/2004' and '11/02/2004'
    to
    BETWEEN #11/01/2004# and #11/02/2004#


    McKirahan Guest

  3. #3

    Default Re: SELECT statement works with query yzer but not in ASP. Help?

    The ASP script will not allow the # sign in the select statement. And the
    problem is not with the BETWEEN clause but with the failure to join and
    properly link the two tables to return the rfc822 field.

    "McKirahan" <com> wrote in message
    news:vlwid.46020$.. [/ref]
    asp. [/ref]
    is 
    > code. [/ref]
    to 
    > post [/ref]
    tim123(removethisfortheemailtowork)omnitronix.com [/ref]

    >
    > Try changing
    > BETWEEN '11/01/2004' and '11/02/2004'
    > to
    > BETWEEN #11/01/2004# and #11/02/2004#
    >
    >[/ref]


    Tim Guest

  4. #4

    Default Re: SELECT statement works with query yzer but not in ASP. Help?

    Don't use BETWEEN and don't use regional date format strings. See if you
    get better results here:

    WHERE c.ondate >= '20041101' and c.ondate < '20041103'

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)




    "Tim Stoner" <com> wrote in message
    news:#phx.gbl... 
    code. 
    post 


    Aaron Guest

  5. #5

    Default Re: SELECT statement works with query yzer but not in ASP. Help?

    Tim Stoner wrote: 
    What is its datatype? If Text, you may need to make it the last column to be
    retrieved in your SELECT list.

    You really should be using a stored procedure with parameters instead of
    dynamic sql. Here is one of my canned responses about running stored
    procedures from asp:

    There are several alternatives.

    1. Use the technique described here:
    http://www.aspfaq.com/show.asp?id=2201

    Personally, I don't like this technique since:
    a. You have to worry about preventing hackers from injecting SQL into
    your code (there are ways to prevent this - see the SQL Injection FAQ at
    www.sqlsecurity.com)

    b. You have to correctly delimit your parameter values, just as if you
    were creating a dynamic SQL statement (actually, that is exactly what
    you are doing here). You also have to correctly handle string values
    that contain literal characters that are normally used as delimiters.
    While I've done this enough times so that it is second nature to me now,
    in the beginning this was the largest stumbling block to my learning how
    to create strings containing dynamic SQL statements.

    c. There is some performance-impairing overhead involved with both the
    concatenation of the SQL statement that ultimately runs the stored
    procedure, and the preparation of the statement on the SQL Server box,
    which happens before the statement is actually executed.

    d. It forces you to return data only by recordsets: no output or return
    values can be used with this technique. Recordsets require substantial
    resources, both on the SQL Server which has to assemble the resultset
    and pass it back to the client, and on the web server which has to
    marshal the resultset and transform it into an ADO recordset. This is a
    lot of overhead when we're talking about returning one or two values to
    the client.

    However, a lot of people do like this technique because:
    a. They have no problem knowing when and how to concatenate delimiters
    into the SQL statement, and how to handle string parameters that contain
    literal characters that are normally used as delimiters
    b. They have taken the necessary steps to prevent SQL Injection
    c. You can assign the statement to a variable and, if there's an error
    during the debug process, you can response.write the variable to see the
    actual statement being sent to the SQL Server. If the statement has been
    created correctly, you can copy and paste it from the browser window
    into Query yzer and further debug it
    d. They are aware of the performance hit, and consider it to be too
    minor to worry about. (To be fair, in many cases, this perfomance hit is
    relatively minor)

    The alternatives I prefer completely eliminate objection b from above.

    1. If you have output parameters, or you are interested in using the
    Return value from your procedure, use an explicit ADO Command object.
    Now, this can be tricky, especially if you do it the correct way
    (manually create the Parameters collection using CreateParameter instead
    of using Parameters.Refresh which involves an extra time-consuming trip
    to the database). However, there are many stored procedure code
    generators out there that vastly simplify this process, including the
    one I wrote which is available here:
    http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear

    2. The technique I use most often is the
    "procedure-as-connection-method" technique. With ADO 2.5 and higher,
    stored procedures can be called as if they were native methods of the
    connection object, like this:
    conn.MyProcedure parmval1,...,parmvalN
    This completely avoids the need to worry about delimiters, literal or
    otherwise. Plus it turns out that this technique also causes the
    procedure to be executed in a very efficient manner on the SQL Server
    box.

    You can also use this technique if your procedure returns a recordset:
    set rs=server.createobject("adodb.recordset")
    'optionally, set the cursor location and type properties
    conn.MyProcedure parmval1,...,parmvalN, rs

    This technique does not work in ADO.Net, so if you are considering
    porting to dotnet, then you should avoid this technique.

    HTH,
    Bob Barrows




    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  6. #6

    Default Re: SELECT statement works with query yzer but not in ASP. Help?

    Bob

    This was the correct answer
     
    be 

    the rfc822 field was a note field. when I put it as the last field in the
    select statement, like this then it worked:

    SqlStr = "select " & _
    " c.accountno," & _
    " c.ref," & _
    " c.ondate," & _
    " c.ontime," & _
    " c.userid," & _
    " c.rectype," & _
    " c.notes AS histnotes," & _
    " ct.company," & _
    " ct.contact, " & _
    " c.recid," & _
    " m.rfc822 " & _
    "FROM " & _
    "common.dbo.conthist c left outer join common.dbo.mailbox m on c.recid =
    m.linkrecid " & _
    "inner join common.dbo.contact1 ct on c.accountno = ct.accountno " & _
    "where c.ondate>='11/01/2004' and c.ondate<='11/02/2004' " & _
    "ORDER BY c.userid,c.ondate,c.ontime"

    Weird. Thanks very much for the help

    Tim Stoner


    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:phx.gbl... 
    > What is its datatype? If Text, you may need to make it the last column to[/ref]
    be 
    http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear 


    Tim Guest

Similar Threads

  1. update and insert query error, but select works ok.
    By JoyRose in forum Coldfusion Database Access
    Replies: 9
    Last Post: September 15th, 06:49 PM
  2. Cannot put <cfoutput> around a <select> statement usinga query.
    By Bagger Vance in forum Macromedia ColdFusion
    Replies: 4
    Last Post: April 23rd, 06:02 PM
  3. Replies: 0
    Last Post: May 18th, 11:33 AM
  4. Replies: 9
    Last Post: October 24th, 02:25 PM
  5. Replies: 1
    Last Post: July 2nd, 09:09 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139