RSS

Author Archives: humrahimcs

About humrahimcs

Principal Software Developer in Bilytica (pvt) Ltd. Lahore Pakistan.

Add New column in existing MySQL table Specific Row


To add a column LastName to the Student table with a datatype of VARCHAR(60), use the following SQL statement:

ALTER TABLE Student ADD LastName VARCHAR(60);

This first statement will add the LastName column to the end of the table.If you insert the new column after a specific column, such as FirstName, then use this statement:

ALTER TABLE Student ADD LastName VARCHAR(60) AFTER FirstName;

If you want to Add new column to first Column, Then use this statement:

 

ALTER TABLE Student ADD LastName VARCHAR(60) FIRST;

If you want to Add new column with Default Value, Then use this statement:

ALTER TABLE Student ADD LastName VARCHAR(60) Default 0;

 
Leave a comment

Posted by on November 19, 2015 in My SQL

 

Tags: ,

devexpress multiple gridview export to excel in Single File winforms


After two much searching of this solution to export multiple gridview data to single Excel file with separate Sheet of Excel.

Then i find this solution which resolve my problem. this is 100% tested and use in my project . after that i share with you in both language c# and vb.net.

 

 Solution :: Vb.Net

Imports DevExpress.XtraPrinting
Imports DevExpress.XtraPrintingLinks

 

Public Sub MultiFileExportToExcel()

Using saveDialog = New SaveFileDialog()
saveDialog.Filter = “Excel (.xlsx)|*.xlsx”
If saveDialog.ShowDialog() = DialogResult.OK Then
Dim printingSystem = New PrintingSystemBase()
Dim compositeLink = New CompositeLinkBase()
compositeLink.PrintingSystemBase = printingSystem

Dim link1 = New PrintableComponentLinkBase()
link1.Component = grid1
Dim link2 = New PrintableComponentLinkBase()
link2.Component = grid2

compositeLink.Links.Add(link1)
compositeLink.Links.Add(link2)

Dim options = New XlsxExportOptions()
options.ExportMode = XlsxExportMode.SingleFilePageByPage

compositeLink.CreatePageForEachLink()
compositeLink.ExportToXlsx(saveDialog.FileName, options)
End If
End Using
End Sub

 

Solution ::  C#

using DevExpress.XtraPrinting
using DevExpress.XtraPrintingLinks

public void exportToExcel()
{
using (var saveDialog = new SaveFileDialog())
{
saveDialog.Filter = “Excel (.xlsx)|*.xlsx”;
if (saveDialog.ShowDialog() == DialogResult.OK)
{
var printingSystem = new PrintingSystemBase();
var compositeLink = new CompositeLinkBase();
compositeLink.PrintingSystemBase = printingSystem;

var link1 = new PrintableComponentLinkBase();
link1.Component = grid1;
var link2 = new PrintableComponentLinkBase();
link2.Component = grid2;

compositeLink.Links.Add(link1);
compositeLink.Links.Add(link2);

var options = new XlsxExportOptions();
options.ExportMode = XlsxExportMode.SingleFilePageByPage;

compositeLink.CreatePageForEachLink();
compositeLink.ExportToXlsx(saveDialog.FileName, options);
}
}
}

 

Reference url;

https://www.devexpress.com/Support/Center/Question/Details/A2730

 
Leave a comment

Posted by on April 22, 2015 in Vb.Net, WinForm

 

Tags: ,

Deleting older files after a specified number of days using VB.NET


If You want to Temp or your specific Export file History maintain folder to delete file after some days then this function resolve your problem.

Calling Way:

First Way : 

Dim DeleteInfo As Long()

