Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > retrieve data from a access database

Reply
Thread Tools

retrieve data from a access database

 
 
Roar
Guest
Posts: n/a
 
      02-09-2004
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>


 
Reply With Quote
 
 
 
 
Aaron Bertrand - MVP
Guest
Posts: n/a
 
      02-09-2004
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/


 
Reply With Quote
 
 
 
 
Roar
Guest
Posts: n/a
 
      02-09-2004
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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/
>
>



 
Reply With Quote
 
Aaron Bertrand - MVP
Guest
Posts: n/a
 
      02-09-2004
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/


 
Reply With Quote
 
Al Reid
Guest
Posts: n/a
 
      02-09-2004

"Roar" <(E-Mail Removed)> wrote in message news:2LMVb.2668$(E-Mail Removed)...
> 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>
>
>



 
Reply With Quote
 
Roar
Guest
Posts: n/a
 
      02-09-2004
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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/
>
>



 
Reply With Quote
 
Roar
Guest
Posts: n/a
 
      02-09-2004
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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>
> "Roar" <(E-Mail Removed)> wrote in message

news:2LMVb.2668$(E-Mail Removed)...
> > 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>
> >
> >

>
>



 
Reply With Quote
 
Aaron Bertrand - MVP
Guest
Posts: n/a
 
      02-09-2004
*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" <(E-Mail Removed)> wrote in message
news:huNVb.2686$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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/
> >
> >

>
>



 
Reply With Quote
 
Aaron Bertrand - MVP
Guest
Posts: n/a
 
      02-09-2004
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" <(E-Mail Removed)> wrote in message
news:cANVb.2689$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> >
> > "Roar" <(E-Mail Removed)> wrote in message

> news:2LMVb.2668$(E-Mail Removed)...
> > > 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>
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
Roar
Guest
Posts: n/a
 
      02-09-2004



"> 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" <(E-Mail Removed)> wrote in message
> news:cANVb.2689$(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:%(E-Mail Removed)...
> > >
> > > "Roar" <(E-Mail Removed)> wrote in message

> > news:2LMVb.2668$(E-Mail Removed)...
> > > > 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>
> > > >
> > > >
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Database Database Database scott93727@gmail.com Computer Information 0 09-27-2012 02:43 AM
DataBase DataBase DataBase DataBase scott93727@gmail.com Computer Information 0 09-26-2012 09:40 AM
retrieve data from 2 database crespoh Python 3 07-28-2005 08:33 PM
insert record in Ms Access database AND retrieve the autonumber (ID) nicholas ASP .Net 4 10-20-2004 04:02 PM
Retrieve ID after inserting into Access database refer_to_website@nospam.com ASP .Net 0 10-28-2003 10:07 PM



Advertisments