delete from database

[Mhtml]

I have a problem, I keep getting this error an I have no idea why..
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/odyssey/linkdelete.asp, line 13

here is my asp

<%
dim Lname, Lurl, Ldesc
Lname = Request.Form("linkname")
Lurl = Request.Form("linkurl")
Ldesc = Request.Form("Linkdesc")
Lid = Request.Form("linknumber")
'This is the code used to make a connection to our database
Set ConnSQL = Server.CreateObject("ADODB.Connection")
path = Server.MapPath("databases/odyssey.mdb")
ConnSQL.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & path

sqlDelete = "DELETE FROM Links WHERE LinkID = '"&Lid&"' "
ConnSql.Execute(sqlDelete)
%>

<%
ConnSQL.Close
Set ConnSQL = nothing
Set RS = nothing
%>

<%response.Redirect("links.asp")%>

[glenngv]

maybe the data type of LinkID is numeric

sqlDelete = "DELETE FROM Links WHERE LinkID = " & Lid

[Mhtml]

oh my, you sir are a legend. Where did you learn all this?

[Mhtml]

Ok, now I have a problem. I was using the auto number to delete records but if I delete a record the number for each record stays the same.

How can I make it adjust the numbers so that if I delete say record number 1 with an autonumber of 1 then record 2 will then have a autonumber of 1 and so on...

[whammy]

You can't do that with autonumber field.

What you'd need to do is have another updatable field that you can change the number on depending on what you deleted/inserted.

Or, you can delete the autonumber field and then create it again. That will reset all the numbers.

[Mhtml]

didn't think it would be that easy, I couldn't find any functions for that sought of thing in access... So I changed my delete method to delete by site name but I'm not sure whether url would be more practical...Or can I some how stop people from entering a duplicate site name?


But then again someone may have a legit site with the same name...I think I will stick to the url search and destroy method..lol

[whammy]

Obviously URL would be more practical, since URLs are unique by nature. I'd go with that.

P.S. If you want to make sure noone else enters the same site name, just check to see if the URL already exists in the database, before inserting a new record.

[mark123]

Originally posted by whammy
Or, you can delete the autonumber field and then create it again. That will reset all the numbers.

Will the records still be in the same order? How does it work? Can you post some code?:thumbsup:

[Mhtml]

Gees, this thread was a while back. I can't believe how far I've come in asp since then.

Anywho I believe what Whammy meant was deleting the whole field, This can either be done in access by opening the table in design view deleting the field where auto number is and then inserting a new one with the same name and as an auto number.

Or you can do it code wise I think you can do it like this,

SqlDeleteCol = "ALTER TABLE Table_Name DROP Column_Name"
SqlInsertCol = "ALTER TABLE Table_Name ADD Column_Name Autonumber"
conn.execute(SqlDelteCol)
conn.execute(SqlInsertCol)


I may be wrong though.

[mark123]

Originally posted by Mhtml

SqlDeleteCol = "ALTER TABLE Table_Name DROP Column_Name"
SqlInsertCol = "ALTER TABLE Table_Name ADD Column_Name Autonumber"
conn.execute(SqlDeleteCol)
conn.execute(SqlInsertCol)


Thank you. I will experiment with it. :thumbsup:

[mark123]

Originally posted by mark123


Thank you. I will experiment with it. :thumbsup:

Gives the following error:


Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Cannot delete a field that is part of an index or is needed by the system.

cleanUp.asp, line 24

What's that mean? :confused:

Thanks:D

[Mhtml]

Have you got the database set to index?

[oracleguy]

FYI, http://www.codingforums.com/showthread.php?s=&threadid=10590

This seems like it relates to your question.

[mark123]

Originally posted by Mhtml
Have you got the database set to index?

I'm not sure...I don't have the app used to make the database so I asked a friend to build it for me.