retrieve data from a access database

Discussion in 'ASP General' started by Roar, Feb 9, 2004.

  1. Roar

    Roar Guest

    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>
    Roar, Feb 9, 2004
    #1
    1. Advertising

  2. 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/
    Aaron Bertrand - MVP, Feb 9, 2004
    #2
    1. Advertising

  3. Roar

    Roar Guest

    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" <> wrote in message
    news:...
    > 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/
    >
    >
    Roar, Feb 9, 2004
    #3
  4. 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/
    Aaron Bertrand - MVP, Feb 9, 2004
    #4
  5. Roar

    Al Reid Guest

    "Roar" <> wrote in message news:2LMVb.2668$...
    > 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>
    >
    >
    Al Reid, Feb 9, 2004
    #5
  6. Roar

    Roar Guest

    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" <> wrote in message
    news:...
    > 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/
    >
    >
    Roar, Feb 9, 2004
    #6
  7. Roar

    Roar Guest

    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" <> wrote in message
    news:%...
    >
    > "Roar" <> wrote in message

    news:2LMVb.2668$...
    > > 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>
    > >
    > >

    >
    >
    Roar, Feb 9, 2004
    #7
  8. *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" <> wrote in message
    news:huNVb.2686$...
    > 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" <> wrote in message
    > news:...
    > > 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/
    > >
    > >

    >
    >
    Aaron Bertrand - MVP, Feb 9, 2004
    #8
  9. 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" <> wrote in message
    news:cANVb.2689$...
    > 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" <> wrote in message
    > news:%...
    > >
    > > "Roar" <> wrote in message

    > news:2LMVb.2668$...
    > > > 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>
    > > >
    > > >

    > >
    > >

    >
    >
    Aaron Bertrand - MVP, Feb 9, 2004
    #9
  10. Roar

    Roar Guest

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

    > > news:2LMVb.2668$...
    > > > > 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>
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
    Roar, Feb 9, 2004
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    0
    Views:
    390
  2. nicholas
    Replies:
    4
    Views:
    11,280
  3. crespoh

    retrieve data from 2 database

    crespoh, Jul 28, 2005, in forum: Python
    Replies:
    3
    Views:
    4,518
    Steve Holden
    Jul 28, 2005
  4. =?Utf-8?B?c2NvdHRybQ==?=

    retrieve binary data from database and display in browser

    =?Utf-8?B?c2NvdHRybQ==?=, Mar 23, 2007, in forum: ASP .Net
    Replies:
    2
    Views:
    698
    =?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=
    Mar 23, 2007
  5. hib
    Replies:
    0
    Views:
    2,543
Loading...

Share This Page