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

Rory

Right, debugging CGI is notoriously nasty. For starters, if you have option explicit set, turn it off. Secondly, start outputting random text such as "reached this bit" so you know where in the code the error occurs. I think it may be the wierd way in which you treat Rs.Fields("ID").Value as a string in an If statement and yet use the str() function later on which takes a number.

liquidsilver

Why would you want to turn of option explicit, that would just cause you program to run slower in general. With it on, it shouldn't give you a type mismatch error, or am I wrong?

__________________
Small is beautiful

Rory

Actually, a type mismatch can occur for one of two reasons. Firstly, you're trying to do something impossible that VB can't do, such as set an Object equal to a Long. Secondly, you're trying to do something such as set a string equal to a number, which under strict typecasting (option explicit on) will raise an error. By turning it off, and seeing if the error occurs again, you can decide which of the two circumstances it is and eliminate down to specific areas of the code, which is useful with large projects.
Obviously, you'll correct the error and turn Option Explicit back on for the final compile.

liquidsilver

Thanx, that clears up a few things

__________________
Small is beautiful

Rory

Cool. BTW my favourite run time error has to be "Run-Time Error 17765 The VB Learning Edition does not support this functionality".