Benjizzle

I am running an intranet with both ASP and .exe components. I get this error when I am trying to save some records. The error persists even when i change the type of the parameter being passed to the Sub. The major problem is that I cannot trace from which SQL statement the error is arising. If anyone can help me in this i will really appreciate it because it is part of a live system and mission-critical. Even if u dont solve the problem at least if u can give me any information as to when this error occurs when applying SQL statements and how it may be resolved i'll be only too glad. You can also eMail me when u reply so that I am aware soonest. Thanx in advance.

==========THE CODE=====================

Sub saveApproveSamples(SampleListID As String)
On Error GoTo errHandler
Dim TMENUID As String
Dim SqlStr As String
Dim Ref As String
Dim SaluteText As String
Dim YourContract As String
Dim Rs As New ADODB.Recordset
Dim BlendRs As New ADODB.Recordset
Dim BlendBalanceRs As New ADODB.Recordset
Dim CurId As String

TMENUID = "mnuSamples"

YourContract = UCase(GetCgiValue("TxtYourContract"))
SqlStr = "Update SampleList set YourContract='" & YourContract & "',Approved=1 where SamplelistId=" & SampleListID & ""

conn.Execute (SqlStr)

SqlStr = "SELECT SampleListItems.Id," & _
" SampleList.Ref, SampleList.Date,Stock.StockId, Stock.Invoice, " & _
" Grades.Description, Stock.NoOfPacks, Factories.Mark, " & _
" Stock.Weight , Contracts.ContractNo, Contracts.YourConNO,SampleListItems.approved " & _
" FROM SampleListItems INNER JOIN " & _
" SampleList ON " & _
" SampleListItems.SampleListID = SampleList.SampleListID INNER " & _
" Join " & _
" Contracts ON " & _
" SampleList.ContractID = Contracts.ContractId INNER JOIN " & _
" Stock ON " & _
" SampleListItems.StockId = Stock.StockID INNER JOIN " & _
" Grades ON Stock.Grade = Grades.ID INNER JOIN " & _
" Factories ON Stock.Mark = Factories.FactoryID where SampleList.SampleListID=" & SampleListID & _
" AND stock.allocated <> 0 "

SqlStr = "SELECT SampleListItems.ID, SampleList.Ref, SampleList.[Date], Stock.StockID, Stock.Invoice, Grades.Description, Stock.NoOfPacks, Factories.Mark, " & _
" Stock.Weight , Contracts.ContractNo, Contracts.yourConNo, SampleListItems.Approved FROM SampleListItems INNER JOIN SampleList ON SampleListItems.SampleListID = SampleList.SampleListID INNER JOIN" & _
" Contracts ON SampleList.ContractID = Contracts.ContractId INNER JOIN Stock ON SampleListItems.StockId = Stock.StockID INNER JOIN" & _
" Grades ON Stock.Grade = Grades.ID INNER JOIN Factories ON Stock.Mark = Factories.FactoryID Where Stock.Blended<>1 AND (SampleList.SampleListID = " & SampleListID & ") And (Stock.Allocated <> 0)" & _
" Union " & _
" SELECT Blends.Blendid AS ID, SampleList.Ref, SampleList.[Date], Blends.Blendid AS Stockid, Blends.NewInvoiceNo AS invoice, Grades.Description," & _
" Blends.NoOfPacks, 'Blends' AS Mark, Blends.Unitweight AS Weight, Contracts.ContractNo, Contracts.YourConNo, SampleListItems.Approved FROM SampleListItems INNER JOIN" & _
" SampleList ON SampleListItems.SampleListID = SampleList.SampleListID INNER JOIN Contracts ON SampleList.ContractID = Contracts.ContractId INNER JOIN" & _
" Blends ON Contracts.ContractId = Blends.ContractID INNER JOIN Grades ON Blends.Grade = Grades.ID WHERE (SampleList.SampleListID = " & SampleListID & ")"

Set Rs = conn.Execute(SqlStr)

If Not (Rs.EOF Or Rs.BOF) Then
While Not Rs.EOF

CurId = UCase(GetCgiValue("Ch" & Trim(Str(Rs.Fields("Id").Value))))

If CurId = 0 Or CurId = 1 Or CurId = 2 Then
'Approving Blended Teas
If (Rs.Fields("Mark").Value = "Blends") Then
'//Check Whether physically blended
SqlStr = "SELECT StockID, OriginalParentBlendId From Stock WHERE (OriginalParentBlendId = " & Rs.Fields("StockId").Value & ")"

