Quotes/Blanks in ASP

[Shawn]

I have part of the following SQL statement:

'*********
SELECT
d_service_area.service_area AS ServiceArea,
d_environment.environment AS Environment,
d_platform_function.platform_function AS Platform,
d_hw_type.hw_type AS hdwType,
d_hardware.standard AS hdwHardware,
d_hardware.config_vrsn AS hdwVersion,
d_sw_type.sw_type AS sfwType,
d_software.standard AS sfwSoftware,
d_software.config_vrsn AS sfwVersion,
hw_sw.time_frame AS tf,
hw_sw.impl_plan_start_date AS pStDate,
hw_sw.impl_act_start_date AS aStDate,
hw_sw.impl_plan_duration AS pDur,
0 AS cmtTyp,
0 AS cmtSeq,
" " AS cmt
'******************
I need to embed this into my ASP page so that I can make
calls to my DB, but the last line, " " AS cmt , is
throwing me for a loop. How do I handle this?

I know the following isn't correct:
******************
"SELECT " & _
"d_service_area.service_area AS ServiceArea, " & _
"d_environment.environment AS Environment, " & _
"d_platform_function.platform_function AS
Platform, " & _
"d_hw_type.hw_type AS hdwType, " & _
"d_hardware.standard AS hdwHardware, " & _
"d_hardware.config_vrsn AS hdwVersion, " & _
"d_sw_type.sw_type AS sfwType, " & _
"d_software.standard AS sfwSoftware, " & _
"d_software.config_vrsn AS sfwVersion, " & _
"hw_sw.time_frame AS tf, " & _
"hw_sw.impl_plan_start_date AS pStDate, " & _
"hw_sw.impl_act_start_date AS aStDate, " & _
"hw_sw.impl_plan_duration AS pDur, " & _
"0 AS cmtTyp, " & _
"0 AS cmtSeq, " & _
"" "AS cmt " & _

*******************

Thanks in advance for any help!

 

[Ray at]

Use ' instead of " in SQL. Also, you have an erroneous "& _" at the end of
your statement.

When you do need to include " in a string, you double it. Like, "He said,
""Hi. How are you?"""

Ray at work

 

[Dan Boylett]

I know the following isn't correct:
******************
"SELECT " & _
"d_service_area.service_area AS ServiceArea, " & _
"d_environment.environment AS Environment, " & _
"d_platform_function.platform_function AS
Platform, " & _
"d_hw_type.hw_type AS hdwType, " & _
"d_hardware.standard AS hdwHardware, " & _
"d_hardware.config_vrsn AS hdwVersion, " & _
"d_sw_type.sw_type AS sfwType, " & _
"d_software.standard AS sfwSoftware, " & _
"d_software.config_vrsn AS sfwVersion, " & _
"hw_sw.time_frame AS tf, " & _
"hw_sw.impl_plan_start_date AS pStDate, " & _
"hw_sw.impl_act_start_date AS aStDate, " & _
"hw_sw.impl_plan_duration AS pDur, " & _
"0 AS cmtTyp, " & _
"0 AS cmtSeq, " & _
"" "AS cmt " & _

Try :

"0 AS cmtSeq, " & _
""" ""AS cmt " & _

Though you may find if you're running anything > then SQL 6.5 that you
should rather use single qoutes... so :

"0 AS cmtSeq, " & _
"' 'AS cmt " & _

 

[Bob Barrows]

I have part of the following SQL statement:

'*********
SELECT
" " AS cmt
'******************
I need to embed this into my ASP page so that I can make
calls to my DB, but the last line, " " AS cmt , is
throwing me for a loop. How do I handle this?

Two options:
1. Use single-quotes (') instead of double quotes. Most databases recognize
this"
strSQL = "Select ..., ' ' As cmt ... "

2. If your database does not recognize single quotes as delimiters, escape
your double-quotes by doubling them:
strSQL = "Select ..., "" "" As cmt ... "

HTH,
Bob Barrows

 

[swp]

use single quotes instead of double quotes.

 

[swp]

a little more complete answer (total time to type, including these
notes: 15 minutes)

no optimization on the SQL - use INNER JOINs and whatever relations
you have set up already instead of the clunky FROM clause below. I
did use table alias names to cut it down a bit and make it more
readable.

no optimization on building the SQL string - you may want to do other
things with the FROM, WHERE, GROUP BY and ORDER BY clauses.

=====================================
DIM xsql, xrs, xcon
DIM db_name, username, password

db_name = "SystemDSN_you_set_up_in_Data_Sources(ODBC)"
username = "someone_allowed_into_this_database"
password = "some_strong_password_you_set_for_him"

xsql = "SELECT "
xsql = xsql & "sa.service_area AS ServiceArea, "
xsql = xsql & "e.environment AS Environment, "
xsql = xsql & "pf.platform_function AS Platform, "
xsql = xsql & "hwt.hw_type AS hdwType, "
xsql = xsql & "h.standard AS hdwHardware, "
xsql = xsql & "h.config_vrsn AS hdwVersion, "
xsql = xsql & "swt.sw_type AS sfwType, "
xsql = xsql & "s.standard AS sfwSoftware, "
xsql = xsql & "s.config_vrsn AS sfwVersion, "
xsql = xsql & "hstime_frame AS tf, "
xsql = xsql & "hs.impl_plan_start_date AS pStDate, "
xsql = xsql & "hs.impl_act_start_date AS aStDate, "
xsql = xsql & "hs.impl_plan_duration AS pDur, "
xsql = xsql & "0 AS cmtTyp, "
xsql = xsql & "0 AS cmtSeq, "
xsql = xsql & "' ' AS cmt "
xsql = xsql & "FROM "
xsql = xsql & "d_service_area sa, "
xsql = xsql & "d_environment e, "
xsql = xsql & "d_platform_function pf, "
xsql = xsql & "d_hw_type hwt, "
xsql = xsql & "d_hardware h, "
xsql = xsql & "d_sw_type swt, "
xsql = xsql & "d_software s, "
xsql = xsql & "hw_sw hs "
xsql = xsql & "WHERE blah blah blah "
xsql = xsql & "ORDER BY bitty boppity boo "

set xcon = server.CreateObject("ADODB.Connection")
xcon.Open db_name, username, password
set xrs = xcon.execute(xsql)
if (not xrs.eof and not xrs.bof) then ' we got records back
' do stuff with your recordset called xrs
else
' error message or whatever you do with no data returned
end if
set xrs = nothing
xcon.close
set xcon = nothing

=====================================

swp