Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder
    Join Date
    Sep 2006
    Location
    Skopje, Macedonia
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    asp.net search for access database

    Hi!!!

    So again me with my questions!

    OK today's question:
    I implemented search for my database on my site but the thing is that i can search it only with whole names and if some visitor doesn't know the whle name of the thing that he searches for he won't find it. I am familliar with SQL queries and i know that i could use the % sign to replace characters no mather how much and i found that the same character in access is replaced with the *. So if you have any idea, I'm here!

    Thanks in advance!
    Lallo

  • #2
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,091
    Thanks
    2
    Thanked 23 Times in 23 Posts
    The % sign is used as part of a pattern search. That kind of search still works the same way whether you're talking about an Access database or any other kind of database. Show us your query. You may have something incorrect in the way that you've set it up.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • #3
    Regular Coder
    Join Date
    Sep 2006
    Location
    Skopje, Macedonia
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Code:
    sub Sorting(sender As Object, e As System.EventArgs)
    dim category as string = kategorija.Text
    if category = ""
    sql="SELECT * FROM muzika ORDER BY pejac ASC"
    else
    sql="SELECT * FROM muzika WHERE kategorija = '" & category & "' ORDER BY pejac ASC"
    end if
    dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("../../rezerva.mdb"))
    dbconn.Open()
    dbcomm=New OleDbCommand(sql,dbconn)
    dbread=dbcomm.ExecuteReader()
    customers.DataSource=dbread
    customers.DataBind()
    dbread.Close()
    dbconn.Close()
    end sub
    this is the code...
    all yours...

  • #4
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,091
    Thanks
    2
    Thanked 23 Times in 23 Posts
    Have you checked your query to see if category is being populated correctly? Have you tried running your query within Access? If so, does it give the expected results?
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • #5
    Regular Coder
    Join Date
    Sep 2006
    Location
    Skopje, Macedonia
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Code:
    sub Search(sender As Object, e As System.EventArgs)
    dim dbconn,sql,dbcomm,dbread
    dim searchBy as string = by.Text
    dim searchKey as string = key.Text
    sql="SELECT * FROM muzika WHERE " & searchBy & " = '" & searchKey & "' ORDER BY pejac ASC"
    dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("../../rezerva.mdb"))
    dbconn.Open()
    dbcomm=New OleDbCommand(sql,dbconn)
    dbread=dbcomm.ExecuteReader()
    customers.DataSource=dbread
    customers.DataBind()
    dbread.Close()
    dbconn.Close()
    key.Text=""
    end sub

    Sorry but i gave you wrong code...


    Anyway this code is already checked - the keyword is searchKey and what happens is:
    I have the following files in my database:
    1. Lallo --------- Dean Kocevski
    2. nickname --------- firstName lastName

    so when i pass as keyword 'dean kocevski' it will return Lallo but if I pass as keyword 'dean*' or '*kocevski' or '*dean*' or "*kocevski*" it returns NOTHING ....

    Ideas?

  • #6
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,091
    Thanks
    2
    Thanked 23 Times in 23 Posts
    So don't put the asterisks in the search box, or else filter them out prior to building your sql statement.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • #7
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,091
    Thanks
    2
    Thanked 23 Times in 23 Posts
    I thought about my last answer a little more and decided I should clarify it. You haven't shown us whatever search form you're using, but I assume it may have choices for exact phrase or wildcard choices (via the * you mentioned). So what you really need to do is set your "where" clause to an equal condition if they pick exact phrase and use the "like" structure if they have a wildcard in their selection. You still need to filter out the asterisk(s) though when building your query.
    Last edited by vinyl-junkie; 03-23-2007 at 04:22 PM. Reason: Wrong word. Gaaa!
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • #8
    Regular Coder
    Join Date
    Sep 2006
    Location
    Skopje, Macedonia
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks vinyl-junkie!!!! It works with the LIKE!!!!


  • #9
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,091
    Thanks
    2
    Thanked 23 Times in 23 Posts
    You're welcome! I'm glad I was able to help.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • #10
    Regular Coder
    Join Date
    Sep 2006
    Location
    Skopje, Macedonia
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I just need one more information.
    I just bought donamin name and hosting and on the server they offer me mySQL. OK I am happy about that but I cannot connect to the database. The code that I use is the following:

    Code:
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.Odbc" %>
    
    <script runat="server">
      private const string ConnStr = "Driver={MySQL ODBC 3.51 Driver};" +
        "Server=localhost;Database=(my database name);uid=(username);pwd=(password)";
    
      protected override void OnInit(EventArgs e)
      {
        base.OnInit(e);
    
        using(OdbcConnection con = new OdbcConnection(ConnStr))
        using(OdbcCommand cmd = new OdbcCommand("SELECT * FROM (table name", con))
        {
          con.Open();
          dgrAllNames.DataSource = cmd.ExecuteReader(
            CommandBehavior.CloseConnection | 
            CommandBehavior.SingleResult);
          dgrAllNames.DataBind();
        }
      }
    </script>

    I already created the database I am sure for the user and the pass and for sure that i created the table that I need...

    Any idea?

    PS: I think that I have wrong access to the database's path but I don't know how to fix it

  • #11
    Regular Coder
    Join Date
    Sep 2006
    Location
    Skopje, Macedonia
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Smile

    About the previous reply - I solved the problem!

    Anyway thanks for everything, very soon my site will be ready and I'll inform you to see what have I done


    Best Regards, Lallo


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •