p2p.wrox.com: Archives - asp

asp_databases thread: Re: SQL Statement

Message #1 by arshad siddiqui <ash_arshad@y...> on Mon, 22 Apr 2002 22:51:01 -0700 (PDT)

Hi,
In sql statement instead of using
strSQL= _
 	"SELECT au_lname " &_
 	"FROM Authors " &_
 	"WHERE au_lname LIKE strPassedAuthor"

use this statement:
strSQL= _
 	"SELECT au_lname " &_
 	"FROM Authors " &_
 	"WHERE au_lname LIKE '%"&strPassedAuthor&"%'"

I suppose this would work.Any other problem,please let
me know.
Thanks
Bye
--Arshad--




--- Hal Froot <hfroot@a...> wrote:
> Ken Thanks for the help but I am still doing
> something wrong. I have
> attached the two pages that I am using. Perhaps you
> could provide some
> further assistance to this newbee.
> 
> 
> 
> <%@language=vbscript%>
> <%option explicit%>
> <html>
> <body>
> <form name=SearchByName action=Search1.asp
> method=post>
> Enter The Name of The Author You Would Like to Look
> Up<br>
> <input type=text name=AuthorLName><P>
> <input type=submit>
> </form>
> </body>
> </html>
> 
> 
> 
> <%@language=vbscript%>
> <!--metadata type="typelib" File="C:\Program
> Files\Common
> Files\System\ADO\msado15.dll"-->
> <%option explicit%>
> <%
> Dim  adOpenForwardOnly, adLockReadOnly, adCmdTable,
> strConnectionString,
> strPassedAuthor, strCriteria
> Dim objConn, objRS, strSQL
> 
> strPassedAuthor=Request.Form("AuthorLName")
> 
> adOpenForwardOnly=0
> adLockReadOnly=1
> adCmdTable=2
>
strConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data
>
Source=C:\Inetpub\wwwroot\BegASPFiles\pubs.mdb;Persist
> Security Info=False"
> Set objConn=Server.CreateObject("ADODB.connection")
> Set objRS=Server.CreateObject("ADODB.recordset")
> objConn.Open strConnectionString
> objRS.Open "authors", objConn, adLockReadOnly,
> adCmdTable
> strSQL= _
> 	"SELECT au_lname " &_
> 	"FROM Authors " &_
> 	"WHERE au_lname LIKE strPassedAuthor"
> %>
> <html>
> <body>
> <%
> 
> '************************************old
> code***************************
> 'objRS.Find strCriteria
> 'objConn.Execute  ="SELECT au_lname FROM Authors
> WHERE au_lname='Ringer'"
> 'adCmdTable="SELECT au_lname FROM Authors WHERE
> au_lname='strPassedAuthor'"
> 'objConn.Execute "SELECT au_fname From Authors where
> au_lname='Ringer'"
> 'response.write objRS("au_lname").value&"<BR>"
> 'while not objRS.EOF
> 'Response.write objRS("au_lname").value & " it
> works"&"<BR>"
> 'objRS.movenext
> 'Wend
>
'**************************************************************************
> while not objRS.eof
> 
> %>
> <a href=search.asp?<%=objRS("au_id").value%>>
> <%=objRS("au_fname").value & "
> " & objRS("au_lname")%></a><P>
> <form
> action=xxx.asp?<%=objRS("au_id").value%>><input
> type=hidden
> name=<%=objRS("au_id").value%>><input
> type=submit></form>
> <%
> 	objRS.movenext
> 	wend
> %>
> </head>
> </html>
> 
> Thanks
> 
> -----Original Message-----
> From: Ken Schaefer [mailto:ken@a...]
> Sent: Sunday, April 21, 2002 9:49 PM
> To: ASP Databases
> Subject: [asp_databases] Re: SQL Statement
> 
> 
> <%
> strSQL = _
>    "SELECT au_lname " & _
>     "FROM Authors " & _
>     "WHERE au_lname='Ringer'"
> 
> Set objRS = objConn.Execute strSQL
> %>
> 
> Cheers
> Ken
> 
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: "hal froot" <hfroot@a...>
> Subject: [asp_databases] SQL Statement
> 
> 
> : What an I missing in my SQL statement so that the
> only values returned are
> : where au_Lname="Ringer"
> 
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 
> 
> 
> 


__________________________________________________
Do You Yahoo!?
Yahoo! Games - play chess, backgammon, pool and more
http://games.yahoo.com/

Message #2 by "Drew, Ron" <RDrew@B...> on Mon, 22 Apr 2002 14:59:40 -0400

Did not take a real good look at the code, but did see the following. 
First put a response.write after
strPassedAuthor=3DRequest.Form("AuthorLName")

