Monday, March 19, 2018

vfpoledeb.1 provider is not registered on the local machine

Have u Install VFP Ole DB Provider in your System ?

If No then Please Install it from following link.

http://www.microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en

Tuesday, January 30, 2018

C# print file on LAN Printer

If you want to print any file on network printer then you can try with this code block



  // Change Default Printer  
         System.Management.ManagementObjectSearcher search =  
                 default(System.Management.ManagementObjectSearcher);  
         System.Management.ManagementObjectCollection results =  
                 default(System.Management.ManagementObjectCollection);  
         System.Management.ManagementObject printer =  
                 default(System.Management.ManagementObject);  
         search =  
          new System.Management.ManagementObjectSearcher("select * from win32_printer");  
         results = search.Get();  
         //Get Default Printer  
         System.Management.ManagementObject defaultPrinter = null;  
         foreach (System.Management.ManagementObject foundPrinter in results)  
         {  
           System.Management.PropertyDataCollection  
              propertyDataCollection = foundPrinter.Properties;  
           System.Diagnostics.Debug.WriteLine(foundPrinter["Name"]);  
           if ((bool)foundPrinter["Default"]) // DEFAULT PRINTER  
           {  
             System.Diagnostics.Debug.WriteLine(foundPrinter["Name"]);  
             System.Diagnostics.Debug.WriteLine(foundPrinter["Location"]);  
           }  
         }  
         // Set Default printer  
         foreach (System.Management.ManagementObject foundPrinter in results)  
         {  
           System.Diagnostics.Debug.Print(foundPrinter["Name"].ToString());  
           if (foundPrinter["Name"].Equals(@"\\192.168.10.139\HP DeskJet 2130 series"))  
           {  
             System.Management.ManagementBaseObject outParams =  
                foundPrinter.InvokeMethod("SetDefaultPrinter", null, null);  
             if (outParams == null)  
               System.Diagnostics.Debug.WriteLine("Unable to set default printer");  
             Int32 retVal = (int)(uint)outParams.Properties["ReturnValue"].Value;  
             //if (retVal == 0)  
             //  return true;  
             //else  
             //  return false;  
             break;  
           }  
         }  
  try  
         {  
           ProcessStartInfo info = new ProcessStartInfo();  
           info.UseShellExecute = true;  
           info.Verb = "printto";  
           info.FileName = CurrentEmailFilePath;  
           info.CreateNoWindow = true;  
           info.WindowStyle = ProcessWindowStyle.Hidden;  
           Process p = new Process();  
           p.StartInfo = info;  
           p.Start();  
           p.WaitForInputIdle();  
           System.Threading.Thread.Sleep(3000);  
           if (false == p.CloseMainWindow())  
             p.Kill();  
         }  
         catch (Exception ex)  
         {  
           throw;  
         }  



Tuesday, November 28, 2017

SQL Get all Index create script from Database


To get all script from database as a create new index into another database you can use the following


 --Get all Index Script  
 SELECT ' CREATE ' +   
   CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END +   
   I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' +    
   I.name + ' ON ' +   
   Schema_name(T.Schema_id)+'.'+T.name + ' ( ' +   
   KeyColumns + ' ) ' +   
   ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') +   
   ISNULL(' WHERE '+I.Filter_definition,'') + ' WITH ( ' +   
   CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ',' +   
   'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ',' +   
   -- default value   
   'SORT_IN_TEMPDB = OFF ' + ',' +   
   CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ',' +   
   CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ',' +   
   -- default value   
   ' DROP_EXISTING = OFF ' + ',' +   
   -- default value   
   ' ONLINE = OFF ' + ',' +   
   CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ',' +   
   CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END + ' ) ON [' +   
   DS.name + ' ] ' [CreateIndexScript]   
 FROM sys.indexes I    
  JOIN sys.tables T ON T.Object_id = I.Object_id    
  JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid    
  JOIN (SELECT * FROM (   
   SELECT IC2.object_id , IC2.index_id ,   
     STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END   
   FROM sys.index_columns IC1   
   JOIN Sys.columns C    
     ON C.object_id = IC1.object_id    
     AND C.column_id = IC1.column_id    
     AND IC1.is_included_column = 0   
   WHERE IC1.object_id = IC2.object_id    
     AND IC1.index_id = IC2.index_id    
   GROUP BY IC1.object_id,C.name,index_id   
   ORDER BY MAX(IC1.key_ordinal)   
     FOR XML PATH('')), 1, 2, '') KeyColumns    
   FROM sys.index_columns IC2    
   --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables   
   GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4    
  ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id   
  JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id    
  JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id    
  JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id    
  LEFT JOIN (SELECT * FROM (    
   SELECT IC2.object_id , IC2.index_id ,    
     STUFF((SELECT ' , ' + C.name   
   FROM sys.index_columns IC1    
   JOIN Sys.columns C    
     ON C.object_id = IC1.object_id    
     AND C.column_id = IC1.column_id    
     AND IC1.is_included_column = 1    
   WHERE IC1.object_id = IC2.object_id    
     AND IC1.index_id = IC2.index_id    
   GROUP BY IC1.object_id,C.name,index_id    
     FOR XML PATH('')), 1, 2, '') IncludedColumns    
   FROM sys.index_columns IC2    
   --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables    
   GROUP BY IC2.object_id ,IC2.index_id) tmp1    
   WHERE IncludedColumns IS NOT NULL ) tmp2    
 ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id    
 WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0   
 --AND I.Object_id = object_id('Person.Address') --Comment for all tables   
 --AND I.name = 'IX_Address_PostalCode' --comment for all indexes   


