VBA - VB.NET - EXCEL

http://www.siddharthrout.com/vb-dot-net-and-excel/

DATAGRIDVIEW ROW FORMATING

SAMPLE:

Private Sub dgvData_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles dgvData.CellFormatting

        If dgvData.DataSource IsNot Nothing Then
            Dim drv As DataRowView
            Dim dt As DataTable = CType(dgvData.DataSource, DataSet).Tables(0)

            If e.RowIndex >= 0 Then
                If e.RowIndex <= dt.Rows.Count - 1 Then
                    drv = dt.DefaultView.Item(e.RowIndex)
                    Dim dfm As Date
                    Dim dto  As Date
                    dfm = drv.Item("Period From")
                    dto  = drv.Item("Period Upto")                      
                    If Now.Date >= dfm And Now.Date <= dto Then
                        e.CellStyle.ForeColor = dgvData.DefaultCellStyle.ForeColor
                    Else
                        e.CellStyle.ForeColor = SystemColors.GrayText
                    End If
                End If
            End If
        End If
    End Sub

@@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT

@@IDENTITY returns the id of the last thing that was inserted by your client's connection to the database. Most of the time this works fine, but sometimes a trigger will go and insert a new row that you don't know about, and you'll get the ID from this new row, instead of the one you want
SCOPE_IDENTITY() solves this problem. It returns the id of the last thing that you inserted in the SQL code you sent to the database. If triggers go and create extra rows, they won't cause the wrong value to get returned.
IDENT_CURRENT returns the last ID that was inserted by anyone. If some other app happens to insert another row at an unforunate time, you'll get the ID of that row instead of your one.




Following code nicely work with Sql server 2005

DECLARE @last_id INT;

INSERT INTO [my_table] ([col1],[col2])
OUTPUT INSERTED.[id] INTO @last_id
VALUES (@col1,@col2)




More Info:
http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row 

SUM OF A DATAGRIDVIEW CELL USING LINQ

SAMPLE:1

Dim qry0 = From theRow As DataGridViewRow In dgvData.Rows _
                      Select theRow
Dim t = qry0.Sum(Function(x As DataGridViewRow) x.Cells("Amount").Value)

** dgvData - Datagridview name "Anount" is column name

-----------------------------------------------------------------

SAMPLE:2

Dim qry1 = From theRow As DataGridViewRow In dgvData.Rows _
              Where theRow.Cells("Year").Value > 2010 _
              Select theRow

Dim t = qry1.Sum(Function(x As DataGridViewRow) x.Cells("Amount").Value)

LINQ to Untyped DataSet Samples

SAMPLE:1

VB.NET

Dim query = From em In ds.Tables("Employees").AsEnumerable() _
            Where em.Field(Of String)("FirstName") = "Bijo" _

            Select em

'To display the results
   For Each em In query
       Console.WriteLine("Name = {1} {2}", _
                em.Field(Of String)("FirstName"), _
                em.Field(Of String)("LastName"))

        Next


C#

var query = from em in ds.Tables("Employees").AsEnumerable() _
            where em.Field<string>("FirstName") == "Bijo"

            select em.Field<string>(“FirstName”);



C# METHOD-2

var query = ds.Tables("Employees").AsEnumerable()
    .Where(dr => dr.Field<string>("FirstName") == "Bijo")

    .Select(dr => dr.Field<string>("FirstName"));

READING XML DOCUMENTS USING XDOCUMENT

READING XML DOCUMENTS USING XDOCUMENT

<employees>
    <department name="HR" >
        <employee ID="1" Name="Alex" passcode="100"/>
        <employee ID="2" Name="Anil" passcode="1545"/>
        <employee ID="3" Name="Vinod" passcode="856"/>
        <employee ID="4" Name="Sibi" passcode="1254"/>
    </department>
    <department name="Sales" >
        <employee ID="1" Name="Jeen" passcode="700"/>
        <employee ID="2" Name="Anil" passcode="545"/>
        <employee ID="3" Name="Kichu" passcode="86"/>
    </department>
</employees>


TO READ THE ABOVE XML USING XDOCUMENT OBJECT

Dim xdoc As XDocument = XDocument.Parse(<<xml string>>)
Dim deptList = From dl In xdoc.Descendants("department") Select dl

For Each dl In deptList
    '//PROCESS EACH DEPT.
    'ie dl="<department name="HR">
               <employee ID="1" Name="Alex" passcode="100"/>
               <employee ID="2" Name="Anil" passcode="1545"/>
               <employee ID="3" Name="Vinod" passcode="856" />
               <employee ID="4" Name="Sibi" passcode="1254" /> 
            </department>"

     'to read attributes use dl.Attribute("name").Value
 
     For Each em In dl.Elements("employee")
         '//PROCESS AN EMPLOYEE
         'ie em="<employee ID="1" Name="Alex" passcode="100" />"
     Next

Next

READING XML DOCUMENTS

READING XML DOCUMENTS

EXAMPLE-1

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
     <generalsettings>
          <billheader>
                <title>Title Name</title>
                <phone> 0+91 xxxx xxxx</phone>
                <email> xxxxxx@xyz.com</email>
                <web address="hotelxyz.com"/>
          </billheader>
    </generalsettings>


To load the xml into an xml document object:

  Dim xmldoc As New XmlDocument
  xmldoc.LoadXml(<<xmlString>>)

To select a specific node from xml document:
     Dim titleNode As XmlNode = xmldoc.SelectSingleNode("/generalsettings/billheader/title")
     Dim titleText =   titleNode.InnerText

To read attribute values:
     Dim webNode As XmlNode = xmldoc.SelectSingleNode("/generalsettings/billheader/web ")
     Dim webAddress =   webNode.Attributes("address").Value



EXAMPLE-2

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<waiterList>
    <waiter ID="1" Name="Alex" passcode="100"/>
    <waiter ID="2" Name="Anil" passcode="1545"/>
    <waiter ID="3" Name="Vinod" passcode="856"/>
    <waiter ID="4" Name="Sibi" passcode="1254"/>
</waiterList>

To Read All Waiter Elements:

nodelist = doc.GetElementsByTagName("waiter")
For Each node As XmlElement In nodelist
   //process nodes//
Next

PAGING SQLSELECT QUERY

IMPLEMENT PAGING IN SQL SELECT QUERY

SAMPLE QUERY-1

SELECT x.*
  FROM (SELECT c.id,
               c.ChannelName,
               c.ChannelAddress,
               ROW_NUMBER() OVER (ORDER BY c.id) AS rank
          FROM CHANNELS c) x
 WHERE x.rank BETWEEN 100 AND 200

SAMPLE QUERY-2

With ChannelEntities As
(
    Select ROW_NUMBER() Over (Order By id) As Row,
        id
    From CHANNELS
)
Select C1.Id,
C1.ChannelAddress,
C1.ChannelName
From ChannelEntities c
Inner Join CHANNELS c1 on c.Id = c1.Id
Where c.Row Between 100 AND 200
Order By c.Row Asc