How to use ON DUPLICATE KEY in insert statement

How to use ON DUPLICATE KEY in insert statement

Problem:
In case of inserting or updating record we use separate logic using if statement or case statement:
e.g.:

if(i==0)
insert here

if(i=1)
update here

we can terminate the above if statement by using On Duplicate Key.
by using this only one statement can be use for insert and update record.

*note: user_nm coulmn must be unique

Example:

INSERT INTO `posts` (`user_nm`, `no_of_update`, `inserted_on`, `update_on`)
VALUES ('a_user', 1, NOW(), NOW())
ON DUPLICATE KEY
UPDATE `no_of_update` = `no_of_update` + 1, `update_on` = NOW()

Fore more, continue here...

Email Blaster

Email Blaster
*Features
- Send unlimited emails.
- Send test email, so you can see your email format and looks.
- Show real time progress in counting sent emails.
- Show progress in percentage(%)
- Send email both format Text and HTML format.
- Alternate Solution of Cron Job(Less feature).
- Alternate Solution of SQL Agent Job(Less feature).
- Solution of Script Timeout.

Fore more, continue here...

Multiple step OLE DB operation generated errors Check each OLE DB status value if available No work was done

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done


If you are working with MS Access database then you encounter with the "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done" error.
There are many reasons that produce the above error.
But the common error is Ms Access database password is mismatching. the 2007 Ms Access database password length is 20 characters and if you mistakenly set 21 characters as a password then last 21th character will automatically suppressed by Ms Access Database. for example you have types password as:
abcdefghijklmnopqrstu

then last "u" will automatically deleted and Ms Access will not inform you about it and set the password as:
abcdefghijklmnopqrst

You will not aware this Ms Access activity and while working with C# or VB.Net you will used the password as:
abcdefghijklmnopqrstu
then you can face the "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done" error.

So just check the password and you will not face this types of error.

Fore more, continue here...

How to use Limit in ms access sql query

How to use Limit in ms access sql query


use below ms access sql
SELECT TOP 90 PERCENT * FROM tablename

Fore more, continue here...

MS Access alphanumeric column sorting as number

Alphanumric column sorting as numeric in MS Access

You have Ms Access Database table as listed belwo:
------------------------------------------
id,p_num, city,branch
------------------------------------------ 
1,P1,a,b
2,P9,c,d
3,P19,e,f
4,P2,g,h
5,P111,i,j
6,P100,k.l
7,P10,m,n
8,P7,o,p
------------------------------------------

Now you want to show the record by "p_num" column, you can see "p_num" is a alphanumeric value and you want to sort is as below:
------------------------------------------ 
id,p_num, city,branch
------------------------------------------ 
1,P1,a,b
2,P2,g,h
3,P7,o,p
4,P9,c,d
5,P10,m,n
6,P19,e,f
7,P100,k.l
8,P111,i,j
------------------------------------------
So in this case use this sql:

SELECT id,Mid(p_num,2,12) AS new_p_num,city,branch FROM my_tbl order by CInt(Mid(p_num,2,12) )

In the above sql query "Mid(p_num,2,12)" 2 is the starting position of p_num column and 12 is the maximum digits. Here I am assuming after "P" number is not more that 12 digits.

Fore more, continue here...

Exact date problem in ms access sql

Exact date problem in ms access sql

===============================

Suppose you have a column in the table of ms access database with DateTime. and you are storing Date and Time values. After that if you use query "select *from tbl where creationDate>=#07/30/2014# and creationDate<=#07/30/2014#  then you will surprise that no result will come as output. this is because your column is have both Data "DateTime".

So the problem is that how to show the data even data are exists in the table?

Append the time in the query as listed below:

"select *from tbl where creationDate>=#07/30/2014 00:00 AM# and creationDate<=#07/30/2014 23:59 PM#"

Fore more, continue here...

Difference between primary key and unique key in sql

Difference between primary key and unique key in sql.
======================================================

Primary key                            Unique key
1-can not be null                    1-can be null and multiple null
2-can not be same value        2-can not be same value
3-only be one column name  3-multiple column could be unique

suppose emp_id is primary key and city is the unique key
primary key                                   unique key
-----------------------------------------------------------------------------------
emp_id        emp_nm        sal        city
-----------------------------------------------------------------------------------
0                  AA            18000        (null)      
1                 BB             18000        NEW DELHI
3                CC             18000        NOIDA
2                DD             18000        (null)
4                EE             18000        KANPUR
5                FF             18000        (null)
6                GG             18000        (null)
----------------------------------------------------------------------------------

Fore more, continue here...