ASP Database Error
I am trying to delete an item from a web site via a database but I'm getting the following error: Microsoft JET Database Engine error '80040e14' Syntax error (missing operator) in query expression 'CDListingID ='.
I am trying to delete an item from a web site via a database but I'm getting the following error:
Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression 'CDListingID ='.
/deleteconfirm.asp, line 28
Here is the script. Does anyone see a problem? I can't for the life of me figure it out:
<%
strUserName = session("UserName")
strAction = request("cmdAction")
'strSortType = request("SortType")
'strSortType2 = request("SortType2")
lID = request("ID")
If lID > 0 Then
sSQL = "Select * FROM CDListing WHERE CDListingID = " & lID
Set rsSearch = objADO.Execute(sSQL)
end if
if Request.Form <> "" and strAction = "Delete Listing" then
' Create a recordset object to store specific table information
Set RSSearch = Server.CreateObject("ADODB.Recordset")
'Establish what records you want selected for the SQL query string
SQL = "Delete * From CDListing where CDListingID = " & lID
'Set the recordset object equal to the SQL query string
RSSearch.Open SQL, objADO, 1, 3
Response.Redirect "listingdeleted.asp"
end if
%>
Any light you can shed is appreciated and thanks in advance for your help.
Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression 'CDListingID ='.
/deleteconfirm.asp, line 28
Here is the script. Does anyone see a problem? I can't for the life of me figure it out:
<%
strUserName = session("UserName")
strAction = request("cmdAction")
'strSortType = request("SortType")
'strSortType2 = request("SortType2")
lID = request("ID")
If lID > 0 Then
sSQL = "Select * FROM CDListing WHERE CDListingID = " & lID
Set rsSearch = objADO.Execute(sSQL)
end if
if Request.Form <> "" and strAction = "Delete Listing" then
' Create a recordset object to store specific table information
Set RSSearch = Server.CreateObject("ADODB.Recordset")
'Establish what records you want selected for the SQL query string
SQL = "Delete * From CDListing where CDListingID = " & lID
'Set the recordset object equal to the SQL query string
RSSearch.Open SQL, objADO, 1, 3
Response.Redirect "listingdeleted.asp"
end if
%>
Any light you can shed is appreciated and thanks in advance for your help.
Participate on our website and join the conversation
This topic is archived. New comments cannot be posted and votes cannot be cast.
Responses to this topic
I don't know why anyone would want to run a website from an Access database... but anyway ...
The only problem I can see is that you need to encapsulate part of your SQL statements in apostrophes. For instance, in your SELECT statement:
SELECT whatever FROM whatever WHERE whatever = " & whatever
That's essentially what you have. Proper syntax of this statement would be:
SELECT whatever FROM whatever WHERE whatever = '" & whatever & "'"
Note the apostrophes delimited the criteria to filter your results in the WHERE statement.
You would need to do the same for the DELETE statement.
--Alexander
The only problem I can see is that you need to encapsulate part of your SQL statements in apostrophes. For instance, in your SELECT statement:
SELECT whatever FROM whatever WHERE whatever = " & whatever
That's essentially what you have. Proper syntax of this statement would be:
SELECT whatever FROM whatever WHERE whatever = '" & whatever & "'"
Note the apostrophes delimited the criteria to filter your results in the WHERE statement.
You would need to do the same for the DELETE statement.
--Alexander
Here is the format I used with both the select and delete lines:
sSQL = "Select * FROM CDListing WHERE CDListingID = '" & lID & "'"
SQL = "Delete * From CDListing where CDListingID = '" & lID & "'"
Here is the new error I am receiving now:
Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.
/deleteconfirm.asp, line 28
Did I get the format wrong? I have purchased numerous scripts (both cgi/perl and asp) for different web sites I have created and only know enough to make minor modifications to the scripts, so please bear with me.
Out of curiosity, would you please explain to me the advantages of cgi/perl over asp? I just purchase scripts based on what features they offer and not on any kind of technical expertise.
Thx again.
sSQL = "Select * FROM CDListing WHERE CDListingID = '" & lID & "'"
SQL = "Delete * From CDListing where CDListingID = '" & lID & "'"
Here is the new error I am receiving now:
Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.
/deleteconfirm.asp, line 28
Did I get the format wrong? I have purchased numerous scripts (both cgi/perl and asp) for different web sites I have created and only know enough to make minor modifications to the scripts, so please bear with me.
Out of curiosity, would you please explain to me the advantages of cgi/perl over asp? I just purchase scripts based on what features they offer and not on any kind of technical expertise.
Thx again.
sSQL = "Select * FROM CDListing WHERE CDListingID = '" & lID & "'"
SQL = "Delete * From CDListing where CDListingID = '" & lID & "'"
These statements are formed correctly for syntax. Now your error is most likely a problem with trying to look up data that is of a different datatype in the database.
This can be tough to hunt down and exterminate. The basics of this is:
1. The database has a column named "lID" that is of datatype integer.
2. The variable being used in lID in your code is a string.
When you execute the SQL, you're trying to lookup a string when the JET engine is expecting you to give an integer for that column.
This is tough to chase down because you have to see where lID is declared in the code as a string (or whatever datatype it really is) and compare the database to see what datatype it expects. Sometimes you have to make adjustments to this for it to work correctly, too.
As for your other question... I'm not suggesting cgi/perl is better. I'm suggesting that Access databases should not be used to drive a website. You should use a SQL Server 7 or 2000 to handle this job. Access databases have fundamental limitations that will hinder a website eventually when there are a lot of hits. Access databases are also quite slow when accessed like this.
I like ASP... I prefer ASP.NET (I do a lot of programming in ASP.NET/SQL Server 2000), but classic ASP is sometimes a nightmare.
cgi/perl is definitely not any easier! I would dare to say it's tougher to deal with. If you're using ASP, chances are your site is on a Windows server and getting it to run cgi/perl could be a literal b!tch.
SQL = "Delete * From CDListing where CDListingID = '" & lID & "'"
These statements are formed correctly for syntax. Now your error is most likely a problem with trying to look up data that is of a different datatype in the database.
This can be tough to hunt down and exterminate. The basics of this is:
1. The database has a column named "lID" that is of datatype integer.
2. The variable being used in lID in your code is a string.
When you execute the SQL, you're trying to lookup a string when the JET engine is expecting you to give an integer for that column.
This is tough to chase down because you have to see where lID is declared in the code as a string (or whatever datatype it really is) and compare the database to see what datatype it expects. Sometimes you have to make adjustments to this for it to work correctly, too.
As for your other question... I'm not suggesting cgi/perl is better. I'm suggesting that Access databases should not be used to drive a website. You should use a SQL Server 7 or 2000 to handle this job. Access databases have fundamental limitations that will hinder a website eventually when there are a lot of hits. Access databases are also quite slow when accessed like this.
I like ASP... I prefer ASP.NET (I do a lot of programming in ASP.NET/SQL Server 2000), but classic ASP is sometimes a nightmare.
cgi/perl is definitely not any easier! I would dare to say it's tougher to deal with. If you're using ASP, chances are your site is on a Windows server and getting it to run cgi/perl could be a literal b!tch.
I have absolutely NO familiarity with SQL but would like to try it since I've read that it is superior to the database driven sites I currently have.
I can move to an SQL server. What would I have to do to make that transition? Is there some conversion process I would need for the database or would I be starting from scratch? Hmmm ... sounds like I'm rambling because I don't know what I'm talking about so it's up to you to interpret what I'm thinking and NOT what I'm saying!
I can move to an SQL server. What would I have to do to make that transition? Is there some conversion process I would need for the database or would I be starting from scratch? Hmmm ... sounds like I'm rambling because I don't know what I'm talking about so it's up to you to interpret what I'm thinking and NOT what I'm saying!
There is an upgrade path from Access to SQL Server, but importing/converting the data automatically will require access to the Enterprise Manager. To load this utility, you would need a SQL Server 2000 CD to install the client tools.
Unless of course, your host provider has a web interface or whatnot to manage the SQL Server, but this is unlikely. You might want to ask them.
Unless of course, your host provider has a web interface or whatnot to manage the SQL Server, but this is unlikely. You might want to ask them.