Also change your sql statement as follows.
strSQL=3D "SELECT au_lname " &_
	  "FROM Authors " &_
	  "WHERE au_lname LIKE '%" & strPassedAuthor & "%'"

Last, I would use
<%Option Explicit%>
<!-- #include file=3D"adovbs.inc" -->
At the top versus dim for adOpenForwardOnly  etc...

-----Original Message-----
From: Hal Froot [mailto:hfroot@a...]
Sent: Monday, April 22, 2002 11:45 AM
To: ASP Databases
Subject: [asp_databases] Re: SQL Statement


Ken Thanks for the help but I am still doing something wrong. I have
attached the two pages that I am using. Perhaps you could provide some
further assistance to this newbee.



<%@language=3Dvbscript%>
<%option explicit%>
<html>
<body>
<form name=3DSearchByName action=3DSearch1.asp method=3Dpost>
  Enter The Name of The Author You Would Like to Look Up<br>
  <input type=3Dtext name=3DAuthorLName><P>
  <input type=3Dsubmit>
</form>
</body>
</html>



<%@language=3Dvbscript%>
<!--metadata type=3D"typelib" File=3D"C:\Program Files\Common
Files\System\ADO\msado15.dll"-->
<%option explicit%>
<%
Dim  adOpenForwardOnly, adLockReadOnly, adCmdTable, strConnectionString,
strPassedAuthor, strCriteria
Dim objConn, objRS, strSQL

strPassedAuthor=3DRequest.Form("AuthorLName")

adOpenForwardOnly=3D0
adLockReadOnly=3D1
adCmdTable=3D2 
strConnectionString=3D"Provider=3DMicrosoft.Jet.OLEDB.4.0;Data
  Source=3DC:\Inetpub\wwwroot\BegASPFiles\pubs.mdb;
  Persist Security Info=3DFalse"
Set objConn=3DServer.CreateObject("ADODB.connection")
Set objRS=3DServer.CreateObject("ADODB.recordset")
objConn.Open strConnectionString
objRS.Open "authors", objConn, adLockReadOnly, adCmdTable
strSQL=3D _
	"SELECT au_lname " &_
	"FROM Authors " &_
	"WHERE au_lname LIKE strPassedAuthor"
%>
<html>
<body>
<%

'************************************old code***************************
'objRS.Find strCriteria 'objConn.Execute  =3D"SELECT au_lname FROM 
Authors
WHERE au_lname=3D'Ringer'" 'adCmdTable=3D"SELECT au_lname FROM Authors 
WHERE
au_lname=3D'strPassedAuthor'" 'objConn.Execute "SELECT au_fname From
Authors where au_lname=3D'Ringer'" 'response.write
objRS("au_lname").value&"<BR>" 'while not objRS.EOF 'Response.write
objRS("au_lname").value & " it works"&"<BR>" 'objRS.movenext 'Wend
'***********************************************************************
***
while not objRS.eof

%>
<a href=3Dsearch.asp?<%=3DobjRS("au_id").value%>> 
<%=3DobjRS("au_fname").value
& " " & objRS("au_lname")%></a><P> <form
action=3Dxxx.asp?<%=3DobjRS("au_id").value%>><input type=3Dhidden
name=3D<%=3DobjRS("au_id").value%>><input type=3Dsubmit></form> <%
	objRS.movenext
	wend
%>
</head>
</html>

Thanks

-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Sunday, April 21, 2002 9:49 PM
To: ASP Databases
Subject: [asp_databases] Re: SQL Statement


<%
strSQL =3D _
   "SELECT au_lname " & _
    "FROM Authors " & _
    "WHERE au_lname=3D'Ringer'"

Set objRS =3D objConn.Execute strSQL
%>

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "hal froot" <hfroot@a...>
Subject: [asp_databases] SQL Statement


: What an I missing in my SQL statement so that the only values returned
are
: where au_Lname=3D"Ringer"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


---
Change your mail options at http://p2p.wrox.com/manager.asp or to
unsubscribe send a blank email to

Message #3 by "Kim Iwan Hansen" <kimiwan@k...> on Mon, 22 Apr 2002 18:13:41 +0200

Hi Hal,

You're doing a couple of things wrong...

** your code ... ****************************************
objRS.Open "authors", objConn, adLockReadOnly, adCmdTable
strSQL= _
	"SELECT au_lname " &_
	"FROM Authors " &_
	"WHERE au_lname LIKE strPassedAuthor"
*********************************************************

You open the recordset as a table (authors)... then you specify the sql
string with your select statement.

