Problem with Looping

[stewartorris]

Hello,

I am trying to create a looping sequence with the code below. I know I probably have something wrong with my syntax, as usual.


Basically, I am trying to generate new records in a table (tblserials) based on the variable "qty." Nothing is getting written to the table.

Can someone please help me out by nudging me in the right direction. Thanks so much!


JSO


------- CODE -------


<!--#include file="includes/adovbs.inc"-->
<!--#include file="includes/dbConnections.asp"-->
<%
Dim i
varYear1 = Request.querystring("year")
company = Request.querystring("company")
delivType = Request.querystring("delivType")
prodAcro = Request.querystring("prodAcro")
qty = Cint(Request.querystring("quantity"))
F5 = Request.querystring("F5")
transID = Request.querystring("transID")


'--- Insert Serial Numbers into tblSerials Loop ---


Do until i = qty

i = i + 1

'--- unique ID ---
' Create a server recordset object
Set rs = Server.CreateObject("ADODB.Recordset")

' Select all data from the table
sql = "SELECT TOP 1 serialID FROM tblSerials WHERE progAcro = '"& prodAcro &"' AND delivType = '"& delivType &"' AND custID = '"& company &"' ORDER BY serialID desc"

' Execute the sql
rs.Open sql, cnADO

If rs.eof = TRUE then
lastSerialID = 0
else
lastSerialID = Cint(rs("serialID"))
end if

' Close and set the recordset to nothing
rs.close
set rs=nothing

newSerialID = lastSerialID + 1

If newSerialID < 10 then
newSerialID = "00" & newSerialID
end if

If newSerialID < 100 AND newSerialID > 9 then
newSerialID = "0" & newSerialID
end if

serialNo = prodAcro & varYear1 & delivType & newSerialID & company

Set MyConn=Server.CreateObject("ADODB.Connection")
Set RS=Server.CreateObject("ADODB.RecordSet")

MyConn.Open pDatabaseConnectionString

RS.Open "Select * From tblSerials", MyConn, adOpenDynamic, adLockPessimistic, adCMDText

RS.AddNew
RS("serial")= serialNo
RS("transID")= transID
RS("status")= 1
RS("progAcro") = prodAcro
RS("delivType") = delivType
RS("custID") = company
RS("serialID") = newSerialID
RS.Update


RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing


Loop

'--- End of Serial number loop
response.redirect "invoice_add.asp?invoiceNo=" & F5


%>

[Angelika]

Try this:
<%
Dim i
varYear1 = Request.querystring("year")
company = Request.querystring("company")
delivType = Request.querystring("delivType")
prodAcro = Request.querystring("prodAcro")
qty = Cint(Request.querystring("quantity"))
F5 = Request.querystring("F5")
transID = Request.querystring("transID")


Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open pDatabaseConnectionString

' Create a server recordset object
Set RS=Server.CreateObject("ADODB.RecordSet")

'--- Insert Serial Numbers into tblSerials Loop ---
Do until i = qty
i = i + 1

'--- unique ID ---
' Select all data from the table
sql = "SELECT TOP 1 serialID FROM tblSerials WHERE progAcro = '"& prodAcro &"' AND delivType = '"& delivType &"' AND custID = '"& company &"' ORDER BY serialID desc"

' Execute the sql
RS.Open sql,MyConn

If RS.eof = TRUE then
lastSerialID = 0
else
lastSerialID = Cint(RS("serialID"))
end if
rs.close

newSerialID = lastSerialID + 1
If newSerialID < 10 then
newSerialID = "00" & newSerialID
end if

If newSerialID < 100 AND newSerialID > 9 then
newSerialID = "0" & newSerialID
end if

serialNo = prodAcro & varYear1 & delivType & newSerialID & company

RS.Open "Select * From tblSerials", MyConn,3,3
RS.AddNew
RS("serial")= serialNo
RS("transID")= transID
RS("status")= 1
RS("progAcro") = prodAcro
RS("delivType") = delivType
RS("custID") = company
RS("serialID") = newSerialID
RS.Update
RS.Close

Loop

Set RS = Nothing
MyConn.Close
Set MyConn = Nothing

[stewartorris]

That worked! You rock!

Thanks so much.

[stewartorris]

NEVERMIND!

I'm a big goof.

My request.querystring("quantity") was incorrect!

Thanks. You're code worked.


You made my day!