By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,509 Members | 1,227 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,509 IT Pros & Developers. It's quick & easy.

retrieve data from a access database

P: n/a
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>
Jul 19 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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 & "%'"

?

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
Jul 19 '05 #2

P: n/a
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" <aa***@TRASHaspfaq.com> wrote in message
news:ed*************@tk2msftngp13.phx.gbl...
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 & "%'"

?

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

Jul 19 '05 #3

P: n/a
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???

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
Jul 19 '05 #4

P: n/a

"Roar" <no@adress.no> wrote in message news:2L******************@news4.e.nsc.no...
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

================================================== =====
' 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>

Jul 19 '05 #5

P: n/a
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


"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:Oc**************@TK2MSFTNGP11.phx.gbl...
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???

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

Jul 19 '05 #6

P: n/a
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.

"Al Reid" <ar*****@reidDASHhome.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

"Roar" <no@adress.no> wrote in message

news:2L******************@news4.e.nsc.no...
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

================================================== =====
' 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>


Jul 19 '05 #7

P: n/a
*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/


"Roar" <no@adress.no> wrote in message
news:hu******************@news4.e.nsc.no...
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


"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:Oc**************@TK2MSFTNGP11.phx.gbl...
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???

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


Jul 19 '05 #8

P: n/a
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" <no@adress.no> wrote in message
news:cA******************@news4.e.nsc.no...
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.

"Al Reid" <ar*****@reidDASHhome.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

"Roar" <no@adress.no> wrote in message news:2L******************@news4.e.nsc.no...
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

================================================== =====
' 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>



Jul 19 '05 #9

P: n/a

"> 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.


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


"Roar" <no@adress.no> wrote in message
news:cA******************@news4.e.nsc.no...
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.

"Al Reid" <ar*****@reidDASHhome.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

"Roar" <no@adress.no> wrote in message

news:2L******************@news4.e.nsc.no...
> 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

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

> ' 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>
>
>



Jul 19 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.