Escaping single quote in SQL

Very simple, in order to escape a single quote in SQL, just add another single quote before your actual single quote as the following example:

Error:  UPDATE SampleTable SET (Title = ‘Welcome to Bob’s Burgers’)

Solution:  UPDATE SampleTable SET (Title = ‘Welcome to Bob”s Burgers’)

ERROR: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.

This week I was working on a migration script where I needed to run multiple sql queries. Since I was going to use this only once, I decided to use SqlHelper classes to connect and run sql to MsSql2012. Part of this included inserting and updating a date into the database. On my local computer this was working fine however, when placed on the server, I kept on getting the below error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.

A sample of the Sql I was trying to use was:

UPDATE Table SET StartDate = '26-Mar-15 2:53:42 PM' WHERE ID = 15

I googled this error and amongst the top suggestions/solutions I found:

• sql query is passing the wrong date format so it goes out of range
• you should not pass DateTime values as sting in your SQL statement. Instead use parameters such as (    cmd.Parameters.AddWithValue("@mvendid", mVendid);  )

None of the 2 above worked. I tried all sorts of formats (“dd/mm/yyyy hh:mm:ss”, “mm/dd/yyyy hh:mm:ss”, “yyyy/mm/dd hh:mm:ss”, etc etc). I tried also declaring a new date in the sql and saving it, which still gave same error. I was about to give up when I though what about the computer’s date?

Solution:

We checked my computer’s date format from “Control Panel -> Region and Language -> Formats” and noticed that the short date was completely different. Since I was desperate, we tried changing the server’s local date to match my date. Just like a miracle it worked fine.

 

 

Hope this helped … as I was going mental for a while there!!!

SQL Computed Values

Lets say I would like to query a table but I would like to compute a column according to another value.

Example:

Original Table Columns:

• Id (int)
• FirstName (varchar)
• LastName (varchar)
• Married (bit)
• FamilyName (varchar)

I would like to get from table “Females” the following data, where Last Name should have the original nee surname if married:

• Id
• FirstName
• LastName

Sql Statement: 

SELECT Id, First Name, (CASE WHEN Married = ‘1’ THEN LastName + ‘ Nee ‘ + FamilyName ELSE LastName END) As LastName FROM Females

 

Result would look like:

Id	First Name	Last Name
1	Jane	Smith
2	Sue	Duffy Nee Perry
3	Kelly	Johnson Nee Smith
4	Marie	Dickson

Where 1 and 4 have Married as False, while 2 and 3 have Married as True

 

 

 

 

MS SQL error: Invoke or BeginInvoke cannot be called on a control until the window handle has been created

Solution: Run as administrator (Right-click -> Run as Admin)

Microsoft SQL Server Error 3415

I had a database which I detached while still using SQL server 2005. Now that I have upgraded to Server 2008 I was receiving a 3415 error message each time I attempt to attach it to a new Database. I have read a lot of posts that I need to download this and that, or that I need to do this and that, nothing worked. I was not sure if I felt upset or stupid when I realised that all I needed to do is enable my PCs permissions. To do so, you need to:

• Open the “User Account Control Settings” (type in Start if using Vista or Windows 7)
•  Scroll down permissions to “Never Notify”
• Click OK
• ENJOY 🙂

Error: You cannot save changes that would result in one or more tables being re-created

When trying to save something to a table, such as new fields or isNull tick box, set primary keys or change data types, the above error may pop up. This may happen due to attempts to:

• Change data type on existing columns such as text to varchar
• Check allow nulls or uncheck allow nulls on existing columns
• Change column sizes
• Change foreign key constraints

The solution is easy, if you already have the SQL Management Studios,

Using Visual Studio 2008:

• click Tools, select Options
• from the pop up, select Designers from the left side menu
• Uncheck the “Prevent saving changes that require table re-creation” as shown below
• Save

Using Visual Studio 2010 or Visual Studio 2012:

• click Tools, select Options
• from the pop up, select “Database Tools” from the left side menu
• from the sub-categories of Database Tools, select “Table and Database Designers”
• Uncheck the “Prevent saving changes that require table re-creation”
• Save

 

If you are using the built in SQL server express in-built in the Visual Studio 2008 or 2010, you are required to download the SQL management studio 2008 and possibly also the SQL Server 2008 Service Pack 1 (try without installing Service Pack 1 first)

Available from:

Sql Management Studio 2008: http://www.microsoft.com/download/en/details.aspx?id=7593

Sql Service Pack 1: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20302

Only servers up to Microsoft SQL 2005 are supported

When trying to connect a new Database Connection to the Visual Studio 2008 or higher, a support problem comes up stating that only servers up to 2005 are supported.

This can easily be solved by installing the Visual Studio Service Pack 1 available from http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=13276 ready in iso format.

Convert DateTime to String in Stored Procedure

In some cases, it is very hard or complicated to display the time as a string. For instance, when using repeaters, binding is done through the eval so data cannot be manipulated. For this reason it is quite easy to manipulate the time in the stored procedure and return it as a string. Below is an example of the code required to convert date to string:

CREATE PROCEDURE sp_sampleConvertDateToString

AS

SELECT Column1, Column2, CONVERT(nvarchar(100), Column3, 106) AS Column3String

FROM DummyTable

RETURN

it is also possible to return the current date instead of a column from the table, which would looks like:

CREATE PROCEDURE sp_sampleConvertDateToString

AS

SELECT *, CONVERT(nvarchar(100), GetDate(), 106) AS DateNow

FROM DummyTable

RETURN

There are various ways to display the date as string, below is a list of the mostly used options available to display the date and time.

Date as String	Parameter	Stored Procedure Code
01/01/11	1	SELECT CONVERT(nvarchar(100), getDate(), 1)
01 Jan 11	6	SELECT CONVERT(nvarchar(100), getDate(), 6)
Jan 01, 11	7	SELECT CONVERT(nvarchar(100), getDate(), 7)
Jan 01 2011 0:00PM	100	SELECT CONVERT(nvarchar(100), getDate(), 100)
01 Jan 2011	106	SELECT CONVERT(nvarchar(100), getDate(), 106)
01/01/2011 0:00:00 PM	22	SELECT CONVERT(nvarchar(100), getDate(), 22)