ALTER PROCEDURE [dbo].[sp_LDAP_Query] 

as  

set nocount on

select  * 
from  openquery(ADSI, ' 
select  title, sAMAccountName, displayName, userAccountControl
from    ''LDAP://DC=xxx,DC=COM'' 
where   objectCategory = ''User''') 
where title is not null and (userAccountControl <> '66050' and userAccountControl <> '514')
order by displayName

exec vecellio.dbo.sp_LDAP_Query

<add key="ConnectionString" value="Data Source=VGIBESQL;Initial Catalog=XXX;Integrated Security=SSPI;"/>

If mySqlConnection.State = Data.ConnectionState.Closed Then
            mySqlConnection.Open()
        End If

        Try
            mySqlCommand.Connection = mySqlConnection
            mySqlCommand.CommandType = Data.CommandType.StoredProcedure
            mySqlCommand.CommandText = ("vecellio.dbo.sp_LDAP_Query")
            mySqlCommand.Connection = mySqlConnection
            mySqlDataAdapter.SelectCommand = mySqlCommand
            mySqlDataAdapter.Fill(mySqlDataTable)
            Session("myMenuDataTable") = mySqlDataTable
        Catch ex As Exception
            Message = ex.ToString
            myError = True
        End Try

System.Data.SqlClient.SqlException: An error occurred while preparing the query " 
select  title, sAMAccountName, displayName, userAccountControl
from    'LDAP://DC=Vecelliogroup,DC=COM' 
where   objectCategory = 'User'" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI". 

Dim con = CreateObject("ADODB.Connection")
        Dim com = CreateObject("ADODB.Command")
        Dim RS = CreateObject("ADODB.Recordset")

        con.Provider = "ADsDSOObject"
        con.Open()
        com.ActiveConnection = con
        com.CommandText = "select sAMAccountName from 'LDAP://DC=Vecelliogroup,DC=COM' where objectCategory = 'User'"

        RS = com.Execute()

        Do Until RS.EOF
            Dim mystring As String
            mystring = RS.Fields("sAMAccountName").ToString
            RS.MoveNext()
        Loop

Dim cn As New OleDb.OleDbConnection("Provider=ADSDSOObject")
Dim cmd As OleDb.OleDbCommand = cn.CreateCommand()
cmd.CommandText = "select sAMAccountName from 'LDAP://DC=Vecelliogroup,DC=COM' where objectCategory = 'User'"

Dim da As New OleDb.OleDbDataAdapter(cmd)
da.Fill(mySqlDataTable)
Session("myMenuDataTable") = mySqlDataTable

select sAMAccountName from 'LDAP://DC=Vecelliogroup,DC=COM' where objectCategory = 'User'

where title is not Null and (userAccountControl <> '66050' and userAccountControl <> '514')

quote:

---

Originally posted by snufse

Hi,

That worked great. I need to expand my LDAp query select

select sAMAccountName from 'LDAP://DC=Vecelliogroup,DC=COM' where objectCategory = 'User'

to include

where title is not Null and (userAccountControl <> '66050' and userAccountControl <> '514')

I have problems with the syntax. Thank you.

---

Dim prefixText As String = "vg"
Dim cn As New Data.OleDb.OleDbConnection("Provider=ADSDSOObject")
Dim cmd As Data.OleDb.OleDbCommand = cn.CreateCommand()
cmd.CommandText = "select sAMAccountName, displayName, title, userAccountControl from 'LDAP://DC=Vecelliogroup,DC=COM' where objectCategory = 'User' and title <> ' ' and (userAccountControl <> '66050' and userAccountControl <> '514') and sAMAccountName like '%" & prefixText & "%'"
 Try
            Dim da As New Data.OleDb.OleDbDataAdapter(cmd)
            da.Fill(mySqlDataTable)