I was trying to query a complicated sql query with joins, groups, etc and after upgrading mySQL the group by stopped working.
In order to fix issue I had to open mySQL and on the database I had to run the following query:
SET GLOBAL sql_mode = '';
Category Archives: Databases
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
Nhibernate simple Select, Insert, Update and Delete
Nhibernate is one of the many Object-Relational Mapping (ORM) tools for the .NET platform.
A very quick description of ORM:
ORM is the process of mapping objects to database relations without the requirement of writing SQL statements (such as SELECT, INSERT, UPDATE, DELETE, JOIN, etc etc) generally through the use of XML files.
Nhibernate eliminates the requirement of using ADO.NET, Linq, etc and thus is the back bone of the Data Access Layer (in nThier Architectures).
Requirements for this Tutorial:
- NuGet Package Manager (unless you already have NHibernate)
- Visual Studio 2010
- MS SQL 2005 or 2008
This tutorial will be split in 12 steps:
Step 1:- Create a 2-Thier Project
Step 2:- Install Nhibernate
Step 3:- Define Business Objects (Domain Folder)
Step 4:- Define the Mapping (Mappings Folder)
Step 5:- Setting XML Schema
Step 6:- Configure Nhibernate
Step 7:- Create ISessionFactory
Step 8:- Create Table from Schema and Test Connection
Step 9:- Create Repositories
Step 10:- Define Method from Repositories
Step 11:- Testing Add, Update, Delete Method
Before we continue, I want to add that “I know this is a very long and mostly text based tutorial” but I have searched the internet sooo much for a decent working tutorial without errors and complications that this post here should cover it ALL … I also did it again while writing it to make sure no errors are found. So, hold on and read through… EVERYTHING should be there 🙂
So, lets begin …
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)
Storing Greek | Polish | Cyrillic (Russian) Alphabet in Database
When saving cryillic alphabet or other languages which do not use the english alphabet, the datatypes text and varchar do not support the alphabet symbols.
It usually turns all text into ??????
For instance I had tested saving “Интернет-программирование ” into a column with datatype text and it saved all as ” ???????- ? ???? ???”.
In order for this not to happen we simply save the column with the datatype nvarchar(MAX) since nvarchar supports a vast range of symbols which other datatypes do not. The only disadvantage of such datatype will be that it takes some more space than other datatypes.
PS. To send Such alphabets in an email it is required to first add the following:
MailMessage mm = new MailMessage(“EmailAddress”, “EmailAddess”);
mm.IsBodyHtml = true;
mm.SubjectEncoding = System.Text.Encoding.UTF8;
mm.BodyEncoding = System.Text.Encoding.UTF8;
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
Error: The data types text and text are incompatible in the equal to operator
While taking security measures to hash password I chose text as a datatype, what I did not know is that the such type is considered as a blob type and is very limited. Comparing text with text is one of such limitations. To fix this problem I had to change the type of the Password to varchar(150) to make space for the hashing and other security measures.
Rochcass 