Access is strange August 24, 2009

After hours trying to figure out how to get a sum from one table’s rows into another table (a fairly simple statement in SQL Server), I finally found this from peter57r

peter57r
awestrope,

Try this instead: it avoids the aggregate query.

UPDATE  tblOptions inner join tblOptionsPricing
on tbloptions.optionid = tbloptionspricing.optionid
SET BasePrice= nz(BasePrice) + ([LastCalculatedPrice]*[Qty])

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21826239.html

(this was the search I used in google:  updating table based on query operation must use an updateable query aggregate)

This is a completely different idea from the way it works in SQL Server, it adds up as it goes along.  So, just be sure to empty the appropriate cell before running if you need to start from scratch (which I do).  This is much better than using asp to update one row at a time which I was about to give up and do.  I can’t use VB.  This all used to be done in a module in Access called by a stored proc.  But, Access does not let you call that module if you’re coming from asp. 

However, you can’t use the nz function from asp either, so just change it to an iif, like this:

SET wins = IIf(wins Is Null,0,wins)+1;

Thank you Peter!  You are a life saver today!

LDAP in ASP February 5, 2009

I had a hard time finding everything I needed for this.  This is a conglomeration of several sources and what worked for me.  I’m not using a MS AD, which most seemed to be.

This only does the query to see if a user exists.  I really need one to authenticate, but that’ll be next.

Dim conLDAP 'As ADODB.Connection
 Dim strSQL 'As String
 Dim strLDAPConn 'As String
 Dim rsUser 'As ADODB.Recordset
 
 strSQL = "Select uid,sn,givenname,cn,telephoneNumber,mail,manager,objectClass,dc,ou From 'LDAP://xxx.xxx.xxx' where uid='username'"
 
 Set conLDAP = Server.CreateObject("ADODB.Connection")
 conLDAP.Provider = "ADSDSOOBject"
 conLDAP.Open 
 Set rsUser = conLDAP.Execute(strSQL)
 
 if not rsUser.EOF and not rsUser.bof then
  do while not rsuser.eof
   'Iterate through available user attributes
   For count = 0 to rsUser.Fields.Count - 1
    sAttribName = rsUser.Fields(CInt(count)).Name
    sAttribVal = rsUser.Fields(CInt(count))
   
    If IsArray(sAttribVal) Then
     For i = lbound(sAttribVal) to ubound(sAttribVal)
      sAttribList = sAttribList & sAttribName & ":: " & sAttribVal(i) & "<BR>"
     Next
    Else
     sAttribList = sAttribName & ": " & sAttribVal & "<BR>"
    End If
    response.Write(sAttribList & "<BR>")
    sAttribList = "" 
   Next
   rsuser.movenext
  loop
 end if
 rsUser.Close
 set rsUser = Nothing 

ASP & LDAP January 30, 2009

This forum had some good info & links to MS:

http://www.tek-tips.com/viewthread.cfm?qid=969032&page=1

userstring = WSHNetwork.UserName

On error Resume Next

Dim cont’ As IADsContainer
Dim usr’ As IADsUser

szUsername = “domainname\Administrator”
szPassword = “Password”

const ADS_SECURE_AUTHENTICATION=&h0001
const ADS_SERVER_BIND=&h0200
set ons=getobject(“LDAP:”)
set cont=ons.OpenDSObject( _
    ”LDAP://OU=SBSUsers,OU=Users,OU=MyBusiness,DC=companName,DC=local”, _
    szUsername, _
    szPassword, _
    ADS_SECURE_AUTHENTICATION or ADS_SERVER_BIND)

‘ Filter users.
cont.Filter = Array(“user”)

For Each usr In cont
    If lcase(usr.sAMAccountName) = lcase(userstring) Then
      shortName = usr.sAMAccountName
      ldpath = usr.distinguishedName
    End If
Next