Before run the script For your new database you also need to create file group and file name for index. In my case i give name index for file and file-group name.





SQL Drop index script from Database


If yo want to get all drop index script from database you can use this.

like '%idx_%'  = index prefix


 --Drop All Index  
 declare @qry nvarchar(max);  
 select @qry =   
 (SELECT 'DROP INDEX ' + ix.name + ' ON ' + OBJECT_NAME(ID) + '; '  
 FROM sysindexes ix  
 WHERE  ix.Name IS NOT null and ix.Name like '%idx_%'  
 for xml path(''));  
 SELECT @qry  


This will return all drop index script on result. now copy the script for your use.





Tuesday, November 21, 2017

Ensure that this project has Microsoft.Bcl.Build installed and packages.config is located next to the project file


In my Xamarin form application their was dependency Restclient on
Microsoft.Bcl.Build
Microsoft.Bcl.
i just remove all using nuget and install Newtonsoft.Json

Thursday, November 9, 2017

SQL get all table column which is null in database




 create table #SuspectColumns (  
   TABLE_SCHEMA sysname,  
   TABLE_NAME sysname,  
   COLUMN_NAME sysname  
 )  
 declare csrColumns cursor fast_forward for  
   select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME  
     from INFORMATION_SCHEMA.COLUMNS  
     where IS_NULLABLE = 'YES'  
 declare @TABLE_SCHEMA sysname,  
     @TABLE_NAME sysname,  
     @COLUMN_NAME sysname,  
     @sql nvarchar(max)   
 open csrColumns  
 while (1=1) begin  
   fetch next  
     from csrColumns  
     into @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME  
   if @@FETCH_STATUS<>0 break  
   set @sql = N'if not exists(select 1 from ' + QUOTENAME(@TABLE_SCHEMA) + N'.' + QUOTENAME(@TABLE_NAME) + N' where ' + QUOTENAME(@COLUMN_NAME) + N'is not null)  
            insert into #SuspectColumns values (''' + @TABLE_SCHEMA + N''',''' + @TABLE_NAME + N''',''' + @COLUMN_NAME + N''')'  
   exec sp_executesql @sql  
 end /* while */  
 close csrColumns  
 deallocate csrColumns  
 select * from #SuspectColumns  
 drop table #SuspectColumns  



This will return table name and column name which is null in database











Tuesday, November 7, 2017

sql check duplicate column values for same id


i want to check  if any customer has more than 1 same insplanid_FK.


 ;with cte as  
 (  
 SELECT *,  
 count(cusid_FK) OVER (partition by cusid_FK,ccardholdername,insplanid_FK) as V_COUNT  
 FROM cusinsplan  
 WHERE ISNULL(insplanid_FK,'')!=''  
 )  
 select * from cte where V_COUNT>1  









vfpoledeb.1 provider is not registered on the local machine

Have u Install VFP Ole DB Provider in your System ? If No then Please Install it from following link. http://www.microsoft.com/download...