Friday, July 22, 2016

jQuery - Searching a multi-dimensional array

Last days ago, I had an issue to search multi-dimensional arrays in JavaScript / jQuery.
This post will give you a quick example how it works.

First we create an multi-dimensional array.
In my example we create an array for each employee...

var hansi = { firstName: "Hansi", lastName: "Hansen", wage: "2000" };
var paul = { firstName: "Paul", lastName: "Paulsen", wage: "1900" };
var otto = { firstName: "Otto", lastName: "Ottensen", wage: "1500" };
var thomas = { firstName: "Thomas", lastName: "Tomate", wage: "2500" };

...and then push them all together in another array. And here is our multi-dimensional array:

var employees = [hansi, paul, otto, thomas];

Now we want to search for an employee with the first name "Thomas", so we define a variable with the search term:

var searchValue = "Thomas";

Then we use the grep function from jQuery to search for the term "Thomas" in our multi-dimensional array:

var result = $.grep(employees, function (e) {
   return e.firstName == searchValue;
});

Now we just need to check our results variable:

if (result.length == 0) {
   alert('Error: ' + searchValue + ' not found');
} else if (result.length == 1) {
   employee = result[0];
} else {
   alert('Error: Multiple items found');
}

See the results:

Employee Details: First Name: Thomas / Last Name: Tomate / Wage: 2500

Here is the complete code again:

<!DOCTYPE html>
<head>
<script   src="https://code.jquery.com/jquery-3.1.0.min.js"   integrity="sha256-cCueBR6CsyA4/9szpPfrX3s49M9vUU5BgtiJj06wt/s="   crossorigin="anonymous"></script>
<script type="text/javascript">
$(document).ready(function() {

var hansi = { firstName: "Hansi", lastName: "Hansen", wage: "2000" };
var paul = { firstName: "Paul", lastName: "Paulsen", wage: "1900" };
var otto = { firstName: "Otto", lastName: "Ottensen", wage: "1500" };
var thomas = { firstName: "Thomas", lastName: "Tomate", wage: "2500" };

var employees = [hansi, paul, otto, thomas];

var searchValue = "Thomas";
var result = $.grep(employees, function (e) {
return e.firstName == searchValue;
});

if (result.length == 0) {
alert('Error: ' + searchValue + ' not found');
} else if (result.length == 1) {
employee = result[0];
} else {
alert('Error: Multiple items found');
}

document.write("Employee Details: ");
document.write("First Name: " + employee.firstName + " / ");
document.write("Last Name: " + employee.lastName + " / ");
document.write("Wage: " + employee.wage + " ");
});
</script>
</head>
<body>
</body>
</html>


Tuesday, July 19, 2016

SQL - Fehler - ARITHABORT

Heute mal in Deutsch, da ich den genauen Wortlaut der englischen Fehlermeldung nicht kenne. :-)

Das Thema ist mal wieder SQL.



Ich hatte vor kurzem bei einem SQL Statement folgenden Fehler:

"Fehler bei SELECT, da die folgenden SET-Optionen falsche Einstellungen aufweisen: 'ARITHABORT'. Überprüfen Sie, ob die SET-Optionen für die Verwendung mit indizierten Sichten und/oder Indizes für berechnete Spalten und/oder gefilterte Indizes und/oder Abfragebenachrichtigungen und/oder XML-Datentypmethoden und/oder Vorgänge für räumliche Indizes richtig sind."

Dieser Fehler ist in der Regel recht einfach zu beheben. Es muss lediglich der Wert für ARITHABORT auf ON festgelegt werden und schon funktioniert die Abfrage (zumindest in meinem Fall :-) ).

SET ARITHABORT ON
...

Ich hoffe, ich euch damit viel Sucherei ersparen...

Friday, July 15, 2016

SQL Query - Get multiple row results in one column with XML and STUFF()



Today I want to show you, how you can put the results of an SQL query into one column.

First I create a temporarily table for our data and fill some data in it:

CREATE TABLE #table (product nvarchar(255)) 

INSERT INTO #table
SELECT 'product-1' as product
UNION 
SELECT 'product-2' as product
UNION 
SELECT 'product-3' as product
UNION 
SELECT 'product-4' as product


Now we have our base table for our query. Here are the results of this table

Query:
select * from #table

Results:
product
product-1
product-2
product-3
product-4

Now we can see, we have 4 rows with our example products. Maybe we have a requirement to put all products in one row. This can be done by using the STUFF function and XML.
Here is the code:

