Help with this code

[brianh]

The following code is breaking on the objrs.open. Can you help???? Thanks....

**************************************
**************************************
<% @Language = VBScript %>
<%

Option Explicit


Dim objConn, objRs, strQ, strMeth


'Change the display of boolean value to image

function YesNo()
' If bool value is true, return yes.
if objRs("present") = true then
YesNo = "<img src=images/imgIn.gif>"
' If bool value is false, return no.
else
YesNo = "&nbsp;"
end if
end function



'check form for sorting method and assign SQL and Text

If Trim(Request.Form("radsort")) = "fin" then
strMeth = "Financial Services"
strQ = "select e.lname, e.fname, e.PhoneNumber, e.ACESID, i.in, e.UserName from tblEmployees e, tblInOut i where e.Unit='fin1, fin2, fin3, fin4' order by e.lname"
ElseIf Trim(Request.Form("radsort")) = "soc" then
strMeth = "Social Services"
strQ = "select e.lname, e.fname, e.PhoneNumber, e.ACESID, i.in, e.UserName from tblEmployees e, tblInOut where e.Unit='Soc Serv1, Soc Serv2' order by e.lname"
ElseIf Trim(Request.Form("radsort")) = "cler" then
strMeth = "Support Services"
strQ = "select e.lname, e.fname, e.PhoneNumber, e.ACESID, i.in, e.UserName from tblEmployees e, tblInOut i where e.Unit='SUPPORT SERVICES' order by e.lname"
ElseIf Trim(Request.Form("radsort")) = "whosin" then
strMeth = "Whos in?"
strQ = "select e.lname, e.fname, e.PhoneNumber, e.ACESID, i.in, e.UserName from tblEmployees e, tblInOut i where i.In=True order by e.lname"
Else strMeth = "Alphabetical"
strQ = "select e.lname, e.fname, i.in, e.phonenumber, e.Username from tblEmployees e, tblInOut i order by e.lastname"
End If


set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=g:\InOut\dbInOut.mdb"

set objRs = Server.CreateObject("ADODB.Recordset")

objRs.Open strQ, objConn

%>

**********************************************

set objRs = Server.CreateObject("ADODB.Recordset")

set objRs = objConn.Execute (strQ)

[brianh]

Did not work. I think that it is in the query, I altered the query. Does it look OK?

If Trim(Request.Form("radsort")) = "fin" then
strMeth = "Financial Services"
strQ = "select tblEmployees.lname, tblEmployees.fname, tblEmployees.PhoneNumber, tblEmployees.ACESID, tblInOut.in, tblEmployees.UserName from tblEmployees, tblInOut where tblEmployees.Unit='fin1, fin2, fin3, fin4' and tblInOut.UserName = tblEmployees.UserName order by tblEmployees.lname"

ElseIf Trim(Request.Form("radsort")) = "soc" then
strMeth = "Social Services"
strQ = "select tblEmployees.lname, tblEmployees.fname, tblEmployees.PhoneNumber, tblEmployees.ACESID, tblInOut.in, tblEmployees.UserName from tblEmployees, tblInOut where tblEmployees.Unit='Soc Serv1, Soc Serv2' and tblInOut.UserName = tblEmployees.UserName order by tblEmployees.lname"

ElseIf Trim(Request.Form("radsort")) = "cler" then
strMeth = "Support Services"
strQ = "select tblEmployees.lname, tblEmployees.fname, tblEmployees.PhoneNumber, tblEmployees.ACESID, tblInOut.in, tblEmployees.UserName from tblEmployees, tblInOut where tblEmployees.Unit='SUPPORT SERVICES' and tblInOut.UserName = tblEmployees.UserName order by tblEmployees.lname"

ElseIf Trim(Request.Form("radsort")) = "whosin" then
strMeth = "Whos in?"
strQ = "select tblEmployees.lname, tblEmployees.fname, tblEmployees.PhoneNumber, tblEmployees.ACESID, tblInOut.in, tblEmployees.UserName from tblEmployees, tblInOut where tblInOut.In=True where tblInOut.UserName = tblEmployees.UserName order by tblEmployees.lname"

Else strMeth = "Alphabetical"
strQ = "select tblEmployees.lname, tblEmployees.fname, tblInOut.in, tblEmployees.phonenumber, tblEmployees.Username from tblEmployees, tblInOut where tblInOut.UserName = tblEmployees.UserName order by tblEmployees.lastname"
End If

*******************************************
Also, when IE gives you the error message and says what line it is on. Is it giving you the actual line number ot the line numbers only in relation to the VB script?

When you get the line number of the error, it is the actual line number (if you have Visual Interdev, just open the asp and ctl+g, then enter the line number)

But that may not help, since SQL errors generally don't appear until the execute or open statement.

What is the error?

[brianh]

I don't have InterDev, only notepad right now. Here is the error.

Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

/InOut/formtest.asp, line 46

I looked at your sql, and nothing jumped out to me.

Only suggestion at this point is to run each of your SQL one at a time in Query analyzer (if you are using SQL server) or in SQL view if you are using access

sorry.

[brianh]

Your advice was perfect. I ran it in Access (SQL view) and foudn two errors, I corrected them and it worked. Thank you!

[monte96]

On a side note, executing the SQL through the connection object will leave you with a readonly cursor, even if you specified otherwise. So if you don't need to update the recordset, executing through the connection object is best, but if you need to update, use the rs.open method.