DeleteInfo = DeleteFilesXDaysOld("c:\temp\", "*.*", 60)

Console.Writeline(DeleteInfo(0) & " file(s) deleted.")
Console.Writeline("Freeing " & DeleteInfo(1) & " bytes.")


Second Way:

Imports System.IO

 Dim DeleteInfo As Long()
 If My.Computer.FileSystem.DirectoryExists(Path.GetTempPath & "\Export\Invoice") = True Then
 DeleteInfo = DeleteFilesXDaysOld(Path.GetTempPath & "\Export\Invoice", "*.*", 2)
 End If

Check Directory Exist or Not in Vb.Net else create

Dim GLFilePath as string = Path.GetTempPath & "\Export\Invoice"

 If My.Computer.FileSystem.DirectoryExists(Path.GetTempPath & "\Export\Invoice") = False Then
 My.Computer.FileSystem.CreateDirectory(Path.GetTempPath & "\Export\Invoice")
 End If

Function Delete Files:

Private Function DeleteFilesXDaysOld( _ ByVal Path As String, _ Optional ByVal Pattern As String = "*.*", _ Optional ByVal Days As Integer = 365) As Long() ' ReturnValues is an array which holds 2 values ' The first being the count of files deleted ' The second being the amount of space freed Dim ReturnValues = New Long() {0,0} Dim File As String Dim FileInfo As System.IO.FileInfo Dim Files() As String = _ System.IO.Directory.GetFiles(Path, Pattern) Try For Each File In Files FileInfo = New System.IO.FileInfo(File) If FileInfo.LastWriteTime < _ Today.AddDays(0 - Days) Then ReturnValues(0) += 1 ReturnValues(1) += FileInfo.Length() FileInfo.Delete() End If Next Return ReturnValues Catch exn As Exception MsgBox(exn.ToString, _ MsgBoxStyle.Critical Or _ MsgBoxStyle.OkOnly Or _ MsgBoxStyle.SystemModal, _ "Error") Return ReturnValues End Try End Function

 
1 Comment

Posted by on February 15, 2015 in Vb.Net

 

Tags: , , , , ,

group by sequence number MySQL


Before you use below sample code i prefer you use this URL code which better for you.

http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/

 

SAMPLE DataTable

CREATE TABLE CountryCity
 (
 city VARCHAR(100),
 country VARCHAR(100),
 population int
 );

INSERT INTO CountryCity VALUES ('Lahore', 'Pakistan',8175133); 
 INSERT INTO CountryCity VALUES ('Multan', 'Pakistan',3792621); 
 INSERT INTO CountryCity VALUES ('Liquat Pur', 'Pakistan',2695598); 
 
 INSERT INTO CountryCity VALUES ('Kalkata', 'India',2181000);
 INSERT INTO CountryCity VALUES ('Dehli', 'India',808000);
 INSERT INTO CountryCity VALUES ('Goya', 'India',422000);
 
 INSERT INTO CountryCity VALUES ('Riyadh', 'KSA',7825300);
 INSERT INTO CountryCity VALUES ('Madina', 'KSA',1016800);
 INSERT INTO CountryCity VALUES ('Makkah', 'KSA',770800);

MySQL Query to Get Top 2

To get the 2 largest Country cities for each country, you can use the following query in MySQL:
SELECT city, country, population
FROM
(SELECT city, country, population,
@country_rank := IF(@current_country = country, @country_rank + 1, 1) AS country_rank,
@current_country := country
FROM CountryCity
ORDER BY country, population DESC
) ranked
WHERE country_rank <= 2;

 

The query returns:

 


city
country population
Kalkata India 2181000
Dehli India 808000
Riyadh KSA 7825300
Madina KSA 1016800
Lahore Pakistan 8175133
Multan Pakistan 3792621

 

How It Works

Explanation of the MySQL query:

  • Session Variables

Currently MySQL does not support ROW_NUMBER() function that can assign a sequence number within a group, but as a workaround we can use MySQL session variables.

These variables do not require declaration, and can be used in a query to do calculations and to store intermediate results.

@current_country := country

This code is executed for each row and stores the value of country column to @current_country variable.

@country_rank := IF(@current_country = country, @country_rank + 1, 1)

In this code, if @current_country is the same we increment rank, otherwise set it to 1. For the first row@current_country is NULL, so rank is also set to 1.

For correct ranking, we need to have ORDER BY country, population DESC

So if we just execute the subquery:

   SELECT city, country, population, 
       @country_rank := IF(@current_country = country, @country_rank + 1, 1) AS country_rank,
       @current_country := country 
  FROM Countrycities
  ORDER BY country, population DESC

We get the list of cities ranked by population within the country:

city country population country_rank current_country
Kalkata India 2181000 1 India
Dehli India 808000 2 India
Goya India 422000 3 India
riyadh KSA 7825300 1 KSA
Madina KSA 1016800 2 KSA
Makkah KSA 770800 3 KSA
Lahore Pakistan 8175133 1 Pakistan
Multan Pakistan 3792621 2 Pakistan
Liquat Pur Pakistan 2695598 3 Pakistan
  • Selecting Range

When we have a rank assigned to each city within its country, we can retrieve the required range:

   -- Get top 2 for each country
   SELECT city, country, population
   FROM (/*subquery above*/) ranked
   WHERE country_rank <= 2;
 
   -- Get the city with 3rd population for each country
   SELECT city, country, population
   FROM (/*subquery above*/) ranked
   WHERE country_rank = 3;

ROW_NUMBER() – Oracle, SQL Server and PostgreSQL

In Oracle, SQL Server and PostgreSQL you can achieve the same functionality using ROW_NUMBER function:

   SELECT city, country, population
   FROM
    (SELECT city, country, population, 
                  ROW_NUMBER() OVER (PARTITION BY country ORDER BY population DESC) as country_rank
      FROM cities) ranked
   WHERE country_rank <= 2;

This query works in Oracle, SQL Server and PostgreSQL without any changes and returns:

city country population
Kalkata India 2181000
Dehli India 808000
Riyadh KSA 7825300
Madina KSA 1016800
Lahore Pakistan 8175133
Multan Pakistan 3792621
 
Leave a comment

Posted by on February 12, 2015 in My SQL

 

Tags: , , ,

How to show Time from DateTime Rdlc Report Field


One of my recent requirements involved retrieving just the time portion of a records value,  generally the  we have the data type saved as a date/time format. First of all we want to show only time at rdlc report . so, this is easiest way to show time or if you want to show date.

First :: For Time.

=FormatDateTime(First(Fields!LoginTime.Value, “DS”), DateFormat.LongTime)

First :: For Date.

=FormatDateTime(First(Fields!LoginTime.Value, “DS”), DateFormat.ShortDate)

Rdlc DateTime Formula

 
Leave a comment

Posted by on January 7, 2015 in Crystal Report

 

Tags: , , , ,

incorrect datetime value for function str_to_date


Hi,

when you use Mysql function “str_to_date” in Asp.net  code to merg code from one database to other and you can aslo move (map) Time along with Date . then common error occure after add time

SELECT STR_TO_DATE(‘[21/Apr/2009:07:14:50 +0100]’, ‘[%d/%b/%Y:%H:%i:%S +0100]’);

The SQLException does not come directly from MySQL, it’s probably triggered by your client language. MySQL will just generate a warning you can normally ignore. Whatever, the ALLOW_INVALID_DATESSQL mode should actually do the trick:

Warning:

mysql> SET @@SESSION.sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test (date_created) VALUES (str_to_date('','%m/%d/%Y'));
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------+
| Level   | Code | Message                                               |
+---------+------+-------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '' for function str_to_date |
+---------+------+-------------------------------------------------------+
1 row in set (0.00 sec)

No warning:

mysql> SET @@SESSION.sql_mode='ALLOW_INVALID_DATES';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test (date_created) VALUES (str_to_date('','%m/%d/%Y'));
Query OK, 1 row affected (0.03 sec)
Specifier Description
%a Abbreviated weekday name (Sun..Sat)
%b Abbreviated month name (Jan..Dec)
%c Month, numeric (0..12)
%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%f Microseconds (000000..999999)
%H Hour (00..23)
%h Hour (01..12)
%I Hour (01..12)
%i Minutes, numeric (00..59)
%j Day of year (001..366)
%k Hour (0..23)
%l Hour (1..12)
%M Month name (January..December)
%m Month, numeric (00..12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00..53), where Sunday is the first day of the week
%u Week (00..53), where Monday is the first day of the week
%V Week (01..53), where Sunday is the first day of the week; used with %X
%v Week (01..53), where Monday is the first day of the week; used with %x
%W Weekday name (Sunday..Saturday)
%w Day of the week (0=Sunday..6=Saturday)
%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y Year, numeric, four digits
%y Year, numeric (two digits)
%% A literal % character
%x x, for any x not listed above
 
Leave a comment

Posted by on November 13, 2014 in Uncategorized

 

hidden files on of usb


ollow all the steps given below.

1) Click on the below link and download the file “AutorunExterminator”

http://en.kioskea.net/download/download-11613-autorun-exterminator

Extract it –> Double-click on “AutorunExterminator” –> Plug your pendrive now.

This will remove the autorun.inf files from drives and plug your pen drive to

remove the autorun.inf file from it also.

[Note : Make sure Dot Net Framework is installed in your PC.

OR

Click on the below link and download the Dot Net Framework.

http://www.microsoft.com/…]

Better Solution
2) Click on “Start” –>Run –> type cmd and press Enter.

Here I assume your pendrive drive letter as G:

Enter this command.

attrib -h -r -s /s /d g:\*.*

You can copy the above command –> Right-click in the Command Prompt and

paste it.

Note : Replace the letter g with your pen drive letter.

Now check for your files.

3) After that, download the Malwarebytes’ Anti-Malware from the below link

http://en.kioskea.net/download/download-105-malwarebytes-anti-malware

Update it –> Perform “Full Scan”

Note : Default selected option is “Quick Scan”.

 
Leave a comment

Posted by on October 27, 2014 in System Issue

 

Tags: , , ,