Set BlendBalanceRs = conn.Execute(SqlStr)
If Not (BlendBalanceRs.EOF Or BlendBalance.BOF) Then '//Rejected After Physical blending
If (CurId = 2 Or CurId = 0) Then
SqlStr = "UPDATE Blends SET ContractID = 0,Rejected=1 where " & Rs.Fields("StockId").Value
conn.Execute (SqlStr)
SqlStr = "Update Stock set Auction=0,Approved=" & CurId & " where StockId =" & BlendRs.Fields("StockID").Value
conn.Execute (SqlStr)
SqlStr = "Update Allocation set Auction=0,Deleted=1 where StockId =" & BlendRs.Fields("StockID").Value
conn.Execute (SqlStr)
SqlStr = "update SampleListItems set Deleted=1 where (Stockid=" & BlendRs.Fields("StockID").Value & ") and (SampleListID=" & SampleListID & ")"
conn.Execute (SqlStr)

End If
Else 'Rejected before physical blending
SqlStr = "UPDATE Blends SET ContractID = 0,Rejected=1 where " & Rs.Fields("StockId").Value
conn.Execute (SqlStr)

SqlStr = "SELECT DISTINCT Allocation.StockId FROM Blends INNER JOIN BlendSchedule ON Blends.BlendID = BlendSchedule.BlendID INNER JOIN Contracts ON Blends.ContractID = Contracts.ContractId INNER JOIN ContractDetails ON Contracts.ContractId = ContractDetails.ContractId INNER JOIN" & _
" Allocation ON ContractDetails.DetailId = Allocation.ContractDetailId WHERE (BlendSchedule.BlendID =" & Rs.Fields("StockId").Value & ") AND (BlendSchedule.Removed = 0) AND (SampleList.SampleListID = " & SampleListID & ") AND (Allocation.Auction <> 1)"
Set BlendRs = conn.Execute(SqlStr)

If Not (BlendRs.BOF Or BlendRs.EOF) Then
Do While BlendRs.EOF = False
If (CurId = 2) Then
SqlStr = "Update BlendSchedule Set Removed = 1 where Stockid=" & BlendRs.Fields("StockID").Value
conn.Execute (SqlStr)
SqlStr = "Update Stock set Auction=0,Blended=0 where StockId =" & BlendRs.Fields("StockID").Value
conn.Execute (SqlStr)
SqlStr = "Update Allocation set Auction=0,Deleted=1 where StockId =" & BlendRs.Fields("StockID").Value
conn.Execute (SqlStr)
SqlStr = "update SampleListItems set Deleted=1 where (Stockid=" & BlendRs.Fields("StockID").Value & ") and (SampleListID=" & SampleListID & ")"
conn.Execute (SqlStr)
Else

SqlStr = "Update SampleListItems set approved=" & CurId & ", ApprovalDate='" & DateFormat(Date) & "' where Id =" & BlendRs.Fields("ID").Value
conn.Execute (SqlStr)
SqlStr = "Update Stock set Approved=" & CurId & " where StockId =" & BlendRs.Fields("StockID").Value
conn.Execute (SqlStr)
End If

BlendRs.MoveNext
Loop
End If ' //End of if teas exist in the blends
End If '//End of Blends
Else
If (CurId = 2) Then
SqlStr = "Update Stock set Allocated = 0, Auction = 0,Approved=" & CurId & " where StockId =" & Rs.Fields("StockID").Value
conn.Execute (SqlStr)
SqlStr = "Update Allocation set Auction=0,Deleted=1 where StockId =" & Rs.Fields("StockID").Value
conn.Execute (SqlStr)
SqlStr = "update SampleListItems set Deleted=1 where (Stockid=" & Rs.Fields("StockID").Value & ") and (SampleListID=" & SampleListID & ")"
conn.Execute (SqlStr)
Else

SqlStr = "Update SampleListItems set approved=" & CurId & ", ApprovalDate='" & DateFormat(Date) & "' where Id =" & Rs.Fields("ID").Value
conn.Execute (SqlStr)
SqlStr = "Update Stock set Approved=" & CurId & " where StockId =" & Rs.Fields("StockID").Value
conn.Execute (SqlStr)
End If
End If
End If
Rs.MoveNext
Wend

End If

SendHeader ("Approval Samples")
Send "<p>Changes Successfully Saved </p>"
SendFooter
Exit Sub

errHandler:
Send "<p>" & Err.Description & "</p>"
Send "<a href = ""samples.exe?mod=samples&Stat=viewlist"">Back To List View</a>"
SendFooter
End Sub