February 2nd, 2003, 11:20 AM
Error when deleting multiple records in a database
I get this message when trying to delete:
The column prefix 'username' does not match with a table name or alias name used in the query.
I am not sure what this error is. I checked all over the place for the problem. Can someone help.
Thanks in advance.
Last edited by zwarte; February 2nd, 2003 at 01:54 PM.
February 2nd, 2003, 11:27 AM
The command you used to delete the columns is invalid.
February 2nd, 2003, 12:10 PM
This is the command I am using:
DELETE FROM Table WHERE username=someusername OR username=someusername OR username = someusername
This a multiple delete
February 2nd, 2003, 12:25 PM
i knew it...
DELETE FROM Table WHERE username='someusername' OR username='someusername' OR username = 'someusername';
February 2nd, 2003, 12:54 PM
Tried that, same error message.
February 2nd, 2003, 12:57 PM
to explain my assumptions (i never did a single line of ASP or VBScript):
The error message seems to tell that somehow data from a column or variable ("info@interviewprep") got into the query in a place / way that it is being interpreted as column name (-prefix).
Are you running another query before or after this one?
Can we have some lines of your code?
February 2nd, 2003, 01:53 PM
for each num in xide
if whr<>"" then whr=whr & " OR "
whr=whr & "username=" & num
sql="DELETE FROM Table WHERE "
sql=sql & whr
set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = "dsn=mydsn"
Command1.CommandText = sql
Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Hope this helps.
February 2nd, 2003, 01:56 PM
you stil didnīt put my corrections in there...
whr=whr & "username='" & num & "'"
February 2nd, 2003, 02:05 PM
Yeah, I did. I just didn't post that copy in. I revereted to original that gave me problems, cause this is doing the same.
Thanks for your concern.
February 2nd, 2003, 02:11 PM
Sorry, then this problem is beyond my scope.
Maybe anyone else here can help? (i know there is few people here doing ASP, but maybe someone new here?)
February 2nd, 2003, 02:26 PM
No worries! I used what you suggested and switched criteria to match and ID. For some reason, @ symbol was the problem.
I appreciate everything. Thumbs up.
February 2nd, 2003, 02:35 PM
thinking about it again. the "@" is the problem. is it a special char in ASP? like defining a variable or array or something?
in php/mysql, you would have to escape the string with mysql_escape_string() or something similar to make it compatible to the databasesīs expected format.
there should be a similar command for ASP that should solve your problem.
tnx for the flowers
February 3rd, 2003, 03:01 PM
the trick is IIRC to put a ' into the string before the @.
so the final string owuld look like "firstname.lastname@example.org"
that's what you use in SQL to add ' to the string "that's it" would need too look like "that''s it" (with the middle '' being 2 of ' )