The SQL Query Viewer Web Part allows to query external SQL Server databases and displays the result as a pageable list without having to resort to Sharepoint Designer.
The Enterprise version adds OLE-DB and ODBC data source support (MS Access, Excel, CSV, Oracle etc.), allows to create Web Part connections (row filters etc.), sort columns and define search filters.
The Web Part can be used with - Windows Sharepoint Services V3 - MOSS 2007 - Sharepoint 2010 - Sharepoint 2013 - Sharepoint 2016 - Sharepoint 2019 (both classic and modern pages).
The following parameters can be configured:
Database Connection String (supports both SQL Server and Windows Integrated Security)
SQL Select statement or Stored Procedure
Number of rows displayed per page
Display of column totals
Sort Filters
Column Sorting
Column Formatting
dynamic parameters can be passed to the SQL Query via URL parameters and web part connections
SQL Query Viewer Web Part for Sharepoint 2007 30 day Evaluation Version
Free
SQL Query Viewer Web Part for Sharepoint 2010 30 day Evaluation Version
Free
SQL Query Viewer Web Part for Sharepoint 2013 30 day Evaluation Version
Free
SQL Query Viewer Web Part for Sharepoint 2016 30 day Evaluation Version
Free
SQL Query Viewer Web Part for Sharepoint 2019 and SE 30 day Evaluation Version
Free
SQL Query Viewer Web Part Basic Version Basic Version per Server License Key (allows to query Microsoft SQL Server databases)
USD 175.00
SQL Query Viewer Web Part Enterprise Version for SP 2007 and SP 2010 Enterprise Version per Server License Key (adds OLE-DB/ODBC/Stored Procedure) support, Web Part connections for row filtering and Search filters). You can request a 30 day Enterprise Version Trial Key for evaluation purposes.
USD 250.00
SQL Query Viewer Web Part Enterprise Version for SP2013, SP2016, SP2019 and SharePoint SE Enterprise Version per Server License Key for Sharepoint 2013, 2016 and 2019. (adds OLE-DB/ODBC/Stored Procedure) support, Web Part connections for row filtering and Search filters). You can request a 30 day Enterprise Version Trial Key for evaluation purposes.
USD 250.00
SQL Edit Web Part for SP2007 and SP 2010 30 day Evaluation Version
Free
SQL Edit Web Part for SP 2013 30 day Evaluation Version
Free
SQL Edit Web Part for SP 2016 30 day Evaluation Version
Free
SQL Edit Web Part for SP 2019 and SE 30 day Evaluation Version
Free
SQL Edit Web Part per Server License Key for Sharepoint 2007, 2010, 2013 and 2016
USD 125.00
Deployment Instructions for SP 2010
Deployment Instructions for SP 2013
Deployment Instructions for SP 2016
Deployment Instructions for SP 2019 and SE
Deployment Instructions for SP 2019 "Modern" pages
SQL Edit Web Part Installation Instructions
Notify me by e-mail if a new release is made available:
Connection Password: if you use SQL Server authentication, enter the User account password into this field. It will be automatically encrypted for security reasons. Leave this field empty if you use an OLE-DB connection.
SQL Query: Enter the desired SQL Select or Stored Procedure statement into this field.
Example (using the Microsoft Northwind database): SELECT CompanyName, City, Phone, Homepage FROM Suppliers ORDER BY CompanyName
Query Placeholders:
- Use the {1} placeholder to pass a Filter value to the query (via a connected Filter web part) Range: {1} for Field Value connections, {1} - {12} for Filter Values connections
- Use the {uid} placeholder to pass the current logged on user name
- Use the {uid@} placeholder to pass the current logged on user name including the "@domain.com" postfix
- Use the {url} placeholder to pass the current page URL
- Use the {xxx} placeholders (where xxx is an URL querystring parameter) to pass any number of URL paramater
Example assuming that the URL parameter "id" is contained in the page URL: SELECT Lastname,Firstname,Title FROM Employees WHERE ID={id}
Dynamic placeholder to filter as provided by a connected multivalue choice Filter Web Part: Configured query: SELECT CompanyName, City FROM Suppliers WHERE City IN ('{3}') Chosen in filter web part: Toronto, Chicago, Paris Resulting query: SELECT CompanyName, City FROM Suppliers WHERE City IN ('Toronto','Chicago','Paris')
- Use the {more=nn} placeholder in front of a selected SQL column to truncate long text fields (where nn= maximum nbr. of characters to display). The full text is displayed as a tooltip.
Example: SELECT Lastname,Firstname,Title,{more=20}Notes FROM Employees
- selected values starting with "http://" are automatically converted into hyperlinks
- selected E-Mail address values are automatically converted into "a href" mail URL's:
Example using a dynamic filter placeholder (as provided by a connected Filter Web Part): SELECT CompanyName, City, Phone FROM Suppliers WHERE City LIKE '{1}%'
Example using an URL querystring placeholder: SELECT CompanyName, City, Phone FROM Suppliers WHERE City='{city}'
Stored Procedures: Enter the name of the Stored Procedure, followed by optional parameters. Add each parameter as a "name=value" pair and separate multiple parameters by semicolons.
Example 1 (refers to Microsoft Northwind sample database): SalesByCategory;CategoryName=Seafood;OrdYear=1997
Example 2 (refers to Microsoft Northwind sample SQL Server database accessed via ODBC): CALL SalesByCategory(?);@CategoryName=Condiments
Example 3 Using filter values (Search Filter(s)) for stored procedures parameters: SQL Query: YourStoredProcedure;ProfileCode={f:!ProfileCode};ProfileVersion={f:!ProfileVersion};Amount={f:!Amount} Search Filter(s): !ProfileCode;!ProfileVersion;!Amount
Column Totals: optionally enter the name of the column(s) for which you want to display the column total(s). Separate multiple columns by a semicolon.
Search Filter(s): (Enterprise version only) Enter one or more Column names (separated by semicolons) to allow for interactive searching.
Example: CustomerID;@LastName;Firstname;@City;
If you place an "@" ampersand character on front of the filter name, a combo box is used (as opposed to a text input box) which is automatically populated with the available column values.
If you place an "#" character on front of the filter name, a date picker is used (as opposed to a text input box).
If you place an "!" exclamation mark character in front of the filter name, the filter is using an exact match (as opposed to the default which is applying a wildcard filter)
You can also pass Querystring parameters via the page URL to the Search box(es) by adding the corresponding table column names and the desired values as name=value pairs.
Each dropdown filter can optionally be preset by adding the preset value as follows: @Month=April
You can optionally override the default size of the search boxes by appending the desired size in pixels (separated by a colon): @Month:200;Last Name:150
You can optionally specify a "friendly" search filter label by prefixing the column name with the desired friendly name, separated by a "|" pipe character.
Example: Job Title|@Title
You can also use the defined filters for replacing placeholders in the SQL query:
Example: SELECT Lastname,Firstname,Title FROM Employees WHERE Firstname='{f:!FirstName}' Search Filter(s): !FirstName After entering "George" in the search textbox the following replacement occurs: SELECT Lastname,Firstname,Title FROM Employees WHERE Firstname='George' This way only the records which satisfy the condition are queried from the database.
Search Filter Button: Enter the text of the Search Filter button (leave this field empty to suppress the button)
Search Filter Reset Button: Enter the text of the Search Filter Reset button (leave this field empty to suppress the button)
Example: Clear Filter
Column Formatting: Column Formatting allows to optionally format the column values individually as follows: Enter each column name to be formatted followed by a ":" colon and the formatting expression. The actual column value as returned by the database query is represented by the {v} placeholder. You can specify other columns by using the {ColumnName} placeholder (where "ColumnName" is the name of the desired column)
Examples:
display the "City" column with a yellow background: City:<p style="background-color:yellow">{v}</p>
display the "LastName" column in boldface: LastName:<B>{v}</B>
append the "FirstName" column value to the "LastName" column: LastName:{v},{FirstName}
display the "OrderID" column as a hyperlink: OrderID:<a href="http://www.someplace.com/orders/orderDetail.aspx?id={v}">{v}</a>
display the "EMail" column as an icon using the "mail" placeholder: EMail:mail
display the "State" column as an icon: State:<img src="/Sitename/PictureLibrary/{v}.gif">
embed the current Page URL into the column: OrderID:<a href="{url}?id={v}">Postback with dynamic URL parameter</a>
embed the {more=nn} placeholder to truncate long text fields (where nn= maximum nbr. of characters to display). The full text is displayed as a tooltip. Notes:{more=20}
display the numeric "Salary" column without a decimal fraction using the {i} placeholder: Salary:{i}
display the numeric "Product Code" column without a decimal fraction and without thousands separators using the {I} placeholder: Product Code:{I}
display the numeric "Salary" column with 2 decimal places using the {M} placeholder: Salary:{M}
display the numeric "Sales" column as a currency with 2 decimal places using the {C} placeholder: Sales:{C}
display the numeric "PercentCompleted" column as a percentage using the {%} placeholder: PercentCompleted:{%}
display a datetime column using the {date=xxx} custom date format option (where xxx is a format template as described below): Created:{date=MM/dd/yyyy}
The following formatting options are available:
d - Numeric day of the month without a leading zero. dd - Numeric day of the month with a leading zero. ddd - Abbreviated name of the day of the week. dddd - Full name of the day of the week.
h - 12 Hour clock, no leading zero. hh - 12 Hour clock with leading zero. H - 24 Hour clock, no leading zero. HH - 24 Hour clock with leading zero.
m - Minutes with no leading zero. mm - Minutes with leading zero.
M - Numeric month with no leading zero. MM - Numeric month with a leading zero. MMM - Abbreviated name of month. MMMM - Full month name.
t - AM/PM but only the first letter. tt - AM/PM ( a.m. / p.m.)
y - Year with out century and leading zero. yy - Year with out century, with leading zero. yyyy - Year with century.
Separate multiple column formattings by semicolons. Example: City:{v};Company:{v}
Column Widths: allows to optionally set the desired width in pixels for specific columns: Enter each column name for which you want to set a specific width, followed by a ":" colon and the width in pixels. Separate multiple column width settings by semicolons. You can suppress the display of a column by setting its width to zero.
Example: LastName:200;FirstName:150
You also can use this setting to specify the column heading alignment:
Rows per page: The web part supports paging and lets you specify the desired number of rows per page.
Show Nbr. of Records: Enable/disable the display of the total number of selected database rows at the bottom of the list.
Show Column Headers: turn on/off the column headers (for example if your query returns a single value which you do not want to display as a table).
Show all entries: either show all selected rows or none when no search filter is active. This setting can be used to not return any rows when one or more search filters are displayed but no filter has been applied by the user.
Enable Grouping: if enabled, alternates the row color when the value in the leftmost column changes.
Grid Lines: choose one of the following options: - none - Horizontal - Vertical - Both
Alternating Row Color: enter the optional color of the alternating row background (leave blank to use default). Enter either the HTML color names (as eg. "red" etc.) or use hexadecimal RRGGBB coding (as eg. "#CCFFCC")
Header CSS Style: enter the optional custom CSS style attribute(s) to customize the appearance of the table header. Example: background-color:orange; font-size:14px; color:white
Export to CSV: Show/hide the "Export" button for Excel CSV File Export
CSV Separator: Enter the desired CSV field separator character (Default=Comma). Use a semicolon in countries which use the commas as a decimal separator.
Export Button Text: define the display name of the "Export" link
Show 'Print' Button: Show/hide the "Print" button for printing the results
Header Area Text: enter an optional text to be displayed in the web part's header area.
Page Refresh Interval: enter the optional page refresh interval in minutes (0 = no page refresh)
Data Cache Retention Time: enter the data cache retention time in minutes to speed up consecutive page visits. Enter "0" to turn off the cache feature.
License Key: enter your Product License Key as supplied after purchase of the license. Leave this field empty if you are using the free version.
Please enter a comment below if you have problems with the installation, want to give feedback or have suggestions for improvements:
Could someone explain Step 2 please. Where is the SPS web application bin directory or GAC?
Thanks
Juerg 5/14/2009 09:53
Nick,
the BIN directory of a typical Sharepoint installation is located at c:\inetpub\wwwroot\wss\VirtualDirectories\80\bin
the GAC is a synonym for the c:\windows\assembly directory
Russell Powell 5/20/2009 17:01
This is a very nice webpart. What would be really great is if there was a way for it to use a parameterized query and either prompt for the parameter value or accept a connection from a seperate dropdown list webpart where the parameter value could be chosen.
Thanks again for a very nice webpart.
Russ
Nick 5/26/2009 15:37
Juerg,
Thanks for the prompt reply. Great web part by the way. Would you also know if it was possible to display data in alternate colors?
Juerg 5/27/2009 15:38
Nick, do you mean indivdual coloring of table cells depending on some predefined conditions (eg. if a value exceeds a certain limit etc..) ?
Henrique 5/27/2009 20:15
I followed the steps to install the “Free SQL Query Viewer Sharepoint Web Part”
I am getting the following error on the page:
Error: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
Did I miss something ? How can I fix that ?
Juerg 5/29/2009 10:45
Henrique, please try to put the DLL into c:\windows\assembly (GAC) instead of the BIN folder (also make sure to delete the DLL in the BIN folder. You should then also reset IIS to properly unload/re-load the DLL. Web parts deployed to the GAC run at a higher privilege level.
Nick 6/3/2009 14:26
Well lets say I have a column name is "Phone Number" and I want all the phone numbers printed red, or "User Names" printed blue.
Juerg 6/3/2009 18:09
Nick, you can insert HTML tags directly via the SQL statement as follows:
SELECT CompanyName,'<font color=green>' + City + '</font>' AS City, Phone FROM Suppliers ORDER BY CompanyName
Please note the SQL "AS" token which is needed to assign a display name to the manipulated column. You'll have to re-download the web part, since we had to apply a small change to it (the grid component by default automatically strips out all HTML tags, which needed to be taken care of). Just extract the DLL, replace the old one and do an IISRESET to unload/reload the DLL.
Normann P. Nielsen 6/8/2009 08:12
Any support for Oracle via ODBC? Seems to be a great webpart, but must sources I need to access is oracle based :-(
Juerg 6/9/2009 18:03
Normann, the SQL Query web part currently only supports Microsoft SQL Server (due to the .Net architecture) but we plan to add ODBC (OLE-DB) driver support in the summer.
Nick 6/10/2009 14:54
Juerg,
Awesome, thanks for the update!
sdave 6/15/2009 10:46
Would be great if SQL results from this webpart can be used as a lookup column on another list
Omar Jonguitud 6/15/2009 22:47
Really nice, it would be terrific to send some parameters to this webpart. Im writing a query about servers in my company and i would need to show them by location.
Thank you very much for it anyway, pretty good job
Santiago VB 6/16/2009 17:39
Hi, could someone build a query that sends parameters to the webpart?? what about to show the elements of a specific column as a link? Its possible?
I include the tag "<A href="http://bla/" bla>" + column_name + "</A>" in the query, the column displays his values but as a single text...not as a link. Thanks!
Juerg 6/17/2009 00:12
Santiago,
regarding Hyperlinks: The web part automatically transforms columns that start with http:// into hyperlinks. Thus you could create your query as follows (by preceding the column with ‘http:// +’):
SELECT CompanyName, ‘http://’ + City, Phone….
We applied a minor modification to the web part to suppress the leading http:// in the display part of the rendered link for aesthetic reasons. Thus you might re-download the ZIP file, extract and replace the DLL.
regarding Parameters: We are planning support (as soon as our resources allow us) to enable web part connections (as a receiver) to be able to receive dynamic parameters from other web parts and use these parameters in the SQL Query.
Santiago VB 6/17/2009 18:03
Hi, Amrein, thanks for the response.
My query is similar to : SELECT 'http://' + Producto AS Producto, Compradas FROM dbo.Licencias_Estado_x_Producto ORDER BY Producto
Is in the correct form?
I downloaded the new DLL, and replaced it on the server...but the field 'Producto' is still showed as a normal text.
thanks a lot, and nice work!!
Juerg 6/23/2009 15:14
Santiago,
did you do an IIS Reset ? This is needed to unload/reload the DLL.
Jerome 6/24/2009 17:07
Great component ! It would be even greater if this web part could be combined with the Google Chart web part, or if the two web part can be connected together so that we can display a graph whose data comes from SQL Server !
Santiago VB 7/1/2009 18:26
Hi Amrein... My sharepoint server crashes!! Now its working again (Don't panic! It isn't related to you WebPart !!).... Yes, I did a iisreset, after reinstall the DLL... Must I download the DLL from the same link above? Thanks!
Santiago VB 7/2/2009 14:09
Hi Amrein...
Please, ignore my last comment! It's Working as I expected!!!
Thanks!
Kevin 7/9/2009 23:36
Using Windows Authentication:
Error: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
This error is because Sharepoint is taking the credential from a service to pass it through SQL server (USWND-S-SHARE02) and SQL is getting the error because it does not exist an user called 'NT AUTHORITY\ANONYMOUS'
Steve 7/10/2009 00:14
Thoughts on how to pass domain credentials?
SQL authentication works fine, but for security I prefer to use Domain Service Account.
Juerg 7/10/2009 11:21
Steve, did you try the corresponding connection string ? (Initial Catalog=database;Data Source=servername;Integrated Security=SSPI;) Windows Authentication does not allow you to explicitely pass credentials but uses the IUSR_machinename account (anonymous mode) or the current client's user account (integrated mode).
Pat 7/17/2009 18:15
This looks great. But is it possible to use indivdual coloring of cells depending on some predefined conditions? (For Ex. if a value in a particular cell is a positive value then text color of that row should be green, otherwise it should be red)
Ted Pezzullo 7/22/2009 19:14
Juerg, I am also getting the error
Error: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
I tried to follow your advice and copy the dll into the GAC, but it would not let me. I tried to use the gacutil.exe, still with no luck. Any suggestions.
Using Moss 2007
Camille 7/24/2009 10:49
Hi,
Fist, thanx! 'cause your tool changed my life!
But i want to know if we can put some space between differents columns because this is not really visible when the data are too close to each other.
thx for ur help!
Jonathan 7/27/2009 16:40
Thanks for this web part. Any chance you could add the ability to update/insert/delete rows?
Thanks!
Juerg 7/29/2009 15:36
Ted, you have to add the following line to the "<SAFECONTROLS>" section of your Web.Config file:
Please make sure that this is on one single line (no line breaks). Please also make sure that you placed the line into the right web.config file, since you might have multiple Sharepoint applications installed on the same machine. The default application typically is found in
Inetpub/wwwroot/wss/VirtualDirectories/80
Juerg 7/29/2009 16:23
Camille, we actually use a cell padding of 3 pixels for the data grid. Can you send us a screen shot of your grid ?
Juerg 7/31/2009 11:10
Pat and Jonathan,
we could implement these features a a custom enhancement..
Patrick 8/14/2009 11:10
I use this connection string Initial Catalog=Outhouse;Data Source=server1;Integrated Security=SSPI; and get the error Error: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Is the web part not using the sharepoint user name?
Juerg 8/14/2009 13:49
Patrick,
you have to run your Sharepoint web site in "Integrated Windows Authentication" mode. The web server does not pass on your Windows account if the web site runs in "Anonymous mode".
Nick 8/27/2009 11:34
All fine on the install but how do i deploy the dll to the CAG.
Juerg 8/28/2009 10:39
Nick,
just use drag&drop to put the DLL into c:\windows\assemby. You should do this directly on the server (eg. not via RDP or a network share).
Aashish 8/31/2009 20:13
I was able to deploy the web part pretty easily, thank you for the instructions.
My website/sharepoint app is configured to run in Windows Authentication mode (We use Kerberos to do delegation for Excel Services) and it works well.
I am unable to get your webpart working with integrated authentication. I keep getting the error: Error: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Any ideas on how to troubleshoot and what to change.fix?
Thanks in advance.
-Aashish
Aashish 8/31/2009 20:29
Nevermind...I got it working. We have constrained delegation and in order to pull data from the SQL source I had to add the MSSQLSvc service to the constrained delegation tab.
Thx for the tool, works well.
Aaron P. 9/2/2009 19:38
I'm having the exact issue as Ted. I'm getting the following error message:
Error: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
I'm 100% sure I've modified the correct Web.Config file and I've also been unable to move the DLL file into the C:\Windows\Assembly folder, either manually, or by attempting to install it with gacutil.exe. Any other suggestions?
Web Guy 9/2/2009 23:25
Hmmm... i try the free version and it errors:
I am not trying to do web part connections...
0 record(s)
Web Part Connections only supported in licensed version
Error: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
Juerg 9/3/2009 11:55
Web Guy, yesterday a bug found its way into the current version which displays the “Web Part Connections only supported..” even if you did not create a connection. This now has been fixed. Please re-download the ZIP file, extract and drop the DLL into the GAC (see below).
Permission problem: the settings on your Sharepoint server do not allow to have this web part in the BIN folder. Please move it to the GAC instead (by dropping it into “c:\windows\assembly”), delete it from then BIN folder and then also do an IISRESET to unload/reload the DLL from memory.
Juerg 9/9/2009 15:26
Aaron, check your web.config for your current trust level setting by locating the line: <TRUST originUrl="" level="xxx" /> (where xxx is either “WSS_Minimum” or “WSS_Medium”)
then go to “C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\CONFIG” and open the file wss_minimaltrust.config or wss_mediumtrust.config depending on your trust level setting.
Add the SQL Client Assembly (if it's not already present):
I am trying to run a SQL query from two different servers to pull information into SharePoint. I keep getting this error:
Error: The server principal "namsapcoe" is not able to access the database "WSS_Content" under the current security context.
Any ideas/advice would be greatly appreciated.
Juerg 9/11/2009 10:49
Tanya, we've never encountered this problem, but you can find quite some info via Google when searching for server principal "current security context"
Brian 9/12/2009 05:06
First off, thanks so much for these free web parts. Love them!! My WSS site is from a Project Server 2007 install. so if I install this web part, is it possible to query the Project Server SQL DB to get key project attributes? Essentially report off the WSS DB. I'm assuming I can query any DB as long as I have the particulars and privilidges?
Juerg 9/14/2009 11:02
Brian,
yes, you can indeed query any DB that can be reached from your Sharepoint Server
Femi Adegbesan 9/28/2009 23:57
Juerg,
Thanks for your time today. I learnt an interesting lesson that I decided to share with your forum readers.
My datasource for the webPart was a view. Because of the functions I used on the columns, t-SQL changed the type to SQL_VARIANT
Well, today I learnt that "SQL_VARIANT" fields can break the webPart in ways that cost time. So, if you find that the webPart does not display some columns, check. It might be because the field type is "SQL_VARIANT"
This became a problem as your webPart would not recognize these columns.
I was able to use CONVERT(varchar(50),fieldName) to solve the issue.
Great tool by the way. Thanks.
Amat 10/19/2009 10:11
I'm evaluating your WebPart, but I'm geting Kevin's error:
----Using Windows Authentication: Error: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) This error is because Sharepoint is taking the credential from a service to pass it through SQL server (USWND-S-SHARE02) and SQL is getting the error because it does not exist an user called 'NT AUTHORITY\ANONYMOUS' ----
Anyone has solved this error, maybe is caused by trusted connections in SharePoint Central Administrator?.
Thanks and nice job.
Juerg 10/19/2009 19:03
Hi Amat, in the SQL Management Studio, right click the SQL server instance, choose “Properties”, and under “Select a Page list”, click “Connections”. Now you should see a checkbox labelled "Allow Remote Connections to This Server". Make sure it is checked. That should take care of the remote connection issue.
Philipp 11/26/2009 05:47
Hi,
Impersonation is turned on (checked it in web.config), in the connection string it is defined to use "Integrated Security=SSPI", but I get this message:
Error: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Can someone pls help?
Juerg 11/26/2009 12:27
Hi Philipp, You most probably encountered the "double hop" problem: This is a problem that occurs if you are trying to access a database that is installed on a separate database server with the current user's credentials. Windows is not able to pass the user's credentials from the SharePoint server to the SQL Server if you are using NTLM authentication.
You can use Kerberos to authenticate against your web application and turn on delegation. If you use delegation, the web application that you are running your web part from will delegate the user's credentials to the SQL Server. Because the credentials are delegated, the credentials will be passed to the SQL Server without a problem and you will get rid of the double hop issue. Note that in order for your SharePoint web application to use Kerberos authentication you have to configure this on your web application: (see http://technet.microsoft.com/en-us/library/cc263449.aspx)
You also could use SQL Authentication to authenticate against the database. As you are passing SQL credentials in the connection string no double hop issue will occur. This would however require you to turn on SQL authentication on the SQL Server.
Bob Storey 11/30/2009 20:06
Juerg, I am also getting the error Error: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
I tried to follow your advice and copy the dll into the GAC, but it would not let me. I tried to use the gacutil.exe, still with no luck. Any suggestions. Using Moss 2007
I have also checked the trust level in my config file and the SecurityClass Name ="SqlClientPermission" is already in the C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\CONFIG\wss_mediumtrust.config
I have tried your suggestions. The dll is in the bin file when I get the message. I have tried to move it to the /windows/assembly/ but the system won't let me paste it or move it or drag-drop. I am on as console and with full administrative privileges. What do I try now.
Juerg 12/15/2009 13:11
Bob,
can you please drag & drop the DLL directly at the server console using Windows File Explorer ?
Tom 1/16/2010 14:49
Juerg --
Excellent webpart, we use this all the time, very useful to us, a lot of thought went into it, thank you !
Tom
Systems Programmer
Nikolaos Vasileiadis 1/18/2010 12:43
What about XLSX files? What kind of SQL Query is used then?
Nikolaos Vasileiadis 1/18/2010 12:57
OK let's make it more specific: I want to display values from the "Sales" worksheet located in a "e:\sales\results.xlsx" workbook.
What values of DB Connection String and SQL query should I use?
Juerg 1/18/2010 13:55
please proceed as follows for XLSX files:
DB Connection String: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\test.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES" Please replace c:\test.xlsx with your file path. Also note that you might have to install the Microsoft 2007 Office System Driver Data Connectivity Components on your server. You can download the driver from http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en
SQL Query: SELECT * FROM MyRange Just use regular SQL Syntax, where “MyRange” is a named range within a work sheet (just mark a block of cells and then assign a range name to it via the right mouse contect menu). Include the Header row as the first row of your range.
Example: DB Connection String: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\test2.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES"
SQL Query: SELECT * FROM ProductRange
Matt 1/21/2010 17:09
I got everythign working except for the column sorting and the search filters. When I check the box for the sorting, it does nothing. Same with the search filter. I put in the two fields I want to search by and nothing happens.
Juerg 1/21/2010 17:13
Matt,
please request a 30 day Enterprise Version trial license key (see on top of this page). The filter and sort features are disabled for the Basic Version.
Damian 2/9/2010 02:19
Hi,
Email addresses are getting converted to an envelope symbol/link. Is it possible to display the email itself in the table?
Juerg 2/9/2010 14:47
Damian,
is this an important issue ? If yes, we will add a new configuration option
Hans 2/15/2010 10:21
I've installed it into my Sharepoint 2010 installation. The installation worked fine, but I cannot place the webpart into any page. System says "An error occurred while attempting to add the item to the page."
is there a limitation for Sharepoint 2010?
Juerg 2/15/2010 14:41
Hans, this most probably is a permissions problem (due to a “wss_minimal” trust level setting in your web.config. We thus recommend that you move the web part DLL from the BIN directory into c:\windows\assembly (where it runs with full trust).
Hans 2/18/2010 08:14
I', playing with this web part, and it is really nice, and so fast. I have a question regarding passing the query string from the URL.
If I have the following URL:
http://intranet/market/1045/default.aspx?PageView=Shared
I want to use the '1045' from the URL as my City parameter in the example below, how can I do this?
SELECT CompanyName, City, Phone FROM Suppliers WHERE City='{city}'
Juerg 2/18/2010 10:28
Hans, we have now added the {url} Query placeholder so you can now pass the current page URL to your SQL Query. Your SQL query thus would become:
SELECT CompanyName, City, Phone FROM Suppliers WHERE City= SUBSTRING('{url}',23,4)
eg. you dynamically embed the current page URL into the SQL query and then use the SUBSTRING function (assuming you are connecting to an SQL Server) to extract your City number from the URL.
Please re-download and replace the DLL, followed by an “iisreset” if you placed the DLL into c:\windows\assembly.
Hans 2/18/2010 11:10
Thanks, I'm not certain that the dll file is replaced properly cause I don't get any results back from the query. Is it still version 1.0.0.0 ?
(I don't get any error messages back either)
Juerg 2/18/2010 11:17
Hans,
please check the version number in the browser:
The most recent version is 1.1.4
Damian 2/22/2010 05:52
Being able to display the email address instead of an envelope would be a preferable option for us.
Juerg 2/22/2010 17:04
Damian, E-mail addresses are now displayed as regular links (unless you use the "mail" placeholder in the "Column Formatting" option to display the envelope icon). To update, please re-download the Zip file, extract and then replace the DLL (followed by an "iisreset" if you placed the DLL in c:\windows\assembly).
flavio 2/24/2010 18:56
Hi there, I'm evaluating the WebPart, but I keep on having the error:
SQL Execute Error: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I use SQL Server 2000 running on the same server of MSS, any clue ?
Thanks
Juerg 2/25/2010 09:23
flavio, is your SQL Server configured to allow remote connections (see Microsoft Help article at http://support.microsoft.com/kb/914277/en-us )? In the SQL Management Studio, right click the SQL server instance, Choose “Properties”, and under “Select a Page list”, click “Connections”. Now you should see a checkbox labelled "Allow Remote Connections to This Server". Make sure it is checked. That should take care of the remote connection issue.
flavio 2/25/2010 09:46
Juerg,
I'm using sql server 2000, and there is no the option you indicated, in the enterprise manger I only find in the "Connection" tab the "Allow other SQL Servers......" and it is checked.
All the ODBC remote connection to this SQL servers are working correctly.
Patrik 2/27/2010 00:54
Hi Juerg, I'm evaluating this web part, and encountered an error when running a stored procedure (on a MS SQL Server 2000). When running a procedure without parameters it works fine - but when a parameter is expected, the web part will not accept it. In my case I'm trying to pass the {uid} as parameter, and the error message from the web part is:
"SQL Execute Error: Could not find stored procedure 'st_WSS 1147'."
The stored procedure (which is found if I remove the parameter from the procedure) is naturally named st_WSS and my UID is also naturally 1147 - which displays correctly if I test run "select {uid}". Any chance of fixing, or have I missed something? Thanks in advance
Juerg 3/2/2010 10:35
Patrik, you need to use the following syntax to pass one or more parameters to your stored procedure:
Enter the name of the Stored Procedure, optionally followed by one ore more parameters (separated by a semicolon). Add each parameter as a "name=value" pair and separate multiple parameters by semicolons.
Example: st_WSS;userID={uid}
assuming that the name of your parameter is "userID"
Patrik 3/2/2010 13:18
Thanks Juerg, that works like a charm.
Chuck 3/10/2010 15:25
Good morning, I'm trying to get this aprt setup, but it is returning the following error:
SQL Execute Error: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
I am passing the same connection information as the SQL Chart Web Part and it works without issue.
Thanks.
Juerg 3/10/2010 19:04
Chuck, the reason might be that you installed the SQL Chart Web Part DLL into c:\windows\assembly (most probably you used the WSP-based installation which put the DLL into c:\windows\assembly) but placed the SQL Viewer Web Part DLL into the application’s BIN folder where it does not have sufficient permissions to access SQL Server. We thus recommend to move the DLL from the BIN folder into c:\windows\assembly. Please use mouse drag&drop either at the server or via RDP (not via a network share) and then perform an “iisreset” command.
Dynnise 3/16/2010 21:17
Is there any way to control the column widths?
Thanks. Dynnise
Juerg 3/17/2010 13:59
Dynnise, we now have added the new "Column Width" configuration property which allows you to explicitly set the width of specific columns. Please re-download the updated Zip file, extract and then replace the DLL (followed by an "iisreset" command if you placed the DLL in c:\windows\assembly)
Dynnise 3/18/2010 15:10
I have to reiterate what others have said: This is an awesome tool. It has saved us so much time. We haven't had time to implement Reporting Services yet, and this is a nice way to get reports on our portal in the meantime. And the improvements that you keep adding make it even better! Thanks! Dynnise
Ross 3/23/2010 22:01
Hi, I'm evaluating this web part and SharePoint is still very new to me so these previous posts have been a big help. I'm trying to configure for Windows Authenication, but am getting the error, "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." I'm using the specified connection string and Windows Authentication is already enabled for this site. SQL Authentication works, but it's not how we want to access the database. Any suggestions? Thanks!
John 3/24/2010 19:32
Greetings, I am evaluating your product (using the enterprise 30 day license) and I am encountering problems passing parameters to a stored procedure - would you please give an example of passing data from a "form web part" to a stored procedure esp. with regard to your "sql query" code under Miscellaneous. -- Im assuming that this is possible, earlier messages alluded to the code being upgraded. also if data passing is possible what is the max number of variables that can be passed. -- Thank you in advance -- I look forward to using your product.
Juerg 3/25/2010 10:22
John, you can pass a single Filter value (using any of the Filter web parts or the “Form” web part to the stored procedure as follows: (Example refers to Microsoft Northwind sample database):
SalesByCategory;CategoryName=Seafood;OrdYear={1}
eg. use the {1} placeholder to pass the filter value to the Stored Procedure parameter. You only can pass one variable to the query but you could use the web part’s “Search Filter(s)” setting to further filter the results.
Nicolas 4/26/2010 20:07
Hi, excelente webpart!
Request: using {url} in the Column Formatting item. I need to do a redirect to the same page with an extra parameter.
Eg.
From http://localhost/site/detail.aspx?id=123 redirect to http://localhost/site/detail.aspx?id=123&person=456
Thanks
Juerg 4/27/2010 15:57
Nicolas, is the web part embedded on the page at http://localhost/site/detail.aspx?id=123 ? Is the "id" parameter a dynamic one (otherwise you could hard-code the URL into your SQL statement) ?
Nicolas 4/27/2010 19:21
Juerg, yes, "id" is dynamic, it is generated from another page with a Column Formatting option.
I do a table with the webpant inside and a link generated to detail.aspx but with a extra parameter.
Juerg 4/28/2010 15:56
Nicolas, you now can embed the current Page URL into a column via the "Column Formatting" option:
OrderID:<a href="{url}?id={v}">Postback with dynamic URL parameter</a>
Please re-download the updated Zip file and replace the DLL.
MattH 5/1/2010 09:17
Great WebPart guys! Got the Eval at the moment, asked our IT to get the Enterprise Trial so I can see how well sorting and filtering suits my needs (Wanting it to be just like a regular list in SP). I'm also wanting to change the column titles that I get back from my SQL Query. As an Example, I get "id" as the Column name, but I want it to read "Name", I thought I could use something like column id heading "Name" In the SQL Query section, but, I'm just getting SQL errors. Any thoughts? There are about 5-6 of these that I want to change... Column widths don't seem to be working for me as well, is this because of the eval version? Thanks!, Matt.
Juerg 5/3/2010 11:58
MattH, you can easily change the Column names via the SQL Query by using the "AS" column alias. Example: SELECT ID AS Name FROM YourTable WHERE... Setting the column width(s) as described above should work ok (on the premise that your SQL statement is correct).
Nicolas 5/4/2010 17:06
Thanks Juerg, it worked.
Now, another question:
I'm using OleDB to connect to Oracle.
I do a call to the same page with accumulative parameters, but if one of the querys there is using a parameter that still doesnt exists, the result is:
SQL Execute Error: ORA-00936: missing expression
eg.
First call to the page: http://xxxx/mysite/Result.aspx?idCompany=1
One query use idCompany, worked
Another query use idCompany and idBranch, error
Second call to the page: http://xxxx/mysite/Result.aspx?idCompany=1&idBranch=2
One query use idCompany, worked
Another query use idCompany and idBranch, worked
I still cant figure out who to solve it in the query
Thanks!
Nicolas 5/4/2010 18:49
sorry, its me again.
New question:
After every refresh of any page that use the webpart a new connection to the database is created for each one. But in Oracle it is never closed, only if I do a reset of the IIS Admin service the sessions will be killed. Is there a way to close it after every execution?
MattH 5/5/2010 09:21
Hi Juerg,
The "AS" SQL thing worked great. Thanks for that. I'm still seeing two issues.
The first, I still seem to be unable to get column widths working. I've tried the "AS" name, in addition to the original name, to no avail. Is there a way that I can see what the plugin is doing with these values? They don't seem to have any effect. What can I do to try to doignose this?
The second, is that for some columns, we return multiple values. These turn up OK in the table view, however, when I do an export to CSV, it messes up the input to Excel. I get the second item in the cell, starting a new line. Have you seen this before?
Also, is there a PDF manual, or other doc that comes with the purchased version of this?
Thanks again, Matt
MattH 5/5/2010 09:25
On filtering, I see the white edit boxes above my table. these work pretty good. Is it possible to add an option, to enable filtering and sorting just like a regular SP list? I find this to be a very compact and efficient method. I have a number of users that are just getting used to the SP filtering mechanisms and I don't want to introduce them to another method (at least, have a choice/option to choose which one to use...). Thanks again... MattH
Juerg 5/5/2010 15:22
Nicolas,
we found a bug which prevented the OLE-DB connection from be properly closed.
This is now fixed.
Please re-download the updated Zip file, extract and then replace the DLL.
Juerg 5/6/2010 17:00
MattH, regarding the column widths: you need to refer to the column name alias(es) as specified in the AS clause. Example: SQL Query: SELECT Lastname AS [Last Name], Title AS [Job Title] FROM Employees Column Widths: Last Name:100;Job Title:150
Nicolas 5/6/2010 18:32
Amazing, Juerg, the cleaning of inactive connections works great. Thanks!
Nicolas 5/10/2010 15:24
Hi Juerg, I'm using ODBC to connect to Oracle. I'm getting this error when calling a Stored Procedure with 2 parameters: SQL Execute Error: ERROR [42000] [Microsoft][ODBC driver for Oracle][Oracle]ORA-00900: invalid SQL statement On simple querys it work fine. Do I need to use it: {call stored_procedure)}?
Juerg 5/10/2010 16:35
Nicolas, you need to enter the Stored Procedure and its parameter(s) into the “SQL Query” field as follows: NameOfProcedure;Parameter1=value1;Parameter2=value2 eg. enter the name of the Stored Procedure, followed by optional parameters. Add each parameter as a "name=value" pair and separate multiple parameters by semicolons.
Juerg, I'm doing that, in fact it worked when I use OleDB, but just changing the Connection String to ODBC it fails. My call is "pck_name.proc_name;param1=1;param2=2". I need to use ODBC because I dont have to hardcode the user/pass in every query.
Juerg 5/12/2010 13:23
Nicolas, we have now added Stored Procedures support for ODBC. Example (refers to Microsoft Northwind sample SQL Server database accessed via ODBC):
CALL SalesByCategory(?);@CategoryName=Condiments
Remark: do not use curly braces when entering the name of the stored procedure. Please re-download the updated Zip file and replace the DLL.
Dougy 5/26/2010 17:33
Is there a way to make the search boxes a dynamic lookup list from the sql database?
Juerg 5/27/2010 10:57
Dougy,
this is currently not possible. Would it be ok if the search box dropdown would present a list of all distinct values contained in the query result set of a specific row ?
Khalid Hussain 6/15/2010 22:47
Hi Jureg, good tool have a question is any way i can hide the Header row ?
Juerg 6/16/2010 15:19
Khalid, yes, you can do this via the following „Header CSS Style” setting: background-color:white;font-size:1px;color:white;line-height:0px
Ted Pezzullo 6/21/2010 19:59
Hi I just downloaded a new version of the SQL Query webpart. I installed it and activated it with my license key. I had an older version of this webpart installed. I now get the following error in my queries...
"SQL Execute Error: Could not find stored Procedure"
thanks for your help
Ted Pezzullo 6/21/2010 20:28
Hi, I found the problem. In my SQL select statements I had 'set nocount on' This did not effect the older webpart but it did for this webpart. Great webpart by the way, very very useful.
thanks,
Ted
jeff 7/5/2010 13:36
I have a stored procedure that takes user network login as parameter. How do I do with it?
Juerg 7/5/2010 14:06
Jeff, you can pass the current user's login name to the stored procedure via the {uid} placeholder. Example: SalesByCategory;CategoryName=Seafood;UserAccount={uid}
Jeff 7/9/2010 20:33
Juerg,
Thank you for your reply. As a parameter of my procedure, the network user ID is not manual input during webpart configuration. Rather it is a dynamic parameter, meaning, different user see different data because of different user ID. Webpart is setup at sharepoint server not at each user's desktop. I tested it using {uid} I got no output at all. Thanks.
Juerg 7/12/2010 11:53
Jeff, the {uid} placeholder is dynamic and is replaced at run-time by the user's network login name (without the domain prefix) Example of a SQL Query: SELECT * FROM Employees WHERE Username='{uid}'
will get replaced by SELECT * FROM Employees WHERE Username='jeff'
(if the current user happens to be \\somedomain\jeff)
Nicolas 7/14/2010 15:19
Juerg, I just want to thank you for your replys and bug fix over this webpart. We are using it at production environment with big success. Thanks!
jeff 7/15/2010 22:16
Thank you, Juerg When I enter following at SQL Query box it works: select * from tbl_users where userid ='{uid}'
It output 1 line record. I then enter a procedure in the SQL Query box like below proc_test;uid='{uid}'
it does not output anything. Here is my procedure definition: create proc proc_test (@uid varchar(10)) as select * from tbl_users where userid = @uid
Anything wrong with my procedure? Thanks a lot
Juerg 7/16/2010 11:16
jeff, which database are you targeting (SQL Server, Oracle or other ?)
Juerg 7/16/2010 12:10
jeff, please enter your query as follows: proc_test;uid={uid} eg. do NOT put the parameter in quotes (the stored procedure already knows that it is of type varchar)
Jon Archer 7/23/2010 17:41
I'm trying to get the query string option working but I keep getting the error - SQL Execute Error: Invalid column name 'BR06', which is a value in the data not a column name. My syntax is SELECT No_, [Sell-to Customer No_]FROM dbo.[Live$Sales Header] WHERE [Sell-to Customer No_]={@No_}
My URL Query string is in the format @No_=BR06 for example . Could you please enlighten me as to where I'm going wronmg. Thanks
dbellard 7/24/2010 08:54
Will this Webpart interface with an SAP application database. Will the license also allow an unlimited use of the webpart throughout the SharePoint environment.
Jon Archer 7/26/2010 10:28
Very good web part that works well but unfortunately for us we won't be able to stop using sharepoint designer for sql queries because I need nested levels of groups and dynamic hyperlinks embedded in the results.
Juerg 7/27/2010 09:48
Jon, you'll need to surround the value in the WHERE clause by quotes since the value is not numeric, but a string: .. WHERE [Sell-to Customer No_]='{@No_}' You might also first check your SELECT statement with a fixed value (instead of the dynamic parameter) to see if it works OK.
Juerg 7/27/2010 09:58
dbellard, yes, you will just need to use the proper DB connection string to the SAP database (typically a DB2 system). The web part allows unlimited use (a separate license is needed for each production web front-end server).
Alex 7/27/2010 11:40
Hi!First thank you for the web part. We use it for connections to several access dbs. Our SQL statement looks like this: SELECT Tab_GS.*, Tab_Ha.HAName, Tab_Ha.HAOrg FROM Tab_GS INNER JOIN Tab_Ha ON Tab_GS.HARel = Tab_Ha.HAID As search filters we definded: @GSID;@Orga;Name;@GSArt;@HARel;HAName;@HAOrg The type of column Orga is number. If we use the filter feature of your web part and either enter some value in a textfield or select a value from the dropdown listbox the web part exececutes always a query with the "LIKE" operator. This fails because "LIKE" cannot be used in conjunction with numbers. A possible solution is to convert the numbers to string for example with the function CStr. But this is not convinient. The same also seems to happen I we query a SQL Server database. Thanks! Alex
Juerg 7/28/2010 13:34
Alex,
we have now changed the behavior of the drop down filter to use the "=" operator instead of the LIKE string operator if the drop down filter refers to a numeric table column.
We have updated the Zip file so you can re-download it and replace the DLL.
Alex 7/29/2010 11:29
Hi Juerg! Thank you for the modification. It works now.
Dave 7/29/2010 19:40
Is there any way that you could make it so that we could reference the google api locally?
In other words I could download the google api and point the web part to my local version so that I am not sending our data across the wire
Juerg 7/29/2010 20:20
Dave, to which web part are you actually referring to (the SQL Query Viewer web part does not use the Google API) ?
Marc 7/30/2010 11:42
Good webpart but does it work with Sharepoint 2010 ?
Marc 7/30/2010 11:44
Sorry it is written...
Nicolas 8/2/2010 15:22
Hi Juerg, is there any way to get a value from a <form> post?. I mean, I call a page to display KPI about a salesman logued at a previous page.
Juerg 8/2/2010 16:06
Nicolas,
this is currently not supported but we'll add this feature to the next release.
Nicolas 8/2/2010 19:23
Great, that would be awasome! Thanks
Dilip H Sant 8/27/2010 05:00
Great webpart, we purchased the enterprise license of it two days ago, I have request though, it would be very nice to have a column to allow a text to be displayed when the query results are zero. I have a SQL query where I may get zero results and hence my webpart page is all blank, if i could show some alternate text as "No records Found" it would be very good
Juerg 8/27/2010 10:26
Dilip, if you turn on the web part’s „Show Nbr. of Records” setting, it will actually display “0 record(s)” below the title:
Dilip H Sant 8/27/2010 21:14
Thanks Juerg, Yes that is one option, but I would want to show them a custom friend message, and I understand we do not have anyplace holder to capture the messages from the query. If we had it would be great. I am trying to get my DBA to do some alternate way to show "You have (0) records" kind of return. But having such a option to display the message section will be very useful
Juerg 8/30/2010 18:39
Dilip, you can now let the web part display a custom message if the query returns zero records by adding the following line to the <appSettings> section of your web.config file:
Is there a way to do column filtering when column names have spaces in them? Haven't been able to make this work. Thanks.
Scott 9/14/2010 19:35
I am trying to install this in a Sharepoint 2010 farm with issues. I can add the solution but when I go to deploy it keeps saying “Deploying”. The timer job says it finished successfully but there is nothing under the site collection features. Is there something I’m missing?
When exporting to csv and opening directly in Excel my norwegian letters æ,ø and å gets weird; is there any way of priming the csv with the correct language format?
Juerg 11/23/2010 16:17
Eugen, we have now fixed this problem, eg. the CSV export is now using Unicde encoding. Please re-download the updated Zip file, extract and then replace the DLL, followed by an “iisreset” command if you placed the DLL in c:\windows\assembly.
David 11/29/2010 23:52
Any thoughts on how to get around the default command timeout setting (30sec)?
Juerg 11/30/2010 15:36
David, we have now added the new „AESV_CommandTimeout" appSetting variable to specify the command timeout in seconds (eg. overriding the 30 second default timeout). Please add the following new line to the appSettings section of your Sharepoint application’s web.config file:
where nn is the desired timeout in seconds. Please re-download the updated Zip file, extract and then replace the DLL, followed by an “iisreset” command if you placed the DLL in c:\windows\assembly.
David 12/6/2010 19:17
Thank you Juerg, that did the trick.
Matt 12/16/2010 22:21
I see that you have added a 'Clear Filter' button. I am running 1.1.16. Is this feature not available in this version? Also, the export to CSV is great, but I want to be able to export the filtered results instead of the entire unfiltered query. Is this possible?
Juerg 12/20/2010 15:28
Matt, the "Clear Filter" option became avaulable after 1.1.16 (the current version is 1.1.26) We now have changed the export feature to resepect the current filter settings. Please re-download the updated Zip file, extract and then replace the DLL, followed by an “iisreset” command if you placed the DLL in c:\windows\assembly.
Matt 12/21/2010 17:56
The export still doesn't work the way I want even with the new version. I have a query set up that show all of our customers and all products that they are renting from us. I then have a search filter so we can enter in a customer's account number and the list will shorten to show only things that that particular customer has. When exporting, it still exports all 12,000 records instead of just the records for the filtered customer.
Matt 12/21/2010 18:03
Nevermind, Juerg... Its working now! I had 1.1.25. Thanks for taking care of this so quickly!
Kevin 1/3/2011 20:41
Love the webpart, BUT.
I would like to have a way to put a simple search box and submit button on a page and render these results via the webpart. Is that possible?
Juerg 1/3/2011 21:14
Kevin,
I assume you are aware of the web part's "Search Filter" option, but you would like the web part to not show anything unless something is entered into the search box. In this case you would connect it to a filter web part or we would add a feature which ensures to not return anything if the search box is empty.
Kevin 1/3/2011 21:36
Juerg
That is correct. I would like it to remain blank. Can you email me?
Juerg 1/4/2011 12:21
Kevin, we have now added a new „Show All Entries” web part option, which, when turned-off and no filter is active will not show any rows. Thus in your case you need to uncheck this option. Please re-download the updated Zip file, extract and then replace the DLL, followed by an “iisreset” command if you placed the DLL in c:\windows\assembly.
Kevin 1/4/2011 14:26
Perfect Juerg. Now once you get the grouping fixed :-) Seriously though, nice addition. Thank you!!
Dave 1/12/2011 09:41
I've used the @ColumnName but the resulting combo box is not sorted making selection difficult. I notice from your screen shot, that the lastName combo box is not sorted either. Is there a way to sort this list?.
Juerg 1/12/2011 17:53
Dave, we have now added automatic sorting to the filter combo boxes. Please re-download the updated Zip file , extract and then replace the DLL, followed by an “iisreset” command if you placed the DLL in c:\windows\assembly
Anita 1/23/2011 19:54
Is there any way to format the Date column to exclude the time stamp?
Mike W 1/23/2011 22:46
Anita,
I had the same thing and was able to do it in the actual query string....see below example.
CONVERT (varchar(12),SQLTABLECOLUMN) as RESULT
........Where "SQLTABLECOLUMN" is the sql field you are calling, and "RESULT" is what you want the column name to be displayed as.
Mike 1/23/2011 22:50
In the trial version, your copyright is displayed in the web part. If I pay for the full version, will this be removed. If not, it's definitely a deal killer, as I can't display other company names on our site due to policies.
Thanks.
Juerg 1/24/2011 12:29
Mike,
yes, the copyright message will go away as soon as you enter a production license key.
Anita 1/24/2011 14:03
Thanks Mike W!
Anita 1/28/2011 22:12
I can't get the filtering option to work. I have added the column name and the filter box appears, but the list doesn't filter based on the value entered. Help!
Juerg 1/28/2011 22:58
Anita, are you using the enterprise version ? The filter is not activated in the basic version.
Russ 3/1/2011 22:22
Can you access remote Oracle and SQL Server databases using UNC naming conventions?
Juerg 3/3/2011 21:42
Rustin,
SQL Server: You can put the host name of your SQL Server directly into the connection string: Example: UID=sa;Initial Catalog=Northwind;Data Source=TestSQL;
Oracle: You will need to install an ORACLE ODBC driver or OLE DB provider on your Sharepoint box. There are several options for this. A good source is: http://connectionstrings.com/oracle
Kevin 3/4/2011 22:09
Juerg,
Greetings. What's the current version?
Juerg 3/7/2011 10:15
Kevin,
the current version is 1.1.34
Danny 3/23/2011 11:15
Using the enterprise trial and using AD via SQL and able to retireve all records using:
select * FROM OPENROWSET('ADSDSOObject', 'adsdatasource;', 'SELECT sn, cn, mail, co, displayName FROM ''LDAP://ourdc'' where objectCategory = ''Person'' and objectClass = ''User'' and displayName =''*danny*'' order by displayName')
Works fine, however, I'd like to use a user entered filter:
select * FROM OPENROWSET('ADSDSOObject', 'adsdatasource;', 'SELECT sn, cn, mail, co, displayName FROM ''LDAP://ourdc'' where objectCategory = ''Person'' and objectClass = ''User'' and displayName =''*{displayName}*'' order by displayName')
with displayName in Search Filter(s). Error is: SQL Execute Error: Could not fetch a row from OLE DB provider 'ADSDSOObject'. Am I using this correctly?
Juerg 3/23/2011 13:57
Hi Danny, the search filter is actually not applied in the SELECT statement but rather used to filter the selected result list. Thus you should
remove the "and displayName=…" condition from the SELECT statement
add "displayName" to the web part’s "Search Filter" setting (which you probably already did).
shafa 3/24/2011 14:02
In filter is use a "like" command with '%%', if i want filter only "I", there show me a also II and III and IV. Can I change a filter command??
Juerg 3/24/2011 17:45
shafa, we have now added the option to specify an exact match (as opposed to the default wildcard filter search). You can specify to use an exact match by prefixing the filter column name with an "!" exclamation mark. Please re-download the updated Zip file, extract and then replace the DLL.
shafa 3/25/2011 11:08
Thanks:), I Have one more problem, "column totals" sum only the view, how to make sum if 1100 records are filtered??
shafa 3/25/2011 14:30
I have one more problem:)
If in records is money type box and value is -500 i have error:
DataBind Error: Invalid format of the input.
Juerg 3/25/2011 19:42
shafa, we have now fixed the problem when summing a column which contains negative numbers (thus you can re-download the updated Zip file and replace the DLL) . We will also be going to add an option in the next release to allow for summing all values irrespective of the current search filter settings.
shafa 3/28/2011 08:38
Thanks. It is working. I' m waiting for summing.
Simon 3/31/2011 20:37
Hi,
I'm wondering if '{uid}' will be translated to lower case or upper case during run time? Or no translation during runtime.
thanks,
Juerg 4/1/2011 15:54
Simon, both the {uid} and {uid@} placehloders are inserted as is (no forcing to upper or lower case)
Simon 4/1/2011 21:17
Hi,
Is there a way that a deafult value could be assigned to Search Filter(s)?
Thanks,
Juerg 4/6/2011 18:17
Simon,
this is currently not possible. Would it be sufficient to add default values to the dropdown filters ?
ChrisK 4/14/2011 18:06
Juerg, fantastic tool. I have a few formatting questions that I have not been able to find the answer to on this page. First, does the webpart support conditional formatting for rows? For instance if a row contains a certian value I would like to highlight it. Second, for date/time columns, can I display just the time and remove the date? Also, I have a column that is displaying a number value as currency, how do I format the column to just display the text or number? I would very much appreciate your help. Thank you.
ChrisK 4/14/2011 22:28
Juerg,
I formatted the data type in the stored procedure for the currency and date/time problem using the CONVERT function and it displays correctly. I will still try and figure out a way to do the conditional formatting but if you have any ideas that may speed up the process, please let me know. Thank you.
Juerg 4/19/2011 19:34
Chris, since a visual grouping feature has been lacking so far, we proceeded to add this feature to the web part by adding the new “Enable Grouping” setting which uses your proposition to switch the alternate row color when the value in the first column is changing.
mike 5/10/2011 21:30
Is there a way to display an image in the web part that is based on a value returned from the query? For example, the query returns either a 1, 2 or 3, and the image displays either a Green, Yellow or Red stoplight image instead of the numeric value.
Juerg 5/11/2011 11:23
Mike, you can use the web part’s “Column Formatting” setting to specify images: Example,assuming you have prepared the following images: 1.gif, 2.gif and 3.gif and the “State” column gives back 1,2 or 3:
SELECT LastName,.., CASE WHEN Salary>4000 THEN 1 ELSE 2 END AS State FROM Employees…
to add dynamic comparisons.
mike 5/19/2011 20:36
Hi Juerg, thanks so much for the great web part - we just purchased the enterprise version and the response from our users is extremely positive! I have a question about pulling data from 2 separate databases and combining into a single SQL Query web part. Is it just a simple database link or is there a more efficient way to accomplish the same?
mike 5/20/2011 20:12
We are using an oracle connection string in the following format Driver={Oracle in OraClient10g_home2};Dbq=database;Uid=REPORTSUSER;Pwd=Password1;
The connection and web part works fine. Is there a way to only pass all of the connection string EXCEPT the password and pass the password in the password field? We would like to mask the password so that it cannot be pulled from the cleartext connection string. Entering the password in the password field only and not the connection string generates a missing password error message. Thanks
Abbas 5/22/2011 09:04
Hi, this seems a nice web part for various use. But is it possible that we can group the records by multiple fields? and we can extract or collapse the view in the web part? I need this very much!
Michael 7/5/2011 22:00
I have been unable to get the querystring parameters to be passed as a parameter via the page URL. It seems to have no effect on the data results.
You can also pass Querystring parameters via the page URL to the Search box(es) by adding the corresponding table column names and the desired values as name=value pairs.
Example:
..yourpage.aspx?City=Redmond&LastName=Davolio
Juerg 7/6/2011 10:57
Michael, did you also add the corresponding Search filters to the web part ? The example you mention relates to passing default values to your search filters. Please note that you also can directly inject querystring parameters into your SQL query (see description in the "SQL Query" section on the web page).
Matteo 7/29/2011 16:13
Hello Amrein Engineering,
Very cool gadgets. I will be testing the SQL View Webpart over the next few days.
On the SQL view webpart have you given any thought about adding a feature such as the following.
Have a expand/contract ( +/- ) feature where the results of a second query, scoped by a value(s) in the row being expanded are displayed in say a table format. So you would need a second select statement and possibly a second set of column formats and such.
This way you can reduce the number of columns displayed in the row, because you can get more details by clicking the +/- expand and button.
Some thing like
Last Name First Name
====================
+ Davolio Nancy
- Fuller Andrew
Title: Vice President , Sales
City: Seattle
Email: [email protected]
Salary: 3200
State: Washington
+ Leverling Janet
+ Peacock Margaret
What Say Yee?
Russell Scheinberg 8/2/2011 19:01
I have purchased and installed the enterprise version and really love it. It is exactly what I've been looking for. Great job! I was wondering if there is a way to control or set the width of the text boxes used for filtering. Thanks.
Juerg 8/3/2011 18:07
Russell, we have now added the possibility to specify the width of the search boxes in pixels as follows:
Search Filters Example: Last Name:150;@Country:200
Please re-download the updated Zip file, extract and then replace the DLL in c:\windows\assembly, followed by an iisreset command.
Rand Batchelder 8/18/2011 21:12
I haven't been able to get the export to csv working. I have turned it on in the options. I don't see an export button. There is a export choice in the menu, but it exports some xml about the web part
Juerg 8/18/2011 21:20
Rand,
please check the web part's "Export to CSV" option and also enter the desired text into the "Export Button Text" field. Both settings are located in the "Miscellaneous" section in the web part tool pane.
Matteo 9/8/2011 17:45
Hello Juerg ,
I was trying to see what the licence details are for the your various web parts, but could not see a page with this info.
Can you point me to where the details would be?
Regards Matteo
Sagar Hussain 9/15/2011 17:52
Hi
How do I format a numeric value to say 2 decimal places or four decimal places?
Juerg 9/15/2011 18:28
Sagar, assuming that you are using SQL Server, you can use the CAST function as follows:
SELECT CAST(Salary AS Decimal(9,2)) AS Salary
The above example formats the number to 2 decimal places.
Matteo 9/20/2011 05:28
Hello Juerg
How would you use the {more=nn} feature when using stored procedures?
Matteo
Juerg 9/20/2011 15:23
Matteo, if you have a chance to either modify the existing stored procedure or create a new one, then you simply could use the T-SQL “LEFT” function as follows:
SELECT LEFT(Comment,20),…
(the above would truncate the “Comment column to 20 characters max.) If you can’t do the above, we’ll introduce a new “Column Formatting” option to truncate the column to the desired max. number of characters.
Nick 9/20/2011 19:43
Are there more column formatting options?
Juerg 9/21/2011 10:01
Nick, you can use Column Formatting mainly to inject HTML and CSS styling as desired. Examples:
- Center a column: City:<center>{v}</center> - Right-adjust a column: City: <p style="text-align:right">{v}</p> - Left-adjust a column: City: <p style="text-align:left">{v}</p> - Display the value in a specific color: City: <font color=red>{v}</font> - etc..
To re-format the values (say truncate text or output a number in a specific format), rather use the corresponding T-SQL functions in the web part’s “SQL Query” SELECT statement. Please indicate what kind of formatting you would like to apply and we’ll send you detailed instructions.
Steve 9/21/2011 15:22
We just purchased the Enterprise version, and the {more=nn} placeholder is not working. The full contents of the column are always displayed.
Here is the query:
SELECT {more=10}[ID] + ' - ' + [Name] as Project ,Budget ,Actual ,OverBudgetAmount as 'Amount Over' ,cast(round(OverBudgetPercent, 2) as varchar(20)) + '%' as 'Percent Over', PMName ,PMEmail FROM LeadershipDashboard.dbo.OverBudget Where Type = 'P' Order By OverBudgetAmount Desc
Juerg 9/21/2011 15:51
Steve, the {more=xx} placeholder does not work with aliased columns (in your case the column is assigned the “Project” alias). A simple workaround is to use the T-SQL LEFT function instead:
Hello Juerg,
RE: {more=nn} in Stored procedures,
Yes i can use the SQL Left function, however i was really referring to the hover feature when using the {more=nn} on a column in a SELECT.
Since the {more=nn} feature is part of the select that the tool parses out, but of course i cannot use it inside a PROC so i loose not only the LEFT functionality but the Hover over and display as tool tip as well.
It would be nice to preserve this hover feature with PROCs
REgards
Matteo
Rand Batchelder 9/26/2011 17:23
Can the web part menu be disabled for normal users. We don't want users to be able to change the connection string, etc
Juerg 9/26/2011 18:38
Rand,
please restrict the page permission (referring to the page that hosts the web part) for normal users to „Read“ or „View Only” permissions.
shawn 9/28/2011 02:18
I am pulling in a url and it is being converted to a hyperlink without issue. however, I am wondering if there is a way to show something like "click here" instead of the full url?
Juerg 9/28/2011 10:03
Shawn, you can define your own column formatting via the web part’s “Column Formatting” setting:
Example (assuming your column is named “Website”): Website:<a href="{v}">click here</a>
Please note that {v} is the placeholder that represents the actual value.
Juerg 9/28/2011 19:48
Matteo, we have now also added the {more=nn} placeholder to the web part's Column Formatting option: Example: Notes:{more=20}
Please re-download the updated Zip file, extract and then replace the DLL.
Chris 10/13/2011 22:07
Is it possible to use the "Column Formating" option to construct a URL but not using only the {v} token. I have this table and I would like to construct a URL in the second column using the first column value as a POST parameter. They share the same "Column2" filtering parameter Ex: 1st page SQL Query: select column1 as "Column1", column2 as "Column2" etc... Other page SQL Query select samecolumn as "SameColumn", data1 as "Data1" etc... In the Column Formatting box of the first page I want: Column2: <a href="http://.../default.aspx?SameColumn={Column1}">{v}</a> This way I can use the value of the first column on each row to construct a URL that pass the value of Column1 (and not just {v}) of the current column... In other words, the only URL parameter that seems to work in the Column Formatting is {v} and nothing else. I should be able to substitute with {Column1} or any other value from the same row. Thanks
Juerg 10/14/2011 19:10
Chris, I guess you would like to use placeholders in the “Column Formatting” setting which allow you to refer to other column values within the same row (besides the {v} placeholder which represents the current column value). Please note that you actually also can inject HTML via the SQL SELECT statement to construct your URL. Example: SELECT A, '<a href="sompage.aspx?someparameter="' + B + '>click here</a>' AS [FriendlyName], C …
(ie. assemble an <a href.. string by concatenating the fragments directly in the SELECT statement). If you cannot use this approach (as eg. when you use stored procedures) we will add a new placeholder which allows you to refer to the other columns.
Chris 10/14/2011 20:08
What I need is in a "Column Formatting" like: B FriendlyName:<a href=”somepage.aspx?someparameter={A FriendlyName}”>{i}</a>
because the SQL wouldn't let me do: SELECT A as [A FriendlyName], ‘<a href=”sompage.aspx?someparameter =”’+ {A} + ‘>B</a>’ AS [B FriendlyName], C …
(The 'click here' in your example is in fact the B value, a count) Thanks! ... note. somehow I can't format that message in multiple box when I post a comment... I sent my message by email too.
Juerg 10/17/2011 12:00
Chris, can’t you use:
SELECT A as [A FriendlyName], '<a href="sompage.aspx?someparameter=' + A + '">' + B + '</a>' AS [B FriendlyName], C..
ie. You create the hyperlink by adding the „A“ column as a parameter and display the “B” column as the link’s display name.
Chris 10/17/2011 16:08
Thanks Juerg, Unfortunately, this doesn’t work. I had tried that myself but I get a SQL Execute Error: ERROR[22018][Microsoft][ODBC driver for Oracle][Oracle]ORA-01722: invalid number My query is: select A as "A FriendlyName", '<a href="http://…/viewtable2.aspx?parameter='+ A + '">' + B + '</a>' as "B FriendlyName", C as "C FriendlyName” from table1
returns that error. In the ‘Column Formatting’ section, I have: B FriendlyName:{i}
The table A is a string and all other columns B, C,… are numbers. I think this way doesn’t work because if you add a string before/around a number, you need to use a function that would convert B into a string as well (which doesn’t work), or use the to_number() function with the string (which obviously doesn’t work neither)… In fact, if I simply do:
select A as "A FriendlyName", '-test-‘+ B as “B Friendly Name”, C as “C FriendlyName” from table1
(just adding a string prefix to the number column), I get the same error. Really, I think having the ability to use the post-query formatting instead of SQL to contruct this URL, or very complex queries where you could even have:
B FriendlyName:<a href=”someotherpage.aspx?parameter1={A}&meter2={C}”>{i}</a> would be great… Thanks for your help… Christophe
Juerg 10/17/2011 16:44
Chris, you would have to use the Oracle „to_char” function to convert the “B” column from a number to a string:
select A as "A FriendlyName", '<a href="http://…/viewtable2.aspx?parameter='+ A + '">' + to_char(B) + '</a>' as "B FriendlyName", C as "C FriendlyName” from table1
We agree that a formatting option that allows to inject any column would be nice to have and we’ll add this feature if you cannot successfully solve your problem via the SELECT statement.
Tamara 10/17/2011 16:54
Juerg, I am following the steps you outlined on 8/1/8/2011 to Rand regarding exporting to CSV and have had no success. I am currently running the trial version of Enterprise on SP2007. Any thoughts?
Chris 10/17/2011 16:56
I get the same invalid number with the to_char() function. I had tried this. For numbers, even with a simple string addition: select A as "A FriendlyName", '-test-'+ B as "B Friendly Name", C as "C FriendlyName" from table1 I get this error... Thanks
Tamara 10/17/2011 17:00
Please disregard my last comment. I found it at the very bottom of the webpart as a hyperlink. The text 'Export Button Text' was misleading to me. I wouldn't consider the current visual as a 'button' and expected it at the top of the webpart. We have a large number for the visible output, and it's difficult to notice at the bottom.
Otherwise, we are very happy with this webpart! Once we get a chance to use it more we can include more suggestions to make it even better.
Tamara 10/17/2011 17:19
One more question: When I export to CSV, null data misaligns the output on an excel spreadsheet. Is there a work-around for that? Would specifing with a CASE WHEN statement to have null's displayed as blanks (or another symbol) fix this problem?
Tamara 10/17/2011 17:41
Update regarding csv export: The null values are not creating the misalignment, it's hard returns in text fields. Please email me if you can help with this issue.
Juerg 10/17/2011 17:55
Tamara,
we will have to create a fix for this problem. Would it be OK to just replace the hard return with a blank character when exporting to CSV ?
Juerg 10/18/2011 18:16
Christophe, we have now added the feature to allow to inject column placeholders into the web part’s “Column Formatting” option. Please specify the column placeholder by using the column name (as displayed in the web part’s header), enclosed in curly braces.
Example: LastName:{FirstName} + ' ' + {LastName}
Please re-download the updated Zip file, extract and then replace the DLL, followed by an “iisreset” command if you placed the DLL in c:\windows\assembly.
Tamara 10/18/2011 20:34
Juerg, after following the steps you sent, and redeploying the webpart, I am unable to get this to work. I even attempted to add the new web part and created my report from scratch. Can you, or another user, confirm that the fix does indeed work and that I must be missing a step on my end? Thanks
Chris 10/19/2011 15:40
Thanks Juerg, amazing turn around on all requests !!!
Can you please help me on using this to construct a URL in the Column Formatting? I am struggling to make it work...
Column2: <a href="http://.../somepage.aspx?Column1={Column1}">{i}</a> doesn't work.
nor does
Column2:'<a href="http://.../sompage.aspx?Column1='+{Column1}+'">'+{i}+'</a>'
or many combinations of that...
Thanks
Juerg 10/20/2011 18:08
Christophe,
the {columnName} placeholders now always use the original row values and the {i} placeholder is now also treated like the {v} placeholder which should solve your problems.
Please re-download the updated Zip file from our web site, extract and then replace the DLL.
Chris 10/20/2011 23:43
Thanks Juerg!
Now I can easily build URLs using the Column Filtering column like:
PlaceHolder3:<a href="http://.../somepage.aspx?Filter1={PlaceHolder1}&Filter2={PlaceHolder2}">{v}</a>
Awesome !!!
Matteo 10/25/2011 00:16
Hello Juerg I posted this suggestion before but it got no traction so I thought I would try it again. When i have fifteen columns of data to display on the list, it does not look to good because you need a really wide page to display all of the data. You could resort to fine tuning the column widths, stacking two columns of data in the same column separated by a line break <br> or Char(13)+Char(10), and of course i can always use the {more=10} feature to get the hover effect to display a blob of text. All of these are neat ways to optimize space but they are not really a good substitute for good old fashion Expand/Collapse or +/- buttons to allow the user an option to expand to show more details or to collapse back down to save space. You could have a feature where you specify the 5 or so columns you would like to see on the row and then the 10 or 15 columns you would like to see stacked beneath the row. So initially the users see just the 5 columns but via the +/- button can display the remaining 10 columns of data stacked beneath the row. You could still use the size and format features only now you would have a vertical stack of column data being displayed beneath the row based on what columns you wanted to see on the + button. Submitted for your consideration. Matteo
Viktoria 11/3/2011 19:00
Hi,
Recently we bought your SQL Query Viewer Web Part Enterprise Version and we are very happy about it. However, I can’t make our dynamic filter placeholders work.
I put a basic query in SQL Query field (select company from contact1 where key2='Broker Dealer' and company='{DB}%'). It show “No records found”. I connect this web part with the filter web part and send values (that contained DB variable). But when I filter in the filter web part there nothing is changing in my SQL Query Viewer Web Part. So I can only see a massage “No records found”
I don’t want to use filters because I need to send the variable to couple of web parts.
Any suggestions?
Thanks, Viktoria.
shawn 11/3/2011 21:09
based on this statement in the configuration section, "selected values starting with "http://" are automatically converted into hyperlinks", can the WP be setup to recognize "https://" as a hyperlink?
Juerg 11/4/2011 10:14
Viktoria, please us the {1} placeholder when connecting to a Sharepoint filter web part. This placeholder will be replaced by the value passed by the filter web part at runtime.
Juerg 11/4/2011 10:16
Shawn, only "http://" is currently recognized, but not "https://". This is an omission and we will fix this as soon as possible. Please note that you also can create the hyperlink via the SELECT statement.
Example: SELECT A, B, '<a href="' + C + '">click here</a>' AS Link, D etc...
You also can use the web part's "Column Formatting" option to create a hyperlink:
Example: display the "Website" column as a hyperlink: Website: <a href="{v}">{v}</a>
Jordi 11/7/2011 12:25
How to format a Date field to remove the timestamp? If I convert the field to string in the SQL, the column is not sorted correctly.
Juerg 11/7/2011 12:40
Jordi,
please just declare the original column name in the SELECT ORDER BY clause to preserve the sort order.
Jordi 11/7/2011 16:55
Juerg, i need to work with the interactive column sorting.
Thanks
Juerg 11/9/2011 13:54
Jordi, we have now added the new {date=xxx} Column Formatting option (description see above). When applying this option, the underlying date is still preserved, so interactive column sorting will work as expected. Example: OrderDate:{date=MM/dd/yyyy}
Jordi 11/21/2011 13:34
Thanks Juerg. For a NULL Date the value is the date default 01/01/1990. Could it be blank ?
Thomas 12/2/2011 17:03
Im testing your SQL-Query Viewer Webpart and got this Error: "DataBind Error: Der Index war ausserhalb des Arraybereichs." I should add that my Database uses 575 Columns which might give you a Clue. Its the Reason im testing you Product because SP can't handle so many Columns. The Data is displayed till this Point.
Dexter 12/7/2011 22:42
You made a great Web Part. It's powerful to present our CRM system data on Sharepoint web pages.
How could I make a table with records for the current month and a text filter changeable to show any previous month's data? I could put "month" in Search Filter(s) and add {1} into query. But how to dynamically set the default month as the current month instead of a specific month?
Could you make it possible to set the default value of a filter as current month?
Juerg 12/20/2011 14:06
Dexter,
does your "Month" column contain the text representations (ie. "January", "February",..) or the month number (ie. 1..12) ?
Mark Kraska 12/20/2011 20:53
Wanted to do a query against Active Directory.
Can I put the connection in the query? like:
CREATE VIEW bloggers_view
AS
SELECT givenName AS FirstName, sn AS LastName
FROM OPENQUERY (ADSI,
'SELECT givenName,sn FROM LDAP:// DC=yourcompany,DC=com')
WHERE sn IS NOT NULL
Juerg 12/23/2011 14:56
Mark, can you try to enter the following “DB Connection String”:
Dexter, we have now added the {year}, {month} and {day} placeholders to inject the current date into the search filter's default value:
Example: @Month={month}
Plase re-download the updated Zip file, extract and then replace the DLL, followed by an “iisreset” command if you placed the DLL in c:\windows\assembly.
DaveK 12/29/2011 19:52
Is is possible to pass multiple parameters to a stored procedure from a form or url? In this instance, I need to pass three...Thanks!
Juerg 12/30/2011 11:36
Dave, you can pass any number of URL parameters to a stored procedure as follows:
Example (refers to Microsoft Northwind sample database): SalesByCategory;CategoryName={food};OrdYear={year}
where the {food} and {year} placeholders refer to the 2 URL parameters “food” and “year”.
doug 1/6/2012 17:22
We've noticed that the {UID} value is not passing to a stored procedure. I have one configured as follows: Get_MySubscriptions;Username={UID@} and have logging emanenabled in the Get_MySubscriptions stored procedure that writes the parameter to a table. If I put UID=xyz on the URL, it passes, but if I don't, it is blank (but not null).
Juerg 1/6/2012 18:08
Doug, please note that both the {uid} and the {uid@} placeholders are case sensitive (ie. you have to enter them in lowercase). So your query should be: Get_MySubscriptions;Username={uid@}
Juerg 1/6/2012 18:47
Thomas,
the web part actually only supported 99 columns so far but we have now extend this to 999 columns.
Please re-download the updated Zip file, extract and then replace the DLL, followed by an “iisreset” command if you placed the DLL in c:\windows\assembly.
Cheryl 1/6/2012 22:19
Our DVP really wants a "Submit" or "Go" button next to the filter options - something to differentiate between the "Clear Filter" button. I suppose just hitting "enter" isn't intuitive enough. Is there some javascript or anything I can do to provide a button that basically just posts the request? Thanks!
Juerg 1/9/2012 15:07
Cheryl, we have now added the new “Search Filter Button” web part option for creating an optional search button. Please re-download the updated Zip file, extract and then replace the DLL, followed by an “iisreset” command if you placed the DLL in c:\windows\assembly
David C 1/27/2012 21:37
I have two questions - First of all, you're install .bat and subsequent documentation has been very clear and concise. Now my first question (easy one), the footer in the Webpart (stating Eval and Amerin Co info). Can that be hidden? Or is that only available after a license purchase? If its only after a license purchase, then no problem, I can wait.
Next (more difficult) some data on my SQL server as a "ntext" data type with carriage returns. But when I select from the DB using the webpart, the returned data seems to show all the text on a single like (no carriage returns). Any ideas why that might happen? Thanks, DC
Juerg 1/30/2012 16:41
David, the „evaluation“ footer message will be suppressed as soon as the license key is applied. Your observation is correct: the current version does not reflect embedded carriage returns in ntext fields. We thus have now fixed this and you might consider to re-download the updated Zip file, extract and then replace the DLL, followed by an “iisreset” command if you placed the DLL in c:\windows\assembly.
Mark 1/31/2012 00:17
Good evening Juerg, we recently purchased the enterprise version of this web part and I have just a few questions (fyi I'm still using version 1.1.64, not 1.1.65):
Could it be possible to set the sql statement field "as is" when is copied ? I mean, leaving its formatting and indentation (so, let the 'carriage return' and 'space' on it) ? i.e. : select T1.FIELD1, T1.FIELD2, T2.FIELD3 from TABLE1 T1 join TABEL2 T2 on T2.ID = T1.ID where T1.FIELD4 = 'constant' This would be very helpful for whom (like me) prepare the sql queries and need to mantain / edit them.
In this case, it would be useful to have a 'comment' sintax also before and after the real sql statement (maybe with a standard /* MYCOMMENT */ for all sql languages ?)
I've seen that on this page, on the download panel, a release number is shown (now I can see Release 1.1.44 but, for what I have seen, the actual release number is 1.1.65), am I correct ? Will you update that number too ? Anyway, I've just registered for the 'Notify me by e-mail if a new release is made available' so I think I will be informed with that service.
Thanks for your support
Mark 1/31/2012 00:26
Mmm... actually I've seen that the carriage return doesn' work even on this comments... :-) Anyway, did you understand what I mean ?
Juerg 1/31/2012 15:00
Mark,
the Sharepoint interface unfortunately removes the carriage returns from the text input boxes in the web part tool pane (as eg. the "SQL Query" text box). We will be adding a custom "enhanced" text input box in a future release.
the web part now (with version 1.1.66) allows to embed comments into the "SQL Query" setting. The comments need to be enclosed by /* some comment */
the current release is 1.1.66 (and it's now also reflected at the top of this page).
BTW, when adding a comment (like this one), line feeds will now be respected.
Mark 1/31/2012 16:13
Juerg, thanks for the prompt reply. I will try the new web part asap. Please, keep us informed as soon as the new "enhanced" text input box will be available.
Mark 1/31/2012 17:22
I've just tried with version 1.1.66 but the comment /* some comment */, if written BEFORE the sql statement, cause the following error:
SQL Execute Error: Could not find stored procedure ''.
Juerg 1/31/2012 17:24
Mark, please make sure not to enter any leading blank characters (ie. the first two characters need to be /*).
Mark 1/31/2012 19:00
In my case I have a blank character after the '*/':
/* my comment */ select etc...
So blank characters are not allowed before '/*' or after '*/' (only if the comment is BEFORE the 'select' keyword). In other words, if we exclude the comment, the sql statement can't start with a blank character.
Juerg 1/31/2012 20:01
Mark, yes, correct. We will fix this in the nect version (ie. to allow for blank characters).
Chris 2/2/2012 17:35
Hi Juerg,
Following up on 'ChrisK' question above that I don't see an answer for, is it possible to inject css conditionally for a column based on values from another columns.
For example, if the value in one column is YES, then another column (or columns) text font is GREEN, and if it is NO, then another column (or columns) text font is RED...
Thanks Chris
DaveK 2/13/2012 22:58
Hi Juerg,
I have a stored procedure I would like to pass 2 or 3 parameters to via the search filters. Is this possible without using a URL Filter?
When I pass the values as part of the SQL Querry it works as expected. However, when I attempt to pass via the search filters, I get conversion errors varchar to int or varchar to decimal even though all paramertrs are varchar or Object reference not set to an instance of an object.
Thanks.
Juerg 2/15/2012 17:40
Chris, you can inject conditional formatting via the SQL statement as eg.
SELECT CASE WHEN Col1='YES' THEN '<font color=green>' + Col2 + '</font>' ELSE '<font color=red>' + Col2 + '</font>' END AS MyColumn,..
This will inject the current filter values into your SQL stored procedure parameters. However, your query most probably will fail if you pass empty values to the 3 parameters when entering the page, so you might need to add suitable default values for the 3 filters:
Is there a way to set permissiions on this web part?
Juerg 2/22/2012 11:22
Anita, just use the „Target Audiences“ setting in the „Advanced” section of the web part’s tool pane to make the web part visible only to specific audiences.
Matteo 2/23/2012 00:20
Hello Juerg You previously mentioned that the original select or Stored procedure call was executed once and then the filters acted on the result set. However in your reply to DaveK you indicated that filter values could be injected into the Stored procedure call. So Does this mean that Stored procedure call is being executed each time the filer values changes when you use the ={f:ProfileCode} syntax?
Matteo
Thomas 2/23/2012 10:20
Matteo, yes, the stored procedure call is being executed each time the filter values change.
Matteo 2/26/2012 01:16
Hello Thomas Thanks for the reply. This is very good news. I will now be re-thinking some of my uses of the web part. Just one question; How are the filters, that build a drop down list from the result set content, populated? If I use the filter value to scope my result set from the Stored Proc then i would potentially be reducing the values in the column that I am using for the filter drop down. I almost need to provide the gadget a separate select or Stored Proc that should be run to populate the filters with drop down lists. Please advise. Matteo (Oh by the way, great gadget)
Cheryl 2/28/2012 16:48
Hi, using the parameters in the query works great (where ProjectManagersText = '{1}' until you have an apostrophe in the field (i.e. O'Leary). I haven't had any success trying to use the Replace('{1}','''','') either. How can we encode apostrophes in parameters?
David 2/28/2012 23:54
Is it possible to hide a column but use the data from that column to apply formating etc to another column? I need to make a column a Link but display user friendly text as the link itself. I have the two columns in my database(URL, Product). I can reformat the product colum like so: Product:<a href={URL} target="_blank">{v}</a> ... but i need a way to hide the URL column from the sql results.
Thomas 2/29/2012 10:56
David, it is not possible to hide a column but you can prepare the SQL query with HTML for nice rendering. Example for SQL Server:
This way a HTML link appears in the "ProductLink" column.
Matteo 2/29/2012 15:21
David & Thomas I found that you can hide a column from displaying. The trick is to set its width in the Column Width Parm to zero. I do this in a names directory list I have. I have three name columns, DISPLAYNAME(e.g, Smith, Bill), LASTNAME (e.g, Smith) FIRSTNAME(e.g. Bill). I set the widths for LASTNAME & FIRSTNAME to zero (i.e., Column Width Parm - LASTNAME:0;FIRSTNAME:0). This way I save some space by displaying just one column with name info, but since the LASTNAME & FIRSTNAME columns are still part of the data set I can use them as filters or anywhere else you can use a column value.
Works like a charm.
Matteo
Victor 2/29/2012 15:50
Hello Juerg,
in the SQL Viewer webpart, passing parameters to a stored procedure via search filters does not seem to work. The search filter always seems to be applied against the dataset that was initially brought in. In other words, stored procedure call is NOT being executed each time the filter values change. I am certain I use the correct syntax: StoredProcedure;parm={f:parm}
Another question: is Ver_1_1_69 the latest version of the SQL Viewer webpart?
Thanks,
Victor
Thomas 3/1/2012 17:14
Hi Victor, version 1.1.69 is the latest one. Please send the complete content of the fields „SQL Query“ and „Search Filter(s)“ to [email protected]. Whatever the content in the field „SQL Query“ is, it should be executed each time the page is refreshed.
Mark 3/5/2012 16:11
Hi there, I am attempting to use the 30-day trial to test out. I used the automatic install (install2010.bat). In Central Admin > Solution Mgmt i deployed the solution which came back successful. However, it still notes it as "Not Deployed" and i cannot find it in the Site Collection Features (step 5). Any thoughts? This is SharePoint Foundation 2010.
Juerg 3/5/2012 16:15
Mark, did you click on "Deploy Solution" in step 4 (in Central Administration/Solution Management/AESQLWebpart.wsp) ?
Mark 3/5/2012 16:19
Yes I did. It says it was successful in deploying the solution however in going back into the Solution properties it says its not deployed.
Mark 3/5/2012 16:21
As well, the path to this in SP 2010 Foundation is: Central Admin > System Settings > Manage Farm Solutions
Juerg 3/5/2012 16:39
Mark, does the "Deployment status" say "Not deployed" but the "Last Operation Result" says "The solution was successfully deployed" ?
Mark 3/5/2012 16:55
Yes, that is exactly what it says.
Juerg 3/5/2012 17:03
Mark, can you try the following instructions. Basically this will cancel the deployment being stuck in progress and allowing you to start again. Unfortunately, this issue is occasionally seen with SharePoint Solution Packages. First, run this command which will show you all in-progress deployments, (there will probably be only the one).
stsadm -o enumdeployments
Look for the JobId string corresponding to the SQL Viewer Web Part deployment job and then run:
stsadm -o canceldeployment -id "you job id string here"
This should cancel the deployment and if you go to the solution management pages in Central Administration the solution should be listed as 'not deployed'. From this point you can try again. We have found that it usually just works the second time; if not the SharePoint logs will need to be analyzed to try and determine what is causing the failure.
This stsadm command will do a deployment, the same as pressing the deploy button within the Central Administration UI.
Hopefully when you run this command it will now install correctly.
Mark 3/5/2012 17:12
After doing: stsadm -o enumdeployments
It just returns <Deployments Count="0" />
Mark 3/5/2012 17:22
I skipped to the last step: stsadm.exe -o deploysolution -n SQLWebpart.wsp -allcontenturls -immediate -allowgacdeployment
Now its stuck at "Deploying" within CA. and I can see the job under enumdeployments
Mark 3/5/2012 17:29
Cancelled the job and attempted it again via cmd line, just stuck at "deploying". I have cancelled the job again. Which logs can I send your way and to what address?
Appreciate the help!
Juerg 3/5/2012 17:36
Mark, please get the most recent log file from: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS and send it to [email protected]
Mark 3/5/2012 17:46
Thank you. I just sent the file to support.
Jason Williams 3/6/2012 14:26
I am trying to query an Oracle server that is a Linked Server to my SQL2008R2 server. Do you have any guidance as to what I should put in the connection string for this? I've looked at connectionstrings.com but there is nothing about linked servers there.
Juerg 3/6/2012 18:06
Cheryl, we have now fixed the problem with single quotes added to the SQL Query via the {1} web part filter connection placeholder. Please re-download the updated Zip file , extract and then replace the DLL, followed by an “iisreset” command.
Thomas 3/7/2012 10:06
Jason, you have connected an Oracle Server to SQL2008R2 Server using the SQL Server feature "Linked Server". So if you open the SQL studio you see there the linked Oracle Server and you can query it. In this situation you need only for example an ordinary SQL Server OLEDB-Connection to connect to your SQL2008R2. Best solution should be the provider "SQL native client". Then you are connected to the SQL Server and automatically also to the linked Oracle Server embedded in SQL Server. To refer to objects on a linked server, you must use four-part notation: server.catalog.schema.table e.g. SELECT * FROM LinkedServer.catalog.schema.table It's important, that you use the full hierarchical addressing. First you can test this notation in SQL studio and afterward with your connection.
Jason Williams 3/13/2012 20:01
Thanks for your help on the last thing, Thomas. Is there a way to copy this web part from one zone on a SP 2010 page to another? I'm envisioning a page with multiple queries on it. It would be much easier to do all the formatting and setup once, and then just change the SQL query each time.
Juerg 3/13/2012 21:38
Jason, you can actually export the pre-configured web part (usint the web part’s “Export” function) and then upload the .webpart file into your Sharepoint’s Web Part gallery from where you then can add your pre-configured web part to a page.
Hans 3/30/2012 12:20
Hi,
I am using your SQL Query Viewer web part frequently on our extranet extracting data from our ERP system. I really like the formatting functions you have added and I have a couple of questions that maybe already have a solution. I am using the web part for a product catalog and would like to add a product picture to each returned row. The picture is a jpg file named identical as the product part no that is returned in one of the columns. The jpg file resides in a file structure on the sharepoint server (but can of course be in a sharepoint picture library as well). Is there a syntax that can be used for doing this? Second question is maybe more an sql question, but I give it a try with you guys: The Sql query returns data from a text field containing multiple lines of data formatted by TAB and CRLF, how can I maintain this formatting when it is presented through SQL Query Viewer on the sharepoint site?
Hans 3/30/2012 13:26
Sorry, I found an answerto my first question (excellent solution!), but I am still interested in finding away to keep the formatting from text fields.
Thanks
Thomas 3/30/2012 13:30
Hans, you can do it this way:
Picture: SELECT '<img src="'/someSite/MyPicturePath/' + ProductPartNo + '.jpg" border="0" width="20">' AS 'Picture', ... If ProductPartNo isn't a text type, then instead of ProductPartNo write: CONVERT(varchar,ProductPartNo)
Tabs and CRLF: SELECT '<TABLE><TR><TD>' + REPLACE(REPLACE(MyTextField,CHAR(9),'</TD><TD>'),CHAR(13)+CHAR(10),'<BR>') + '</TD></TR></TABLE>' AS MyTextHTML, ...
laureano 4/12/2012 01:34
how I can save the query data in a list? and can I do query of a list?
thanks.
Juerg 4/12/2012 17:14
Laureano, you only can export the query data to an Excel file which you then could again import into a Sharepoint List. The SQL Query Viewer web part is designed to access external databases and thus is not able to also access Sharepoint Lists.
Matteo 4/13/2012 02:51
Hello Juerg, I noticed today that when i use the "mail" column formatting option (e.g. Email:mail) that regardless if the column is empty or has a value the envelope icon always displays. It would be nice if the envelope icon would not be displayed if the column value is null or an empty string. At the moment i am doing this in my sql to get this effect.Submitted for your consideration.
Matteo
Matteo 4/13/2012 05:26
Juerg Is there a way to have the column name or filter name being displayed something other than actual column name? I like the feature in the "Quick Form" Web part that lets you a use a "label alias" (e.g., Friendly Label|ListColumnName=T50) via the pipe character. This would be very handy. Matteo
Juerg 4/13/2012 14:02
Matteo, we have now fixed the problem with the “:mail” column formatting option if the field is empty. We also now have added the possibility to specify friendly labels for the search boxes by prefixing the column name with the friendly name, separated by a “pipe” character.
Example: Job Title|@Title
Please re-download the updated Zip file, extract and then replace the DLL.
Kenny 4/13/2012 15:50
Hi Juerg for filters is it possible to have the word 'All' defaulted in the dropdown instead of it being blank? Thanks.
Jorran 4/13/2012 20:41
Juerg, I followed the jinstallation directions and everything was working perfectly. I was adding another Web Part and when I pressed apply, the Dashboard I am working on had an unexpected error and would only display that error on it. I retracted the deployment of your web part and everything (except that webpart obviously) was working like the sql graph I have from you as well working fine. Then I tried to deploy again and same thing happend. I completely removed the webpart, downloaded the trial again and it worked for a little bit but stopped and did the same thing after trying to add my 2nd webpart. I emailed the support email my logs and information. Any suggestions would be wonderful - would love to buy this product - if we get the trial working correctly.
Juerg 4/16/2012 19:44
Kenny, we have now added the new „AESV_EmptyCombo" appSetting variable to specify the desired string to be displayed for selecting all values (ie. overriding the blank entry). Please add the following new line to the appSettings section of your Sharepoint application’s web.config file:
Please re-download the updated Zip file, extract and then replace the DLL, followed by an “iisreset” command.
adriano 4/21/2012 00:17
when a query returns many records sends this message:
Run SQL Error: timeout value expired. The waiting time elapsed before the completion of the operation or the server is not responding how I can fix this?
Thomas 4/23/2012 09:46
Adriano, please look at posting 11/30/2010 15:36 from Juerg.
Cheryl 5/2/2012 20:12
Juerg, We would like to have the filtering enable an "OR" type clause, i.e. with check boxes (like SSRS). Basically, we need to be able to filter for things like Status = Yes or Pending, or Status != No .... Is there any way to implement this? Thanks!
Thomas 5/3/2012 11:49
Cheryl, please enter for example the filter Fname as follows:+Fname If the filter is prefixed by a plus sign, it will be OR’ed (instead of AND’ed). The default "=" filter comparison operator (prefix !) can be overridden for numeric and date values to either specify "greater than", "greater or equal", "less than" or "less or equal" by adding either the ">", ">=", "<" or "<=" suffix. Example: @Year<=;!Price> You need here the prefix ! to prevent the SQL LIKE Operator (not necessary for combos @). But there is no "!=" filter comparison operator. Instead of !=’No’ you can use =’Yes’ or you can add a new adapted column in the SQL query to change the filtering from "!=" into "=".
Matteo 5/18/2012 21:13
Hello Juerg
Today i was trying to use the ={f:SearchFilterName} syntax on a stored procedure call to get the stored procedure to execute again rather that just filtering the initial result set. My Stored procedure has 4 parameters so i setup 4 columns as Search filters and then mapped each of the search filters using the "{f:" syntax to my stored procedure call in the SQL Query Option of the web part. It worked ok until i set one of the Search Filters as a "Combo Box" using the Search Filter "@" syntax. I noticed that value picked via this Search Filter with the "@" was no longer being submitted as a Parm value in the Stored procedure call but rather it was just being used a filter value on the result set. I would think that regardless of the Search filter Qualifier (e.g. "@") the value picked should be submitted to the Store Procedure Parm, like the other Search Filter values.
On another point, it would be nice if i was able to create a Search Filter Combo box with some preset values, that did not have to be values from a column in the result set. Especially when using the ={f:SearchFilterName} syntax. You may have a Stored procedure with a parm that has Opt1, Opt2, Opt3 as entries. Then depending on the option chosen it would act accordingly. This way i would not have to create a fake column to spoof the web part..
Please advise.
Matteo
Chris 5/24/2012 01:04
I noticed that the notation {i} always formats thousands,etc... with the , notation... Ex: 1,300
It becomes an issue when that number represents an database ID...
Is there a way we could use {I} to specify the regular notation 1234 or {i} to use 1,234 (which is great for counts btw)?
Thanks!
Thomas 5/24/2012 11:08
Hi Chris, no, {I} doesn’t work. Try {v} instead of {i}. Depending of the underlying data it could work. If not you can prepare the value in SQL: SELECT ROUND(MyDataColumn,0) FROM MyTable To change the type also, e.g. to int: SELECT CONVERT(int, ROUND(MyDataColumn,0)) FROM MyTable Truncating instead of rounding: SELECT CONVERT(int, MyDataColumn) FROM MyTable
Chris 5/24/2012 15:29
You do not understand what I mean. I know about the {i} since I was the one who requested it because intergers in Oracle, somehow were rendered as float (2.000). Juerg quickly implemented the {i} to make sure the integer would appear as integer. If I use {v}, my integer keys becomes floating points keys again...
{i} works very well for counts or totals, but in case of primary_key for example, a number like '1,234' is incorrect.
So I am suggesting that you guys add a new formatting option that would remove the commas from the integer notation so that it appears as '1234' and not '1,234' which seems to be a rendering issue, not a SQL problem.
I was suggesting {I} but anyother would be great... BTW, {i} works great for total/count so no need to change that.
Thanks
Thomas 5/25/2012 10:03
Hi Chris, please try: SELECT REPLACE(REPLACE(CONVERT(varchar,MyIntColumn),'.000',''),',','') AS MyInt FROM MyDataTable
Chris 5/25/2012 20:32
Thomas, this is not working.
My column is an INTEGER already and the web part RENDERS it with this notation. The database returns the correct value, it is the {i} formatting option that uses this notation, not the database...
Implementing another formating option {I} or {d} will fix the problem, not changing the query.
Thanks
Juerg 5/29/2012 14:59
Chris, we have now added the new {I} formatting option (as suggested by you) to format integer numbers without thousands separators. Please re-download the updated Zip file, extract and then replace the DLL, followed by an “iisreset” command.
Thomas 5/29/2012 15:17
Hi Matteo, the query/stored procedure is always executed independent of the used filter types. But “{f:” filters are replaced in the query/stored procedure text by the corresponding filter text values before execution. To use combo filters in the stored procedure you write: {f:@MyFilterColumn}
Richard 5/29/2012 15:24
Love the component! I'm just having some difficulty opening the csv file (via Export) on a Mac. I continue to get the message "Excel does not recognize this file format". I am running Office 2011. It works fine on my Windows PC. I have "," as my separator. Thanks!
Thomas 5/29/2012 16:49
Richard, try ";" instead of "," as csv Separator in the web part configuration. If it doesn't work, please send an attached example file to [email protected]. Do you open the file in the browser or do you download and store it?
Chris 5/31/2012 15:52
I am intrigued... In the configuration panel, there is a "Show 'Add New Record' Button" checkbox...
What is it for? Do you guys have a webpart that would allow a form to be opened for inserting/editing DB entries? (If so I'd buy it yesterday ;)
When I click on it, nothing happens, and this documentation page doesn't include anything about this option...
Then place the web part onto the same page as the SQL Query Viewer web part. You now can select a record (or “Add a new record”) in the SQL Query Viewer web part and then add a new record, update an existing record or delete a record using the SQL Edit web part.
To be able to select an entry for editing/deleting, you either can add a new “Edit” column to the SQL Select statement as follows (the below examples are based on the Microsoft Northwind Employees database table):
SELECT LastName,FirstName,City,BirthDate,EmployeeID AS Edit FROM Employees
and then format it as a link by entering the following into the web part’s “Column Formatting” field: Edit:<a href="{url}?EmployeeID={v}">Edit</a>
This produces the following:
You also can choose to use the unique ID column to be the a hyperlink by selecting:
SELECT EmployeeID,LastName,FirstName,City,BirthDate FROM Employees
and then format it as a link by entering the following into the web part’s “Column Formatting” field: EmployeeID:<a href="{url}?EmployeeID={v}">{v}</a>
Matteo 5/31/2012 21:25
Hello Juerg,
Further to your reply to Chis RE: the "SQL Edit" Web Part.
First Way Cool!! I have a couple of questions
Q1.... Let me see if I have this right. The "SQL Viewer" web part and the "SQL Edit" web part can act like a kind of Master List and detail/update area.
And they are not linked together per say, however when you build a "href" tag with a ? parm, with the parm name matching the "Unique ID Column" from the "SQL Edit" web part, then this is what connects the two webparts together because the "SQL Edit" web part is looking for a URL parm of the value entered into "Unique ID Column".
Is this correct?
Q2.... Will you be creating a SQL Edit Web Part documentation page as with the other Web Parts?
Q3.... Are there any other undocumented webparts you care to share?
Q4... (ok this is my last question ... well for this post) Do you have a web part that will do the following: - Create a Drop down list box of url links - When the users chooses an item from the list then go to that page - Features should be - Source can be a back end Database or a SharePoint List - Option to open link in new tab/window or current window - various options for CSS styling, especially the Header and Footer text options that you have on a number of your other gadgets as these are most use full. - Optionally provide domain name to be allied to all links if a link starts with a "/" - Identify columns for the Link text and the Link URL - The SQL View web comes close to this functionality with the drop down list option on the Column filters, but lacks ability to fire a link when you pick an items from the list.
Well that is for now
Matteo
P.S. Hey Chris, great question. Does this now mean you Bought the web part like yesterday?
Matteo 5/31/2012 21:42
Hello Juerg,
Is it possible to have a SharePoint list as a data source for the SQL Viewer web part? What would be the DB Connection String?
In SharePoint when i create an MS Access view, where it shows the SharePoint List in MS Access (like a linked table), the db string looks like;
Matteo, as much as we know there is no db connection (connection string) for accessing a SharePoint list. So it's also not possible for the SQL Viewer web part to get such a list.
Thomas 6/1/2012 15:27
Matteo,
Q1: Correct Q2: No Q3: Yes, Juerg will send you an email with a list. Q4: No
Chris 6/1/2012 18:40
Matteo,
I bought the SQL Query Viewer part (enterprise version) a little more than a year ago and it is THE BEST webpart every developed!!! Support is outstanding.
Apparently the SQL Edit Web Part is free with the enterprise version, I just never noticed before...
Juerg, where can I vote again for this web part as the Best Webpart of the year 2012? :)
Chris 6/1/2012 18:50
I thought I posted it yesterday but I don't see it anymore... so report...
Would you mind implementing this new feature?
Allowing the use of {COLUMN_NAME} in the SQL QUERY when it is passed in the URL?
I have table with millions of rows and I don't want to first load the whole thing, then filter {SOMETABLE_ID} for a corresponding one.
Example:
http://page.aspx?SOMETABLE_ID=10 constructed from another page through the Column Formating
In my example the query could then be run as: SELECT SOMETABLE_ID, SOMETABLE_name, etc... FROM TABLE WHERE SOMETABLE_ID=10
See what I mean?
Thanks
Chris 6/1/2012 18:56
I want to point out that in my case, the table is so big that just loading everything pre-filtering leads to a:
SQL Execute Error: Exception of type 'System.OutOfMemoryException' was thrown...
The way that page would be called is through limiting the number of rows returned using the SQL QUERY with some condition (i.e. Passing parameters that limit to a subset of rows)...
So using filtering only won't work. thanks
Chris 6/1/2012 19:01
Sorry one more thing;
I understand this could create a conflict with the current {} being passed to the FILTERING. So in this case, there should be no COLUMN FILTER for that placeholder...
Like: if COLUMN FILTERS include SOMETABLE_ID, then load all rows for query, then filter.
If there is no COLUMN FILTER for SOMETABLE_ID (or @SOMETABLE_ID), then expect the SQL QUERY to have a {SOMETABLE_ID} place holder in it (in the WHERE statement)..
Juerg 6/4/2012 14:44
Chris, the web part already supports the embedding of URL placeholders in the SQL statement.
Example: SELECT SOMETABLE_ID, SOMETABLE_name, etc... FROM TABLE WHERE SOMETABLE_ID={SOMETABLE_ID}
where you would pass the parameter via the page URL: http://page.aspx?SOMETABLE_ID={I}
There will be no conflict with any {f:Filtername} placeholder(s) contained in the SQL query (since these placeholders start with “f:”).
matteo 6/9/2012 02:58
Hello Thomas,
Thanks for the {f:@MyFilterColumn} syntax, that did the trick. However i ran into anther issue with the "Show all entries" check box.
I created a SQL Query using a Stored procedure call where all of the parms have the "{f" syntax so that the sp is always rerun when you hit the submit button.
Now when i set the "Show all entries" check box ON the web part works very nicely showing the data initially and such and then subsequent clicks of the submit button, with varying values for the filters, works just fine
Now when i set the "Show all entries" check box OFF, it appears initially to work fine. Drop downs are populated, no records show. Then if i enter a filter value and hit the submit button, i do not see any results.
It is almost as if the "Show all entries" check box is tied to the SQL Query being run and of course the {f syntax is always running the query, as opposed to not using the {f syntax where the query is only run once and then the filters are applied to the intial data set.
Can you take a look?
Matteo
matteo 6/9/2012 03:04
Hello Chris
I agree about that the SQL viewer web part is a great web part. I would happily add my vote for best web part.
Nice to see you bought it. We bought the bundle and have enjoyed working the various web parts into the mix. I especially like Accordion, Twitter, and Form web parts.
Matteo
Thomas 6/11/2012 10:26
Matteo that was a bug, thanks. Please download the fixed version version 1.1.79
Stephen 7/9/2012 21:44
The result display from a query seems to have issues when '(' or ')' are used. Is the a way to handle these characters? The close bracket does not show at all.
Juerg 7/9/2012 23:13
Stephen, can you indicate the SQL SELECT statement you are using ?
Paul Bedford 7/10/2012 23:10
Is this available for SharePoint Online and Office365?
Juerg 7/23/2012 12:40
Paula, Sharepoint Online unfortunately does not allow to directly connect to a database (as eg. SQL Azure). What kind of data would you like to access ?
Mark 7/31/2012 10:25
Dear Juerg, are there any news about your comment on 1/31/2012: "...the Sharepoint interface unfortunately removes the carriage returns from the text input boxes in the web part tool pane (as eg. the "SQL Query" text box). We will be adding a custom "enhanced" text input box in a future release...". This could really help us on sql query formatting (and, therefore, implementation too).
Thomas 7/31/2012 14:44
Mark, we are working on a complete new custom web part's editor, which allows us to make the controls as desired. Once we have that, we will also equip the SQL Query Viewer web part so that the line breaks will remain.
Mark 8/1/2012 14:05
Ok, perfect, keep us informed.
Chris 8/1/2012 17:26
Would it be possible to have the Group (alternate color rows) use the column used when Sorting, instead of always the left-most column?
If a user wants to sort a table for a COLUMN, then it would probably mean that this specific COLUMN be used when grouping the rows together...
Thanks
Thomas 8/8/2012 10:56
Chris, that’s not a feature of the actual web part. We think about this idea for a future release.
Brad 8/13/2012 21:04
is it possible to do conditional formatting of the font color depending on the value with a case statement in a sql query (like the KPI but without the image). So for example: case when value 1 >= 90 then <p style="font-color:green">[value]</p> else <p style="font-color:red">[value]</p> end?
Juerg 8/14/2012 11:55
Brad, you can inject conditional formatting via the SQL statement as eg.
SELECT CASE WHEN Col1>=90 THEN '<font color=green>' + Col1 + '</font>' ELSE '<font color=red>' + Col1 + '</font>' END AS MyColumn,..
Phu Huynh 8/14/2012 23:45
I'm retrieving data from a stored procedure. Is there a way (on the web-part) that controls the columns to display instead of displaying all the columns returned from the stored procedure?
Thanks,
Phu Huynh
Simon 8/15/2012 16:56
I am playing around with the basic evaluation version and I have a formatting issue. I am extracting values that are stored in SQL as the MONEY data type. When I run the SQL in Management Studio my results come out with only 2 decimal places, however when I run the same script in this webpart my results come out with 4 decimal places. How can I format the results back to 2 dps?
Simon 8/15/2012 17:40
Another quick question.
When you apply a column format say <center>; Is it possible to enforce the same formatting on the column header?
Juerg 8/15/2012 18:37
Phu, yes, you can hide a column by specifying its column width to be zero via the web part’s “Column Widths” setting. Example: Salary:0
Juerg 8/15/2012 19:08
Simon, we now added the new {M} column formatting option to display a number with 2 decimal places by adding the following to the web part's "Column Formatting" setting: Example: Salary:{M}
We also added the new option to override the horizontal alignment of the column headers by entering the following into the web part's "Column Widths" setting: Examples: Lastname:<> (center) Lastname:<>100(centered using a column width of 100px) Department:< (left) Salary:> (right)
Pease re-download the updated Zip file, extract the WSP solution file and then run the following Sharepoint 2010 PowerShell command:
assuming that you stored the WSP file in the root folder of the C: drive on the server.
Matteo 8/24/2012 14:42
Hello Juerg,
I was trying out the {xxx} placeholders (where xxx is an URL querystring parameter) today for the first time and i found that it operated not quite the way i needed it to.
Here are the particulars:
I built a staff directory that has the filters "LastName", "FirstName" and "@Department", with the department filter being a drop down. I wanted to create a link using the value in the department column such that when a list of staff is presented a user could just click on the department name and then get a list of all of the staff in that department. Now yes of course the user could just make a selection from the department drop down but i wanted to give them this other option as well.
So i used the {xxx} placeholders (where xxx is an URL querystring parameter) syntax to make the value in the department column a link.
So now when i click on the link in the department column it displays just those staff members in the department that was clicked.
Very good.
However, when using this link the data set now only contains the staff in that one department. So if the user then wants to find a staff member that is in a different department they are out of luck because the data set has now been scoped to only include the staff in the first department clicked and of course the @Department drop down only contains that one department to choose from.
And further, if the user hits the reset button they still get just the one department in the data set.
At this point the user, to get back to the original data set on the page, can either parse out the querystring parameters in the url or they are forced to close the page and then open it again so that the querystring parameter is gone.
I don't imagine that this default behavior of the querystring parameters can be changed as there are most likely customers needing this option to work as it currently does.
So i need a way to get back to the original data set after clicking on this new department link.
Here are some options as i see it.
Option 1: (this is my current work around) - In the header text put in a link to the page that does not have any querystring parameters and then use this link as the reset button.
Option 2: - Set up a new parameter "Ignore querystring parameters on reset" - when selected the reset routine would ignore all querystring parameters and run the web part as if it is the first time on the page.
Option 3: - Any ideas from your end?
Submitted for you kind consideration.
Matteo
Simon Evans 9/26/2012 19:00
I've seen there has been some discussion around setting font colours within the data table dynamically within the underlying SQL statement, however I am looking to set the background colour of each data row based on data in the SQL statement, i.e. my data lists invoices awaiting approval and I am seeking to highlight each row with a different background colour after x days has elasped from initial registration.
Having read your documentation and discussion forum I see I could do this by setting each cell background individually <p style="background color:red">..</p>, etc. within teh SQL, but I'm outputting 14 data items per record and I'm not keen to add 14 additional case statements to my SQL if I can avoid it.
Is there another way?
Many Thanks.
Thomas 9/30/2012 17:39
Simon, as you know, if the background color is constant, then you can use the configuration field “Column Formatting“. But if it’s dynamic you must implement the cases in SQL. If you structure the SQL query with line breaks and indents then it’s easy to maintain. You can send me your query. I will insert the cases to give you an example what I mean.
Russell Scheinberg 10/23/2012 18:27
I was just wondering what the current version is. I have 1.1.77. Thanks.
Juerg 10/23/2012 18:35
Russell, the current version is 1.1.91
Russell Scheinberg 10/23/2012 19:29
Many thanks. Great product!
Matteo 11/2/2012 17:16
Hello Amrien,
I noticed today that if I have a page where I have inserted the SQL Viewer Web Part twice, that if i click the submit or reset button on one of the instances of the Web Part, that in fact both instances react as if the buttons have been clicked on both instances of the web part at the same time. Should not these multiple instances of the web part on the same page operate independently?
Please advise
Matteo
Thomas 11/5/2012 16:34
Matteo, to prevent a page refresh after clicking on a Search Filter Reset Button you must append in both miscellaneous configuration fields “Search Filter Reset Button”: /norefresh The Submit Button behavior is this way by design and usually a desired feature. But of course it doesn’t fit for all cases.
Matteo 11/6/2012 13:00
Hello Thomas
Thanks for the response
The “/noreset” option on the “Search Filter Reset Button” value worked OK, in that only the one web part was affected when clicking this button.
Too bad that there is not a similar option for the “Search Filter Button”. It would come in very handy. I guess i have to try and rethink my approach.
Regards
Matteo
Kim 12/10/2012 19:52
I'm currently using this on 2010, will it work on 2013?
Juerg 12/10/2012 22:10
Kim, the SQL Query Viewer web part is compatible with SP2013. Please use the "install2013.bat" file for installation.
Dan Neuman 1/14/2013 17:34
I am having problems with wrapping text, and I can't find the problem. Is there an option to add a "nowrap" tag in the TD attributes?
Thomas 1/14/2013 18:10
Dan, You can define html formatting for each column/field: Web Part Miscellaneous Section Configuration, field “Column Formatting”. E.g. the field City, instead of “;” in styles, you must use “¦”, the value placeholder is {v}: City:<span style="white-space: nowrap¦">{v}</span>;
Ryan 2/1/2013 14:33
Great web part! However I could use a little help getting a search filter to work. My SQL Query is "SELECT NPA, CallingNumber AS Number, BldgAbbrv AS Building, Location, CustomerName, LastUser, LastUpdate, LastUpdateE911, Demarc, {more=20}Notes FROM Numbers Order By CallingNumber"
That part works great, however I would like to offer a search by CallingNumber and BldgAbbrv. The SearchFilter I tried using is: Number;@Building;. I have also tried CallingNumber;@BldgAbbrv; with no success.
I get an error at the bottom of the web part that states: "SQL Query Error: Object reference not set to an instance of an object. ". The search boxes never appear at the top. What am I doing wrong? Thanks!
Thomas 2/11/2013 15:58
Ryan, the "Search Filter" feature is only available in the enterprise version. To activate the enterprise version you must request a free trial license key by the link above (30 day Enterprise Version Trial Key).
Mark 2/18/2013 18:04
Dear Thomas, Juerg, are there any news about your comment on 7/31/2012: "Mark, we are working on a complete new custom web part's editor, which allows us to make the controls as desired. Once we have that, we will also equip the SQL Query Viewer web part so that the line breaks will remain." ?
Nicolas 2/20/2013 00:17
I installed the product, enter the trial code, configure the query, string connection, etc and show the result correctly, but when I try to use Search Filter(s) feature, i get the following error:
[NullReferenceException: Object reference not set to an instance of an object.] AESQLWebpart.AESQL.OnPreRender(EventArgs e) +17897 System.Web.UI.Control.PreRenderRecursiveInternal() +107 System.Web.UI.Control.PreRenderRecursiveInternal() +223 System.Web.UI.Control.PreRenderRecursiveInternal() +223 System.Web.UI.Control.PreRenderRecursiveInternal() +223 System.Web.UI.Control.PreRenderRecursiveInternal() +223 System.Web.UI.Control.PreRenderRecursiveInternal() +223 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3393
Juerg 2/20/2013 13:26
Nicolas, can you indicate what you entered into the web part's "Search Filters" setting ? Also, does the error persists when you navigate away and the re-open the page ?
Nicolas 2/20/2013 15:37
Hi Juerg,
I entered the names of the columns of my query, separated by semicolon:
User_Name0;Netbios_Name0
If I navigate away and re-open the page, the webpart is gone. I need to put a new one to try again.
Juerg 2/20/2013 16:10
Nicoals, this is quite strange. Is it possible to arrange a remote session to allow us to investigate the problem ?
Nicolas 2/20/2013 17:46
Sure. Should I send an email to support to schedule the remote session?
Mark, we will release version 2 of the web part later this year. This version will then also contain a better editor to enter the SQL query.
Michael H 3/1/2013 09:49
Is it possible to justify the column headings? I have been able to ceter,left and right justify the column values but the heading stays in the same place...
Juerg 3/1/2013 13:00
Michael, yes, you can use the web part's "Column Widths" setting to specify the column heading alignment:
I am using SharePoint 2013. When I set the Gridlines to 'Both' the outer gridlines don't display in Internet Explorer. Works fine in every other browser, and it worked fine in SharePoint 2010.
Juerg 3/7/2013 19:28
Mark, we have now added the „AESVgrid“ CSS class to the web part which now allows you to style the web part via your CSS style sheet or via a Content Editor web part.
The style to re-add the missing outer border is as follows:
.AESVgrid {border:1px solid silver}
You also can add it as follows via a CEWP placed on the same page:
Please re-download the updated Zip file, extract the WSP file and then update the web part solution.
Nicolas 3/26/2013 18:26
Application is great! One question. Do you provide multilingual support? It would be nice if we could set the label for "record(s)", or "Export" button...
Juerg 3/26/2013 18:43
Nicolas,
Custom formatting of the “xx of yy records” footer: You can use the new „AESV_NbrRecords" appSetting variable to specify the layout of the records resume in the footer. Please add the following new line to the appSettings section of your Sharepoint application’s web.config file:
You can also let the web part display a custom message if the query returns zero records by adding the following line to the <appSettings> section of your web.config file:
Export button: Please use the web part’s “Export Button Text” setting to localize the “Export..” link.
Jill Riesenberg 3/29/2013 23:22
We would like to use this web part to connect to our Salesforce cloud-based application. Do have anything you can point me to that will step me through creating a connection string for this type of data source?
Hi, Is it possible to search a date range with using drop downs? (From: and To: )
Regards
Juerg 4/16/2013 11:14
Tom, you can use the following 4 Search Filter comparison operators:
The default “=” filter comparison operator can be overridden for numeric and date values to either specify "greater than", "greater or equal", "less than" or "less or equal" by adding either the ">", ">=", "<" or "<=" suffix as follows:
Example: @Year<;Price>
Tom 4/23/2013 13:45
Can you date ranges in drop downs or date picker? For example: from date 01/01/2013 (as one drop down) and to date as another drop down in the search filters
Regards Tom
Juerg 4/23/2013 16:37
Tom, you can use date ranges in dropdown filters (the dropdown will list all dates selected by the query). You also might first format the column to shown the date only using SELECT convert(varchar, yourdate, 111) AS yourName:
Chris 4/30/2013 21:44
I have added the SQL Viewer Web Part to a Default New Form. WHen a user changes a value in the new form I would like to send a filter value from the new form to the SQL Viewer. I have setup the connection and am using the {1} placeholder but there is not change?
Juerg 5/1/2013 10:38
Chris, the "New Iem" List form will transmit the value (as configured when setting up the web part connection) only after the "OK" button (adding the new item to the list) has been clicked. This also closes the dialog window so the result displayed by the SQL Viewer will not be visible anymore.
Chris Gorton 5/1/2013 12:20
Thanks Juerg.
Is there any potential workaround you can think of to get around this?
Juerg 5/1/2013 15:27
Chris, the "New Item" List form will transmit the value (as configured when setting up the web part connection) to the connected web part only after the form's "OK" button (adding the new item to the list) has been clicked, so you cannot use the web part connection to transmit a value as soon as the user enters something into that form field.
Steve Enge 5/8/2013 20:45
Hi Juerg -
Will the SQL Query Viewer access SSAS cubes if you use an OLE DB connection string specifying a MOLAP Provider, and and a MDX query rather than a SQL query?
Thanks, Steve
Juerg 5/9/2013 13:16
Steve, the web part should be able to use a MSOLAP OLE DB connection string in the form of:
and then be able to execute an MDX query, but it only is able to represent the returned results as a flat table of rows.
Steve Enge 5/15/2013 20:53
Has anyone tried to get the SQL Viewerweb part to read SSAS cubes (a competing product, FilterZen, will):
CONNECTION STRING
Provider=MSOLAP.4; Data Source=SQL08R203; User ID=kcmhsas\testdss; Password=******; Initial Catalog=ServiceFundingDwhV2; MDX Compatibility=1; Safety Options=2; MDX Missing Member Mode=Error
QUERY STATEMENT
WITH MEMBER [Measures].[Label] AS [Population].[Population].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[UniqueName] AS [Population].[Population].CURRENTMEMBER.UNIQUENAME SELECT {[Measures].[Label], [Measures].[UniqueName]} ON COLUMNS , [Population].[Population].MEMBERS ON ROWS
Juerg 5/16/2013 15:43
Steve, we noticed that the web part actually treats all queries that do not start with “SELECT..” as stored procedures. We thus now have fixed this so that a query that starts with “WITH ..” also is treated as an SQL statement. Make sure to follow the “WITH” clause with a space character as eg.
WITH MEMBER [Measures].[Label] AS …
Please re-download the updated Zip file, extract the WSP file and then update the web part solution.
Shawn 5/30/2013 16:40
I am evaluating this webpart for our company portal. It setup perfectly and is executing a very complex query from another sql server just fine. But I noticed that when I test from a web browser outside of our network using https and port :987 it has the cannot connect pipe error. If I access internally and use the built-in caching option it seems to work okay and show the remote workers a cached page. Any other work around to allow a live query for the remote workers?
Juerg 6/1/2013 16:17
Shawn,
do you get a “provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server” error ?
If yes, open SQL Management Studio, right click the SQL server instance, choose “Properties”, and under “Select a Page list”, click “Connections”. Now you should see a checkbox labeled "Allow Remote Connections to This Server". Make sure it is checked. That should take care of the remote connection issue.
Auke 6/4/2013 16:06
It is possible to use one SQL Query Viewer web part to retrieve values from a SQL table and use these values in a next SQL Query Viewer webpart as dynamic paramaters?
Matteo 6/8/2013 08:14
Hello All,
Form day 1 using our SharePoint sites i have wanted an easy way to determine what web front end server (we have 2 of them) a user is connected to. The other day i stumbled upon a way to do this using the Sql Query Viewer web part.
The trick for determining what web front end server you are connected to is using the T-SQL system function host_name() which gets the client workstation. The client workstation is the machine hosting the page that is requesting the SQL query. Since the web part is on a page in a SharePoint web site and the SharePoint farm has more that one web front end server, the host_name() value will actually be the name of the particular web front end server where the page is hosted.
I did up a blog entry with the particulars. Take a look.
1. Can I use this for anonymous access? 2. Can I have Individual Item printable view.
Juerg 7/16/2013 18:04
Srikanth,
Yes, but you need to be careful when connecting to the database using Windows authentication (where the current user’s permission is used) so you need to make sure to grant your users the proper database permissions. There is no such problem when using SQL Server authentication.
You can open a row in a detail view popup dialog but there is currently no “print” option for the detail view.
Matteo 7/18/2013 16:30
Hello Amrein,
I have a couple of questions
1) Parm: "Edit Dialogue Page URL" I noticed in the 1.1.107 version of the web part there is a newish parm called "Edit Dialogue Page URL". Would you be so kind as to describe what the purpose of this parm is and mabe provide an example or two of how it would be used.
2) Juerg's response to Srikanth on 7/16/2013 18:04 I noticed in Juerg's response the comment "You can open a row in a detail view popup dialog". Can you please explain what this is referring to? I do not know of any functionality of this web part that does such a thing.
Regards
Matteo
Brian Lukanic 7/19/2013 16:23
Hi Juerg,
Would it be possible to add an Edit icon and code that allows that icon to be used as a placeholder like you do for some of the other keywords, such as mail? A little icon that connotes "edit" rather that the word would be a nice feature.
Thanks, Brian
Juerg 7/20/2013 12:12
Brian, you can actually use an image instead of text for the column pointing to the “Edit” dialog window:
The _Layouts/images folder for example contains a large number of icons.
Jonathan Schatz 7/25/2013 22:40
Hi, I have the Enterprise version (awesome web part!). Having trouble with dbase (dbf) files. Will the program support dbase files though an ODBC connection? Thanks!
Juerg 7/26/2013 11:59
Jonathan, you can either use the Microsoft dBase ODBC driver: Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\mydbpath;
or use the ACE OLEDB driver: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\folder;Extended Properties=dBASE IV;User ID=Admin;
Hello, can the fields of the ‘Edit Webpart’ be made dropdowns if needed (similar to the filters)? Or radio buttons if it is a true/false var. Thank you! Anna
Juerg 9/4/2013 18:51
Anna, you can configure dropdown boxes in the SQL Edit Web Part via the “Lookup Columns” setting as follows :
Example 1 (explicit): City=Rome|London|New York
Example 2 (via SQL): City=SELECT DISTINCT City,City FROM Employees
(ie. Column1=SELECT DisplayValue,TransmittedValue FROM Table)
Kevin 9/8/2013 01:47
Is there any way to pass the current Filter values from the Web Part to another List? I would like to be able to use the filters as URL parameters in a separate Link list. Thanks.
Juerg 9/9/2013 19:37
Kevin, this is currently not possible (ie. the SQL Viewer web part filters are not implemented as Sharepoint Filter web parts).
geoff 9/16/2013 17:47
any chance this can work with the secure store to pull creds? We frown on Sql Auth at my company and would prefer to use a service account.
Michael 9/19/2013 18:23
Hello, I'm running out of ideas, maybe someone can help me out... I have two WebParts on a WebPage, let's Name them 'Overview' and 'Detail'. In the Overview I Display some data with a column 'Order'. The value of Order should be a Hyperlink which should update the Detail Web Part with some Information concerning the clicked order. Is there any way to build something like this?
Thanks a lot for your help Regards Michael
mark 9/25/2013 12:06
Dear Juerg, Thomas, following Thomas' comment on 21 July 2012: "Mark, we are working on a complete new custom web part's editor, which allows us to make the controls as desired. Once we have that, we will also equip the SQL Query Viewer web part so that the line breaks will remain.", are there any news ? We would really appreciate to have it...
Juerg 9/26/2013 13:43
geoff, you could use Windows authentication if either the SQL Server is on the same machine or you are using Kerberos. You alternatively can specify a ConnectionString “name” (as opposed to the fully qualified connection string itself) in the web part’s “Connection String” setting, which is then used to look up the actual connection string in the web.config “connectionStrings section:
You then could use the Microsoft .Net asp_regiis command to encrypt the connection string in the web.config file to protect the credentials.
Juerg 9/30/2013 15:01
Mark, we have added the feature (SQL Query editor preserving the line breaks) to the new version of the SQL Query Viewer web part (download currently only available for Sharepoint 2010/2013). Please download the new version 2.0 of the SQL Query Viewer web part from the following location: /download/AESQLWebpartV2.zip, extract the WSP file and then update the web part solution.
mark 9/30/2013 18:43
Ok Juerg, I've tried updating my web parts to the 2.0 version, but when I try to update the SQL command on my web parts already configured, I get the error:
Exception information: Exception type: FileNotFoundException Exception message: <nativehr>0x80070002</nativehr><nativestack></nativestack>Nessun Web con nome ".../AESQLWebpart/propTextEditor.aspx".
Mark, the propTextEditor.aspx file seems to be missing in the c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS\AESQLWebpart folder on your Sharepoint server. Did you use stsadm or the Update-SPSolution cmdlet to update the web part solution ? We also can send you the file so you can manually put it there.
mark 10/1/2013 15:50
I used stsadm. Anyway, under c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS\AESQLWebpart folder on my Sharepoint server I have the file 'propTextEditor.aspx'...
Juerg 10/1/2013 17:27
Mark, can you indicate the full URL of the page that hosts the SQL Viwer web part ?
Nicola 10/7/2013 14:28
Hi, we have an issue exporting the € euro sign to CSV. Trying with a "select '€' as EURO" we have the good value on the webpart by a '' in the CSV. I see the note from Eugen Dahl on 11/23/2010 and I have no problems with those characters. Only with the EURO at the moment. Thanks
Andrew 10/7/2013 18:07
For AESQLEdit fields corresponding to Int datatypes, a validation error is thrown if the value is less than 0, although a negative value should be acceptable. Is there any way to circumvent this issue, allowing a negative value to be entered?
Juerg 10/15/2013 15:54
Nicola, we were able to locate and fix the problem with the Euro Unicode character. Pease re-download the updated Zip file, extract the WSP file and then update the web part solution.
Juerg 10/15/2013 16:17
Andrew, we have now fixed the problem with the web part rejecting negative integer values. Please re-download and update the SQL Edit Web Part by downloading it from: www.amrein.com/download/AESQLEditWebpart.zip
Bruno 10/25/2013 17:09
I get a list of our employees to create a birthday list. The list gets the first name, last name and birthdate. When I sort by birthdate the oldest person is on top and the youngest at the end of the list. But when sorting I would like to ignore the year, so that sorting starts on 1st of January, continues with the 2nd of January and so on. Even when possible I would like to have the list sorted depending on the actual day. So when we have for example the 25th of October the first entry in the list should be the person with birthday on the 30th of October. Is this possible?
Juerg 10/25/2013 18:56
Bruno, to sort by Birth date (ie. ignoring the year) please use the below query:
SELECT LastName,FirstName,Birthdate FROM Employees ORDER BY DATEPART(month,Birthdate),DATEPART(day,Birthdate)
John Whiteley 11/5/2013 15:29
Does the SQL query web part support recursive queries using common table expressions?
Our web part is displaying the following error: SQL Execute Error: Could not find stored procedure ''.
Juerg 11/5/2013 16:29
John, we have now fixed the problem when specifying an OLAP or a recursive query . Please download the updated version of the SQL Query Viewer web part, extract the WSP file and then update the web part solution.
Russell Schenberg 12/3/2013 19:45
Juerg, I am constantly grateful for this and the companion edit web part. They make my life so much easier.
I have a question about the search filters.I was wondering if there was a way to cascade the dropdown search filters. So that if one search field has A,B, C, etc. And another has A1, A2,A3,B1, B2, B3, etc. If you select A in the first filter then the other search field will only pull from the displayed items (A1, A2, A3 but not B1, B2, B3). Like the built-in Sharepoint list filters behave.That way you can gradually drill down your searches more easily if you have long dropdown lists to begin with.
And thanks again for all your great products. We love them!
Zaid 12/18/2013 12:55
I tried loading Arabic language data inside the webpart, and it is showing Arabic data as junk characters. Does this webpart support Arabic language?
Juerg 12/18/2013 18:23
Zaid, do you connect to an SQL Server ? Also, does the content show up incorrectly in all browsers (as for example in Chrome) ?
Zaid 12/19/2013 09:44
Juerg, After some investigation, it turned out to be data source provider issue. I was using OLEDB driver and when I changed it ODBC driver, it started showing Arabic data properly. Thanks.
Troy Hall 12/31/2013 02:11
Great work. I am having a small issue with sorting the drop down filters. Is there any way to manually set the order of these filter options that will not modify the results themselves? It looks like by default they are ordered alphabetically.
Juerg 1/3/2014 13:33
Troy, we think the only way to achive a custom sorting would be to add an option to manually (explicitly) specifiy the search data to be displayed by the search filter. This only makes sense if there are not a multitude of possible values and the range of values stays constant over a period of time (to avoid having to update the search filter configuration).
Steve Murphy 1/3/2014 22:43
When I publish the page in SharePoint 2013 the Add New Record link stops working. When I am editing the page, it works fine. Any ideas?
Jimmy 1/7/2014 20:02
Hi Juerg - Is there a way to vertically align the search filters for the SQL Query web-part? Thanks
Mo A. 1/7/2014 21:34
Is it possible to use a filter with multi-select. For example, return back all users from the Texas and California offices.
Great product!
Juerg 1/8/2014 14:36
Jimmy, yes, you actually can embed a „<br>” HTML line break tag at the appropriate place in the web part's "Search Filters" setting.
Example: Name;@Country;@Location;<br>@Group
Juerg 1/10/2014 12:56
Mo, the SQL Viewer web part does currently not support multiple choice filters but you actually could use our free “Filter” web part (see /apps/page.asp?Q=5835) and connect it to the SLQ Viewer web part:
You then could use the below SQL SELECT statement (example): SELECT * FROM SomeTable WHERE City IN('{1}')
Juerg 1/10/2014 19:45
Steve, this is caused by the SP2013 "minimal download strategy" when placing the web part on a "publishing" page. We will fix this problem in the web part's next release.
Matteo 1/13/2014 19:25
Hello Amrien,
I have a question about the SQL Edit web part.
I need to set the value of a column on the record being updated to the network userid of the person adding/updating the record via the SQL Edit web part. This would be a column that the user does not have access to but is set on the add or update. I was thinking that I might be able to use the DB function “SUSER_SNAME()” in some trigger code however I am connecting to the database with a service account so that the “SUSER_SNAME()” will not be the name of the individual user but rather of the service account.
What I need to do is to be able to use something like the {UID}, {@UID} placeholders, that the SQL Viewer web parts has, to set a default value for a column in the SQL Edit web part. And while I am asking other values that I might set it to are Today's date or Today's DateTime or a string or numberic value or the {URL}, or the value in the DB function host_name(), etc...
I guess that this is a little bit like the question Anna asked above about how to create a drop down list of values to pick from for a column being updated via the SQL edit but only in this case the value is just put into the column without the end user seeing it or even being aware that it is happening.
Is this some things that the SQL edit Tool can do?
Please advise
Matteo
Matteo 1/17/2014 17:15
Hello Amrein
I have a few follow-up questions;
1) SQL Edit: Column Name Aliasing for the form Label - It appears that the physical table column Names defined in the "Form Fields" parameter are the labels on the SQL Edit form generated. - Is there a way to create an alternate Label Text per column? - I am thinking of the way the pipe "|" character in used for a similar purpose in the quick form web part - So maybe the "Form Fields" parm might look like <Alias Label>|<column Name>
2) SQL Edit: Extra Text blobs in the generated form - I would like to be able to display a text/html blob before/after some columns - I would use this to provide some instruction to the user about how fill in an value - The Quick Form Web part, in the "Form Template" parm, allows this. - If you put in a string of text or an html blob, it just displays this text - I am using the header area text but it would be nice to be able to place some text any where on the form. - Also, if you can include an "footer Text" parm that would also be a good thing.
3) SQL Edit: Date & Datetime columns - I noticed for my Date or DataTime columns that it always displays just the date only picker - Can the gadget display a date and time picker for DateTime columns?
4) SQL Viewer: Show 'Add New Record' Button parm - Is it possible to customize the link text from 'Add New Record' to what ever i want. - Much like the "Search Filter" and the "Search Filter Reset" buttons
5) SQL Viewer: Show 'Print' Button parm - Is it possible to customize the link text from 'Print' to what ever i want. - Much like the "Search Filter" and the "Search Filter Reset" buttons
Regards
matteo
Juerg 1/17/2014 20:09
Matteo, The SQL Edit web part (starting with version 1.1.5) actually supports the {uid}, {userid} and {today} preset placeholders.
{uid} inserts the current user account (without the domain part) {userid} inserts the current user account (domain\username) {today} inserts the current date
The {userid} and {today} placeholders are only effective when adding a new record. In the case of updating records please use the {timestamp} and {modifiedby} placeholders which both affect both edited and new records.
You also can optionally hide these fields in the form by using the “/hidden” qualifier as follows: Building,!Modified={timestamp}/hidden,ModifiedBy={modifiedby}/hidden
SQL Viewer web part: We have now added the new SQL Viewer „Options“ web part setting which allows you to set custom labels for the “Print and “Add New Record” labels.
Example: printbutton=Print data..|addnewrecordbutton=add a new database record..
Please download the updated version of the SQL Query Viewer web part and then update the web part solution.
Matteo 1/18/2014 14:38
Sorry Forgot to put my Name/email on this item ============================= Hello Amrein
Some additional items
6) SQL Edit: A cancel button - It would be nice to have an optional "Cancel" button feature - Where when clicked no update would be made and the view of SQL Edit gadget s now invisible. - This provides the end user an option to bail out of the update should they choose to do so - Currently to cancel an update the end user must abandon the page which is not very nice. - Display/use of Cancel button should be optional - Should be able to customize the text on the button, like the update button
7) SQL Edit: A Delete button - Should be able to customize the text on the button, like the update button
8) SQL Edit: Optional "Confirmation Dialogue Box" on Update, Delete, Cancel - It would be nice to have an option to present to the user a "Confirmation Dialogue Box" ("OK" or Cancel") when performing Update, Delete, Cancels (amusing you like my Cancel idea above) - It would be nice if the text displayed in the "Confirmation Dialogue Box" was configurable so it could be customized per use.
9) SQL Edit: "Use explicit Update" - What is the purpose of the "Use explicit Update" option? - What does it do? - How is it different from a regular update?
Regards
Matteo
Matteo 1/19/2014 22:53
Hello Juerg
Thanks for the note on the {UID} , {USER}, ! read only and /hidden options they work OK.
1) Text String assigned to a column value - The various {} parms are great but i also have the need to always set a column to a set value. - Is it possible to just assign a string of text to a column in the same fashion as the various {} place holders? - Also how would i set a column value to Null or Null String or some spaces?
2) ! Read Only option - I notice that on date and datetime columns, set as read only using the "!", that the Date picker icon and functionality was still working. - You could us the date picker to choose a date - It then changes the date on the page - It does not however actually change the value in the database - Perhaps the date picker should not be added to the page for column in Read only mode
3) {timestamp} - I notice that this using this place holder only gave the current Date, Hour, Minute - Is there an option to have it also return seconds & Micro seconds?
Matteo
Mo A. 1/21/2014 15:02
Is it possible to control the row height?
Juerg 1/21/2014 16:28
Mo, yes, please add the below CSS style to your CSS style sheet:
.AESVgrid TR {height:34px}
(adjust the height as needed)
You also can directly inject the style by adding the below to the web part's "Header Area Text" setting:
<style> .AESVgrid TR {height:34px} </style>
Juerg 1/21/2014 19:44
Matteo, we have now added the following new options to the SQL Edit web part:
The “Delete” button’s caption can now be localized via the “Submit Button Text” setting. Example: Update|Delete record
Field label „friendly“ names can now be set up as follows: Label|FriendlyName; Example: FirstName|First name
Additional text can now be placed below the form field as follows (by appending it to the form field, separated by a colon): Example: FirstName:please enter the emplyoee's name
Preset fields with a default value when adding a new database record: Just add the preset value, prefixed by an equal sign. Example: LastName=Guggenheim
The date picker icon is now suppressed when a date/time field is flagged “read-only”
“Cancel” button: we recommend to use the new option to open the SQL Edit web part in a Sharepoint 2010/2013 dialog window which includes a “Cancel” button
The “Use Explicit Update” option is helpful if your form fields do not include any key column information. The web part then uses an SQL “UPDATE” command to write the database record.
Anna 1/22/2014 16:52
Hello, Can we set what values a filter can have? Example: we want data from last 30 day. We have ‘where’ clause that states: … where date <= {f:!days} ‘Days’ is a filter text box. We would like to have the filter to be a dropdown and assign 30, 60, 90 days values, rather than making user type their number. Thank you!
Juerg 1/27/2014 15:37
Anna, we have now added the new option to predefine the values of a search dropdown filter.
“Search Filters” example: @Days=[30,60,90]
(eg. place the preset values into square brackets and separate the values by commas).
Please download the updated version of the SQL Query Viewer web part, extract the WSP file and then update the web part solution.
Phil 2/21/2014 22:09
Juerg and Amrein crew:
I use this part (ENT) so much and love it.
I'm having an issue with using it for a particular stored procedure. The page load seems to be extremely long even though I have unchecked the 'show all entries' and have a search filter set up for the user to type a search term.
It seems that it is returning the entire data set when the page loads even though it does not display.
Is there a way to have the part NOT return the full data set for a SP upon initial page load?
Juerg 2/24/2014 17:03
Phil, the web part indeed loads the data set irrespective of the “Show all entries” setting. However, you might consider to use the web part’s “Cache Retention Time” setting which allows the web part to cache the data for the specifies number of minutes (as eg. 120 for two hours). In this case only the very first page access will take longer and subsequent page visits should be much faster.
Matteo 2/25/2014 18:58
Hello Juerg & Phil
There is another way to handle the large data volume problem.
Using the “{f:” syntax (see discussion above for this feature) on the stored procedure call will actually rerun your stored procedure each time you hit the submit button, allowing you to pass your filter values into the parms of the stored procedure.
For example; Let us say you have a large set of Sales data and you will always be filtering on SalesYear and SalesArea.
You would set up the SQL Viewer gadget as normal using the SalesYear and SalesArea columns as filters and then you would add a few parms to your stored procedure.
Your “SQL Query” value, when running a stored procedure would go from;
Where the “pSalesYear” and “pSalesArea” would be new parameters into the stored procedure.
The trick here is to construct your “Where” clause on the select in your stored procedure to return no rows if the new parms are empty. This way the page will render very fast initially. Then as you enter Values in your filters, you are not scoping the memory table of results, but actually re-running your stored procedure with your filter values in your where clause returning just those records you need. Or to say it a slightly different way, the code in your stored procedure must do the filtering based on the parms coming into the sp.
Now this works ok as long as you do not use the “@” prefix for your filter. Of course this prefix builds up a unique list of values to filter on based on the values in the column on the initial run of the stored procedure. If you are not returning any values in your initial run of the stored procedure then you will get no values for the drop down.
Possible work around here is that when the filter parms come in empty, instead of returning no rows, return enough rows of data, so that those columns using the “@’ filter option , will have enough data examples to build a proper list of drop down values. and since you have the setting on to not show data initially then no one would see these records.
Another possibility it to use the new filter syntax “@Days=[30,60,90]” (see a couple of notes above) where you hard code in the values for the drop down.
Also not that when you use the “@” or “!” filter prefixes you must also use these prefixes in your “{f:” syntax. For example
Is there a way to set the background colour or image per cell? I do see of course the various alternating/grouped row background color options as well as the <p style="background color:red">..</p> method. However the <p style="background color:red">..</p> method just controls the background color of the text in the cell not the cell background color. I need a finer control over these table cell properties, like background-color. Any suggestions?
Matteo
Matteo 3/17/2014 00:32
Hello Amrein,
Is there an option, when configuring a search filter to display a "Date Picker" gadget. I have a need to allow the user to choose any date and then I return some data based on the date entered. I am using the {f: syntax, which of course always re-runs the sp that i have with this date as a parm. It is not practical for me to include all of the dates in the data set to feed a drop down and the new "@Days=[30,60,90]" option is cool but does not help here. I was thinking that something like this "DateParm=[DatePicker]" would do the trick. Any suggestions?
Matteo
Matteo 3/17/2014 01:44
Hello Amrein
I noticed today that when I set column widths, other than setting the width to 0 (zero), that the widths were not respected unless the “Show Column Headers” option was turned on. Should not the column widths be respected regardless of the “Show Column Headers” option setting? I am using 2.0.18 version of the SQL Viewer.
Please advise
Matteo
Juerg 3/31/2014 20:09
Matteo, we have now also added the option to use a date picker in the web part’s “Search Filters” setting:
Please define a search filter box as a date picker by using the “#” prefix.
Example: LastName;#BirthDate
We have now also added the new „/css=…“ option (to be embedded in the SQL statement. This allows you to inject conditional CSS styles as needed.
Example: SELECT ID,LastName,FirstName,CASE WHEN City='London' THEN City + '/css=background-color:DodgerBlue;color:white' ELSE City END AS City FROM Employees
We have now also fixed the problem with the column width settings not being respected when the table header is turned off. Please download the updated version of the SQL Query Viewer web part, extract the WSP file and then update the web part solution.
Matteo 4/3/2014 03:02
Juerg,
Way cool!! I shall test these changes.
Matteo
Matteo 4/3/2014 03:05
Hello All
So I have been using the Amrein SQL Query Viewer Web Part for a while now. One of the challenges has been formatting the data to fit into some very tight spaces. It seems, like always, that there is no one good design choice but only a choice between two not so good choices.
Sometimes I have a narrow web part zone on a page where i need to list 7 columns of data but I only have room for maybe 2 or 3 not so wide columns. The usual solutions are;
- Use a smaller font - Truncate the content - Use the {more=5} syntax to display the column content via a cursor hover - Reduce the column pixel width, which may wrap the content to a second row - Stack up a few columns in one column (e.g. col1:{v}<br>{Col2}<br>{Col3}) - Use the SQLEdit in non-update mode
If only there was a way, to maybe have the "Stack" solution have two view states; an expand view state; and a collapsed view state. That way by optionally clicking on a +/- icon you can expand an individual row to see more content, then click it again to hide this additional content.
Well here is how to do it. See the URL to a blog entry below. Enjoy
Hi guys. I recently purchased the enterprise version of this web part. I configured it with my SQL query (a CTE query, for what it's worth) using {1} for my placeholders, and made sure to deselect "show all entries." The goal is to show no entries until the user submits a term from a connected web part. But no matter how much I adjust it, the web part still always shows all results on the initial page load. Both a connected text filter part and a connected HTML form part work as expected when submitting terms. But I really need it to display nothing when the page is first loaded or filters cleared. Any idea what's going on here?
On another note - I've added my registration key in the web.config, but still it complains about Evaluation Version unless I type the key directly into the web part.
Kent 5/23/2014 07:06
Follow-up to my last comment. My query has a structure like:
WHERE ECNUMBER LIKE '%{1}%' OR ECNAME LIKE '%{1}%'
When I substitute a space in the placeholder spot:
WHERE ECNUMBER LIKE '% %' OR ECNAME LIKE '% %'
I get a wide open result set of all records. This is what appears to be happening when the page initially loads. I thought the problem might have something to do with my connected HTML form web part, so I made sure to fill its single text input with a default value of "Keyword or number." When submitted as the search term, this returns zero results, as expected.
Juerg 5/26/2014 14:39
Kent, please use the following SQL query:
WHERE (ECNUMBER LIKE '%{1}%' OR ECNAME LIKE '%{1}%') AND '{1}'<>''
This ensures that no rows are selected if the filter value is empty.
Kent 5/27/2014 17:25
Thank you, Juerg. That worked perfectly. Great products - I have now purchased and am using the staff directory, image carousel, and list search as well. All with great results.
Andrew 6/27/2014 11:17
I have the enterprise version of the SQL query viewer web part and am trying to get the SQL Edit Dialog working but cannot.
I am satisfied that the parameter passing works because I am able to use the older SQL Edit web part.
Is the Query Viewer web part supposed to display detail like the Edit web part? If so, what am I missing?
Juerg 6/29/2014 16:41
Andrew, the SQL Edit web part can be configured as a Sharepoint popup window from within the SQL Viewer web part:
To tag one of the SQL Viewer columns as the link to open the Edit window, please include the column that will be used as the unique ID (“EmployeeID” in the below example) in the SQL query:
SELECT LastName,FirstName,City,EmployeeID FROM Employees
and then enter the below into the web part’s “Column Formatting” setting to link the ID column to the popup using the “:detailpopup” tag, followed by a “pipe” character and the desired display text:
EmployeeID:detailpopup|Edit..
Andrew 6/30/2014 02:15
Thanks Juerg for that top tip. As a viewer, this pop-up box will be really useful
Andrew 6/30/2014 03:21
Juerg, The width of the edit fields seems fixed and does not change with different values of Textbox Nbr of Columns. Is there a way to make each field wider?
Juerg 6/30/2014 11:10
Andrew, you can globally specify the width of the edit fields via the web part’s “Textbox Nbr. of Columns” setting. To specify the width individually, please append the desired number of columns to the form field (“Form Fields” setting), prefixed by a “/” character.
Example: Firstname,Lastname/50,City
Andrew 7/1/2014 05:49
Is it possible to use {uid} or query parameters in either the Title or Header Area Text?
As a workaround I am using some javascript in a web part.
Juerg 7/1/2014 10:50
Andrew, we have now added the option to use the {uid} token both in the web part’s „Header” and “Footer” settings.
Please download the updated version, extract the WSP file and then update the web part solution.
Michael Sutton 7/4/2014 16:30
Hello. I am a site collection administrator and I can see the Miscellaneous tab; colleagues who are not site collection administrators cannot. When I am not site collection administrator, I cannot see the Miscellaneous tab. What permissions are required to edit the web part? I do not want to make everyone site collection administrator.
Juerg 7/7/2014 12:01
Michael, you will need to assign „Design” permission in the site(s) where you intend to use the web part (via “Site Actions/Site Permissions”).
Michael Sutton 7/8/2014 16:08
Sorry, still not working. User had Full Control; now has both Full Control and Design. Only a site collection administrator can see the miscellaneous tab, that is the onle useful one. Is there any update on this? It would be a great web part if I can let other people, who are not site collection administrators, use it!
Juerg 7/15/2014 15:12
Michael, can you open the web.config file associated with your Sharepoint application (typically located at c:\inetpub\wwwroot\wss\VirtualDirectories\80) and search for the below line:
I was testing some of the recent Changes and found the following issues. I am using the 2.0.30 version of the Sql Viewer web part
1) new ,/css='' option (to be embedded in the SQL statement.This allows you to inject conditional CSS styles as needed.) - This works ok in that the styles in the CSS get applied - However if you have any html in the column value on the select before the /css='', the "column html" does not get rendered, but rather it is displayed on the screen as part of the column value - try the following select
2) # Date Picker functionality for "Search Filters" and Querystring parameters - I tried the # Date Picker and it worked ok to fill the parm in my Stored Procedure and such, but i found a problem with using this column in Querystring parameters - Before i used the # Date Picker feature i could construct a Querystring parameter as below and it worked fine http://my.SiteURL.aspx?RecDate=2014/07/01
- I then changed the web part to use the # Date picker functionality. That is
- When i ran the page and picked date using the date picker it worked just fine - However my url with Querystring Parms http://my.SiteURL.aspx?RecDate=2014/07/01 no longer worked. - It seemed as though the date value was not getting into stored procedure - I noticed that when using the date picker the date format was mm/dd/yyyy so i then tried the url http://my.SiteURL.aspx?RecDate=07/01/2014, but it to failed. - So my question is what date format value do i put in the Querystring parameters when using the # Date picker feature.
--here is how i defined my Parms in my Stored Proceedure
ALTER PROCEDURE [dbo].[calGet_Month] @pInitialDate as Datetime ,@pURL as varchar (500) AS Begin set nocount on
Please advise
Matteo
Juerg 7/21/2014 21:22
Matteo, we were able to fix both issues. Please download the updated version, extract the WSP file and then update the web part solution.
Steve Murphy 7/25/2014 16:42
Any chance that a column formatting option for percent can be added? Also, what about percent as opposed to sum in the totals column? Is there a way to do this now and I just can't figure it out.
Juerg 7/28/2014 12:24
Stephen, please enter the below into the web part’s “Column Formatting” setting:
ColumnName:{num=P1}
(where “ColumnName” is the name of the column containing the percent values as a fraction of “1”).
Michael 8/6/2014 15:32
Hello, I have got a question concerning the SQL Edit Web Part: Is there a way to Display a field of datatype smallint as a Checkbox in the Edit-Dialog? Thanks
Juerg 8/6/2014 19:10
Michael, the web part currently only displays fields of datatype “Boolean” as a checkbox.
Anthony 8/25/2014 17:59
I am trying to put the search fuctionality on the webpart and i am receiving an error that says "Object reference not set to an instance of an object." Any idea what this means or hot to fix it. We already fave the external list showing and rendering a html image, we just need the search capablities of the web part to connect to the already created external list
Juerg 8/26/2014 12:10
Anthony, the web part's "Search" functionality is disabled in the trial version so you need to request a 30 day Enterprise Version Trial Key for evaluation purposes in order to test the search interface.
Anthony 8/27/2014 16:22
Thanks Juerg
Quentin 9/16/2014 16:26
I am using the SQL Edit Web Part “Lookup Columns”. It is working except if my form column is blank the lookup will select the first item by default from the lookup query. Only way I have found to add a blank entry, is to add it to the lookup query, is this the correct method?
Quentin 9/16/2014 17:12
When working with SQL Edit Web Part “Lookup Columns", I found an issue with using a SQL nulls for then blank lookup Name/ID in the lookup query when the ID column is and integer. When updating the form record and the field is blank, I get the error: "SQL Update Error: Input string was not in a correct format. Couldn't store <> in IndustrySubGroupID Column. Expected type is Int32."
My column is defined as “nullable”
Quentin
Quentin 9/18/2014 19:11
I can not set a form field to blank with the SQL Edit Web Part. I have SQL form and when I open the form then delete the text in a column and don;t change any other columns then select Update, the changes are not saved. The new "blank" text is not save to the DB table.
Thanks Quentin
Juerg 9/19/2014 19:00
Quentin, to force the SQL Edit web part to update an empty field, please append /UpdateEmpty to the field in the web part's "Form Fields" setting.
Example: FirstName,LastName,City/updateempty
Juerg 9/20/2014 17:21
Quentin, to add a blank entry to the dropdown box, please select an empty row (“Lookup Columns” web part setting) as the first item as shown in the below example:
City=SELECT '','' UNION SELECT City,City FROM Employees
Quentin 9/25/2014 23:17
Is it possible to move the SQL Viewer “Add New Record” Button so it is not on the bottom, I would like to move it to the top?
Also your suggestions work good.
Thank you Quentin
Juerg 9/26/2014 19:10
Quentin, we have now added the new option to put the "[addnewitem]" token into the web part's "Header Area Text" setting. The [addnewitem] token represents the link to open the "new item" dialog box. Please download the updated version, extract the WSP file and then update the web part solution.
Matteo 10/7/2014 17:33
Hello Amrein
I am using version 2.0.35 of the sql viewer web part.
I noticed a problem today when using the “{f:” syntax to provide a value to a stored procedure.
I am filtering on a couple of columns, using the @ to do a dropdown.
When the initial page appears the filter drop downs have the expected values.
When I select a value from one or more of the filter drop downs and then click my filter button, the filter values make their way into the stored procedure OK, however the filter dropdowns are now empty of any values.
This is a change from past versions where the filter dropdowns, were filled from the initial running of the stored procedure and then retained these values no matter how many times you selected a value then clicked the filter button, each time of course re-running the sp again..
FYI: here are a couple of parms from my config of the SQL Viewer
You can use the “groupstyle” option to style the group headers as needed.
Marco 10/9/2014 14:43
Juerg Thank you for your quick answer.
Alignment heading & specify column width is working. thank you.
the grouping thing not. Do I need to install the Web Part again?
Thx Marco
Juerg 10/9/2014 14:46
Marco, yes, please re-download the SQL Query Viewer Web Part and then update the web part solution.
Matteo 10/28/2014 14:51
Hello Amrein
I have a page with multiple sql viewer web parts on it.
In the first web part the use picks a value from one of the filter dropdowns then clicks the filter button. The Filter value is provided to the underlying stored procedure using the “{f:” syntax so that the Stored Procedure for the First web part is run again. In this SP I am doing some update to a few underlying tables based on the filter value entered.
The second SQL viewer web part is displaying the results of the Updates done by the first web part.
However, I noticed that the second web part is not refreshing it’s self when the first web part has had its filter button clicked.
Is there some option to make this happen? I almost need a full page reset.
Please advise
Matteo
Marco 11/12/2014 11:32
Hei guys
I have another issue:
I have a list of testcases which I can filter by several conditions. One of the filter is a "exact match" filter which filters the column "testcase_number".
now my question. Is it possible to create a link which leads to the webpart already filtered by a specific testcase_number?
So for excample: www.mysharepoint/AESQLViewer/filter_field_1:anyvalue
so similar to a link to a filtered Sharepoint list.
Is that possible?
thank you and regards Marco
Juerg 11/13/2014 16:50
Marco, you can actually pass URL parameters either directly to your SQL query:
SELECT * FROM Employees WHERE Department='{Dept}'
(the above example assumes that the value is passed via the “Dept” URL parameter. Please note that the placeholder uses curly braces)
or you can use the search filters which are automatically preset if an URL parameter of the same name as the filter is present in the page URL.
John 11/20/2014 19:06
Hello,
Can you please explain the proper way to change font color for the group header text?
e.g. grouping=1|groupstyle=font-weight:bold;border-bottom:1px solid gray;padding-top:10px
Thank you.
Juerg 11/20/2014 19:18
John, please use the "color" CSS style as shown in the below example:
Hi Juerg, great web part which we now use extensively. Is it possible to colour code numbers 0 - 10 in a column, if its 0 no colour, 1 = red, 2 = amber, 3 = yellow etc through to 10= green?
Juerg 12/2/2014 17:29
John, yes, please enter the below into the web part’s “Column Formatting” setting:
Status:[1=color:red|2=color:amber|3=color:yellow]
eg. enter the column name (“Status” in the above example), followed by a list of CSS style settings for each value to be styled.
Anna 12/10/2014 20:29
Hello, Is there a way to pass a url parameter to the Edit Webpart or the Dialog Edit? Or when adding a new record can we pass a parameter to prepopulate some fields? Thank you!
Juerg 12/12/2014 21:13
Anna, the SQL Edit web part supports the {uid}, {userid} and {today} preset placeholders.
{uid} inserts the current user account (without the domain part) {userid} inserts the current user account (domain\username) {today} inserts the current date
“Form Fields” example:
Anna 12/22/2014 20:32
Thank you for the reply. I was more curious if we can pass other values. For example if we are adding a record for address can we pass a preset city. Thanks!
Juerg 1/2/2015 12:23
It is possible to pass in URL parameter to preset text and date fields when adding a new record.
Hi Juerg, I've just installed Version 2.0.42 on a SharePoint 2010 environment, but it seems that button for SQL Query (to open the enhanced text editor that mantains text format) does not work anymore on Internet Explorer 11 (ok, instead, on Firefox). Does this issue happens to you too ?
Russell Scheinberg 1/9/2015 00:34
Juerg,
I've been using this web part for years and it is truly a workhorse for us. Thanks for all the improvements over time. You may have answered this but I couldn't find it here. Is there a way to have a pull down filter that checks a particular field for null or nonexistent values versus having some value entered. I have a DOB field for which I'd like to be able find all the records where this is blank, but I don't want a list with all the existing values for obvious reasons. Thanks.
Juerg 1/16/2015 19:53
Marco, we are not able to reproduce this problem with IE11 on Sharepoint 2010. Can you check if setting the IE11 User agent string (via F12/Emulation/User agent string) to "Internet Explorer 9" solves your problem ?
Juerg 1/16/2015 20:20
Russell, this is currently only possible when using a text search box (as opposed to a dropdown search box). We’ll check if we can add this option also for dropdown search filters in the web part’s next version.
ifshaan 1/21/2015 13:17
how to you export the calculated total to csv format, export option seems to ignore it
Juerg 1/21/2015 18:46
ifshaan, the Export indeed ignores the "Total" row and we will fix this in the web part's next major release (due in March 2015).
Matteo 1/21/2015 20:47
Hello Amrein
I am using SQL Viewer ver 2.0.44 ad SQL Edit ver 1.1.19.
Today i discovered a problem when using the SQLEdit web part.
I created the edit link to an item on my list generated by SQL Viewer web part.
When i clicked the edit link, the SQL Edit web part opened up ok and i was able to make my edit and then when i clicked the update button and the update was succesfully done.
However when i now clicked the "Reset" button on my SQL Viewer gadget it then opened up the SQL Edit webpart again.
I noticed that after the SQL Edit web part closed the ?id=1 parm was still showing in the URL.
I would suppose that the clicking of the reset button just takes the current URL value and refreshes the screen only the ?ID=1 query parm is still there so the SQL Edit web part opens. Just my guess.
Any way can you take a look.
Matteo
Matteo 1/21/2015 20:50
Hell Amrein,
Any hints as to what, if any, new features will be included in the next release (due in March 2015)?
just curious
matteo
Matteo 1/21/2015 21:25
Hello Amrein
I am using SQL Edit ver 1.1.19.
I was Trying to use the SQL Edit web part to do deletes only.
Of course in the "submit button text" parm I usually enter "Update | Delete" to label the the update and delete buttons.
But what value do I use if I only want to do deletes? I tried "| Delete", but I still got two buttons with the update button having no text.
Please advise
Matteo
Matteo 1/26/2015 08:28
Hello Amrein
I am using the SQL Edit ver 1.1.19.
I have a question about the number of rows that appear for a column when using the sql edit web part.
I know that the parm "Textbox Nbr. of Columns" will set the width for all of the columns and the parm "Textbox Nbr. of Rows" set the number of visible rows for all of the columns.
I also see that that a "/nn" after the Column name in the Forms Field (e.g. Firstname,Lastname/50,City) will set the width of that column to a different value then then "Textbox Nbr. of Columns" parm
Is there a similar setting for the the number of rows to override the "Textbox Nbr. of Rows" for a particular column.
PLease advise
Matteo
Quentin 1/26/2015 17:32
When working with SQL Edit Web Part “Lookup Columns", I found an issue with using a SQL nulls for any blank lookups, I using Name/ID combination in the lookup query and the ID column is and integer. Then when I select the “blank entry” (Using Union for the Lookup Column ) from the Lookup then update the form record I get the error: "SQL Update Error: Input string was not in a correct format. Couldn't store <> in ID Column. Expected type is Int32. Somewhere in the update code you are trying to store “<>” in a integer varible, the error happens before the SQL update, I never see the SQL code when running the SQL Profiler.
This is my Lookup query: IndustryID=select null, null union all select IndustryGroupSector+' - '+IndustryGroup+ ' - '+IndustrySubGroup, IndustryID from gen.Industry
Thank You, Quentin
Quentin 1/26/2015 17:37
Can the SQL Edit Dialog form display the hourglass cursor or an animated gif when the Update button is clicked?
Thanks Quentin
Quentin 1/26/2015 18:15
I have an SQL Query Viewer, I am using the built in “SQL Edit Dialog” and this is working well. I want to add a second SQL Edit Dialog webpart on my page and then open the second “SQL Edit Dialog” webpart from the first “SQL Edit Dialog” form, the second dialog form is a subset of one of the lookup fields. I would want to add a link "Edit" next to the Lookup, is this possible?
Thanks Quentin
Simon Evans 1/26/2015 18:58
Hi,
Fantastic webpart! We have an enterprise licence for this web part though the version we have deployed is v1.1.92. How do we obtain the most recent version?
Regards, Simon
Juerg 1/26/2015 20:47
Simon, just download the most recent version from our web site, extract the WSP solution file and then update the web part solution.
matteo 1/28/2015 14:47
Hello Amrein
I am using SQL Viewer ver 2.0.44.
I just noticed a new parm that i had not seen before.
What is the parm "Column Names" used for?
Matteo
Juerg 1/28/2015 15:21
Matteo, the “Column Names” web part setting is useful to use custom column headers if using stored procedures which cannot be easily edited to supply suitable “AS” column aliases.
Example: Email1Address=Email;LastName=Last name
(eg. enter multiple the column aliases separated by semicolons).
Juerg 1/29/2015 16:57
Quentin, this is currently not possible. We’ll check if we can add this option to the web part’s next release. We'll also check your Lookup query and then get back to you.
Matteo 2/7/2015 03:23
Hello Amrein
I am using SQL Viewer ver 2.0.44.
I noticed a funny bit of business today with the SQL Viewer web part..
I have two columns of data.
Column1 has Value of "Bill Brooks"
Column2 has Value of "416-222-8282<br>416-578-0311<br>905-967-1111"
Note that the "<br>"s are put in the sql.
When i display the columns in their own column the row looks like
Column1 Column2 Bill brooks 416-222-8282 416-578-0311 905-967-1111
That is the embed <br> HTML line breaks, in the column2 data, work as expected.
However if i have the following value in the "Column Formatting" parm
Column1:{V}<br>{Coulmn2}
and in "Column Widths"
Column2:0
My display now looks like
Column1 Bill brooks 416-222-8282<br>416-578-0311<br>905-967-1111
The embedded <br>s in the Column2 data are now displayed as content rather than interpreted as HTML and are displayed along with the other data in column2.
So it looks like embedded HTML works ok when listing a column by itself, but not when you concatenate it with another column in the "Column Formatting" Parm.
Can you take a look?
Matteo
Juerg 2/9/2015 17:58
Matteo, we were able to locate and fix this problem. Please re-download the SQL Query Viewer Web Part and then update the web part solution.
Joseph Markovich 2/23/2015 17:11
How can I create a link that will open the default SharePoint detail screen?
Joseph Markovich 2/24/2015 23:43
Hi Juerg- Yes, that's exactly it -- see the detailed database record in a popup. Would I be able to select which columns of information to display? Or base it off of a SQL view or something? That would be great! -Joe
Matteo 3/3/2015 15:08
Hello Amrein
I am using SQL Query web part version 2.0.44
I am working with the "Bit" data type for the first time in conjunction with the SQL Query Web Part.
When the column data is displayed it shows a nice check box, checked or unchecked depending on the setting for the column.
This is nice.
But now i want to be able to filter on the column.
When I add a column, of bit data type, to the "Search Filter(s)" parameter i get a label of column name and then a single line edit box for the value.
I tried various values in the Single line edit box (e.g., 1, 0, True, False) but none of these values made any differences to the list of items being listed. The filter value did not have any effect.
Anyway, since it was a bit data type and since it displayed a checkbox when listed I was expecting the filter interface to also have a check box rather than a single line edit box, but i guess it would need something that could be set for three states, such as check, unchecked or both, so maybe a Drop down for Blank, Yes, No or Blank, True, False would be better.
Anyway it looks as if the web part does not handle columns of Bit Data type in any way that i can figure out.
Can you take a look or provide an explanation on how Bit Data type columns are handled for the "Search Filter(s)" parameter
Please advise
Matteo
Joseph Markovich 3/6/2015 22:44
Hi Juerg-
Two of my columns in my table are date/time. When using the SQL Edit web part, the date shows correctly for the user to create a new record or edit the existing record. The time part does not show, though. How can I get the time to show for the user to enter new/edit existing?
Thanks. -Joe
Anis Dokku 3/8/2015 07:58
Hi, is there anyway to calculate sql column's average value?
Steven 3/8/2015 22:13
Could you please explain how to format a column as $#,###.## please?
Thanks
Juerg 3/9/2015 13:11
Steven, to format the number as a currency with two decimal places, please enter the below into the web part’s “Column Formatting” setting:
Salary:{num=C2};
(the above example assumes that the column is named “Salary”).
Juerg 3/9/2015 18:07
Joseph, the Sharepoint Date/Time picker unfortunately contains a bug which prevents to use the time portion in combination with the date portion in a web part (eg. only either one works, but not both simultaneously).
Juerg 3/13/2015 15:13
Anis, we have now added the new „/avg“ option (to be appended to the web part’s “Column Totals” setting) to display the average value (as opposed to the total value) for a column.
Example: Salary/avg
Mark Bishop 3/18/2015 16:59
Hi, We have used this webpart to create our own staff holiday planner, reading the holiday entries from our SQL based HR system. We have a row per employee and a column per day of the year. Is it possible to lock in the header row and select columns (eg first 1 to 3 columns) so that they always appear if scrolling down or right.
Ps. We still have issues setting col width as per some users above with the current version. We have tried all above advise to rectify but with no success.
Matteo 4/1/2015 06:51
Hello Amrein,
I am using SQL Viewer ver 2.0.44 ad SQL Edit ver 1.1.19.
I was working with the SQL Viewer/ SQL Edit web parts today and in particular the “ID:detailpopup|Edit..” option in the SQL Viewer “Column Formatting” parm and came across a few things I did not notice till now and I also have a few questions.
I noticed that you do not need the SQL Edit web part on the page when using “ID:detailpopup|Edit..” option to do edits and deletes which is something I had not noticed before.
I was always adding the SQL Edit web art to the page thinking that it was needed, but my testing today tells me that the SQL Viewer web part can list, add, edit and delete records in a table all by itself when using “ID:detailpopup|Edit..” option.
Also today was the first time that I was trying to maintain records in a table where the Primary Key was not an integer auto number, but rather a varchar data type column where the column value will be provided by the person adding the record.
The tricky part of this was that I had to add the primary key column name twice in the SQL Viewer web parts “SQL Edit Dialog” Parm of “Form Fields”. This then displayed the Key Column field on the update form for the user to fill in.
The next tricky part was trying to associate a drop down list with the primary key column. I tried numerous combinations but was unsuccessful, until I noticed a very subtle change in the form labelling.
My Prime Key column name was “LocCode”. I noticed that the after I added in the key c olumn a second time to the “Form Fields” parm that the label on the form for that column was “LocCode1”.
Seeing this I then assumed that the “Lookup Columns” parm needed, not the original “LocCode”, but rather the new created designation of “LocCode1”. As soon as I used the “LocCode1” it worked like a charm.
Final thing I noticed was that of course the columns displayed on the add were the same as those displayed on the edit form, when using the “ID:detailpopup|Edit..” option, which make sense in that what else can the gadget do but use the one “Forms Fields” parm to control this.
The problem of course is that you may wish to have a slightly different set of columns for the add and edit (e.g., such as no update to the prime key column on edit).
Yes I could add in the SQL Edit web part and use the “Edit:<a href="{url}?EmployeeID={v}">Edit</a>” option to create the edit link to fire up the SQL Edit Web part with its own “Form Fields” entry but this would not get you the nice popup window.
Any rate after that long pre-amble here are my questions:
1) Am I correct in saying you do not need need the SQL Edit web part on the page to do edits/deletes if you use the “ID:detailpopup|Edit..” option?
2) I am correct in using the “locCode1”, as described above, in the “Lookup Columns” parm rather than the acutual colum name of “LocCode”?
3) Is there a way using the “ID:detailpopup|Edit..” option with the SQL Viewer web part to have a different list of columns on the form for the Add and the update/delete options? or may be a way to invoke the popup window when using the “Edit:<a href="{url}?EmployeeID={v}">Edit</a>” option?
Matteo
Juerg 4/1/2015 18:16
Mark, it is not possible to lock the header row and one or more left-handed columns when scrolling vertically and/or horizontally. Do you mean that Setting the column width does work for some users, but not others ? If yes, is this dependent on the browser used ?
Juerg 4/1/2015 18:16
Matteo, thanks for your elaborate analysis!
The SQL Viewer web part (starting with version 2.0.0)indeed does not require the SQL Edit web part to also be placed on the page (eg. we added the new “SQL Edit Dialog” section in the web part’s configuration to configure the SQL Edit web part opened as a dialog popup window).
Your observation regarding the “LocCode1” column is correct.
There is currently no way to have the SQL Edit popup display a different set of form fields when adding a new record (as opposed to editing an existing record). We’ll consider to add this option to the web part’s next major release.
Matteo 4/20/2015 16:22
Hello Amrein,
I am using SQL Viewer ver 2.0.44 ad SQL Edit ver 1.1.19.
I noticed today that when I put a value in the “Column Names” parameter, and set on the “Allow Column Sorting” option on, that the columns defined in the “Column Names” parameter no long have sorting capability.
Can you take a look.
Matteo
Matteo 4/20/2015 19:10
Hello Amrein,
I am using SQL Viewer ver 2.0.44
Is it possible in the “Column Widths” parm to use a percentage value rather than a fixed pixel value to set the width of the column?
So instead of “LastName:200;FirstName:150” maybe we could have “LastName:33%;FirstName:150”, where in this example the “lastName” column is set to 33% width of the space available, and the “FirstName” column is set to 150px width.
This feature would be in line with the abilities in an html table to set the column widths to percentages and also help with the “Responsive-ness” of the page as it is viewed on devices with a smaller screens such as tablets and mobile smart phones.
Please advise.
Matteo
Matteo 4/20/2015 19:39
Hello Amrein,
I am using SQL Viewer ver 2.0.44
Is it possible when using the "@" option on a Search filter to generate a drop down list of values, to specify that the list of values be presented in descending order rather that acceding order?
I am putting a search filer on a column that holds a date value and i would like the dates to be displayed in descending order in the drop down rather that acceding order.
Please advise
Matteo
Juerg 4/21/2015 20:33
Matteo, we have now fixed all 3 issues:
Interactive sorting of columns using a column alias
Specifying column widths as percentages as for example:
Lastname:25%;City:50%
Please note to make sure that the table grid uses a 100% width by adding the below into the web part’s “Options” setting:
css=.AESVgrid {width:100%}
Sorting a dropdown column in descending order by adding the below into the web part’s “Options” setting:
css=.AESVgrid {width:100%}|combosort=City/desc
(eg. enter the column name followed by “/desc”)
Please re-download the updated Zip file, extract the WSP file and then update the web part solution.
Matteo 4/28/2015 03:14
Hello Amrien
I am using SQL Viewer ver 2.0.44
I noticed today that when using the “Add-Record” and “Delete Record” routine of the SQL Query Viewer Web part it can cause a problem with the search filter ‘@’ (combo box) option. Here are the particulars;
The column that I am using in my search filter is unique for each row (e.g. @DisplayName).
When I add a new record then click the update button on the form, the record is added to the data set, however any search filter ‘@’ (combo box) options will not be updated to show the new record value if the new record was to contribute a new unique value to the column the search filter is based on.
A similar problem exists for the delete. When deleting a record, that has a unique value contribution to the Combo-box values, is not deleted from the combo-box.
In each case of add or delete I have to reload the page to get the combo-box values to reflect the current record-set.
I did not test the update but I would assume it has a similar problem.
Now of course if I remove the “@” from the search filter column. the search filter “Contains” function works OK. But of course you loose the nice feature of the combo box.
Is it possible to update the combo-box data list based on add/Deletes/Updates that the web part does?
Please advise
Matteo 6/26/2015 19:43
Hello Amrien
I am using SQL Viewer ver 2.0.44
I have a question about the "FormsFied" for the seq Edit Parms
In previous notes it was noted that "to force the SQL Edit web part to update an empty field, append /UpdateEmpty to the field in the web part's "Form Fields" setting. Example:FirstName,LastName,City/updateempty"
And there was a further note "To specify the width individually, please append the desired number of columns to the form field (Form Fields setting), prefixed by a / character. Example: Firstname,Lastname/50,City
Now what is the syntax if i want to both set the width and do the updateempty at the same time?
Matteo
Shawn 6/26/2015 23:47
Hello - when the tools returns pages of results, is there a way to sort a column and have it sort all the data, not just the current page?
Juerg 6/30/2015 14:37
Matteo, we have now added the option to specify both the “/updateempty” and the number of columns to the “SQL Edit” web part as shown in the below example:
FirstName,LastName,City/updateempty/50
Please re-download the updated Zip file, extract the WSP file and then update the web part solution.
Mo A. 7/2/2015 16:36
Hello Juerg - I am trying to query a SQL DB to pull records between two dates. So the end user will select start date and end date from some kind of calendar (SharePoint or jQuery). Could you elaborate on how to pass the variable in the SQL statement?
Matteo 7/2/2015 21:20
Hello Amrein,
Can I use the SQLViewer webpart "Connections" to have one instance of the SQLViewer webpart provide parameters values to a scope the data in a second instane of the SQLViewer webpart on the same page?
If so can you provide some insuctions how to set this up.
Please advise
Matteo
Juerg 7/3/2015 13:07
Mo, you can add two date search boxes to the "Search Filters" setting (using the below comparison operators):
#DueDate>=;#DueDate<
Russell Scheinberg 7/7/2015 19:56
Juerg,
I have a question and I don't see the answer listed before. I have 7 search fields for a view, including two date range fields with the date pickers. The problem is that the row that contains these filter fields is not wrapping but running off the right side of the web part area causing me to have a horizontal scroll bar. I have seen this row wrap before but I don't exactly understand why it is not doing this for this particular view. Is there a way to format this row specifically? Thanks.
Juerg 7/7/2015 20:20
Russell, you actually ca insert line breaks at appropriate places in the “Search Filters” setting by prefixing the filters with a ~ tilde character.
I am having one more issue that I need to ask about. I noticed that to get the edit dialog to accept a blank value you need to add /updateempty to the form fields entry. I did this but I still can't edit my fields to empty values. Any suggestions?
My Form Fields looks like this:
BIllId/hidden,FiscalYear AS Year=2016,Office,Vendor, VendorAccount AS Account, ServiceType AS Service, ServiceDescription AS Description, Equipment/updateempty, InvoiceNumber AS Invoice/updateempty, InvoiceDate AS Invoiced, ApprovedDate AS Approved, Cost, Notes/updateempty
Let me know if you need any additional information about my particular settings.
Thanks again.
Russell Scheinberg 7/14/2015 23:51
I have had a request from one of my users I thought I'd pass on. After editing or adding a record is it possible to display the record that was just accessed for confirmation purposes. This could be done with a confirmation dialog box (as suggested by Matteo awhile back) or by configuring the search filter to automatically display the record just edited or added. Just asking. Thanks.
Russell Scheinberg 7/15/2015 17:55
I've happily been using this great product for years and it seems that I now have a project with issues that have never come up before. So sorry for all the questions.
I notice that the column total is only calculating for the displayed records so that if I am paging at 100 rows each the displayed column total is just for the 100 rows displayed. I would like a total that would not be dependent on the displayed rows but all the rows that are returned in the query or filter request. I didn't see a previous entry for this. Is this possible? Thanks.
Matte 7/16/2015 14:28
Hello Russell & Juerg
The ~ character is a good trick for getting line breaks between filters. I did not know this.
What I have been doing to achieve this is using the "friendly" search filter label where you use the "|" pipe character to get a friendly label for a column filter.
The trick here is that the "friendly" label text is treated like an HTML blob, which of course means that you can put in some <br>s to get a line break and any other HTMLish thing that you would like to do, like bolding text or adding a few lines of "Help Text" mixed in with your filters etc...
Or if you really wanted to get fancy you could do something like
Enter <b>some or all</b> of a persons Last or First Name<br>LastName|LastName;<br>Further refine your search by providing one or more of the following values<br>UserAccount|UserAccount;#BirthDate;@City
Because the "friendly" label is treated like an HTML blob you can get quite creative with how your search filters are presented.
One additional formatting feature that would be nice is the optional ability to have the Search and Reset buttons start on a new line rather than being on the same line as the last filter. Maybe they need their own "friendly" label value so that you can put in a <br> in front of them or any other text as desiredl. Juerg and suggestions on this one?
Anyway just a few thoughts
Regards Matteo
P.S. Juerg it would be nice if the SQL Viewer documentation web page content above was updated with all of these configuration options noted in the various comments.
Juerg 7/20/2015 17:16
Russell,
Confirmation dialog: this is currently not possible (and would be quite difficult to implement).
Column Totals when paging is enabled: we have now added the new option to total all rows (in case paging is enabled). You can enable the option by appending
/all
to the web part’s “Column Totals” setting as shown in the below example:
Sales/all
Please download the updated version, extract the WSP file and then update the web part solution.
Juerg 7/20/2015 17:17
Matteo, thanks for your guidelines! We have now added the option to place the search filter buttons on a new line by prefixing the “Search Filter Button” web part setting with a tilde character as shown in the below example:
~Search
Matteo 7/20/2015 17:19
Juerg,
Way cool. Thanks.
Matteo
Russell Scheinberg 7/20/2015 17:42
Juerg,
Thanks again for your responsiveness. The /all option works great.
As to the confirmation dialog, I understand. Figured it couldn't hurt to ask. Still a great product.
Thanks again.
Matteo 7/21/2015 16:23
Hello Amrein,
I am using SQL Viewer ver 2.0.44
I am using the SQL Viewer web part, as well as it’s SQL edit features, to build a small application.
In my app I need to control when a record can be edited or when I can add new records.
In order to add new records at the moment I can either set the web part parameter "Show 'Add New Record' Button" and i get a link at the bottom of the list or add the text string [addnewitem] tag to the header to get an add records link in my header text, with the link text being controlled by the optional entry of "addnewrecordbutton=<link text>".
To get the update or edit link per row I use the "detailpopup" tag in the web part parameter "Column Formatting" parm (e.g. "IDColoumn:detailpopup|Edit") to get the nice popup edit page.
However, both of these features are invoked via a web part parameter option and therefore not available to be changed via the code in my stored procedure or select.
What i need is a way to invoke the edit or add link per row in my code so i can optionally include an edit and or add link per row based on my code in my select or stored procedure.
Much like the way a edit link can be built to invoke the old SQL Edit web part "<a href="{url}?EmployeeID={v}">Edit</a>", which could be constructed in code per line.
Any Idea if this is Possible?
regards Matteo
Juerg 7/22/2015 19:45
Matteo, would you want to have complete freedom by constructing the link directly via your SELECT statement or would it also be useful to have the web part build the link based on some condition in the current row (as eg. a column having a specific value or by applying some comparison as eg. a value is less than some number etc.) ?
Matteo 7/23/2015 19:50
Juerg,
I would for sure want to be able to build the code in my select. I might want a text, input button, or image to surface the link so if I do all the work in the select than I have maximum flexibility.
Having a couple of other option as you describe would also be nice.
Matteo
Matteo 7/23/2015 19:53
Hello Amrein,
I am using SQL Viewer ver 2.0.48
I got an error today when using the add record feature.
The error i got is:
"DataBind Error: There is no row at position 0."
After a little bit of experimenting i noticed that when the table i am adding records to has no records, that is when i get the error above.
If i manually add a record to the table first, so that it has at least one record, and then use the sql viewer gadget to add the next record, it works fine.
Can you take a look.
Matteo
Matteo 7/23/2015 21:28
Hello Amrein,
I am using the SQL Viewer ver 2.0.53 (yeah i uploaded the latest, well on my test machine)
I found 2 issues when working with the SQLEdit features
Issue # 1 SQ LEdit feature /UpdateEmpty not working ==================================================== I have a column called "icComment".
In the "Form Fields" parm i want to provide a friendly label, plus a help text and the /UpdateEmpty feature for the column.
so for this column i have a vale of;
"icConvictionComment|Comment:Please enter a comment (optional)/UpdateEmpty,"
The friendly label and the help text operate as expected however the /UpdateEmpty feature does not work
If i remove the help text like so
"icConvictionComment|Comment/UpdateEmpty,"
The /UpdateEmpty works ok.
So it looks like the /UpdateEmpty feature does not get along with the ":" help text feature
Issue # 2 Column with "Date" data type note respecting the * required column setting. =======================================================================================
I have a column on a table defined like
"icDate date not NULL,"
In the "Form Fields" parm i want to provide a friendly label, plus a help text and make it required.
So for this column i have a value;
"*icDate|Date: Date of incident (required*),"
When the add dialog box comes up, it has a nice red "*" asterisk to indicate that it is required, however when i hit the update button it saves the record with no value in the column and since the column in the database is defined as "not null" the add dies.
I have other required columns of varchar data type and these work ok.
Can you please take a look. Matteo
Richard Gladwin 7/24/2015 15:42
Hi Juerg
First of all - brilliant little widget! I'm using v2.0.51 but have a few queries that I'm stuck on that I'm hoping you can help me with.
Firstly I have a column which my SQL query returns a 2 decimal placed number. Using the column formatting {C} returns the value with a preceding $ for the currency. Can you let me know how I can change this to show other currencies e.g. £ or €?
Secondly, in my SQL query, I have changed the format of a column to create a new column from the query to show the results in £. However, when I try to total this column within the tool, I get an error "Column Total Error for [columnname]; Input string was not in a correct format". Any idea how I can get round this?
Finally is there any way of changing the font in the text of the columns themselves as opposed to just the headers?
Many thanks in advance Regards Richard
Richard Gladwin 7/24/2015 17:26
Hi there again.
I'm struggling to get results with the {url} function in a SQL query. The URL I have is http://intranetdev.deltarail.com/1439/Pages/default.aspx yet when trying to extract 1439 from the URL using WHERE [NAV UAT 4 MAR 15$Opportunity Entry].[Sharepoint Site No_] = SUBSTRING('{url}', 33, 4) I get no records found. Replacing the SUBSTRING argument with 1439 then the expected results are shown.
Any advice on where I'm going wrong with this?
Many thanks in advance
Richard Gladwin 7/24/2015 17:43
Hi Jeurg
Please ignore my {urL} query as it is now resolved. Many thanks.
Juerg 7/24/2015 18:51
Richard,
to format a column using a currency different from the one corresponding to your site’s regional settings, please use a “Column Formatting” as shown in the below example:
SomeColumn:{£0.00}
When you create a new column used to show the values in a different currency, please make sure to create numeric values (as opposed to string values) and then format the column as shown in the above example.
To globally change the font for all table cells, please add the below to the web part’s “Options” setting as shown in the below example:
css=.AESVgrid TR TD {font:12pt Segoe UI}
Matteo 7/27/2015 20:03
Hello Amrein,
I am using the SQL Viewer ver 2.0.53.
I have used the {f: syntax, which injects the current filter values into your SQL stored procedure parameters, for a while now and they are working great.
I find i am using the filter values more as input parms to do some specific processing rather then the regular default use of the value which is to filter the original data set.
In an application i am building using the SQL Viewer Web part i came up with a new requirement for the search filters today. The user will be entering a Password value into one of the filters.
While i can of course do this, it would be nice if the "Search Filters" parm allowed me to say that this filter should be of type "Password" so as the user enters the value all they see is a string of asterisks (e.g., "****") rather that the actual keyed value. This way it operates very much like a password field on an login screen.
I was thinking that the Search filter already has the following prefixes "@" for a combo box, "!" for exact match, how about "*" for Password Type
Submitted for your consideration.
Matteo
Matteo 7/29/2015 14:28
Hello Amrein,
I am using the SQL Viewer ver 2.0.53.
I noticed today that the where the filters and the filter/reset buttons display on the page there is a very faint light gray background.
The light gray does not always work. Some times i would like a white background color, no color, or some other color to match up with other elements on my page.
Is there a way to set the color or turn off the color of this background?
Please advise Matteo
Juerg 7/29/2015 16:05
Matteo, to override the default silver filter area background color, please add the below CSS to the web part’s „Options“ setting to define your filter area background color as shown in the below example:
css=.AESVFilter {background-color:red !important}
Matteo 7/29/2015 16:10
Hello Juerg
The css option worked very nicely.
Many Thanks
You guys are the best!
Matteo
Russell Scheinberg 7/30/2015 15:46
Is there any way to apply an internal wildcard to a search filter. For example if I have a name field where a name might be listed as both Christopher Smith and Chris Smith and I want both instances, I would like to search for Chris%Smith. Possible? Thanks.
Matteo 7/30/2015 19:00
Hello Amrein,
I am using the SQL Viewer ver 2.0.53.
I noticed an issue today with the optional parameter "combosort=City/desc" that is used to have the search filter combo box list appear in descending order for that column.
The option does work in that all of the unique values in the column are listed in descending order.
However the "empty" value in the list, that i assume is added as part of the code for the combo box feature, is listed at the bottom of the combo box list rather than the top.
This then has the effect of having the top most item on the list being set as a filter value when the page first opens.
So it looks like the "empty" value still needs to be at the top of the Combo Box list even if the "combosort=City/desc" feature is used so that all items are displayed on the initial viewing of the page.
Please advise
Matteo
Juerg 7/30/2015 19:43
Russell, you can configure this type of wildcard search by injectiong the search filter (as entered by the user) into your SQL SELECT statement (as entered into the web part 's "SQL Query" setting).
Example: SELECT Name,City,Phone FROM Employees WHERE Name LIKE '%{f:Name}%'
You will then also need to enter a search box named "Name" into the "Search Filters" setting:
Name
The user can now enter the below search term to find occurences of both Christopher Smith and Chris Smith by entering
Chris%Smith
(eg. using the "%" wildcard character).
Russell Scheinberg 8/3/2015 17:16
Thanks. Works perfectly.
Another question. Is it possible to have both the sum and average displayed for the same column? And if I choose avg, does it average for all records returned or just those on the current page?
Thanks again.
Juerg 8/4/2015 12:30
Russell, it is currently not possible to both display the column sum and the column average simultaneously. Would this be an important option for you ?
The average is actually calculated for all records (as opposed to just the records displayed on the current page in case paging is enabled).
Russell Scheinberg 8/4/2015 17:39
I think our users would like the ability to have both displayed, but it's not a deal breaker. Maybe you could just put that on your list to look at next time your updating the web part.
I did find that the average was displaying based on a per page calculation but using the /avg/all seems to grab the average of everything. Thanks again.
Juerg 8/11/2015 19:43
Matteo, we have now implemented the „Password“ search filter as suggested in your mail (eg. prefix a text filter with the “*” asterisk character to mask the user input). We also fixed the issue with a combo search box filter displaying the “empty” entry at the bottom when sorted descending. Please download the updated version , extract the WSP file and then update the web part solution.
Matteo 8/25/2015 15:33
Hello Juerg,
I am using the SQL Viewer ver 2.0.55
I was testing the recent changes and here are my results.
COMBO BOX DESCENDING ============================================== The optional parameter "combosort=City/desc" is now working great with the empty entry at the top.
PASSWORD SEARCH FILTER OPTION ======================================================== The "Password" search filter "*" is only partially working.
When prefacing the filter column with an "*" you do get a string of asterisks when typing an entry into the search filter and this is great.
However, if you use the column for replacing placeholders in the SQL query (e.g. {f:*PasswordColum} ) the value of the filter is not delivered to the query/stored procedure parm)
EXAMPLE #1 Sql Query: icLoginPasswordPage;pUID={uid};pURL={url};pPassword={f:icPassword} Search Filter(s): icPassword
This combination displays the filter value while typing and delivers the filter value to the sql query.
EXAMPLE #2 Sql Query: icLoginPasswordPage;pUID={uid};pURL={url};pPassword={f:*icPassword} Search Filter(s): *icPassword
This combination has asterisks when typing a search filter, but DOES NOT deliver the filter value to the sql query
EXAMPLE #3 Sql Query: icLoginPasswordPage;pUID={uid};pURL={url};pPassword={f:icPassword} Search Filter(s): *icPassword
This combination has asterisks when typing a search filter and funny enough, DOES deliver the filter value to the sql query
Should not the "{f:" syntax be the same as when using the "@" & "!" prefixes?
Can you take a look.
Please advise.
Matteo
Quentin 8/25/2015 17:35
I am setting up SQL View/edit form and I am getting the following error when I try to save the record. The SQL Viewer is working correctly, my ID column is named as Edit and is a primer key. The edit form pops up when I click the Edit link from the view form. The SQL Edit Dialog is set with my data table name and Unique ID Column (same ID as the query from the view settings)
Error: "SQL Update Error: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."
Matteo 8/26/2015 19:41
Hello Juerg,
I am using the SQL Viewer ver 2.0.55 I came across an issue today.
As you know when displaying a column's content it is treated as an HTML blob. So if you have html tags such as <b>, <br>, <script> etc... they get rendered properly and you see the end result of the render HTML.
However what i need to see is the actual html source code, so i do not want the html rendered.
To do this i swap out the "<", ">","&" characters with their equivalent html release codes of "<", ">" and "&" as i build the result set in my code.
This worked great when i was just displaying the column all by itself, rather than rendering the html it displayed the html source swapping the release codes with the proper character.
However i ran into a problem when i tried to do some column formatting, for example "Col1: {Col1} <br> {Col2}"
The "<", ">" and "&" codes did not display their equivalent "<", ">","&" character, but rather just displayed the "<", ">" and "&" release code.
So the value of a column is displaying differently when it displays in it's own column than when it is included in a column formatting string.
Can you take a look?
Matteo
P.S. this is similar to my 2/7/2015 03:23 posting which has subsequently been fixed.
Juerg 8/26/2015 21:53
Matteo, we have now added the new „/raw“ option (to be appended to the „Column Formatting” setting) which allows you to display the HTML tags (as opposed to the corresponding formatted HTML . Please note that in this case you must not escape the “<” and “>” characters to “<” and “>”
Example: Column1:{Column1}<br>{Column2}/raw
Please re-download the SQL Query Viewer Web Part and then update the web part solution.
Juerg 8/27/2015 20:00
Quentin, please check the web part’s „Use explicit Update“ setting to see if that fixes the issue.
Matteo 9/2/2015 15:40
Hello Amrien,
I am using the SQL Viewer ver 2.0.55
I came across a few funny issues today, that look like bugs to me.
ISSUE# 1 - Executing the Select or Store Procedure more than once. ================================================================== I noticed that when clicking the "Search Filter Button", after providing a filter value, that my stored procedure was being run a second and subsequent time.
I proved this by have the value of "getdate()" as one of the column values in my sp and noticed that this value kept changing as i clicked the "Search Filter Button".
I am not using the "{f:" syntax on any of my stored procedure parms and in this case i though that the select or Stored procedure is only being run once, with the filter values being applied to the result set held in memory from the first and only (i thought) time the select or stored procedure is run.
So, is the Select or Stored procedure run only once or multiple times in this case?
ISSUE# 2 - "Show all entries" option not working when select or Stored procedure parm use the "{f:" syntax. =============================================================== I noticed that when i unchecked the "Show all entries" option, my result set was still being displayed when i opened the page for the first time.
After a bit of testing i noticed that it only did this when i used the "{f:" syntax on one of the parms for the stored procedure.
Can you take a look.
Regards
Matteo
Quentin 9/3/2015 18:08
The "Use explicit Update" almost works. I have 4 fields on the edit form and not all the fields will update. The Comment and UserLogin are updating in the db but the CompletedDateTime is not?
Form Fields: ClientRequirementsHistoryID,!DueDate,CompletedDateTime,Comment,!UserLogin={modifiedby} SQL Code Profiled: exec sp_executesql N'UPDATE dbo.ClientRequirementsHistory SET Comment=@col2,UserLogin=@col4 WHERE ClientRequirementsHistoryID=270',N'@col2 nvarchar(8),@col4 nvarchar(8)',@col2=N'A test 2',@col4=N'QFloor'
Quentin 9/3/2015 18:20
We are using version 2.0.56
Juerg 9/10/2015 13:31
Matteo,
Issue #1: Assuming that you have put a non-zero value into the web part’s “Cache Retention Time” setting to enable caching, the web part re-executes the query only if the SELECT statement or the Stored Procedure parameters(s) change, eg. when using the “{f:” syntax and the user enters a different value (eg. the web part checks if the query has been executed before or not).
Issue #2: We have now fixed this issue when using the “{f:” filter syntax, but no filter value has yet been entered by the user.
Please re-download the SQL Query Viewer Web Part and then update the web part solution.
Matteo 9/10/2015 15:14
Hello Juerg
Using Version 2_0_57
Thanks for the fix.
Issue#1 ============================ I have never used the "Cache Retention Time" parameter before.
I made the assumption that if there is no "f{:" syntax used, that the query is never rerun when clicking the search filter button.
However; from your response my assumption is incorrect.
Would the following be true:
If the "Cache Retention Time" is zero OR the "f{:" syntax is used, then the select/stored procedure is always re-run when clicking the search filter button,
Otherwise
The select/stored procedure is re-run, when the search filter button is clicked, only after the number of minutes defined in the "Cache Retention Time" parm has passed.
Issue#2 ================== I tested the fix and this is now working as expected.
Many Thanks
Matteo
Russell 10/6/2015 18:45
We've recently discovered an odd, but interesting behavior that has cropped up on one of our most heavily used pages. It seems that when the user is on the page for a long time without entering any data (adding or editing) an error stating "No Connection string Specified!" pops up when an addition is attempted. Closing the browser and reloading the page seems to fix this. Everything works fine as long as there is not an extended time without entering data. Sounds like a time out issue on the page but... Any ideas of how to fix this would be appreciated. Thanks.
Roberto 10/6/2015 20:11
Hi Juerg!
On Lookup columns, can i pass a url parameter? I tried, but this don't worked, if you know a way, please help me, thanks...
Regards!
Russell Scheinberg 10/23/2015 23:46
Juerg,
Is there a way to center the checkbox image that displays when you return a Boolean value? I've tried using the <center></center> tag but that just makes them invisible. Thanks.
Juerg 10/24/2015 16:10
Russell, we have now changed the web part to center the boolean (checkbox) columns by default. Please also add the below to the web part’s “Options” setting to render a nicer checkbox:
boolean=checkbox
Please re-download the SQL Query Viewer Web Part and then update the web part solution.
Nicola 10/28/2015 14:21
Hi Juerg, I have the same problem as mark 30/9/2013 18:43 but I can't solve it. Opening the SQL edit I have the error: Exception Details: System.IO.FileNotFoundException: <nativehr>0x80070002</nativehr><nativestack></nativestack>Nessun Web con nome "/pages/_layouts/AESQLWebpart/propTextEditor.aspx".
The file propTextEditor.aspx is present in c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS\AESQLWebpart
Any idea? Thanks
Juerg 10/28/2015 19:07
Nicola, to fix this issue (which only seems to occur in rare cases) please download the special version from our web site at:
extract the WSP file and the update the web part solution.
Richard 11/12/2015 00:06
Hello Juerg, I am getting the SQL Execute Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) error. Remote connections is enabled and Named Pipes is enabled. SQL Server 2008R2. I can connect and query the database using SQL Server Management Studio locally with my Windows Authentication. What else should I check? Thank you.
Juerg 11/13/2015 16:45
Richard, the error indicates that the SQL Server as specified in your connection string was not found. Can you double check your connection string (as entered into the web part's "DB Connection String" setting. Most probably the "Data Source" seems to be wrong (or mistyped).
Tom 11/16/2015 23:41
Juerg - I noticed that using column width : <> produces a <TH> tag with the align property set as 'center". I was not seeing the column labels centered, I did some investigation, found that this seems to not so much work anymore in HTML 5 (align discontinued according to this : http://stackoverflow.com/questions/13992096/align-text-in-a-table-header ) I ended up putting a little javascript on the page iterate through any <TH> tags and set the style.textAlign value to whatever the align property was set at, that did the trick. Maybe in a future release you could add an inline style to the <TH> tag to set the text-align property, just a suggestion. Your webpart is great, we use it all the time, thanks for the great work !
Juerg 11/17/2015 18:19
Tom, you actually can center speific columns via the web part's "Column Formatting" setting as shown in the below example:
Please re-download the SQL Query Viewer Web Part and then update the web part solution.
Emma 12/17/2015 06:59
I'm running v2.0.46 and have an issue with the "Clear Filter" button. Clicking the button will clear the filter drop down, however I would expect that all results from the query would be shown. I can only choose specific filtered views, rather than all. What is the intended functionality of the "Clear Filter" button?
Juerg 12/17/2015 13:55
Emma, if you enabled the web part's "Show all entries" setting to display all records if no filtering is applied, then clicking on the "Clear Filter" button resets the filter(s) and also displays all records.
Sebastiano 2/3/2016 17:32
Hi. I'm try SQL Viewer Enterprise. Fantastic webpart! I would like to report a loittle issue regarding SQL Edit popup: when edit a field via picklist and the database field type is char the picklist definition char length must match the field char length otherwise the saved db value cannot map the correct picklist element. Actualy i bypassed this issue declaring db field type varchar insted char. I'm using SQL Viewver 2.0.62
Juerg 2/5/2016 16:16
Sebastiano, thanks for sharing this information! We’ll check if we can take care of this issue in the web part’s next relase.
Matteo 2/23/2016 22:24
Hello Amrein
I ran into a problem today using the SQL Edit Features of the SQL Viewer web part.
I am using version 2.0.44 of the SQL Viewer Web Part
I am using the “Edit:detailpopup|Edit” syntax on my Edit column which of course contains the id of the record I want to edit.
Also I am connecting to the data base using a SQL Account. Here is the connection string
When I am on a page where I have authenticated myself (i.e., a page on the intranet) and when I click the “Edit…” link things work exactly as expected. All of my SQLEdit Settings work just as expected.
However;
When I am on a page as an anonymous user (i.e. not authenticated, on a public facing page) when I click the “Edit…” I get a request for UID and Password.
Now while I am an anonymous user visiting the page I can tell the web part is connecting to the data source OK because I see the rows of data being returned on the page, but when I click the “Edit..” link as an anonymous user it asked for ta uid/password every time.
Why is it delivering the data set back to the page but won’t let me do an Edit even though the data connection info has all the permission needed to do the work?
Obviously it should not be asking for this uid/password.
Can you take a look? Matteo
Peter 2/24/2016 09:45
When exporting data from the webpart, any field with a comma in the data, has the comma stripped from it.
eg. name='JONES, Bill' ends up as 'JONES Bill' in the exported CSV file when opened in Excel or Notepad (using ',' as the separator). I've tried using a ';' as the separator but that fails to open correctly in excel.
Any suggestions?
Juerg 2/24/2016 19:46
Peter, we have now fixed this issue (eg. commas contained in the data are now properly escaped).
Please re-download the SQL Query Viewer Web Part and then update the web part solution.
Juerg 2/26/2016 16:41
Matteo, we noticed that anonymous users are indeed not by default able to access pages stored in the LAYOUTS folder on the Sharepoint Server (as is the case when opening the SQL Edit form as a popup). We thus have created a replacement for the AESQLEdit.aspx file which now allows for anonymous access.
Matteo 3/1/2016 14:18
Hello Amrein
I am using version version 2.0.44 of the SQLEdit Web part.
I am using the “Column Totals” option for the first time. It worked as expected. However I had paging turned on so it only totalled the records on that page and I wanted it to total all of the records in the data set. So I used the “/all” feature that I noticed from Juerg’s 2015/07/20 post.
With the /all option I got a total row, however there was no total value displayed for the column I was doing my totals on.
I used the “Columns Totals” value of “vCnt/all”, where the column I was totalling was called “vCnt”. Can you take a look.
Also is it possible to have the “Totals” line appear as the first row rather that than the last row? Actually the ability to optionally display it as first and/or last would also be cool.
Matteo
Juerg 3/2/2016 20:55
Matteo, we actually added the „/all“ switch with version 2.0.53 in July 2015, so you will need to update the web part to the current version (2.0.64).
Marcel 3/9/2016 09:01
Dear support, i installed the evaluation version of AESQL to Foundation 2013 (PowerShell as admin Add, Install then Activate in Site collection features). I can place the webpart to a page, but still get an error message 'SQL Execute Error: Login failed for user 'TRK_Reader''. The user has acces to a DB, i tried the same on identical SharePoint server to test, there it works with no problems with same user. Do you have any suggestion what could cause this? Password for SQL user checked several times, everything seems to be ok... Thank you!
Juerg 3/9/2016 14:29
Marcel, are you using SQL Server authentication or Windows authentication (as specified via the "DB connection String" web part setting) ?
Marcel 3/9/2016 17:18
Hi Juerg, i'm using SQL server authentication. I already uninstalled the webpart and installed it new, still the same problem. I also tried to connect directly to the DB via *.udl file - this works. So i guess there must be something wrong with SharePoint in some way ...?
Juerg 3/9/2016 18:55
Marcel, are you using a connection string of the below form:
Yes, that is exactly the connection string i'm using. I'm used this one also on another Foundation server in my domain, it works with no problem...
Juerg 3/10/2016 20:20
Marcel, when saying "on another Foundation server", do you mean that the web part could connect without problem ?
Marcel 3/11/2016 09:47
Yes, i got several Foundation 2013 servers in the same domain. They are almost identically installed, there is no special settings applied on any of them. I tried to install your SQL viewer to other servers, works well. As i said, other NON-SharePoint apps can connect to the database without problem. I don't think this has explicit to do with your webpart, i guess the problem is somewhere in SharePoint itself. I will keep googling, till now i didn't find anything relevant :( I hoped you had similar issue before... Now i really want to solve it because i cannot accept something is not working without any reason :)
Tom 3/12/2016 18:49
Is it possible to use the SQl Edit web part to update / insert a record with a stored procedure (instead of a straight table) ? Our SQl Viewer is bringing in data with a function from two separate but related tables. It would be nice to edit a record in the results and be able to specify which table (along with the unique id) to update. The unique id we are bringing in with the SQL Viewer is now a table name combined with a record Id (formatted as a varchar).
Anna 3/17/2016 19:18
Hello, Quick question, is there a way to make the Look Up columns in the SQL Edit dialog not case sensitive. We have a list and when we edit an item that has “test” instead of “Test” the dropdown list in the look up column is not recognizing it. Thank you!
Juerg 3/20/2016 15:13
Anna, this would be somewhat tricky to implement. Since it is a lookup column, the data should acttually be consistent since the user chooses the value from a dropdown (and thus is not able to chose a random casing). Could you thus make your field values consistent with the lookup values ?
Joseph 3/29/2016 15:09
Hello, first off, this webpart has saved my life! Thank you!
I have a query and edit webpart added to the same page. The query works great, and I can use the edit webpart to update a field. However, I have two issues: 1) - when clicking on the editable link to edit a row, no data populates on the edit webpart form. I can still enter data and submit, but it would be preferable for it to pull the current value. On this note, if I clear a value on the backend, refresh the page, and re-select the edit link for that row, the cleared-out field still has the old data. 2) If I edit a row that has data and clear out the field, I get the following error: SQL Explicit Update Error: Incorrect syntax near the keyword 'WHERE'. Any leads I can follow to figure this out?
Thanks!
Joseph 3/30/2016 15:59
Update: I found out that I needed to use the /updateempty tag to resolve the "WHERE" error. However, I would still like the fields to populate with the live data. When clicking an edit link the edit form will load but will populate blank, or with the incorrect data!
Juerg 4/3/2016 16:46
Tom, do you mean that your query merges records coming from two different DB tables, eg. you cannot simply enter then table name into the “Database Table” setting (since this value would need to dynamically determined from the selected row) ?
Marcel 4/5/2016 11:29
Hello Juerg, just an update to my previous problem with failed login. Finally after many hours of trying, setting and installing - placing the password directly to connection string worked for me. To be honest, i still don't have any idea what is the cause of the problem but it's working now so i will let it that way :)
Ernie J 4/5/2016 17:36
We have an active directory account that were using. It works just fine with Sql Server Management Studio. We have entered the information into the database string as you have prescribed. UID=TRK_Reader;Initial Catalog=SomeDatabase;Data Source=SomeServerName;
The question is do we have to use a service account from SharePoint to connect to the sql server or should an AD account work?
KJ 4/5/2016 17:36
Hi Juerg, reading through these comments it looks like grouping by anything other than the leftmost column is not possible. I have a query that relies on a SELECT DISTINCT to narrow my set to the desired results, but this DISTINCT value (leftmost column necessarily) is not something I want displayed or grouped by. Is there another way to approach this scenario or could the web part be tweaked to allow for grouping by more than the first column - like, say, the second? Ex: SELECT DISTINCT(EMPID), MYGROUP, LAST, FIRST, TITLE, ROLE
Juerg 4/6/2016 15:51
KY, please use the below „nested“ SQL statement to group by a specific column:
SELECT MYGROUP, LAST,FIRST FROM (SELECT DISTINCT EMPID,MYGROUP,LAST,FIRST,TITLE,ROLE FROM SomeTable) A ORDER BY MYGROUP
KJ 4/6/2016 16:46
Great idea. Thanks Juerg!
Juerg 4/6/2016 17:33
Ernie, if you opt to use SQL Server authentication (as suggested in your post), then you will need to specify an SQL Server account (as opposed to a Windows account).
Ernie J 4/8/2016 15:58
Update: Thanks Juerg for your quick response. This is a great web part. Once we got the database people to give us a valid Sql Server Account, it was very simple.
Quentin 6/2/2016 16:04
I have a custom report sent to my users that links directly to the SQL Edit page to edit the record but they get the error "No Connection string specified!" if the user has not open the SharePoint page with SQL Viewer first. Once they go to the page then the direct links to SQL Edit will work, even if they close the browser and click the link 5-10 minutes later. This is the link I am using /AESQLEditWebpart/AESQLEdit.aspx?key=g_06d62c91_9b46_43d9_8fb6_6c96b853ebef&AESEId=COJK4705741
Also is it possible to keep the edit page open when the Submit button (Save) is clicked and/or add a Close button?
Thank you, Quentin
Matteo 6/6/2016 17:23
Hello Juerg
I am using the SQL Viewer Webpart version 2.0.67 on Sharepoint 2010.
I am having some problems with the “Print” feature. The problem is slightly different based on the browser used
In all cases the print process either just does not work or takes so long that end users are long gone from the site by the time the print process responds.
For the test below I have done the following - Set the Show 'Print' Button parameter et on - testing using this url https://www.tcdsb.org/FORSTAFF/PublicStaffDirectory/Pages/test.aspx you should have access to this url to see for yourself - I looked upped a name to get some data displayed and expose the print option. - I then clicked the “Print” option
RESULTS 1) IE 11 version 11.0.9600.17843CO - After clicking the “Print” option a blank web page popped up. - After about 1.5 to 2 minutes you would then see some content on the previous blank page that you could then print. - However, by this time all of the visitors to the page have left and would not have seen this now updated page to print.
2) Firefox version 46.0.1 - After clicking the “Print” option a blank web page popped up. - After about 30 seconds you would then see some content on the previous blank page that you could then print. - You then get a dialogue box popping up that complains about a script - And then another popup screen asking you to select a printer and print the popped up page. - However, by this time all of the visitors to the page have left and would not have seen this now updated page to print.
3) Chrome Version 50.0.2661.102 m - After clicking the “Print” option a nice two panel popup page appears - The left hand side would be used to select a printer, set print options and printing - The right hand panel gives a preview of what will be printing, which is blank and never displays the content that needs to be printed.
I have sent an email with some screen shots
Can you please take a look.
Matteo
Juerg 6/7/2016 21:18
Matteo, we also noticed that the new Google Chrome browser indeed has a problem with the “Print” dialog (it used to work fine in older versions) and have now fixed it.
Please re-download the SQL Query Viewer Web Part and then update the web part solution.
Sean C 6/15/2016 22:27
I'm using a stored procedure and trying to pass a site key with it. I'm receiving the following error:
SQL Execute Error: Conversion failed when converting from a character string to uniqueidentifier.
Any ideas?
Thanks. Sean
Sean C 6/15/2016 23:05
I'm using a stored procedure. I don't want to display all fields/columns in the list. How do show only specific columns?
Thanks. Sean
Juerg 6/26/2016 14:53
Sean, is it correct that one of your stored procedure parameters is of type “unique identifier” (eg. GUID) ?
Can you also indicate what exactly you entered into the web part’s “SQL Query” setting ?
Juerg 6/26/2016 15:37
Quentin, linking to _layouts//AESQLEditWebpart/AESQLEdit.aspx is actually designed to enable opening the SQL Edit form in a Sharepoint Dialog box directly from the SQL Query Viewer web part (eg. the SQL Edit web part gets its paramters directly from the SQL Query Viewer web part). In your case we thus rather recommend to place the SQL Edit web part onto its own page and configure it via the web part settings. You then still can pass in the database record ID via the AESEId URL parameteer.
Orlando 7/6/2016 16:28
Is this web part available for SharePoint 2013 Online and Office365? I need to query external SQL Server database.
Juerg 7/6/2016 16:40
Orlando, unfortunately not since Sharepoint Online/Office365 does not allow web parts to connect to databases (this is a technical restriction imposed by Sharepoint Online).
Sean C 7/8/2016 00:04
Is there a way to specify columns from a stored procedure without having to hide the ones not being used? We have stored procedures that are used in multiple places but are pulling specific fields. It would be easier for use to select the columns to show rather than to hide the ones we don't want to show.
Thanks!
Sean
Juerg 7/19/2016 20:01
Sean, we have now added the new „showcolumns“ option (to be added to the web part’s „Options“ setting) to allow you to only display the columns specified in this option as illustrated below:
showcolumns=Lastname;City;Country
Please re-download the SQL Query Viewer Web Part and then update the web part solution.
Tom Woehr 7/21/2016 18:27
Juerg -- Going way back to 4/3/2016, and your response to me...
1) That's correct. Data is coming in with a UNION from a couple of different tables. As well, some of the data is formatted for display (not the raw data) from joined code tables. The data getting saved / updated would be codes, but the data being displayed is readable, translated by the query from the raw codes.
Although it would be VERY NICE to specify a stored procedure in the SQL Edit web part, I've found a strange work-around with your SQL Viewer, here's what I can do now...
1) A SQL Viewer to pull in the data 2) That data has a column created with SQL with an EDIT button that executes JavaScript. The JavaScript function has as a parameter a unique value / record id for that row. 3) An HTML Form web part on the page, initially hidden with CSS. 4) When the EDIT button is clicked -- Javascript un-hides the Form web part and populates form fields with the data in the row (doing this with getElementsByTagName, detecting the unique id, parsing the data in each TD into form fields) 5) The user is then free to edit 6) The Form web part has some other buttons -- UPDATE /. INSERT / DELETE
here's the key
7) There's a SECOND AE SQL Viewer web part on the page to execute a stored procedure to UPDATE / INSERT /DELETE appropriately. It is "connected" to the HTML Form web part, so those named form fields can be parameters to the stored procedure {1}, {2}, etc.
The stored procedure runs every time the page is loaded, but if the form is empty, no processing is done (by design, int he stored procedure). If the form has data (including some flag for the operation -- update / insert / delete), the stored procedure can handle things accordingly.
Of course, the HTMl Form fields need to be cleared after a refresh to make sure duplicate operations are not performed.
That's my strange work-around, but thanks to the SQL Viewer being able to accept a stored procedure (thank you !) it works. The nice thing is that the HTML Form can be formatted with CSS any way I want, as well as putting custom JavaScript in it to handle events. I can also decide who sees the EDIT button because it's created in the SQL query. The stored procedure to Update / insert / delete can return an appropriate message, which I can display, as well as clearing the form data and hiding the HTML form (with CSS / javascript).
If there's an easier way with the SQL Edit web part to execute a stored procedure though, I'm all ears !
2) Also, Is there a limit to the number of fields i can connect to an AESQL web part containing a stored procedure with a Form web part ? I currently have a form with 26 fields passed as paramters to a stored proc thro AESQL, and finding strange vaues sometimes passed to the SP.
Thanks again for your support, we use these web parts all the time !
-Tom Woehr
Tom Woehr 7/22/2016 17:57
Juerg --
Never ind about the number of fields issue (#2 above), it was just a malformed SELECT tag hiding some adjacent fields in my connected FORM webpart.
-Tom Woehr
Peter 8/9/2016 11:13
Is it possible to assign a dynamic value as the default value for a filter field? For example, a dropdown filter "@Year" is populated with all the possible values from that field, but rather than specify a static value as the default (eg @Year=2016) I would like to specify a dynamic value calculated from the data (eg @Year=max(Year)).
On a related note, is it possible to pass the default value as a filter from another web part (eg @Year={2}) - I haven't tried it, thought just came to me as I was writing this...
Thanks, Peter
Juerg 8/12/2016 16:30
Peter, we have now added the new „combosort=Columnname/desc/top” option (to be added to the “Options” setting) which allows you to sort a search dropdown box in descending order and also make sure to place it at the top (eg. prevent to have the empty choice at the top).
Example: combosort=Year/desc/top
Search boxes currently cannot be preset via filter connections. Would this be important for you ?
Please re-download the SQL Query Viewer Web Part and then update the web part solution.
Sarv 8/16/2016 18:17
Hello Juerg I tested this nice & cool web part. everything works carefully except: 1- the column sorting and the search filters. When I check the box for the sorting, it does nothing. Same with the search filter. please help me to config the web part for allowing search & filter. 2- when I use the edit web part for updating an item, the sql view web part does not show the updated item. regards, Sarv
Juerg 8/16/2016 18:36
Sarv, to enable interactive column sorting (by clicking on the column header) please enable the web part’s “Allow Column Sorting” setting.
Since the web part does not support column filtering via the column headers, please use the web part’s “Search Filters” setting to define as many interactive search filters as needed.
Russell Scheinberg 9/21/2016 00:30
Greetings
Still one of the best web parts available for purchase. I ran into an issue with the SQL edit dialog boxes. I only have a few fields that will be edited in some of my web parts so the edit dialog window is fairly small. But there are date pickers. When one of the date pickers is selected the calendar more than fills up the dialog box so that you can only see part of calendar and have to scroll around to get to the dates. Is there a way to control the size of the SQL edit dialog box to make it bigger?
Thanks.
Juerg 9/21/2016 17:22
Russell, we have now added the new „popupwidth“ and „popupheight“ options (to be added to the web part’s “Options” setting) to set a fixed width and/or height in pixels as illustrated below:
popupwidth=500|popupheight=400
Please re-download the updated SQL Query Viewer web part Zip file, extract the WSP file and then update the web part solution.
Ted 10/31/2016 15:14
Excellent web part, thank you. We would like to pull a logo in the query. The field type is varbinary(max). Is there anyway to do this? Thank You.
Juerg 11/1/2016 10:59
Ted, the SQL Query web part is currently not able to interpret and then display blob data (as eg. an image stored in a varbinary (max) column as a picture (eg. this option would need to be added a s a custom feature).
Ken 11/22/2016 21:11
Are connections to SQL Azure databases supported?
Thanks!
Juerg 11/23/2016 12:30
Ken, yes, please check the below document on how to get the proper ADO.NET connection string (to be entered into the web part’s “DB Connection String” setting).
I noticed today that the "Export to CSV" link and "Record Count" do not display if you uncheck "Show all entries".
Is it possible to have the link displayed when "Show all entries" is not checked.
Alternatively maybe you can create a "Header Area Text" token similar to the [addnewitem] token for adding new records.
Something like [exporttocsv] as a token to put an "Export To CSV" link in the header.
FYI, what i am trying to do, using the SQL Viewer Webpart, is to place on a page just an "Export" link without any other feature of the web part showing. If there is a current combination of parameters that will achieve this please let me know.
Matteo
Juerg 2/1/2017 17:28
Matteo, to hide the web part (eg. just show the number of records and the “Export” button) please add the below to the web part’s “Options” setting:
css=.AESVgrid {display:none}
Saiprasad 3/3/2017 19:14
Hello Amrein
We are using the SQL Viewer webpart.
We are connecting to a SQL DB, this webpart working fine if logged in user has access to DB.
App-pool account has sufficient permission to the DB, is it possible instead of sending logged in users identity, validate against app-pool account for this webpart.
Regards Saiprasad T
Juerg 3/3/2017 19:52
Saiprasad, the web part has the option to connect to a data source using the application poll account when using an OLE-DB provider connection string.
You can enable this option by adding the below to the web part’s “Options” setting:
elevated=1
Please note that you need to have version 2.1.3 (or higher) installed.
Saiprasad 3/3/2017 22:13
Hi Juerg,
Thanks for the response.
We are using 2.1.6 version.
As suggested I placed “elevated=1” in options settings.
Now I am getting below error
“SQL Execute Error: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'”
Example connection string: Initial Catalog=Northwind;Data Source=TestSQL;Integrated Security=SSPI;
Juerg 3/4/2017 12:49
Saiprasad, please note that you need to specify an OLE-DB connection string in this case as for example:
Old connection String: Initial Catalog=test;Data Source=test;Integrated Security=SSPI; As suggested updated to: Provider=sqloledb;Initial Catalog=test;Data Source=test;Integrated Security=SSPI;
After saving webpart properties, still getting same error. SQL Execute Error: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Also one observation, I rechecked the webpart properties, it wiped the “ Provider=sqloledb” from connection string.
Anything else I need to check.
Juerg 3/8/2017 12:21
Saiprasad, we have now added the option to execute the query using the Sharepoint application pool account (eg. the “System Account”) also for SQL Server connection strings (as opposed to only OLE-DB connection strings). You thus can keep using your regular connection string in combination with the “elevated=1” web part option.
Please re-download the SQL Query Viewer Web Part and then update the web part solution.
Russell Scheinberg 3/30/2017 15:52
Juerg
Still one of the best tools we have. Continued thanks from us. I have a question about the SQL Edit Dialog. Is it possible to use the value of a field to display in the header text of the popup? The record has a name and a registration booliean value. I only want the user to be able to change the registration checkbox but just display the associated name with it somehow.
Or do I need to use the full SQL Edit webpart?
This may have been asked before but I haven't been able to find it in the comments.
Thanks.
Juerg 3/31/2017 13:08
Russell, you can refer to field values in the header of the “SQL Edit” web part by embedding the column names (put in curly braces) into the web part’s “Header Area Text” setting.
Example: You are editing Project Number {ProjectID}
Kent 7/14/2017 21:15
Using version 2.0.63. Submitting values from an HTML form web part to the SQL query viewer part to create a search experience. Keywords with an apostrophe seem to create a non-escaped problem. Example: keyword: O'Brien. Error: SQL Execute Error: Incorrect syntax near 'brien'. Unclosed quotation mark after the character string '
Juerg 7/17/2017 16:49
Kent, is your HTML form transmitting multiple fields to the SQL Viewer web part ? Also, are you using the “Get Row From” or the “Get Field Value From” web part connection type ?
Jacob 12/12/2017 17:11
Is is possible to add an <br><hr> in to the edit popup to separate/ Group the fields?
Juerg 12/12/2017 18:49
Jacob, please note that you can add tabs to the top of the form by injecting tokens into the web part’s “Form Fields” setting as illustrated below:
Name,Office,City,DateOfBirth,Salary
You now also can inject horizontal lines by prefixing the field names in the “Form Fields” setting with a minus sigh as eg.
Name,Office,City,-DateOfBirth,Salary
In this case please re-download the SQL Edit web part, extract the WSP file and then update the web part solution.
Jackie 12/13/2017 06:19
Looking to apply different background colors to specific column headings to show grouping of like components. Is this somehow possible?
Juerg 12/13/2017 11:17
Jackie, you can use the below option to add a colored “section” header:
The above header sections can be created via the web part’s “Header CSS Style” setting as illustrated below:
eg. by appending the section header definition at the end, prefixed by a “|” character using the below syntax:
section text:number of columns to span/background color.
Jacob 1/29/2018 20:20
Using SQL edit with a SQL field of type money when editing an existing field that is in the thousands the form shows 1,000.00 that looks nice except when you go to save the changes the coma causes the form to error out saying its not numeric. You have to remove the coma before it will save. Is then a fix for this?
Juerg 1/31/2018 18:08
Jacob, we have now fixed this issue. Please re-download the SQL Edit web part, extract the WSP file and then update the web part solution.
Russell Scheinberg 4/17/2018 15:25
Still loving this fantastic tool. I have a quick question and I am sorry if this has been answered previously but I could not find it. I know you can prepopulate the edit form values but I can't find the right way to do that with a checkbox. I tried =1 and =True but neither seems to work. What is the correct syntax to do this? Thanks.
Juerg 4/17/2018 19:11
Russell, the correct preset would be:
YourBooleanField=1
If this does not work for you, can you quickly check the web part’s version number by opening the web part’s tool pane and looking it up at the top ? You need version 1.1.27 or higher (earlier versions do not support this preset).
Russell Scheinberg 4/17/2018 19:23
Our current version is 2.1.6. I have Exception=1 (Exception being the Boolean field) in the Form Fields box but it seems to be ignoring this as a default in the edit pop up.
Juerg 4/18/2018 10:48
Russell, you actually will need to look up the version number of the SQL Edit web part as opposed to the SQL Query Viewer web part). You either can check with a standalone SQL Edit web part or by temporarily adding
trace=1
to the “Options” setting in the “SQL Edit Dialog” section of the SQL Query Viewer web part. The SQL Edit dialog popup will now (among other information) display its version number.
Russell Scheinberg 4/18/2018 14:45
Now I see. We have version 1.1.7 of the SQL Edit part. Looks like we need to update. Thanks.
Russell Scheinberg 4/18/2018 18:35
I am getting the defaulted checkbox checked on a new record. What I need is to have the checkbox checked by default on an update even if the existing value is 0. Is that at all possible? Thanks.
Juerg 4/18/2018 19:17
Russell, this is not possible since the database record is intended to be edited and thus reflects the current values.
Patrick McKinney 6/27/2018 17:14
Does the SQL viewer work for SharePoint online? If not is there another app you have that could perform that type of function?
Juerg 6/27/2018 17:27
Patrick, there is no way that you can reach an on-premise SQL database server from the Office 365 cloud.
In case the SQL Server is hosted on Azure you still would need to also deploy your own web services in order to be able to query your database over the internet (as eg. from within Sharepoint Online). This means that Microsoft currently does not support any interface which would allow a web part hosted in Sharepoint Online to perform any ad hoc queries (eg. each query would need to be represented by a web service you program and then also host on Azure).
Matteo 7/6/2018 19:45
Hello Amrein
Currently using version 2.1.38 on SharePoint 2016
I have a question regarding the "Column Totals” feature and if a subtotal feature can be incorporated.
I have a list of records order by a column called "SchoolCode".
I am currently setting the "Column Totals" parm as "SchoolCode/all" which gives me a nice Grand total line.
However i would also like a subtotal after a break in the "SchoolCode" value to be displayed just below the last value before the break.
Does such a feature exists or could it be added?
Maybe add a new switch, something like "SchoolCode/all/subtotal" for the "Column Totals” parm
please advise
Matteo
Juerg 7/9/2018 13:28
Matteo, to display the subtotal for each group (assuming that the “Enable Grouping” web part setting is checked) and at least one column has been added to the “Column Totals” setting, please add the below to the web part’s “Options” setting:
grouping=1
Jessica 7/30/2018 23:16
Hello,
I have value NULL as varchar in query. Can I set this as default value when I set dropdown filter with greater than and less than operation.
I tried this, but I want to preset '' as default @[column1]<=;@[column2]>=
Thank you, Jessica
Juerg 7/31/2018 12:00
Jessica, the web part automatically adds the empty selection to the dropdown filters and presets the filters to this value (eg. if the "empty" value is selected then the filter is not being applied).
André 8/21/2018 17:22
I use the grouping feature. I'd like the subtotal without $ sign. It's just an amount but not a currency. Is that possible? Thanks!
Juerg 8/21/2018 18:15
André, we have now added the new "subtotalformat" option (to be added to the web part's "Options" setting) which allows you to override the default currency formatting of the subtotals. To format the subtotals as integers (with zero decimal places), please use the below option:
grouping=1|subtotalformat=N0
Please re-download the SQL Query Viewer web part, extract the WSP file and then update the web part solution.
Matteo 9/14/2018 17:08
Hello Amrein,
I am using the SharePoint 2016 Version 2.1.38 of the SQl Viewer Web Part
Is there an option in the export functionality in the SQL Viewer Web Part to export only certain fields and if not, could such a feature be incorporated?
Sometimes I have to format my columns with HTML or extra spaces and such so that they will be displayed ok on the web page; however, values from these columns can look somewhat funky in a search drop-down or in the exported to excel file.
In these cases, I often have to add the column a second time in a very vanilla format, with a 0 width value to suppress their display, so that the search drop-downs and the exported data to excel is usable.
In other cases I have had to create 2 versions of the data set and 2 instances of the SQL Viewer Web part, one for viewing and one for export.
I can sometimes get around this by using the "Column Formatting" feature but this does not always work
It would be a very handy feature to be able to specify which columns in the data set are to include in the export.
Submitted for your consideration.
Matteo
Juerg 9/14/2018 17:34
Matteo, we added the „exportskipcolumns“ option (to be entered into the web part’s “Options” setting) in December 2014 with version 2.0.41 to suppress specific columns from the export file.
Example: exportskipcolumns=Salary;Birthdate
matteo 9/14/2018 19:36
Juerg
Perfect, worked like a charm.
Nice exchanging messages with you again, it has been a while
I have said it before and i will say it again, YOU GUYS ARE GREAT!!
Matteo
P.S. Now if i could only get you to update this documentation page listing this and all of the other wonderful options / switches and settings that you have shared over the years regarding the use of this web part that would be GREAT GREAT
Thanks Again.
Simon Evans 10/9/2018 10:44
I developed a number of pages, several years ago, using this webpart and your SQL chart webpart for MOSS 2007. My company have now moved to Office365 SharePoint and are looking to decommission our old MOSS 2007 server and have asked me to recreate the pages in the new environment. I can't immediately see an Office365 compatible version of these 2 webparts - Would the 2016 version work? If not do you have a development timeline for Office365 compatible versions of these 2 webparts? Many Thanks
Juerg 10/9/2018 12:10
Simon, we do not have an Office 365 version of the SQL web parts since it is not possible to access local SQL Server databases from Office 365.
Please note that also Azure (in case you plan to host your SQL databases in the cloud) does not offer to remotely query a database via a SELECT statement or a stored procedure (you actually would need to provide a web service for each query).
Anna 11/1/2018 17:34
Hello,
How can you remove a date value from a datetime type field in SQL Edit webpart? The field has a date picker and it allows NULLS in the database. /updateempty does not seem to work with date fields.
Thanks! Anna
Juerg 11/5/2018 15:50
Anna, we have now fixed this issue, eg. you now can update an empty date/time field if it is flagged with the "/updatempty" format option. Please re-download the SQL Edit web part, extract the WSP file and then update the web part solution.
Stephen 3/13/2019 20:07
Is it possible to remove the <br> character between each row?
We already have HTML tags using <p> and do not need the extra breaks between rows.
Thanks
Stephen 3/13/2019 20:10
Is it possible to disable the alternate row color without having to force inline style attributes?
Also to remove the <TR> style colour? style="background-color:#F7F7F7;"
Love the product! Steve
Juerg 3/14/2019 11:30
Stephen, you can override the default „F7F7F7” alternate row color by entering the below into the web part’s “Alternating Row Color” setting:
white
Juerg 3/14/2019 17:42
Stephen, the „<br>“ is inserted into the text if the web part detects an line break character in the original text returned by the query. You can suppress this behavior by adding the below to the web part’s “Options” setting:
rawtext=1
Gordon Green 4/23/2019 13:11
Juerg, When I turn on grid lines, is there a way to set the colour of the lines?
Juerg 4/23/2019 16:52
Gordon, we have now added the new „gridcolor“ option (to be added to the web part’s „Options“ setting) as illustrated below:
gridcolor=red
Please re-download the SQL Query Viewer Web Part and then update the web part solution.
Ramesh 6/11/2019 17:52
How to configure SQL EDIT WEBPART to display edit link on table?
1. Added “SQL Query Viewer Web Part” on page and provided configuration details to display values on the page. All the data was visible. 2. Added “SQL Edit Web Part” on the same page and provided configuration details. Edit link on the page was not available.
Environment : SharePoint 2010
Versions: • SQL Query Viewer Web Part : Version 2.1.27 • SQL Edit Web Part : Version 1.1.76
Please help..
Juerg 6/11/2019 18:22
Ramesh, the most convenient way is to use it as a Sharepoint dialog popup to edit existing records and/or add new records.
After having installed the SQL Edit web part, you then can configure your SQL Viewer web part(s) to incorporate the SQL Edit web part via the SQL Query web part’s “SQL Edit Dialog” configuration section where you need to specify the database table, the form fields and the unique ID column in your database table.
You then can add an “Edit record” column to the SQL Viewer web part via the below two web part settings:
SQL Query: SELECT EmployeeID,FirstName,LastName,Email,EmployeeID AS Edit FROM Employees
Column Formatting: Edit:detailpopup
You then also can check the SQL Viewer web part’s “Show ‘Add New Record’ Button” setting. Please note that you do not need to add an “SQL Edit” web part to the page.
Gordon 7/17/2019 15:03
Is there a way to display RTF data in this web part?
Juerg 7/18/2019 13:17
Gordon, does one of your SQL columns contain RTF text ? If yes, then no, the web part is not able to interpret RTF text.
Gordon 11/19/2019 18:01
Hi Juerg, Is there a way to modify the font size used in the selector on the Pop-up Edit Dialog?
Juerg 11/19/2019 18:45
Gordon, you can modify the font size for the „Edit..“ link by using the below “Column Formatting” web part setting:
eg. by overriding the default “Edit..” link label as shown in the above example.
Saiprasad 7/1/2020 22:19
We have a requirement to display employee data present in a SQL table. SO we are planning to use this SQL viewer webpart to present the content in SharePoint. Can we display image in sql viewer webpart also can this webpart is capable to read the stored image in SQL table and render in UI.
Juerg 7/2/2020 11:01
Saiprasad, yes, the web part can display images stored in the SQL data table as blobs (eg. varbinary(MAX)). We can send you instructions on how to enable this feature.
Mel C. 9/4/2020 13:57
Hi ! Is it possible to add an option that would refresh only on demand ? like a small refresh button ? I would like the webpart not to refresh on page load, only when requested. so my perfect scenario would be : 1- load the page : webpart is empty 2- click on the refresh button : sql data is loaded 3- refresh the page : same data stays there 4- close the page and reopen the page : data is empty do you have an option for this ?
thanks !
Juerg 9/7/2020 16:16
Mel, we have now added the new "refreshbutton" option (to be added to the web part's "Options" setting:
refreshbutton=1
Please re-download the SQL Query Viewer Web Part and then update the web part solution. Please then also make sure to uncheck the "Show all entries" web part setting.
dan 9/8/2020 11:28
Iam trying to connect to a sql DB. but not able to configure the connection string and query. Can you please give me the proper format of the 2 configuration parameters Thank you so much
Juerg 9/8/2020 11:54
Dan, please refer to the detailed instructions at the top of this page:
- DB Connection String - SQL Query
You also can contact us via [email protected] if you need further assistance to successfully connect the web part to your SQL Server database.
Matteo 9/16/2020 04:20
Hello Amrien,
When using the "Export to CSV" option is there a way to NOT include the header row in the CSV?
Please advise
Matteo
Juerg 9/16/2020 12:13
Matteo, we have now added the new „exportheaderrow” option (to be added to the web part’s “Options” setting):
exportheaderrow=0
Please re-download the SQL Query Viewer Web Part and then update the web part solution.
Matteo 9/23/2020 03:17
Juerg
You guys are the best!!!
matteo
Jyoti 12/10/2020 17:08
Hi, I am getting this error from SQL Edit Web Part when there in no record in the database table. How can I add first row in my table using this web part. Also, How can I perform validation while adding a record to a table.
DataBind Error: There is no row at position 0.
Juerg 12/11/2020 15:30
Jyoti, do you get this error when you click the "Add new record" button and the underlying database table is empty or the database query returns no items ?
Jyoti 12/11/2020 16:09
Hi Jureg,
Thanks for the reply. I am getting this error on click of Add new record button. My underlying database is empty and the database query returns no items.
Juerg 12/11/2020 16:59
Jyoti, we have now fixed this issue. Please re-download the SQL Edit Web Part and then update the web part solution.
Data entry validations:
- you can designate a field to be mandatory by prefixing it with an "*" asterisk.
- numeric fields are automatically validated
- you also can validate e-mail fields by appending "/email" to the field name.
Jyoti 12/15/2020 01:38
Hi Jureg,
Thanks for your reply.
How can I use use stored procedure in SQL Edit web part? I am unable to extract values from stored procedure using name value pair in SQL edit web part.
Thanks, jyoti
Juerg 12/15/2020 13:47
Jyoti, the SQL Edit web part is used to either add new or edit existing database table records. It thus needs to know the name of the database table (to be entered into the web part's "Database Table" setting) and the table column names to be displayed on the form (to be entered into the "Form Fields" setting).
Jyoti 12/15/2020 16:24
Hi Jureg,
Thanks for your reply.
Please look at below queries. I want these two values in sql edit webpart. I am putting these queries into Lookup Columns entries.
But I am not getting venuecode which is dependent on venue.
venue=SELECT a,a FROM tableA where (b > 'val1') AND (b < 'val2');
venuecode=SELECT b,b FROM tableA where (b > 'val1') AND (b < 'val2') and a='{venue}';
Thanks Jyoti
Juerg 12/15/2020 17:02
Jyoti, to create a cascaded combo (a combo field dependent on the value of another combo field) please use the below "Lookup Columns" configuration example:
Brand=select distinct Brand,Brand FROM Cars/child=Model; Model=SELECT distinct Model,Model FROM Cars WHERE Brand='{v}'/parent=Brand
Jyoti 12/15/2020 17:36
Hi Jureg,
Thanks for your help.
I am able to create cascaded combo but unable to add new record into my underlying table.
Thanks Jyoti
Juerg 12/15/2020 17:41
Jyoti, do you get an error message ? If yes, please indicate the message.
Jyoti 12/15/2020 18:18
Not getting any error.
All field in the web part disappears and a blank web part is left after clicking update.
Thanks jyoti
Juerg 12/15/2020 18:29
Jyoti, would it be possible to do a screen sharing session to investigate the issue ? If, yes, please send an invitation via your preferred screen sharing tool to [email protected].
Ryan 3/5/2021 23:31
I have a non-null column in a table I'm trying to insert to using the SQL Edit tool, and I seem unable to get the form entry box for that field to display at all. It so happens that the field name is ORDER, so this has been notated in the Form Fields section as [Order]. Not sure if that's what's causing my issue.
Also, is there a way to use the SQL Web Query Viewer edit function in conjunction with this ti show either the 'Edit' link as it currently does, or show an 'Add' link to pre-populate the form with the relevant data from that row?
Thanks
Juerg 3/9/2021 14:27
Ryan, please note that if the "Order" field is of type "bigint" then the SQL Edit web part does not display it. You thus would need to change the field type to "int".
JK 6/15/2021 01:36
Does the SQL viewer(for sharepoint 2019) work for SharePoint online? If it does, is mysql also possible?
Juerg 6/15/2021 10:43
JK, the SQL Viewer web part unfortunately is not available for Sharepoint Online/Office 365 due to rechnical restrictions imposed by Office 365.
Giammy 6/23/2021 14:28
Hi Juerg, I'm using your webpart and it is really a good product. I'm using that with my SQL stored procedure. I've used {1} placeholder to pass the filter value to the Stored Procedure parameter. This cames from another inpute text filter webparte, connected and everything works fine. I'm right now creating an item view and my need is to use not a filter text webpart to pass the filter value to my Stored Procedure, but I wolud like to use a column value of this item view webpart to be injected to pass the filter value to the Stored Procedure parameter. I've have tried to to that but web I conncect both the weparts i cannot find FilterValue {1}, as I had with text filter web part. Can you kindly help me? thank you so much
Juerg 6/24/2021 12:41
Giammy, after having connected the two web parts via the "Get Row From" connection type, insert the name of the list column (“City” in the below example) which is used as the parameter for the Stored Procedure as shown in the below example (enclose the parameter in curly braces):
EmployeeTest;UserID=sadmin;Location={City};
Russell 7/30/2021 16:39
Hello. We started getting messages this morning that our trial license has expired but we have an enterprise license that we have had for years. As I started thinking about it we upgraded our servers in place (not SharePoint which is still 2010) last month. I am wondering if this wiped out our licenses. Do we need to get a new license number? Thanks.
Juerg 7/30/2021 17:29
Russell, your license expired after 10 years of use so we just sent you a new license key.
Dynnise Littlefield 8/27/2021 18:56
Is there a way to filter one AE SQL Viewer web part based on the value of a field in another AE SQL Viewer web part? I have tried using the Sharepoint connection, but haven't been successful. I have a sql viewer web part that displays the schedule for a selected room, and I want another SQL viewer web part to display the inventory in that room. Thanks!
Juerg 8/30/2021 13:14
Dynnise, please configure the „master“ web part as follows, where in the example it is assumed that you want to click the “Country” field to pass its value to the “slave” web part:
SQL Query: SELECT City,FirstName,Country FROM Employees WHERE Country='{parameter}'
In this setup no web part connection is needed since the value is passed to the “slave” web part via the page URL.
Dan Neuman 9/28/2021 21:31
After purchasing a server license and using this control for 10 years, we are receiving a message that our trial license has expired. I sent an email to Support last friday and haven’t received a response. How can I get this fixed?
Juerg 9/30/2021 18:22
Dan, we have now sent you a new license key by e-mail.
Sébastien 7/6/2022 11:42
Hello, I’m using the SQL Query Viewer on SharePoint 2019 Modern page and would like to turn off the Colum Header and the Nb of Records. Can you please explain how to turn them off? Thank you for your answer. Sébastien
Juerg 7/6/2022 15:44
Sébastien, we have now added the "rows=0" and the "columnheaders=0" settings to the web part. Please re-download the SQL Query Viewer Web Part and then update the web part farm solution.
Sébastien 7/7/2022 10:47
Thank your Juerg for your answer and for this update.
Regarding Search Filter, the filter box doesn’t appear on SharePoint 2019 Modern Page for with a Query VIewer. What would be the right parameters? I tested with filter, searchfilter, but none are working and appearing. THanks a gain for your answer and help. Regards, Sébastien
Juerg 7/7/2022 11:18
Sébastien, you can use the "filter=" setting to define your search filters. However, you also need to enter a license key to enable the filters. Please find below your 30 day evaluation license key:
AESV-2-1025539-25861-11-20
Sébastien 7/22/2022 11:14
Hello Juerg, Is there a possibility to include the "Column Width" for Sharepoint 2019 Modern page? Or if existing, waht would be the correct setting to define in the configuration pan? Thanks for your answer. Regards, Sébastien
Juerg 7/22/2022 15:15
Sébastien, we have now added the “columnwidths” setting for modern pages. Please re-download the SQL Query Viewer Web Part and then update the web part farm solution.
Murray 10/14/2022 22:59
Do you have a suggestion as to how I could present multiple values with a check box that would update a single field separated by a delimiter?
Juerg 10/17/2022 11:11
Murray, can you describe your request in more detail since we do not exaclty understand what you are looking for. You also can contact us by e-mail at [email protected].
Murray 10/18/2022 17:31
Hi Juerg,
Thanks for the reply.
If one had a music collection and one wanted to classify the recordings by presenting a list of possible attributes: Jazz, Pop, Up-tempo, swing, rock, solo, group, big band. Could you present this list to a user and give the ability to select more than one which would be stored in the table as Jazz;Up-Tempo;swing;group;big band or Pop;rock;solo
Cheers
Juerg 10/20/2022 17:23
Murray, you can add a multi-select checkbox field to the SQL Edit web part as illustrated by the below example:
We are attempting to update a query and although are able to edit using the Query dialog box, but when we save the page it is not keeping the change. Any ideas? We are on version 2.1.72. Thank you.
Juerg 1/20/2023 14:19
Bryan, are you using the button to the right of the “SQL Query” web part setting to open the dialog box to edit the SQL query ? Also, which browser are you using ?
Murray 4/25/2023 16:26
Hi Juerg, I have a table (not designed by me), that has two columns with a '/' in the field name. I am being asked to update the data in those fields, but I keep getting an error when I include them. How do I escape those characters in the field name (which I am prevented from changing)?
For example, my table might be a movie table with a field defined as "Producer/Director"
How do I access that field name to be able to update the data in that field?
Juerg 4/26/2023 11:57
Murray, please enclose the column in the web part's "SQL Edit/Form Fields" setting in square brackets as eg.
[Producer/Director]
Murray 4/26/2023 16:20
Hi Juerg,
I did try enclosing the column in square brackets and got the following error:
SQL Execute Error: The identifier that starts with 'Title,[Producer,Star,StarEmail FROM MovieDetail WHERE Genre='Comedy' and SubGenre='Slapstick' is too long. Maximum length is 128. Unclosed quotation mark after the character string 'Title,[Producer,Star,StarEmail FROM MovieDetail WHERE Genre='Comedy' and SubGenre='Slapstick'. Incorrect syntax near 'Title,[Producer,Star,StarEmail FROM MovieDetail WHERE Genre='Comedy' and SubGenre='Slapstick'.
The column name is entered in the "SQL Edit/Form Fields" setting as [Producer/Director]
Juerg 4/26/2023 16:23
Murray, please make sure that you do NOT enter a "[" character in front of the web part's "SQL Query" setting as eg.
Title,[Producer,Star,StarEmail FROM MovieDetail WHERE Genre='Comedy' and SubGenre='Slapstick'
Murray 4/26/2023 19:01
Hi Juerg,
I'm confused because in the previous comment the instruction was to "enclose the column in the web part's "SQL Edit/Form Fields" setting in square brackets"
When I remove the fields that have the "/" in the field name, the edit works fine. When the fields with the "/" are included, I get an error.
In your last reply, you are quoting from the error message that I received, which does not match what I'm putting in the Form Fields setting, or the SQL query part.
Juerg 4/27/2023 11:23
Murray, we were now able to loate and fix this issue. Please re-download the SQL Edit web part form this page and then update the farm solution.
You then will need to escape the "/" character in the web part's "Form Fields" with two "//" characters as eg. [Producer//Director]
Murray 5/12/2023 23:48
Hi Juerg,
I'd like to confirm that we're currently running Sharepoint 2013. May I ask if this version contained your update? (this is for escaping the "/" character issue just above.
Thanks!
Juerg 5/15/2023 12:17
Murray, yes, we also updated the SP 2013 version.
Murray 7/28/2023 22:05
Hi Juerg,
I'm wondering if there is a limit on the number of columns that can be edited with the web part. I'm working on a report now that I'm thinking is pushing the limit at around 100 columns. I'm getting this error when I add that one extra column to the report for editing:
DataBind Error: Index was outside the bounds of the array.
Juerg 7/31/2023 11:27
Murray, yes, the current limit is 100 form fields. How many fields will your report need and will you need to be able to edit all these fields via the web part ?
Murray 7/31/2023 16:39
Hi Juerg, I'm looking at 102 columns, and am working with my user to pare that down. Knowing that there's a maximum will assist in that discussion.
Thanks!
Murray 11/7/2024 18:10
Is there a way to change the title in the Edit dialog to something else?
Thanks! Murray
Juerg 11/8/2024 11:39
Murray, yes, please add the below to the web part's "Options" setting:
edittitle=your desired dialog title
Murray 11/8/2024 19:20
Thank you so much!
Next question: is there a way to set the default size of the input boxes in the built in Edit part?
I've got my popup width set to 800, but all of the boxes are displaying with much less width. I was expecting that the input boxes would adjust to the width of the popup
Juerg 11/11/2024 15:41
Murray, you actually can set the number of textbox columns via the web part's "Textbox Nbr. of Columns" setting (in the "SQL Edit Dialog" section). The default is set to "30".
Murray 11/15/2024 17:01
Hello, I have another question:
in the Form Fields parameter of the edit part, is it possible to make the help text dynamic? for example, something like this?
Column1|First Column:{value of column 2}
or Column1|First Column:FunctionCall (column 2)
Your assistance has been most helpful and much appreciated.
Thanks!
Juerg 11/18/2024 14:59
Murray, I assume that you are ediing existing database records. Would you then also expect the help text to dynamically change as soon as the users edits a form field that is referred to by the field's help text ?
Murray 11/18/2024 16:03
Hi Juerg,
In this case the data source for the help text is not being edited, so it would not need to change when the user edits the form. The help text would change from one edit instance to another edit (hopefully that makes sense).
Thank yoiu!
Juerg 11/18/2024 16:45
Murray, where exactly would the help text come from ? Would it be the value of another field in the database record currently being edited ?
Murray 11/19/2024 15:35
Juerg,
Yes, it could be the value of another field. I also thought it could be the value of a function call.
Good web part for both FLV and WMV formats. We use it for both our external as well as our Intranet website.
Vikram Srinivasan, Inovalon Inc.
This web part is available for:
SharePoint 2010
SharePoint 2013
SharePoint 2016
SharePoint 2019
SharePoint SE
SharePoint 2019 'modern'
SharePoint SE 'modern'
Office 365
Related Web Parts
The SQL Chart Web Part allows to chart data selected from the specified SQL Server database or OLE-DB data source. The charts are created using Google's public Chart API
Good web part for both FLV and WMV formats. We use it for both our external as well as our Intranet website.
Deployment Instructions for SP 2010
Post a Comment
