Keeping an updated table

[Nicodemas]

I have a database table that is populated by an ADSI procedure. What I'm trying to do is grab all the local groups off my server, filter some out, and then populate the table with those groups, so that users will always have an up to date copy of the groups on the server to choose from.

However, one of the columns in this table should always stay the same. Take a look:


<%

'###################### DECLARE VARIABLES ################################################
Dim strSQL, ysnSuccess, intCounter

intCounter = 1


'################ DELETE ALL THE GROUPS ON THE SERVER FROM tblGroups #####################
strSQL = "DELETE txtGroup, intGroup FROM tblGroups"
ysnSuccess = adlExecuteSQL(strDB,strSQL,NULL)
If ysnSuccess = False Then
Response.Write "<CENTER><FONT COLOR='#FF0000' SIZE='+2'>The script failed to refresh the list of groups on the server. Contact your unit's page maintainer.</FONT></CENTER>"
%><!-- #INCLUDE VIRTUAL="/include/bottom.asp" --><%
End If



'#### USES ADSI TO GRAB THE AVAILABLE USERGROUPS FROM THE SERVER AND POPULATES THE DATABASE WITH THEM ###########
strServer="SPM-INTRANET-01"
Set objServer = GetObject("WinNT://" & strServer)
objServer.Filter = Array("group")
For Each colGroup In objServer
If colGroup.Class = "Group" Then
If colGroup.Name <> "Administrators" AND colGroup.Name <> "Backup Operators" AND colGroup.Name <> "Guests" AND colGroup.Name <> "Power Users" AND colGroup.Name <> "Replicator" AND colGroup.Name <> "Users" Then
strSQL = "INSERT INTO tblGroups (txtGroup, intGroup) VALUES ('" & colGroup.Name & "', "& intCounter & ")"
ysnSuccess = adlExecuteSQL(strDB,strSQL,NULL)
intCounter = intCounter+1
End If
End If
Next

Set objServer = Nothing
Set colGroup = Nothing
%>





The SQL statement on line 9 DELETES two fields from my table: field "txtGroup" and field "intGroup", but I need the third field, "HasPrimary" to remain untouched.

Each time this script runs, though, HasPrimary is deleted and then reinstated with it's value changed back to False, whereas it needs to remain True if it was so...
How should I go about changing my SQL statement and thus get this whole script running correctly?

[Nicodemas]

Recently tried the ALTER statement in the SQL. No success. Seems that Access doesn't support it.

[Nicodemas]

Hmmm... tried setting it to NULL and then using an UPDATE statement,

Beyond that, I'm sort of stuck. The logic just empties the fields..... how do you think I should go about telling the darn code to update the data correctly?
Quite honestly, I'm getting desperate here. This issue is hot on my list and its taken two days to work on just to get to this point.
Any and all help would be appreciated and credited (hoping this will add incentive ;))



<%
'###################### DECLARE VARIABLES ################################################
Dim strSQL, ysnSuccess, intCounter

intCounter = 1

Function WasitSuccessful()
If ysnSuccess = False Then
Response.Write "<CENTER><FONT COLOR='#FF0000' SIZE='+2'>The page failed to refresh the list of groups on the server.</FONT><P> Contact the the webmaster at <BR><A HREF=""mailto:52cs.webmaster@spangdahlem.af.mil"">52 FW/Webmaster</A>.</P></CENTER>"
%><!-- #INCLUDE VIRTUAL="/include/bottom.asp" --><%
End If
End Function

'################ SET txtGroup TO NULL TO CLEAR OUT THE LIST#####################
strSQL = "UPDATE tblGroups SET txtGroup = NULL, intGroup = NULL"
ysnSuccess = adlExecuteSQL(strDB,strSQL,NULL)
WasitSuccessful()



'################################################################################################### ##########
'# USES ADSI TO GRAB THE AVAILABLE USERGROUPS FROM THE SERVER AND POPULATES THE DATABASE WITH THEM ##
'# PUT IN txtGroup THE NAME OF THE GROUP ON THE SERVER, AND A NUMBER IN intGroup BASED ON THE COUNTER ##
'################################################################################################### ##########
strServer="SPM-INTRANET-01"
Set objServer = GetObject("WinNT://" & strServer)
objServer.Filter = Array("group")
For Each colGroup In objServer
If colGroup.Class = "Group" Then
If colGroup.Name <> "Administrators" AND colGroup.Name <> "Backup Operators" AND colGroup.Name <> "Guests" AND colGroup.Name <> "Power Users" AND colGroup.Name <> "Replicator" AND colGroup.Name <> "Users" Then
strSQL = "UPDATE tblGroups SET txtGroup = '" & colGroup.Name & "', intGroup = "& intCounter
ysnSuccess = adlExecuteSQL(strDB,strSQL,NULL)
WasitSuccessful()
intCounter = intCounter+1
End If
End If
Next

Set objServer = Nothing
Set colGroup = Nothing
%>

[Nicodemas]

Well, the code above simply empties the field, and doesn't replace the old data with the new. Do you see any syntax errors in the UPDATE SQL?

[Nicodemas]

No, sir.