retrieve data from a access database

[Roar]

Hi!

I have got 1 access 2000 DB, one simple search form, and 3 .asp pages (one
for deleting a record, one for inserting a record and one for listing
searchresults). Deleting records works fine, Inserting records works fine,
but my headache is the searchform.. Whatever i type in the searchbox (sok),
the result is all database entries, not just the entries matching the term
I'm asking for. No error code is given.The code for the search is inserted
below. As you may see, this source is not written by me but is modified to
my needs. I'm guessing that something in this code overrules my SQL
statement.

Does anyone see any obvious errors in my code? I have tried with different
SQL statements (as seen below)

In advance
Thnx


<%
' Declaring variables
Dim rs, data_source, no, sok, sql_select

' A Function to check if some field entered by user is empty
Function ChkString(string)
' If string = "" Then string = " "
' ChkString = Replace(string, "'", "''")
End Function


' Receiving values from Form
sok = ChkString(Request.Form("sok"))



no = 0
data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\www\myserver\db\links.mdb" '## MS Access 2000
'sql_select = "select (program, link, beskrivelse) from links where
values like ('" & sok & "') "
'sql_select = "select * from links where values like sok "
sql_select = "select * from links (program, links, beskrivelse) where
program like '%" & sok & "%' or links like '%" & sok & "%' or beskrivelse
like '%" & sok & "%'"




' Creating Recordset Object and opening the database
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "links", data_source
' Looping through the records to show all of them
While Not rs.EOF %>

<form action="del.asp" method="post">
<tr>
<td><%=rs("program") %></td>
<td><%=rs("link") %></td>
<td><%=rs("beskrivelse") %></td>
<td><input type="hidden" name="id" value="<%=rs("id")%>"> <input
type="submit" value="SLETT"></form></td>



</tr>


<%
no = no + 1
rs.MoveNext
Wend
' Done. Now close the Recordset
rs.Close
Set rs = Nothing
%>
<tr>
<td height=10 valign="top" width="160"></td>
<td height=10 valign="top" width="273"></td>
<td height=10 valign="top" width="272"><b>Totalt antall program</b> :
<%=no %></td>
<td height=10 valign="top" width=93></td>
</tr>
</table>
<p>Tilbake til <a href="form_sok.htm">Søkeskjemaet</a></p>
</body>
</html>

 

[Aaron Bertrand - MVP]

This is not a valid SQL statement:

sql_select = "select * from links (program, links, beskrivelse) where
program like '%" & sok & "%' or links like '%" & sok & "%' or beskrivelse
like '%" & sok & "%'"

Did you mean:

sql_select = "select program, links, beskrivelse from links where program
like '%" & sok & "%' or links like '%" & sok & "%' or beskrivelse like '%" &
sok & "%'"

?

 

[Roar]

Hi!

Your statement is exactly what i mean, but that sql statement give me the
same result. It lists up every entry in the database.

I'm still stuck but thanks for replying

 

[Aaron Bertrand - MVP]

Oh, well, maybe you should try:

set rs = data_source.execute(sql_select)

Since your current line of:

rs.Open "links", data_source

Is actually TELLING it to just open the links table. Notice that sql_select
is not used anywhere???

 

[Al Reid]

Hi!

I have got 1 access 2000 DB, one simple search form, and 3 .asp pages (one
for deleting a record, one for inserting a record and one for listing
searchresults). Deleting records works fine, Inserting records works fine,
but my headache is the searchform.. Whatever i type in the searchbox (sok),
the result is all database entries, not just the entries matching the term
I'm asking for. No error code is given.The code for the search is inserted
below. As you may see, this source is not written by me but is modified to
my needs. I'm guessing that something in this code overrules my SQL
statement.

Does anyone see any obvious errors in my code? I have tried with different
SQL statements (as seen below)

In advance
Thnx


<%
' Declaring variables
Dim rs, data_source, no, sok, sql_select

' A Function to check if some field entered by user is empty
Function ChkString(string)
' If string = "" Then string = " "
' ChkString = Replace(string, "'", "''")
End Function


' Receiving values from Form
sok = ChkString(Request.Form("sok"))



no = 0
data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\www\myserver\db\links.mdb" '## MS Access 2000
'sql_select = "select (program, link, beskrivelse) from links where
values like ('" & sok & "') "
'sql_select = "select * from links where values like sok "
sql_select = "select * from links (program, links, beskrivelse) where
program like '%" & sok & "%' or links like '%" & sok & "%' or beskrivelse
like '%" & sok & "%'"




' Creating Recordset Object and opening the database
Set rs = Server.CreateObject("ADODB.Recordset")
======================================================

rs.Open "links", data_source

Tthis line should be
rs.Open sql_select, data_source

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

 

[Roar]

Hi again

By doing this, i get the following error msg:


Microsoft VBScript runtime error '800a01a8'
Object required: 'Provider=Microsoft.J'

/temp/links/sok.asp, line 55

 

[Roar]

Hi!

Which gives the following

Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters.

/temp/links/sok.asp, line 54



Thnx, btw.

 

[Aaron Bertrand - MVP]

*sigh*

All right, let's try from the top.

<%
set conn = CreateObject("ADODB.Connection")
ds = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\www\myserver\db\links.mdb"
conn.open ds
sok = chkString(Request.Form("sok"))
sql = "select program, links, beskrivelse from links where " & _
" program like '%" & sok & "%'" & _
" or links like '%" & sok & "%'" & _
" or beskrivelse like '%" & sok & "%'"
set rs = conn.execute(sql)
do while not rs.eof
response.write rs(0) & "<br>"
rs.movenext
loop
rs.close: set rs = nothing
conn.close: set conn = nothing
%>


--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

 

[Aaron Bertrand - MVP]

Roar, sounds like you're jumping in head first. I suggest going through a
couple of tutorials.
http://www.aspfaq.com/2183

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

 

[Roar]

"> Roar, sounds like you're jumping in head first. I suggest going through
a

And meeting a brick wall too....

couple of tutorials.
http://www.aspfaq.com/2183

Sounds like thats what i have to do. Thnx for your time anyway.

Roar.