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

Hi,

The following statement works in query analyzer but does not work in asp.
Specifically, what does not work is that the rfc822 field from mailbox is
returned properly in query analyzer 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 & "
"

RS.MoveNext
loop

Any suggestions, anyone?

Tim Stoner

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

"Tim Stoner" wrote in message
news:#hDs7sqwEHA.356@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> The following statement works in query analyzer but does not work in asp.
> Specifically, what does not work is that the rfc822 field from mailbox is
> returned properly in query analyzer 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 & "
"
>
> RS.MoveNext
> loop
>
> Any suggestions, anyone?
>
> Tim Stoner
>

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

Re: SELECT statement works with query analyzer 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" wrote in message
news:vlwid.46020$HA.38914@attbi_s01...
> "Tim Stoner" wrote in message
> news:#hDs7sqwEHA.356@TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > The following statement works in query analyzer but does not work in
asp.
> > Specifically, what does not work is that the rfc822 field from mailbox
is
> > returned properly in query analyzer 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 & "
"
> >
> > RS.MoveNext
> > loop
> >
> > Any suggestions, anyone?
> >
> > Tim Stoner
> >
>
> Try changing
> BETWEEN '11/01/2004' and '11/02/2004'
> to
> BETWEEN #11/01/2004# and #11/02/2004#
>
>

Re: SELECT statement works with query analyzer 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" wrote in message
news:#hDs7sqwEHA.356@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> The following statement works in query analyzer but does not work in asp.
> Specifically, what does not work is that the rfc822 field from mailbox is
> returned properly in query analyzer 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 & "
"
>
> RS.MoveNext
> loop
>
> Any suggestions, anyone?
>
> Tim Stoner
>
>
>

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

Tim Stoner wrote:
> Hi,
>
> The following statement works in query analyzer but does not work in
> asp. Specifically, what does not work is that the rfc822 field from
> mailbox is returned properly in query analyzer 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.
>
>
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 Analyzer 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.

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

Bob

This was the correct answer

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

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]" wrote in message
news:OnD62brwEHA.1308@TK2MSFTNGP09.phx.gbl...
> Tim Stoner wrote:
> > Hi,
> >
> > The following statement works in query analyzer but does not work in
> > asp. Specifically, what does not work is that the rfc822 field from
> > mailbox is returned properly in query analyzer 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.
> >
> >
> 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 Analyzer 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.
>
>