[WEB4LIB] Re: SQL/ASP query

[Stephen, Mathew]

Below is an example of why you should use "&" for strings and not "+" 
-----------
from Ian Bretteville-Jensen [mailto:Brett at cosmosys.net] posted on [ASP
SQLServer7];

	You should never use + for string concatenation. If the values
happen to
	be numeric they will add together. If one is numeric and the other
is a
	string, you will get a type missmatch error. Always use & when
concatenating
	strings.

	Eg
	x = 1
	y = 5
	z = "hello"

	x+y = 6
	x+z or y+z gives type missmatch

	x&y = 15
	x&z =   1hello
	y&z = 5hello

	CStr(x) + CStr(z) = 1hello because you are casting x to a string.
-----------

I also forgot to add another comment on the SQL query example below.  It is
a good idea to display the sql statement on the screen to identify errors
during development, for example; [see "<---"]
 
	sql = "select callnumber, title, create_date, create_operator from
tblbook, tblcatalogers"
	sql = sql & "where tblbook.opid = tblcatalogers.opid"
	sql = sql & "and tblbook.callnumber >= 'M' and tblbook.callnumber
<='MZ' "
	sql = sql & "order by tblbook.create_date"

	response.write "sql=" & sql & "<br>" <---

	set rsBooks=connLib.execute(sql)
 
Will result in an syntax error in the sql statement.
The sql error is the result of not adding a " " (space) at the end of the
lines when they are combined, i.e.; 

	sql = "select callnumber, title, create_date, create_operator from
tblbook, tblcatalogers " <---
	sql = sql & "where tblbook.opid = tblcatalogers.opid " <---
	sql = sql & "and tblbook.callnumber >= 'M' and tblbook.callnumber
<='MZ' "
	sql = sql & "order by tblbook.create_date"



-----Original Message-----
From: William Barnes [mailto:wbarnes at husky.bloomu.edu]
Sent: Wednesday, October 04, 2000 8:43 AM
To: Multiple recipients of list
Subject: [WEB4LIB] Re: SQL/ASP query


+ and & are equal operators in VBscript.

I have used both.

Thanks!
--Bill
*******************************************
*  Bill Barnes, CNA, MCP, A+
*  Library Network Administrator
*  Harvey A. Andruss Library
*  Bloomsburg University
*  AL 125
*  ph: 570-389-2813
*  fax: 570-389-3895
*  e-mail: wbarnes at bloomu.edu
*******************************************


>>> "Stephen, Mathew" <MStephen at library.msstate.edu> 10/04/00 09:29AM >>>
The correct syntax is to use "&" instead of "+" when combining string
variables.  For example;

sql = "select callnumber, title, create_date, create_operator from tblbook,
tblcatalogers"
sql = sql & "where tblbook.opid = tblcatalogers.opid"
sql = sql & "and tblbook.callnumber >= 'M' and tblbook.callnumber <='MZ' "
sql = sql & "order by tblbook.create_date"



Mathew Stephen
Mitchell Memorial Library, University Libraries
Mississippi State, MS 39762-5408
(662) 325-8350
http://library.msstate.edu 

-----Original Message-----
From: William Barnes [mailto:wbarnes at husky.bloomu.edu] 
Sent: Wednesday, October 04, 2000 7:19 AM
To: Multiple recipients of list
Subject: [WEB4LIB] Re: SQL/ASP query


Ian, Are you using VBscript?

If so, here's how I do a SQL statement.
This is an example original statement:

select callnumber, title, create_date, create_operator from tblbook,
tblcatalogers
where tblbook.opid = tblcatalogers.opid
and tblbook.callnumber >= "M" and tblbook.callnumber <="MZ"
order by tblbook.create_date

To do this in VBscript, I would do the following:

sql = "select callnumber, title, create_date, create_operator from tblbook,
tblcatalogers"
sql = sql + "where tblbook.opid = tblcatalogers.opid"
sql = sql + "and tblbook.callnumber >= 'M' and tblbook.callnumber <='MZ' "
sql = sql + "order by tblbook.create_date"

set cursor = Conn.Execute (sql)

Notice anything "quoted" before in direct SQL is in single quotes in
Vbscript.
(which is typically just Text fields you want to search on.)

A good web site for information is:
http://www.activeserverpages.com/learn/ 

It basically rocks.  I learned a lot from reading the sample code and it
helped me write some pretty nice web applications.

If you need any more help, just drop me an e-mail.

Thanks!
--Bill
*******************************************
*  Bill Barnes, CNA, MCP, A+
*  Library Network Administrator
*  Harvey A. Andruss Library
*  Bloomsburg University
*  AL 125
*  ph: 570-389-2813
*  fax: 570-389-3895
*  e-mail: wbarnes at bloomu.edu 
*******************************************


>>> Ian Winship <ian.winship at unn.ac.uk> 10/04/00 07:43AM >>>
I am trying to produce some asp scripts to search an Access database, but am
having difficulty getting the syntax right in the SQLQuery = "Select...
statement when I want to use AND, OR to require more than one match in the
same field. I am also a little unclear about the placing of single and
double quotation marks in the statement. 

I have not found any SQL or ASP books or Web sites that state clearly the
syntax. Can anyone point me to something useful for a beginner.

Thanks.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Ian Winship
Learning Resources, University of Northumbria at Newcastle  
City Campus Library, Newcastle upon Tyne NE1 8ST, UK

                    ----------------       
e-mail: ian.winship at unn.ac.uk                
phone:  0191 227 4150      fax: 0191 227 4563 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.webjunction.org/wjlists/web4lib/attachments/20001004/f4d25a46/attachment.htm