Archive for September, 2009

Incorrect Syntax Near “.” during DataAdapter.Fill

September 24, 2009

Warning: Some of the links in this post may distress Cat Fanciers.

What Have I Done To Deserve This ?
Incorrect Syntax near “.” on a DataAdapter Fill means the SQL for your Select Command in your SQLCommand object has a syntax error in it…same as what I did. The bit in the double quotes after ‘Near’ shows what caused the SQL Parser to choke on its own giblets. Hey, look on the bright side. Mine happened while I was doing a practical test during a job interview.

Due to its initial obscurity and brevity, this error caused stage one panic (Brain leaking out of nostil, inflamed Hello Kitty, gargantuan screaming noise from reptilian brain stem), but a few Googles put me right.

How To Fix It

Well, correct the syntax error in the SQL obviously.

Mine came about through a bad concatenation. Like so:

' Create a SqlConnection to the Northwind database.
Using connection As SqlConnection = New SqlConnection( _
connectionString)

' Create a SqlDataAdapter for the Suppliers table.
Dim suppliersAdapter As SqlDataAdapter = _
New SqlDataAdapter()

' A table mapping names the DataTable.
suppliersAdapter.TableMappings.Add("Table", "Suppliers")

' Open the connection.
connection.Open()
Console.WriteLine("The SqlConnection is open.")

'Bad concatenation here
Dim commandString As String = _
"SELECT SupplierID, CompanyName FROM dbo.Suppliers" & _
"WHERE supplierID > 100"

' Create a SqlCommand to retrieve Suppliers data.
Dim suppliersCommand As SqlCommand = New SqlCommand( _
commandString, connection)
suppliersCommand.CommandType = CommandType.Text

' Set the SqlDataAdapter's SelectCommand.
suppliersAdapter.SelectCommand = suppliersCommand

' Fill the DataSet.
Dim dataSet As DataSet = New DataSet("Suppliers")
'Incorrect Syntax Crash Here
suppliersAdapter.Fill(dataSet)

You Bad Concatenation, You

As you can see above, there is no space between my WHERE clause and the SELECT clause.

The resulting command was thus:
“SELECT SupplierID, CompanyName FROM dbo.SuppliersWHERE supplierID > 100”

There is no table called dbo.SuppliersWHERE and no where clause in the query at all.
Fixing it entailed simply inserting a space at the end of the SELECT clause.

Dim commandString As String = _
“SELECT SupplierID, CompanyName FROM dbo.Suppliers “ & _
“WHERE supplierID > 100”

Powershell: Update Active Directory Permissions

September 1, 2009

If you’re new to manipulating Active Directory in Powershell and you want to answer a question like ‘How do I give domain user prod\bruce write access to AD object LDAP://adserver.prod.com/cn=MegaServer, cn=Computers, cn=prod, cn=net’, then this brilliant article by Richard Siddaway ‘Windows Server 2008 Protection from Accidental Deletion’ is just what you need.

I’m just linking it here because its title makes it a bit difficult to find and it deserves more exposure.

Here’s the guts of it

$ou = [ADSI]"LDAP://ou=Thunderbirds,dc=starking,dc=org"

$sec = $ou.psbase.ObjectSecurity

## set the rights and control type

$act = [System.Security.AccessControl.AccessControlType]::Deny

$adrights = [System.DirectoryServices.ActiveDirectoryRights]::Delete

$adrights2 = [System.DirectoryServices.ActiveDirectoryRights]::DeleteTree

## who does this apply to

$who = New-Object -TypeName System.Security.Principal.NTAccount -ArgumentList "", "prod\bruce"

# stop delete

$newrule1 = New-Object -TypeName System.DirectoryServices.ActiveDirectoryAccessRule -ArgumentList $who, $adrights, $act

$sec.AddAccessRule($newrule1)

$ou.psbase.CommitChanges()

# stop deletetree

$newrule2 = New-Object -TypeName System.DirectoryServices.ActiveDirectoryAccessRule -ArgumentList $who, $adrights2, $act

$sec.AddAccessRule($newrule2)

$ou.psbase.CommitChanges()