select STUFF((SELECT distinct ',' + t.product
                    from #table t (nolock)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'') as allProducts

Now the result of this query is this:

allProducts
product-1,product-2,product-3,product-4


Hope you enjoyed this lesson.

Tuesday, July 5, 2016

SharePoint 2010 - New Feature in already deployed solution does not appear




Yesterday, I have created a new feature in an existing SharePoint 2010 solution.
I wrote my code and added the parts to the feature. When I finished my work, I update the solution as usual with this code:

Update-SPSolution -Identity mysolution.wsp -LiteralPath "C:\C#\_DN\MySolution\MySolution\bin\Debug\mysolution.wsp" -GACDeployment

Next, I went to the site collection features and wanted to activate it, but the feature was not appearing in the site collection feature list. What the ...!?!?

After a short Google search I found a solution for this problem.  We need to execute some commands in the PowerShell (SharePoint 2010 Management Shell):

Install-SPFeature -ScanForFeatures

This command will list all not installed features.
You will find your new feature in this list. Now you can install it with the following command:

Install-SPFeature -AllExistingFeatures

After successful execution of this command your feature will appear.

Friday, June 24, 2016

SharePoint - JavaScript - Get parameter from current URL

Today, just one short code snippet to read out an URL parameter with the SharePoint JavaScript libraries:

GetUrlKeyValue(parameter, noDecode, url) is a JavasSript function which we can use to get a query string parameter either from url in the browser or a url we specify.

parameter (string): Query string parameter from the url. 
noDecode (bool)(optional): Specifies whether the value has to be encoded or not. If false value is decoded, else returned as it is. 
url (string)(optional): the url from which Query string values are to be retrieved.(Optional)

Example:

alert(GetUrlKeyValue('a', false, 'www.abc.com?a=te%20sting'));
The above statement will return the value ‘te sting’ from the url paramter a. Here we are specifying our own url.


alert(GetUrlKeyValue('a', false));
The above statement will look for a query string variable a in the browser url, and returns the decoded value.


alert(GetUrlKeyValue('a'));
The above statement will look for a query string variable a in the browser url.

Thursday, June 23, 2016

Code Snippet - SQL - Replacing line breaks with SQL query

If you have line breaks in an SQL string field and you want to replace or remove them, you can use the following code (bold marked):

UPDATE details SET user_text = REPLACE(REPLACE(user_text, char(10), char(32)), char(13), char(32));

Friday, February 26, 2016

SQL Server - Finding values in different columns with CROSS APPLY

I've found a nice way to check values in different columns in an SQL Server table and return only the columns where the search value is in.

In my example, we have a table with several columns (No1 - No7). We want to find out which columns have the value 8 in it and only return these ones.

Check the code:

First we create a table to check.

DECLARE @t TABLE (AdrNr INT, Nr1 INT, Nr2 INT, Nr3 INT, Nr4 INT, Nr5 INT, Nr6 INT, Nr7 INT)

Then we enter some values in it:

INSERT INTO @t
VALUES (500, 3, 4, 8, 42, 5, 76, 91)

This is the SQL code to return only the columns where the value "8" can be found:

SELECT AdrNr, a, b
FROM @t
CROSS APPLY (
    VALUES
        ('Nr1', Nr1),
        ('Nr2', Nr2),
        ('Nr3', Nr3),
        ('Nr4', Nr4),
        ('Nr5', Nr5),
        ('Nr6', Nr6),
        ('Nr7', Nr7)
) t(a, b)
WHERE b = 8

Friday, January 15, 2016

IIS 7+ - Force redirect to HTTPS version of website

If you have a website with a SSL certificate, you may want to redirect all visitors to the HTTPS version of your website by default.
This you can realize with the web.config file of your root directory, if your using IIS 7 or higher.
Just add the bold code part to your web.config file.

<?xml version="1.0" encoding="UTF-8"?> 
<configuration> 
  <system.webServer> 
    <rewrite> 
     <rules> 
      <rule name="HTTP to HTTPS redirect" stopProcessing="true"> 
       <match url="(.*)" /> 
       <conditions> 
        <add input="{HTTPS}" pattern="off" ignoreCase="true" /> 
       </conditions> 
       <action type="Redirect" redirectType="Found" url="https://{HTTP_HOST}/{R:1}" /> 
      </rule> 
     </rules> 
    </rewrite> 
  </system.webServer> 
</configuration>

Monday, January 11, 2016

Tutorial - SQL Server 2014 Express - Job automation

Hi guys,

as you know, in Express Editions of Microsoft SQL Server the agent is not available. So it is difficult to create jobs, which run automated.
I've found a nice way via the command line to create automated SQL jobs without the SQL Server Agent.

First create your SQL statement and save it in an extra file. Name it i.e. "sqlCommand.sql".
Maybe you want to make a daily backup of your database, you create a statement like this, but it can be any valid SQL statement. This is just an example.

BACKUP DATABASE [db_myDatabase] TO  DISK = N'C:\Backup\SQL Server\db_myDatabase.bak' WITH NOFORMAT, INIT,  NAME = N'db_myDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'db_myDatabase' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'db_myDatabase' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''db_myDatabase'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'C:\Backup\SQL Server\db_myDatabase.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO


I you have Microsoft SQL Server 2014 Express installed, you can navigate via command line to the following directory:

"C:\Program Files\Microsoft SQL Server\110\Tools\Binn"

In this directory you find a executable named "sqlcmd.exe".


This executable you can use to execute your SQL Statement saved in your file "SqlCommand.sql".
Replace <server> with your server / machine name and <sqlInstance> with your instance of your SQL Server. The parameter -i tells the executable what SQL script should run. Replace it where your SQL script is saved.

sqlcmd -S <server>\<sqlInstance> -E -i "C:\Jobs\SQL Server\SqlCommand.sql"

When you press enter, the command(s) in your SQL script will be executed.
Now you just have  to create a simple command line script, which can be executed in a task scheduler job.

Command-line script:
c:
cd\
cd "C:\Program Files\Microsoft SQL Server\110\Tools\Binn"
sqlcmd -S <server>\<sqlInstance> -E -i "C:\Jobs\SQL Server\SqlCommand.sql"


That's it. :-)

Wednesday, January 6, 2016

SharePoint 2010 - How to check if SPGroup exists

I've tried to check, if a SPGroup exists this way:

string nameOfGroupToCheck = "My SPGroup";
if(spWeb.Groups[nameOfGroupToCheck] != null)
{
...
}

...but this causes an exception, when the SPGroup is not existing.
Try this one:

string nameOfGroupToCheck = "My SPGroup";
if (spWeb.Groups.OfType<SPGroup>().Where(g => g.Name == nameOfGroupToCheck).Count() > 0)
{
...
}