When you use sql, you don't also use the table command - they do the same
thing, except the table returns the entire table whereas using the sql
string gets you the data returned by the sql code.  When you use sql to
retrieve data, you use the command type adCmdText instead of adCmdTable

Further more, your sql string needs to be built in your asp code so that it
looks like proper sql when the database receives it...

Firstly you need apostrophes (') around your string values.

Secodly you need to embed *the value* of strPassedAuthor into the string,
not just add a string "strPassedAuthor" to the sql.  You do that this way:
"...WHERE au_lname LIKE '" & strPassedAuthor & "';"   (don't forget the
closing apostrophe or you'll get an error!!)

Thirdly, you have forgotten the cursortype in your objRS.open line.


You have to alter your code to this:
*********************************************************
strSQL= _
	"SELECT au_lname " &_
	"FROM Authors " &_
	"WHERE au_lname LIKE strPassedAuthor"
objRS.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText
*********************************************************

Don't forget to dim and add the new constants too!


-Kim


-----Original Message-----
From: Hal Froot [mailto:hfroot@a...]
Sent: 22. april 2002 17:45
To: ASP Databases
Subject: [asp_databases] Re: SQL Statement


Ken Thanks for the help but I am still doing something wrong. I have
attached the two pages that I am using. Perhaps you could provide some
further assistance to this newbee.



<%@language=vbscript%>
<%option explicit%>
<html>
<body>
<form name=SearchByName action=Search1.asp method=post>
Enter The Name of The Author You Would Like to Look Up<br>
<input type=text name=AuthorLName><P>
<input type=submit>
</form>
</body>
</html>



<%@language=vbscript%>
<!--metadata type="typelib" File="C:\Program Files\Common
Files\System\ADO\msado15.dll"-->
<%option explicit%>
<%
Dim  adOpenForwardOnly, adLockReadOnly, adCmdTable, strConnectionString,
strPassedAuthor, strCriteria
Dim objConn, objRS, strSQL

strPassedAuthor=Request.Form("AuthorLName")

adOpenForwardOnly=0
adLockReadOnly=1
adCmdTable=2
strConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Inetpub\wwwroot\BegASPFiles\pubs.mdb;Persist Security Info=False"
Set objConn=Server.CreateObject("ADODB.connection")
Set objRS=Server.CreateObject("ADODB.recordset")
objConn.Open strConnectionString
objRS.Open "authors", objConn, adLockReadOnly, adCmdTable
strSQL= _
	"SELECT au_lname " &_
	"FROM Authors " &_
	"WHERE au_lname LIKE strPassedAuthor"
%>
<html>
<body>
<%

'************************************old code***************************
'objRS.Find strCriteria
'objConn.Execute  ="SELECT au_lname FROM Authors WHERE au_lname='Ringer'"
'adCmdTable="SELECT au_lname FROM Authors WHERE au_lname='strPassedAuthor'"
'objConn.Execute "SELECT au_fname From Authors where au_lname='Ringer'"
'response.write objRS("au_lname").value&"<BR>"
'while not objRS.EOF
'Response.write objRS("au_lname").value & " it works"&"<BR>"
'objRS.movenext
'Wend
'**************************************************************************
while not objRS.eof

%>
<a href=search.asp?<%=objRS("au_id").value%>> <%=objRS("au_fname").value & "
" & objRS("au_lname")%></a><P>
<form action=xxx.asp?<%=objRS("au_id").value%>><input type=hidden
name=<%=objRS("au_id").value%>><input type=submit></form>
<%
	objRS.movenext
	wend
%>
</head>
</html>

Thanks

-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Sunday, April 21, 2002 9:49 PM
To: ASP Databases
Subject: [asp_databases] Re: SQL Statement


<%
strSQL = _
   "SELECT au_lname " & _
    "FROM Authors " & _
    "WHERE au_lname='Ringer'"

Set objRS = objConn.Execute strSQL
%>

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "hal froot" <hfroot@a...>
Subject: [asp_databases] SQL Statement


: What an I missing in my SQL statement so that the only values returned are
: where au_Lname="Ringer"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Message #4 by "Hal Froot" <hfroot@a...> on Mon, 22 Apr 2002 11:45:05 -0400

Ken Thanks for the help but I am still doing something wrong. I have
attached the two pages that I am using. Perhaps you could provide some
further assistance to this newbee.



<%@language=vbscript%>
<%option explicit%>
<html>
<body>
<form name=SearchByName action=Search1.asp method=post>
Enter The Name of The Author You Would Like to Look Up<br>
<input type=text name=AuthorLName><P>
<input type=submit>
</form>
</body>
</html>



<%@language=vbscript%>
<!--metadata type="typelib" File="C:\Program Files\Common
Files\System\ADO\msado15.dll"-->
<%option explicit%>
<%
Dim  adOpenForwardOnly, adLockReadOnly, adCmdTable, strConnectionString,
strPassedAuthor, strCriteria
Dim objConn, objRS, strSQL

strPassedAuthor=Request.Form("AuthorLName")

adOpenForwardOnly=0
adLockReadOnly=1
adCmdTable=2
strConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Inetpub\wwwroot\BegASPFiles\pubs.mdb;Persist Security Info=False"
Set objConn=Server.CreateObject("ADODB.connection")
Set objRS=Server.CreateObject("ADODB.recordset")
objConn.Open strConnectionString
objRS.Open "authors", objConn, adLockReadOnly, adCmdTable
strSQL= _
	"SELECT au_lname " &_
	"FROM Authors " &_
	"WHERE au_lname LIKE strPassedAuthor"
%>
<html>
<body>
<%

'************************************old code***************************
'objRS.Find strCriteria
'objConn.Execute  ="SELECT au_lname FROM Authors WHERE au_lname='Ringer'"
'adCmdTable="SELECT au_lname FROM Authors WHERE au_lname='strPassedAuthor'"
'objConn.Execute "SELECT au_fname From Authors where au_lname='Ringer'"
'response.write objRS("au_lname").value&"<BR>"
'while not objRS.EOF
'Response.write objRS("au_lname").value & " it works"&"<BR>"
'objRS.movenext
'Wend
'**************************************************************************
while not objRS.eof

%>
<a href=search.asp?<%=objRS("au_id").value%>> <%=objRS("au_fname").value & "
" & objRS("au_lname")%></a><P>
<form action=xxx.asp?<%=objRS("au_id").value%>><input type=hidden
name=<%=objRS("au_id").value%>><input type=submit></form>
<%
	objRS.movenext
	wend
%>
</head>
</html>

Thanks

-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Sunday, April 21, 2002 9:49 PM
To: ASP Databases
Subject: [asp_databases] Re: SQL Statement


<%
strSQL = _
   "SELECT au_lname " & _
    "FROM Authors " & _
    "WHERE au_lname='Ringer'"

Set objRS = objConn.Execute strSQL
%>

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "hal froot" <hfroot@a...>
Subject: [asp_databases] SQL Statement


: What an I missing in my SQL statement so that the only values returned are
: where au_Lname="Ringer"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Message #5 by "Ken Schaefer" <ken@a...> on Mon, 22 Apr 2002 11:49:18 +1000

<%
strSQL = _
   "SELECT au_lname " & _
    "FROM Authors " & _
    "WHERE au_lname='Ringer'"

Set objRS = objConn.Execute strSQL
%>

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "hal froot" <hfroot@a...>
Subject: [asp_databases] SQL Statement


: What an I missing in my SQL statement so that the only values returned are
: where au_Lname="Ringer"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Message #6 by "hal froot" <hfroot@a...> on Fri, 19 Apr 2002 18:27:18

What an I missing in my SQL statement so that the only values returned are 
where au_Lname="Ringer"

below is my code:

<%@language=vbscript%>
<%option explicit%>
<%
Dim  adOpenForwardOnly, adLockReadOnly, adCmdTable, strConnectionString
Dim objConn, objRS
adOpenForwardOnly=0
adLockReadOnly=1
adCmdTable=2
strConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=C:\Inetpub\wwwroot\BegASPFiles\pubs.mdb;Persist Security 
Info=False"
Set objConn=Server.CreateObject("ADODB.connection")
Set objRS=Server.CreateObject("ADODB.recordset")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data 
Source=C:\Inetpub\wwwroot\BegASPFiles\pubs.mdb;Persist Security Info=False"
objRS.Open "authors", objConn,adOpenForwardOnly, adLockReadOnly, adCmdTable
objConn.Execute "SELECT au_lname FROM Authors WHERE au_lname='Bennet'"
%>
<html>
<body>
<%

'************************************old code***************************
objConn.Execute "SELECT au_fname From Authors where au_lname='Ringer'"
'response.write objRS("au_lname").value&"<BR>"
'while not objRS.EOF
'Response.write objRS("au_lname").value & " it works"&"<BR>"
'objRS.movenext
'Wend
'**************************************************************************
while not objRS.eof
%>
<a href=search.asp?<%=objRS("au_id").value%>> <%=objRS("au_fname").value 
& " " & objRS("au_lname")%></a><P>
<form action=xxx.asp?<%=objRS("au_id").value%>><input type=hidden name=<%
=objRS("au_id").value%>><input type=submit></form>
<%
	response.write "<BR>"
	objRS.movenext
wend
%>
</head>
</html>

Return to Index

P2P Archives