...

View Full Version : asp.net search for access database



Lallo
03-22-2007, 06:19 PM
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

vinyl-junkie
03-23-2007, 05:28 AM
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.

Lallo
03-23-2007, 05:33 AM
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...

vinyl-junkie
03-23-2007, 05:53 AM
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?

Lallo
03-23-2007, 06:11 AM
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?

vinyl-junkie
03-23-2007, 01:08 PM
So don't put the asterisks in the search box, or else filter them out prior to building your sql statement.

vinyl-junkie
03-23-2007, 05:14 PM
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.

Lallo
03-24-2007, 04:52 PM
Thanks vinyl-junkie!!!! It works with the LIKE!!!!

:):):):):):):)

vinyl-junkie
03-24-2007, 04:59 PM
You're welcome! :) I'm glad I was able to help.

Lallo
03-24-2007, 05:06 PM
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:


<%@ 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

Lallo
03-25-2007, 06:43 AM
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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum