Microsoft OLE DB Provider for ODBC Drivers error '80040e24'

[victoria_1018]

Hi Guys,
Does anyone know what is the following meaning about?

Microsoft OLE DB Provider for ODBC Drivers error '80040e24'
Rowset does not support fetching backward.
/TechnicalReport/WorksOrder/AddWorksOrderNo2.asp, line 27

I am using the following codes to generate a Works Order No.

Dim nextId, diff_Id, counter, Zeros
custrs.movelast
nextId = (Right(custrs("WorksOrderNo"),5))
nextId = nextId + 1
diff_Id = 5 - Len(nextId)
counter = 0
Zeros = ""
While counter < diff_Id
Zeros = "0" & Zeros
counter = counter + 1
Wend
custrs.AddNew
custrs("WorksOrderNo") = Zeros & nextId
session("WorksOrderNo") = custrs("WorksOrderNo")


Before the Works Order No is generated, it is stored in the Microsoft Database as ¡§-¡§. The generated Works Order No will replace the ¡§-¡§ will a number when the administrator approved the works order.

I had tried using this method in quite a number of Adding program and it works fine, however, this is the first time I am using on an Update Program, I had spent hours solving the bugs but it just don¡¦t work.

I had also attached the codes together with this request for help message.

Thanks
Regards
Victoria

PS: Before a Works Order No is generate, the Works Order No is pass from pages using hidden fields

[glenngv]

http://www.lupuspernix.com/lupuspernix/resources/read.asp?NoteID=21

[victoria_1018]

I had read these earlier and as I did not using CursorType to general the new number, that is why I don't quite understand how that works and how to make my work.

Thanks

[glenngv]

i think you can't do this:

Set custrs= custConn.Execute("select * from WorksOrder where Company like '"&session("Company")&"'")
'other codes here
custrs.AddNew
custrs("WorksOrderNo") = Zeros & nextId

you do a SELECT then in the recordset object returned, you will do an AddNew.
what you need to do is to execute a separate SQL statement which is UPDATE.

[victoria_1018]

Hi Glenn,
Am I going to change the following statement,

custrs.Update
custrs("WorksOrderNo") = Zeros & nextId
session("WorksOrderNo") = custrs("WorksOrderNo")

Or use a new set of Update statement?

If I were to use another new set of Update statement, what should I do to stick back to my codes to generate a new number or what other methods do you recommend?

Another question is I am doing a add new here as I need to come out with a new number for the approved works order, and there will be another update statement on the new page, which is AddWorksOrderNo3.asp to update all the information on AddWorksOrderNo2.asp.

Will it affect my program if I were to do an update statement each on both pages?

Thanks
Regards
Victoria

[victoria_1018]

I had try doing an Update Statement, will is pasted below and it don't work, returing me the same error message.
What kind of update must I do inorder to get it work?

Dim nextId, diff_Id, counter, Zeros
custrs.movelast
nextId = (Right(custrs("WorksOrderNo"),5))
nextId = nextId + 1
diff_Id = 5 - Len(nextId)
counter = 0
Zeros = ""
While counter < diff_Id
Zeros = "0" & Zeros
counter = counter + 1
Wend

custrs("WorksOrderNo") = Zeros & nextId
session("WorksOrderNo") = custrs("WorksOrderNo")

sql = "Update WorksOrderNo FROM WorksOrder WHERE Company like '"& session("Company") &"' "
Set custrs = custConn.execute(sql)

Thanks

[raf]

i think glengv is right.

it looks to me your confusing a recorset with the database it was created from. a recordset is just the result of a selection on a database (a 'view' on the database)

if you want to insert a new record, you can't do it by adding a record to the recordset. (it's no because you paint a baird on a photo, that the person grows a baird). the same for chainging the information in a record. Chainging it in the recordset isn't useful. You can change the output to a page etc, buth all these operations won't affect the data in your database.

to change the data in your database, you need an updatestatement. Something like

sql="update WorksOrder set workorderno=anumber where Company like '"&session("Company")&"'"
sql=replace(sql,"anumber",session("WorksOrderNo") )


Don't have time now to try it out. Sorry.

[glenngv]

same error occurs because you still modify the contents of a field in a SELECT recordset. Also your UPDATE syntax is wrong

custrs("WorksOrderNo") = Zeros & nextId
session("WorksOrderNo") = custrs("WorksOrderNo")

sql = "Update WorksOrderNo FROM WorksOrder WHERE Company like '"& session("Company") &"' "
Set custrs = custConn.execute(sql)


you should do:

session("WorksOrderNo") = Zeros & nextId

sql = "Update WorksOrder SET WorksOrderNo='" & session("WorksOrderNo") & "'' WHERE Company like '"& session("Company") &"'"

I put single quotes in the value of WorksOrderNo because I believe its datatype is a varchar not numeric since you are inserting zeroes at the start of it.

[victoria_1018]

Hi,
I had try your the update you provide me but still give me the same error.
I had also use another way, which is creating a new recordset, custrs1 and update the newly generated WorksOrderNo in to the records but it still don't work.

If I choose not to use this method, is there another similar method I can use to create the number.

There are a lot of good example in the internet but all creating random number but I need continue number.

Thanks
Regards
victoria

[whammy]

It sounds like you are trying to do something with scripting (create the next number in the table) when you should have your database do this automatically with an "autonumber" field or a "primary key" that automatically increments.

Am I reading things correctly here?