Exposing locally running Web API to external world using ngrok

Web API Local URL


ngrok to make this URL work for external users

ngrok http [port] -host-header="localhost:[port]"


"This row already belongs to another table" Error

 DataTable dataTableWithDifferences = comparer.Compare();               DataTable sourcetable  = GetData1();               DataTable destinationtable = GetData2();             var updateRecords = (from table1 in destinationtable.AsEnumerable()                                  join table2 in dataTableWithDifferences.AsEnumerable()                                  on (string)table1["UserID"] equals (string)table2["UserID"]                                  select table2);             DataTable dtUpdates = sourcetable.Clone();             updateRecords.ToList().ForEach(row => dtUpdates.ImportRow(row));

SQL Table compare

declare @A table (ID int , Name nvarchar(max))
declare @B table (ID int , Name nvarchar(max))

insert into @A values(1,’zzzzzz1′)
insert into @A values(2,’zzzzzz111′)
insert into @A values(3,’zzzzzz3′)
insert into @A values(4,’zzzzzz4′)

insert into @B values(1,’zzzzzz1′)
insert into @B values(2,’zzzzzz2′)
insert into @B values(3,’zzzzzz3′)


select ‘ Rows in A that are not in B’
select * from @A
select * from @B

select ‘ Rows in B that are not in A’
select * from @B
select * from  @A

How to use arrays in Excel for large data

Excel is such a wonderful tool, but if this is not used correctly it could be a pain. If we want to copy/perform some operation on large amount of data and assign values to each cell this could get slow like hell.


if we have below code

  Dim Data
  Data = Sheets("bid").Range(dtarcollet & 8).Resize(numrowstomap, 1)
  .Range(dcurcollet & (dcurrownum + 1)).Resize(numrowstomap, 1) = Data 



Following will happen inside excel

If you say ‘Sheets(“bid”)’
    the sheets object must search for the sheet “bid” every time.
  If you say ‘Range(dtarcollet & (ctr + 7))’
    the code must calculate the value for “(ctr + 7)” => 8
    convert the result to a string => “8”
    combine the result with the other string “dtarcollet & (ctr + 7)” => “D8”
    the range object must parse the string to get the column and row => D is column 4
    prove if column and row is valid and return the value

All this take some time.

Reference : http://answers.microsoft.com/en-us/office/forum/office_2010-customize/excel-2010-macro-super-slow/6d5ae73c-ca75-e011-8dfc-68b599b31bf5


Now lets have a look at the magic code :

Sub ArrayTest()
  Dim R As Range
  Dim myData
  Dim i As Long, j As Long
  myData = Range("A1:C46494")
  myData = Range("A1").Resize(46494, 3)
  Set R = Range("A1:C46494")
  myData = R
'  'array lower limit of each dimension is always 1
'   For i = 1 To UBound(myData)
'    For j = 1 To UBound(myData, 2)
'      'first dimension is the row, second is the column
'      Debug.Print myData(i, j)
'    Next
'  Next
  Range("K1").Resize(UBound(myData), UBound(myData, 2)) = myData
End Sub

Copy only file path

1. Press Shift and right click on the any file/folder

2. You get additional menu items “Copy as path”.

This will only copy the path not the file.


Note: This is available in windows 7.

To make it run in windows XP install this small utility from here

How to install :

->Extract files

-> Right click on .inf file and install it .


Hope this help 🙂