[RESOLVED] EOF or BOF, trying to link a database

[st3ady]

Hey hey,

I am creating a macro for one of the proprietary programs here that will create new records in a big database. Several of the values of each row cannot be pulled from the proprietary program, but I can pull them from another database that we have (the technician database) by finding the technicians last name and their tech number, but every time I try to get certain values such as the First name, it gives me an EOF or BOF error:

Error Type:
ADODB.Field (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/rrc8281/SickTech/index.asp, line 29

However, when I paste the sqlstmt2 into access, it works fine and returns what I'm looking for.

Here's the code, its pretty short:

<%
dateentered = trim(request.querystring("dateentered"))
techlname= trim(request.querystring("techlname"))
routedate = trim(request.querystring("routedate"))
technum = trim(request.querystring("technum"))
reason = trim(request.querystring("reason"))
add = trim(request.querystring("add"))

dow = WeekDayName(WeekDay(routedate))

if (add <> "") then
response.expires = 60
response.expiresabsolute = now() - 1
response.addheader "pragma","no-cache"
response.addheader "cache-control","private"
response.cachecontrol = "no-cache"

set conn2 = server.createobject("adodb.connection")
conn2.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\8281 Data\WebSite\Scheduler_Extract.mdb"
set SErs = server.createobject("adodb.connection")

int technumz
technumz = technum + 10 - 10

sqlstmt2 = "select [Last Name], [First Name], [Unit Number] from [Tech_Details] where [Tech ID] = " & technumz & " and [Last Name] LIKE '" & techlname & "*' ORDER BY [Service Date]"

set SErs = conn2.execute(sqlstmt2)

Lastname = SErs.Fields("Last Name")
Namespace = " "
Firstname = SErs.Fields("First Name")
district = SErs.Fields("Unit Number")
response.write district
response.write "<br>"
name = Firstname & Namespace & Lastname
response.write name
response.write "<br>"
response.end

set conn = server.createobject("adodb.connection")
conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\8281 Data\WebSite\rrc8281\SickTech\AbsenceTracking.mdb"
set rs = server.createobject("adodb.recordset")
sqlstmt = "select * from [SickTechTable] where 0 = 1"
rs.open sqlstmt, conn, 1, 3

rs.addnew
rs.fields(1) = dateentered
rs.fields(2) = district
rs.fields(3) = routedate
rs.fields(4) = technum
rs.fields(5) = dow
rs.fields(7) = name
rs.fields(11) = reason
rs.update

rs.close
conn.close

response.write "added"
response.end
end if
%>

Sorry, I'm not sure if this should have gone into the ASP or SQL forum but I chose this one. Any help would be greatly appreciated, thanks! :)

[jvanamali]

Instead of * Wildcard character in like statement below use %
sqlstmt2 = "select [Last Name], [First Name], [Unit Number] from [Tech_Details] where [Tech ID] = " & technumz & " and [Last Name] LIKE '" & techlname & "*' ORDER BY [Service Date]"
Use the below one
sqlstmt2 = "select [Last Name], [First Name], [Unit Number] from [Tech_Details] where [Tech ID] = " & technumz & " and [Last Name] LIKE '" & techlname & "%' ORDER BY [Service Date]"

[st3ady]

thanks, Access can be frustrating... :P