Helpful Information

Category: ASP Programming

Error when deleting multiple records in a database

Hello Board,

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.

Hello User!

The command you used to delete the columns is invalid.

Hello M.Hirsch,

This is the command I am using:

DELETE FROM Table WHERE username=someusername OR username=someusername OR username = someusername

This a multiple delete

Thanks

i knew it...

DELETE FROM Table WHERE username='someusername' OR username='someusername' OR username = 'someusername';

Tried that, same error message.

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?

dim num
dim xide
dim whr
dim sql
whr=""
xide=split(request.form("checkbox"),",",-1,1)
for each num in xide
if whr<>"" then whr=whr & " OR "
whr=whr & "username=" & num
next
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
Command1.Execute()
Response.Write(sql)

Hope this helps.

you stil didn�t put my corrections in there...

whr=whr & "username='" & num & "'"

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.

-Jay

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?)

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.

Cheers,

Version

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 :)

Originally posted by M.Hirsch
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 :)

the trick is IIRC to put a ' into the string before the @.

so the final string owuld look like "name'@place.com"
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 ' )