SEARCH YOUR SOLUTION HERE  

problems with LIKE query

Hi

I have an access database which has a table full of users and their scores and another table full of unacceptable user names.

If a user tries to enter their name as "damn this game" i want the asp to spot that 'damn' is listed in my table (tblSwear) and replace the entire name with "anon".

I read on 4 from rolla that when using a like statement if u place % around either side of the word u are comparing then it should allow for other characters appearing around the word.

ie '%damn%'
would find "blah-damn-blah"

but for some reason my code below will only detect a user entering purely "damn" despite my use of the % symbol and it will not detect blahdamnblah"

Code: dim thename thename=request("rude") sql = "SELECT * FROM tblSwear WHERE tblSwear.word LIKE '%" & thename & "%'" 'Select * from Tablename where (Firstname) LIKE '%" & FirstNameVar & "%'" rs.open sql,myconn,3,3 if rs.recordcount > 0 then response.Write("anon") else response.Write(thename) rs.close end if I keep staring at my code and i cant see what the mistake is, i have attached it and the database and files in case any1 has the time to give it a glance.

I will be so grateful for any help.

Posted On: Tuesday 20th of November 2012 12:01:41 AM Total Views:  137
View Complete with Replies




Related Messages:

Import of db problems   (96 Views)
Hi , I have a database that has innodb tables with foreign keys. The export via phpmyadmin runs fine. When I try to import the db I receive foreign keys errors for tables that have a reliance on tables that come "after" it. The table structure is already in place, just that the tables are empty and I am trying to place the data in them. Am I exporting this incorrectly I have started doing this one table at a time, but that is a pain and slow. Is there another way Thank you for your time, oach
Upgraded to MYSQL V 5.0 Backup problems   (75 Views)
Hi I have just tried to back up my databases using the command mysqldump -u username -p database name > path to backup file It has backed up all the table structures but not any of the data in the tables Can anyone advise
Is mysql causing firewall problems (FW-1 at fw01: Access denied)?   (227 Views)
This seems unlikely to me, but I figured I'd ask anyway. For the last several years I have been running a phpbb forum with winxp, apache, php and mysql. None of my users has ever had trouble accessing it. Over the weekend, I tinkered with mysql to tighten up security. Specifically, I - created a mysql user just for the forum - granted that user database specific privs but not global privs - added skip-networking to my.ini to disallow all mysql tcp/ip connections and enabled pipes as described here: http://forums.devshed.com/mysql-help-4/properly-secuiring-msyql-333574.html ) I did not change my apache settings or any settings other than mysql settins. Now, I hear from a user that she can't get into the forum at her work and she is experiencing this error: Quote: FW-1 at smtlfw01: Access denied. I'm guessing her problem is unrelated to my mysql tinkering but I figured I'd ask here just to be sure. Any chance they are related
Transfering database problems   (132 Views)
I have a word press site on my server, that I am transferring to another server. I have dumped and saved my database. There is one database named "Information schema." When I try put this database into the new server, I get the following error message: Code: #1163 - The used table type doesn't support BLOB/TEXT columns I have removed one table was causing this error. Now another table is giving me the same error. I understand what this error means, but I don't see what is causing it. Here's what the table looks like: Code: -- -- Table structure for table `COLUMN_PRIVILEGES` -- -- CREATE TEMPORARY TABLE `COLUMN_PRIVILEGES` ( -- `GRANTEE` varchar(81) NOT NULL default '', -- `TABLE_CATALOG` varchar(512) default NULL, -- `TABLE_SCHEMA` varchar(64) NOT NULL default '', -- `TABLE_NAME` varchar(64) NOT NULL default '', -- `COLUMN_NAME` varchar(64) NOT NULL default '', -- `PRIVILEGE_TYPE` varchar(64) NOT NULL default '', -- `IS_GRANTABLE` varchar(3) NOT NULL default '' -- ) ENGINE=MEMORY DEFAULT CHARSET=utf8; -- -- Dumping data for table `COLUMN_PRIVILEGES` -- what should I do to resolve this dilemma
Joining problems....I think.....   (274 Views)
Hi folks. If I use this query: Code: SELECT p.developmentId development , p.apartmentNum apartment , p.paymentYear , ao.totalDue, ao.otherAmount, ao.otherDescription , SUM(CASE p.paymentMonth WHEN 1 THEN p.payment ELSE 0 END) 'Jan' , SUM(CASE p.paymentMonth WHEN 2 THEN p.payment ELSE 0 END) 'Feb' , SUM(CASE p.paymentMonth WHEN 3 THEN p.payment ELSE 0 END) 'Mar' , SUM(CASE p.paymentMonth WHEN 4 THEN p.payment ELSE 0 END) 'Apr' , SUM(CASE p.paymentMonth WHEN 5 THEN p.payment ELSE 0 END) 'May' , SUM(CASE p.paymentMonth WHEN 6 THEN p.payment ELSE 0 END) 'Jun' , SUM(CASE p.paymentMonth WHEN 7 THEN p.payment ELSE 0 END) 'Jul' , SUM(CASE p.paymentMonth WHEN 8 THEN p.payment ELSE 0 END) 'Aug' , SUM(CASE p.paymentMonth WHEN 9 THEN p.payment ELSE 0 END) 'Sep' , SUM(CASE p.paymentMonth WHEN 10 THEN p.payment ELSE 0 END) 'Oct' , SUM(CASE p.paymentMonth WHEN 11 THEN p.payment ELSE 0 END) 'Nov' , SUM(CASE p.paymentMonth WHEN 12 THEN p.payment ELSE 0 END) 'Dec' , p.reminder FROM payments p, amountowed ao WHERE p.paymentYear = YEAR(CURDATE()) AND p.developmentId = AND p.developmentId = ao.developmentId AND p.apartmentNum = ao.apartmentNum Group BY development,apartment; ....my query works great, and returns expected results. But then, I need to retrieve a column value from another table called 'Owners'. When I update my query to the following, my monthly results are all over the show! It must be something to do with the join, but I just can't fix it! Please help! Code: SELECT p.developmentId development , p.apartmentNum apartment , p.paymentYear, o.Name , ao.totalDue, ao.otherAmount, ao.otherDescription , SUM(CASE p.paymentMonth WHEN 1 THEN p.payment ELSE 0 END) 'Jan' , SUM(CASE p.paymentMonth WHEN 2 THEN p.payment ELSE 0 END) 'Feb' , SUM(CASE p.paymentMonth WHEN 3 THEN p.payment ELSE 0 END) 'Mar' , SUM(CASE p.paymentMonth WHEN 4 THEN p.payment ELSE 0 END) 'Apr' , SUM(CASE p.paymentMonth WHEN 5 THEN p.payment ELSE 0 END) 'May' , SUM(CASE p.paymentMonth WHEN 6 THEN p.payment ELSE 0 END) 'Jun' , SUM(CASE p.paymentMonth WHEN 7 THEN p.payment ELSE 0 END) 'Jul' , SUM(CASE p.paymentMonth WHEN 8 THEN p.payment ELSE 0 END) 'Aug' , SUM(CASE p.paymentMonth WHEN 9 THEN p.payment ELSE 0 END) 'Sep' , SUM(CASE p.paymentMonth WHEN 10 THEN p.payment ELSE 0 END) 'Oct' , SUM(CASE p.paymentMonth WHEN 11 THEN p.payment ELSE 0 END) 'Nov' , SUM(CASE p.paymentMonth WHEN 12 THEN p.payment ELSE 0 END) 'Dec' , p.reminder FROM payments p, amountowed ao, owners o WHERE p.paymentYear = YEAR(CURDATE()) AND p.developmentId = AND p.developmentId = ao.developmentId AND p.apartmentNum = ao.apartmentNum AND p.apartmentNum = o.apartmentNum Group BY development,apartment;
SELECT pid where ( word = '123' AND word = '456') - problems   (79 Views)
Hi I'm missing something pretty obvious here today... again. I've got a DB table called "index" that contains 2 columns being "pid" (page id) and "wid" (word id). What I want to do is select all pid's that have... this word, and this word, and ( this word or this word). So I go... select pid from index where ( word = '123' ) AND ( word = '324' ) AND ( word = '456' OR word = '678' OR word = '876' ) But even though I know there is a page with pid 1 that has rows in the table with all of those numbers, nothing gets returned - nor does MySQL return an error. What am I doing wrong
Mysql with xampp - max_allowed_packet problems   (115 Views)
Trying to do a db restore from a .sql dump... via cmd console in Vista. Keep getting Code: ERROR 1153 (08S01) at line 307: Got a packet bigger than 'max_allowed_packet' bytes Tried upping the max_allowed_packet values in my.cnf ... as high as 1024M. No change. Read in the my.cnf file itself that server config can be managed using same file in the /data director... copied it there w/large values for max_allowed_packet. Still no change. Read at mysql site about a my.ini file, so tried upping the max_allowed_packet value there also. No change. So questions are these...Could it be that I've actually just got a really bad line in my db dump file If so, any ideas how I can remove it The file is so large (400+mb) it crashes my text editor.Is there something else that could be causing these max packet size errors Also tried doing an import in phpmyadmin in firefox via localhost... it seemed to go for about an hour then FF finally crashed. Not my day.
LEFT JOIN problems   (82 Views)
Hi . I'm having a bit of an issue with a left join query. Here's the essence of the problem, using a hypothetical dataset (please don't blame me about any normalization issues; they're out of my control): Code: table1 ******************* SEQ ID ******************* 1 'fred' 2 'barney' 3 'wilma' 4 'pebbles' 5 'bambam' table2 ******************* SEQ TYPE ******************* 1 'foo' 2 'bar' 3 'foo' 4 'baz' 5 'bar' What I'd like to get is the following output: Code: ******************* ID TYPE ******************* 'fred' NULL 'barney' 'bar' 'wilma' NULL 'pebbles' NULL 'bambam' 'bar' Here's my latest attempt (which doesn't work): SELECT t1.ID, t2.TYPE FROM db.table1 t1 LEFT JOIN db.table2 t2 ON (t1.SEQ = t2.SEQ OR t2.SEQ IS NULL) WHERE t2.TYPE = 'bar'; Never was good with joins. Any help would be appreciated. , I just deleted my test table this morning, so I can't re-verify, but r937's solution produces the correct result with the schema and data given and is the preferable query. If it's not working for your real schema and data then post the schema and actual sample data and we can work something out for you. PS. If you post CREATE TABLE statements then I am much happier. INSERTs are great too Quote: Originally Posted by angrytuna I try to distill problems as people are busy and don't have time to read full complicated problem descriptions. It's a slow day at work and I'm looking for something to do...please post the full problem
Convert to 5.0 with password problems   (98 Views)
I am having to go from 4.0 to 5.0. A problem I am having is with passwords. I found out about the 4.1 hashing. What I found I can do is use he password = PASSWORD() OR OLD_PASSWORD() and it finds the older passwords. The problem is I it won't find an entry that has the new password. My password cells are set to varchar(50) so I think they are not being cut short. The only other thing I was thinking was a collation problem. I set my DB to "latian1_general_ci". Does anyone have any suggestions why I cant find a new entry by looking for a password
Having problems passing this SQL statement   (77 Views)
hi , i'm passing an SQL statement to the commandtext of OleDB control in VB2005 and it says INSERT INTO Syntax error whenever i executenonquery here's the SQL string INSERT INTO UseProfiles (GroupID,LoginName,Password,CUser,FirstName,LastName,StreetAddress,City,Phone,MobilePhone,EMailAddre ss,RecordHistory) Values ('1177612676', 'Random', 'randompassword', 'MAT', 'Mo', 'AT', 'Greensvile', 'Lo', '2822487', '055-585484', 'random@gmail.com', 'Yes') all the fields are text (Access database) could any one point out to me the syntax error really need answers urgently thanx
Union causing major problems - please help!!   (121 Views)
I am dynamically building a huge MYSQL query containing many joins, sub queries, and selecting many values. I need to use a union also. Prior to the union I have one record of data and after the union I have a second record for an associated person. The end result is a CSV file. Anyway, if I run the everything prior to the union and everything after the union seperately, it works fine. If I try to run the whole thing at once, my results for the second half are not in the correct columns. for example: Type | ID | Fname | City ___________________________ '1' | 33 | Homer | Springfield ___________________________ '2' | 33 | Springfield | Betty As you can see, the records as associated based on the ID. They are seperated based on Type. Type 1 would be the first half of the union which is correct. Type two would be the second half of the union, and you can see the results are wrong. Now in reality, this query I am building is dynamic and possibly contains over 50 columns (some of which are subqueries.) If anyone can help me I would appreciate it. I am going nuts with this final part of what I'm working on, and this pitfall is irritating. Do my SELECT columns need to be in the same order on both sides of the union That could get hellish due to the dynamic nature of it.
Ft_min_word_len causing problems   (96 Views)
Hi all We've recently updated the config for our MySQL server (4.1.12) to include the following: Code: [mysqld] ft_min_word_len=3 [myisamchk] ft_min_word_len=3 This is causing us some problems when we do an update, we get an error message Error 1016: Can't open file: 'table.MYI' Anybody know what might be causing this We're having to do a repair everytime this happens, which is a hidrance.
Phpmyadmin problems   (79 Views)
I hope this is the right place for this, here is the issue. I have a webserver running the latest stable apache/php/mysql, i tried to install a mail client (dovecot) but it installed mysql along with it. I reinstalled the mysql I had before and found that a bunch of tables needed repairing (had not been closed). Now the problem is an odd one, all my sites work fine, but phpmyadmin says there are no databases. The dropdown still has a list of databases though. If I select one then it says there are no tables. I can run the query "Show Tables" and it lists the tables as a result (so it can see them). Any idea what is causing this
Page 2 - changing commas by joins (was "MySQL query problems")   (79 Views)
I executed something like this SQL SELECT alfa, beta, gamma FROM table WHERE MATCH (alfa, beta) AGAINST ('word') It returns 27 results in 4.1 and 0 in 5.0
Sql 2005 between problems   (132 Views)
peebman2000, i'm in my final stages of building a .net ticket system for a client at work and i'm having a problem with my query. I have a between that is inclusive, in a date range it is not including in the date of a record that matches the todate that the user enters. For example, to select record for the the dates between 02/01/2008-03/04/2008 and there is a record that has the date of 03/04/2008. The results only return records from 02/01/2008-03/03/2008 it does not include the record that has a date of 03/04/2008. In order for me to retrieve records between 02/01/2008-03/04/2008 the user has to put in a date range of 02/01/2008-03/05/2008. The user doesn't like this, because they'll enter in date ranges for monthly and quarterly reports. Below is my query, it does not return the correct results for dates between 02/01/2008-03/04/2008. I also tried doing >= and
Datatype problems with Stored Procedures   (125 Views)
Originally posted by : an S (aspielma@yahoo.com)Datatype problems with Stored ProceduresEvery now and then the evil computer stumps me and I have to go for help.Julio, do you have any idea why SQL dows not like the parameters being passed by ASP The stored proc works when I run it from I_SQL, or use it in other routines.(1) ASP Code: (I am using constants for testing, and yet this still fails!)vDBCommand.CommandText = "prcUpdatePbr"vDBCommand.CommandType = adCmdStoredProcvDBCommand.Parameters.Append vDBCommand.CreateParameter("@PbrID", adInteger, adParamInput, , 1) vDBCommand.Parameters.Append vDBCommand.CreateParameter("@UsrId", adInteger, adParamInput, , 26)vDBCommand.Parameters.Append vDBCommand.CreateParameter("@PubNa", adChar, adParamInput, 45, "CAMPSINI PUBLISHING")vDBCommand.Parameters.Append vDBCommand.CreateParameter("@TaxId", adInteger, adParamInput, , "295504911")vDBCommand.Parameters.Append vDBCommand.CreateParameter("@PubCae", adInteger, adParamInput, , 400055)vDBCommand.Parameters.Append vDBCommand.CreateParameter("@PrAffSoc", adChar, adParamInput, 3, "003")vDBCommand.Parameters.Append vDBCommand.CreateParameter("@MrSoc", adChar, adParamInput, 3, "xxx")vDBCommand.Parameters.Append vDBCommand.CreateParameter("@SrSoc", adChar, adParamInput, 3, "xxx")vDBCommand.Parameters.Append vDBCommand.CreateParameter("@UpdUid", adChar, adParamInput, 15, "500")vDBCommand.Execute (line 176)(2) And here's my error:Microsoft OLE DB Provider for ODBC Drivers error '80040e07' CREATE PROCEDURE prcUpdatePbr[INTERSOLV][ODBC SQL Server driver][SQL Server]Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the CONVERT function to run this query. /cwrreg_0721/CommitLogin.asp, line 176 (3) Here is the stored procedure:CREATE PROCEDURE prcUpdatePbr @PbrId int, @UsrId int, @PbrNa varchar(45), @TaxId int, @CaeNr int, @PrSoc char(3), @MrSoc char(3), @SrSoc char(3), @UpdUid char(15)AS UPDATE tblPublisher SET PbrId = @PbrId, UsrId = @UsrId , PbrNa = @PbrNa, TaxId = @TaxId, CaeNr = @CaeNr, PrSoc = @PrSoc, MrSoc = @MrSoc, SrSoc = @SrSoc, UpdTs = GETDATE(), UpdUid = @UpdUid WHERE tblPublisher.PbrId = @PbrIdAND tblPublisher.UsrId = @UsrId
SQL 7 database hosted on sql 2000 server - problems   (107 Views)
I had a working sql 7 database hosted on sql 7 server. I eventually backedup my sql 7 database and hosted it on a sql 2000 server. I now find that some of my tables have gone missing. Please let me know if I have to specifically do anything to my sql 7 database before hosting on the sql 2000 server
wordwrap problems with response.write   (85 Views)
Originally posted by : andrew (harrison@tiscalinet.it)When the results of my database search are printed out, the text is just printed on one long line and does not wrap. How can I alter: to correct this.To see what I mean you can see it at http://web.zonalingue.com/inmilan/form.ht
Any one else have problems with passing parameters...   (69 Views)
Originally posted by : Chrisb (gatorman@tds.net)Hi allI am at a lost to why asp.net will not pass these parameters to a SQL database:"SELECT * FROM main WHERE (myColumn LIKE '%%')"I have used this in the query analizer and have returned valid search. Is there some reason that vb.net or asp.net will not pass these valuesTIAChris
SQL join problems   (96 Views)
Originally posted by : Terry Inniss (elk@caribsurf.com)I have three tables that can be join but not by primary key. I have some inner joins to link them but my asp crashes on the said SQL statement. Can someone tell me what is the problem