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’)

Advertisements

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.