asp - records not updating - access database

[spastica]

I am using asp with an access database.

I have a column with "ListOrder" that specifies the order each announcement is placed in on the website. I want to automatically update this column each time there is a new entry, by increasing the list order by 1 for each entry that has a list order higher than the new entry (so, say I have a new entry with a list order of 3, I would like all announcements that have a list order of 3 or more to increase by 1 - so 3 moves down to 4, 4 moves down to 5 and so on)

Here is the code I'm using to update the colum. It returns no records, even though there are records higher than the new entry.

The code doesn't update the column either - what could I be doing wrong? the data type for the column is number - long integer. A previous version of code was returning "-1" records - and I'm not sure what that means. Sorry, if these are silly questions, I'm still learning!

thanks in advance!



'---------------
dim SQL_SelLorder, SQL_updateLorder, newLorderNo, rs2
newLorderNo = Request.Form("lorder")

SQL_SelLorder="SELECT * FROM MainNews WHERE ListOrder>=" & newLorderNo &""
On Error Resume Next

SQL_updateLorder = "UPDATE MainNews SET ListOrder=" & currentLorderNo & ""&_
"WHERE ID = " & id & ";"
On Error Resume Next


set rs2 = Server.CreateObject("ADODB.Recordset")

rs2.Open SQL_SelLorder, sDATA_SOURCE

numRecords = 0

while not rs2.EOF

dim currentLorderNo, id
currentLorderNo=rs2("ListOrder")
id=rs2("id")
currentLorderNo = currentLorderNo + 1

SQL_updateLorder, sDATA_SOURCE
rs2.MoveNext()
WEnd

If numRecords = 0 Then
response.write("no records")
end if

Set rs2 = Nothing
'----------

 

[FesterSXS]

first things first - try this. This will force your lorder number to the same data type as it is in the database (an integer). This is probably why you are not getting any results returned...

newLorderNo = CInt(Request.Form("lorder"))

Second - a much easier way of updating the records you want is to do it like this. Since you are only updating records then there is no need to create a recordset. Let SQL do all the work...

Set objConn = Server.CreateObjext("ADODB.Connection")
objConn.Open(sDATA_SOURCE)

SQL_updateLorder = "UPDATE MainNews SET ListOrder=ListOrder+1 WHERE ListOrder>=" & newLorderNo

objConn.Execute(SQL_updateLorder)

Set objConn = Nothing

Tony
________________________________________________________________________________

http://www.SpiraxSarco.com

 

[spastica]

thanks Tony - but that still isn't working - I'm still getting -1 as the amount of records returned (i'm using rs2.recordcount to get that amount)

I can't figure out what could be wrong!

 

[FesterSXS]

That is because of the cursorType of the recordset. You would need to change your rs2.Open line to this:

rs2.Open SQL_SelLorder, sDATA_SOURCE, [b]adOpenStatic[/b]

adOpenStatic is an ADO constant that is declared in your msado.dll. This can be included in your page like this...

<!-- METADATA TYPE="typelib" FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->

I'm curious as to why are you still using rs2.recordcount though. Are you trying to get a value of the amount of results that have been affected by the update?

Tony
________________________________________________________________________________

http://www.SpiraxSarco.com

 

[spastica]

thanks Tony! I'm getting the right number or records returned now, but they are still not updating

I'm using rs2.recordcount just for testing purposes to see if the correct number of records is being found.

 

[FesterSXS]

Are you using the Update code I suggested instead of doing it inside a recordset? Post your code as it is now please and I'll take another look for you.

Tony
________________________________________________________________________________

http://www.SpiraxSarco.com

 

[FesterSXS]

just a thought...What data type is ListOrder in your database? I have been assuming it is an integer. Are you sure its not a text data type?

Tony
________________________________________________________________________________

http://www.SpiraxSarco.com

 

[spastica]

hi Tony,

I tried your code but I was trying to use it in a loop, which is why it didn't work. It works perfectly now - thank you for a very helpful and very elegant solution to my problem

'---------------
dim SQL_updateLorder,newLorderNo, objConn
newLorderNo = CInt(Request.Form("lorder"))

SQL_updateLorder = "UPDATE MainNews SET ListOrder=ListOrder+1 WHERE ListOrder>=" & newLorderNo

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open(sDATA_SOURCE)


objConn.Execute(SQL_updateLorder)

Set objConn = Nothing
'----------

 

[FesterSXS]

Thats great news - glad to help out

Tony
________________________________________________________________________________

http://www.SpiraxSarco.com

 

[cawi17]

Hey guyz... how about using rs.Update instead of using swlstring.... I've trie'd to open a table and then update it using rs.Update... Which locktype or command should we use...?

I've tried using the rs.Update but it doesn't work....
Error Type:
ADODB.Recordset (0x800A0CB3)
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.
/cd2buy/updatecart.asp, line 56

....

Here are the codes


Set cn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.RecordSet")

dataConn= "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("cd2buy.mdb")
cn.open dataConn


sqlstring = "SELECT * FROM CART WHERE CART.Customer_User_Name='" & Session("username") & "'"
rs.Open sqlstring,cn



.....


For i = 0 To UBound(quantityarray)

rs("Quantity") = quantityarray(i)
rs.Update

rs.Movenext
next

......

Thank you...

 

[FesterSXS]

rs.Open sqlstring, cn, adOpenStatic

dont forget to include the msado15.dll file as this contains the ADO constants like adOpenStatic.


Tony
________________________________________________________________________________

http://www.SpiraxSarco.com