Datalink query in SQL Server

Mark Norbury

18 Februari 2012 08:01

I'm creating a datalink to a SQL Server table using the following query.

SELECT BLK, DESCRIPT, LOT, PCL, PCLLINKSID, PID, PLANDEFDOC FROM cityfsj.dataowner.PARCELS_LINKTABLE WHERE PLID = ?

Parameter Name is @PLID

Feature field is PLID

The relationship is one to many.

PLID is nvarchar(10)

My datalink's display name is 'Parcel Information'

When I use the 'Identify' tool to click on a parcel I see the 'Parcel Information' tab but it is always empty for every parcel feature that I 'Identify', even though I know they have a corresponding record in the PARCELS_LINKTABLE table.

I've also tried:

replacing the equals sign with LIKE: WHERE PLID LIKE ?

replacing the question mark with '?'

Everyone combination of these options.

What am I doing wrong and why haven't Latitude documented this in the "Geocortex Administrator's Guide"?

Thanks

Mark Norbury

0

• 

  Permanently deleted user

  18 Februari 2012 08:43

  Need to set your parameter & have your PLID = @PLID (assuming your parameter is called @PLID).

   

  Brad

  0
• 

  Permanently deleted user

  18 Februari 2012 08:52

  Mark,

  The advanced section for the data connection drops you right into the SQL command that will be issued to the data source.  Any syntax here will be specific to the data provider (and SQL dialect) that your external data is being drawn from.  The query appears to be set up against an ODBC connection to SQL Server - you may have better success setting up your data connection with the native SQL Client rather than ODBC.  That said, it still technically should work via ODBC as you have it configured.  Stick with equals (vs. LIKE), and you do not need to put quotes around the ?, since parameterized SQL queries are strongly typed.

  Is the PLID field aliased in SDE or the MXD?  There is an issue that I've encountered where an MXD-level alias set on the join field will prevent the linkage from happening in the Silverlight Viewer.  This is either resolved in the current (1.4) version or the next minor release, I can't recall which.

  I would recommend if you cannot get the linkage to happen with the current setup, to set up a new data connection using the native SQL Client, as that is what I typically use for connections to SQL Server databases.

  John

  0
• 

  Mark Norbury

  18 Februari 2012 09:36

  Here's a bit more background to the issue.

  The data connection is native SQL Client (not ODBC) and I had originally set this up using the datalink wizard, which defined my query using:

  WHERE PLID = @PLID

  The problem was that I had the relationship set as 'one to one' rather than 'one to many', so the 'Parcel Information' tab was not even showing up in the Map Viewer.

  So I looked on the Geocortex forums and found a post saying that the query should be:

  where PLID = ?

  but the forum wasn't clear that it was discussing an ODBC connection.  I also looked on the Charlotte sample site and the query there to a related table was using something similar: where ZIPCODE = ?

  I then switched over to a 'one to many'.  The tab was now showing but no data were appearing.  This is the point at which I posted to this forum.

  Thanks to Brad and John's responses I then realised my where clause was correct in the first place, so I've now switched it back and it works fine: data are showing in the Parcel Information tab.

  Thanks for your help, guys.

  I'd just like to repeat that all this information should be in the administrator's guide.  We shouldn't have to spend hours search around on the forums for it.  Also some more samples in the sample sites using related tables would be useful.  This is a pretty common thing to configure.

  Thanks

  Mark

  0

Du måste logga in om du vill lämna en kommentar.