Exception occurred error when retreiving data from ADODB recordset

[Jarekb]

I've got a form with a table where the user enters an employee id into the first column and upon exiting the cell the below script is triggered which populates the other columns in the table with the employee's information. The problem I'm having is that the code works on the first row of the table, but when I try to enter another employee on row two, I receive a "Exception occurred" error. I can go back and change the employee on the first row and the script works fine, but can't add additional rows.

The odd thing about this is that it worked before, until I modified the SQL to include another table for additional fields I needed.

GetData main script
When user enters employee id this script is triggered. rstrEmplid is the field that's used for what I'm describing. The roster dates are filled in by the user prior to entering data in the table. The script pulls the employee id and roster dates from the form and passes it to a function that uses those variables in a SQL string. getEmplData() creates a db connection, executes the SQL string against it, puts the results into a recordset and returns that recordset back to the main function.

Once the main function is back in control, it takes the results of the recordset and populates the form columns with the data. The error occurs when the user enters the employee id after the first row in the table.

This is the line that's shown with the error.

Response.Write "rstrName=" & rst("name") & vbCrLf

Any help with this is greatly appreciated.

Main script

<%@ LANGUAGE="VBSCRIPT" %>
<% OPTION EXPLICIT %>
<!--#INCLUDE FILE="includes/functions.asp" -->
<%
	Response.ContentType = "Text/Plain"
	
	Dim rstrEmplid, histEmplid, rst, page, jobcode
	Dim rosterYear, rosterMonth, rosterDate
	Dim rosterBegin_DT, rosterEnd_DT
	Dim amount
	
	page = Trim(request.QueryString("page"))
	rstrEmplid = Trim(request("rstremplid"))
	histEmplid = Trim(request("emplID"))
	
	rosterYear = Trim(request("rosterYear"))
	rosterMonth = Trim(request("rosterMonth"))
	rosterEnd_DT = Trim(request("rosterEnd_DT"))
	
	Select Case page
		Case "rosterTable" 
			rosterDate = rosterMonth & " 1, " & rosterYear
			rosterDate = CDate(rosterDate)
			rosterEnd_DT = DateAdd("d",-1,DateAdd("m",1,rosterDate))
			
			rst = getEmplData ("id",rstrEmplid, rosterEnd_DT)
			
			Response.Write "rstrName=" & rst("name") & vbCrLf
			Response.Write "rstrjobcode=" & rst("jobcode") & vbCrLf
			Response.Write "rstrDescr=" & rst("jobDescr") & vbCrLf
			Response.Write "rstrPay=Yes" & vbCrLf
			Response.Write "rstrAmt=" & rst("amount") & vbCrLf
			
		Case "historyUser" 
			...			
		Case "rosterDates"
		        ...
	End Select
%>

Functions include script

<%
    Function getEmplData (qtype, value, endDate)
	    Dim conn, rst, SQL
	    
	    If IsNull(endDate) Then
	    	endDate = Date()
	    End If
	    
	    'Set db connection to get empl data
	    Set conn = Server.CreateObject("ADODB.Connection")
	    conn.Open ("DSN=...;UID=...;PWD=...")
	
		SQL = SQL & "SELECT " & vbcrlf 
		SQL = SQL & "    j.emplid            , " & vbcrlf 
		SQL = SQL & "    j.name              , " & vbcrlf 
		SQL = SQL & "    j.jobcode           , " & vbcrlf 
		SQL = SQL & "    jt.descr AS jobDescr, " & vbcrlf 
		...
		SQL = SQL & "    AND "
		SQL = SQL & "    ( "
		
		If qtype = "id" Then
			SQL = SQL & "    	 j.emplid = '" & value  & "'" & vbcrlf
		Elseif qtype = "usr" Then
			SQL = SQL & "    	 j.oprid ='" & value & "'" & vbcrlf 
		End If
		SQL = SQL & "    ) " & vbcrlf 
		
	    Set rst = Server.CreateObject("ADODB.Recordset")
	    rst.Open SQL , conn
	    SET getEmplData = rst

    End Function   
%>

 

[wvdba]

1) i have had problem with field name being called "name". you may change that and see what happens.
2) vbcrlf after "select" and other commands may not work. are these vbcrlf's continuation of the sql command on a new line? if it is, then you may try "& _" instead of vbcrlf.

 

[Jarekb]

After a bit more debugging I've found that on the second run through the script, the main script passes a blank value as the roster end date to the sql function. At this point I don't think it has anything to do with the SQL, but either something with the way I wrote the code or a symptom of actions taken by the platform this form is built on.

To explain, this form is used in a CMS called FileNet and the table is a dynamic object created in a propriety form designer software. The dynamic part of it just adds rows to the bottom of the table as the end is reached. I don't fully understand what's going on in the background with this dynamic object, so it may or may not be the cause of this issue.

So besides changing the name field in the SQL string and dropping the line breaks in the string, is there anything else that can potentially be wrong with my code?