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’)
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?
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!!!
Lets say I would like to query a table but I would like to compute a column according to another value.
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:
SELECT Id, First Name, (CASE WHEN Married = ‘1’ THEN LastName + ‘ Nee ‘ + FamilyName ELSE LastName END) As LastName FROM Females
Result would look like:
|| Duffy Nee Perry
|| Johnson Nee Smith
Where 1 and 4 have Married as False, while 2 and 3 have Married as True
Solution: Run as administrator (Right-click -> Run as Admin)
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 🙂
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
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”
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)
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
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.