ASP Database Error

[ofisboy 0]

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.

[Vermyn 0]

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

[ofisboy 0]

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.

[Vermyn 0]

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.

[ofisboy 0]

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!

[Vermyn 0]

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.

[ofisboy 0]

I think I'm going to go ahead and move to the SQL server and give it a shot. As luck would have it, my ISP does have a web interface so I can take care of the management myself. I think it's time I learned.

 

Thanks so much for all your help.