SQLServer สร้าง Function Text to Datetime

Create function dbo.TextToDatetime( @DateValue char(8), @TimeValue char(6))
returns datetime
as
begin
declare @DateTimeValue varchar(32)
SELECT @DateTimeValue =convert(varchar, convert(datetime, @DateValue), 111)
+ ' ' + substring(@TimeValue, 1, 2)
+ ':' + substring(@TimeValue, 3, 2)
+ ':' + substring(@TimeValue, 5, 2)

return convert(datetime, @datetimevalue,101)
end

SQLServer การใช้ Cursor

 DECLARE @ColExpir datetime
DECLARE @ColFallprotec datetime
DECLARE @ColWorkid int
--------------------------------------------------------DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT Table_Training_Detalis.DateExpires,Table_Training_Detalis.Worker_ID
FROM Table_Courses
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @ColExpir,@ColWorkid
WHILE @@FETCH_STATUS = 0
BEGIN
update Table_Workers set WHIMIS= @ColExpir where Worker_ID=@ColWorkid

FETCH NEXT FROM @MyCursor
INTO @ColExpir,@ColWorkid
END
CLOSE @MyCursor
DEALLOCATE @MyCursor

VB.net Datatable รวมการใช้งาน

      Dim tb As New DataTable
        With tb
            .Columns.Add("Matno")
            .Columns.Add("MC")
            .Columns.Add("Qty")
        End With
        Dim dr As DataRow
        dr = tb.NewRow
        dr(0) = "A001"
        dr(1) = "MC2"
        dr(2) = "120"
        tb.Rows.Add(dr)

สามารถเขียนได้อีกวิธีในการเพิ่มแถว ลักษณะนี้จะช่วยให้จำนวนบรรทัดลดลง
และเราไม่จำเป็นต้องประกาศตัวแปร Dim row As DataRow เหมือนตัวอย่างการเพิ่มข้อมูลก่อนหน้านี้

dt.Rows.Add(New Object() {"A001", "MC2", 120.00})

'-----------------------------

---set primary key

Dim table As New DataTable()

table.Columns.Add(New DataColumn("MyColumn"))


Dim primaryKey(1) As DataColumn
primaryKey(0) = table.Columns("MyColumn")
table.PrimaryKey = primaryKey


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

update multiple data

 tb202I.Select("mcat = '" & pono & "'").ToList().ForEach(Sub(drow) drow("mordno") = vord)
 
---------------------


get value from datatable
 Dim rows() As DataRow = dtshippoint2.Select("mvalue = '" & xshippoint & "'")
            If rows.Count > 0 Then
                xlocation = rows(0).Item("mlocation")
            End If

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


   Dim foundRow As DataRow() = dtChange.Select("myid = '" & mynewid & "'")
            If foundRow.Length > 0 Then
                foundRow(0).Delete()
             End If

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

'Remove Colume

dt2.Columns.Remove("billid")

 dt2.AcceptChanges()
'----------------------
'Import Text file to Datatable

Dim tb As New DataTable
        With tb
            .Columns.Add("TrackingNo", GetType(String))
            '.Columns.Add("Shorttext", GetType(String))
            '.Columns.Add("qty", GetType(Double))
        End With
        Dim sFilename As String = FTPTextFilePath & filename
        Dim myStream As System.IO.StreamReader = New System.IO.StreamReader(sFilename)
        Dim line As String
        Dim aRow As DataRow
        Do
            line = myStream.ReadLine()
            If line Is Nothing Then
                Exit Do
            End If
            Dim sAry As String() = Split(line, "|")  
            aRow = tb.NewRow 
            aRow(0) = sAry(0)
            'aRow(1) = sAry(1)
            'aRow(2) = CInt(sAry(2))
            tb.Rows.Add(aRow)
        Loop
        myStream.Close()

VB.net ตั้งเวลาการทำงาน Schedule Timer

ref : http://www.thaicreate.com/community/dot-net-schedule.html

วิธีนี้จะต้องเปิดโปรแกรมทิ้งไว้ตลอดเวลา

Download Library

http://www.codeproject.com/Articles/6507/NET-Scheduled-Timer

ให้ Add ไฟล์ ScheduleTimer.dll เข้ามาใน Project

Imports Schedule


Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

          Dim Timer = New ScheduleTimer()
           AddHandler Timer.Elapsed, New ScheduledEventHandler(AddressOf timer_Elapsed)
          Timer.AddEvent(New ScheduledTime("Daily", "16:02"))
           Timer.Start()

End Sub

Private Sub timer_Elapsed(sender As Object, e As ScheduledEventArgs)
           MessageBox.Show("Schedule Working at 16:02")
End Sub

'เมื่อถึงเวลาที่กำหนด ก็จะทำงานตามที่เราต้องการ

'ในความสามารถของ Library ตัวนี้รองรับการกำหนดรูปแบบการทำงานที่หลากหลายมาก เช่น


Run at 6:00 AM on Mondays.

TickTimer.Events.Add(new  Schedule.ScheduledTime("Weekly",  "1,6:00AM"));



Run every 12 minutes starting on midnight 1/1/2003.

TickTimer.Events.Add(new Schedule.SimpleInterval(new
DateTime("1/1/2003"), TimeSpan.FromMinutes(12)));



Run every 15 minutes from 6:00 AM to 5:00 PM.
TickTimer.Events.Add(
new Schedule.BlockWrapper(
new Schedule.SimpleInterval(new DateTime("1/1/2003"),
TimeSpan.FromMinutes(15)),
"Daily",
"6:00 AM",
"5:00 PM"
)
);

DevExpress-Gridview(RowHeader Number)

    Private Sub GridView1_CustomDrawRowIndicator1(sender As Object, e As RowIndicatorCustomDrawEventArgs) Handles GridView1.CustomDrawRowIndicator
        If e.Info.IsRowIndicator AndAlso e.RowHandle >= 0 Then
            e.Info.DisplayText = e.RowHandle + 1 '.ToString
            Dim tSizeF As SizeF = e.Graphics.MeasureString(e.Info.DisplayText, e.Appearance.Font)
            Dim requiredWidth As Integer = Convert.ToInt32(tSizeF.Width) + 16
            Me.BeginInvoke(CType(Function() AnonymousMethod1(requiredWidth), MethodInvoker))
        End If
    End Sub
'----------------------------------
Private Function AnonymousMethod1(ByVal requiredWidth As Integer) As Boolean
        GridView1.IndicatorWidth = If(GridView1.IndicatorWidth < requiredWidth, requiredWidth, GridView1.IndicatorWidth)
        Return True
 End Function

DevExpress-Gridview(RowColor)

Imports DevExpress.XtraGrid.Views.Grid

Private Sub GridView1_RowStyle(ByVal sender As Object, _
ByVal e As DevExpress.XtraGrid.Views.Grid.RowStyleEventArgs) Handles GridView1.RowStyle
   Dim View As GridView = sender
   If (e.RowHandle >= 0) Then
      Dim category As String = View.GetRowCellDisplayText(e.RowHandle, View.Columns("Category"))
      If category = "Beverages" Then
         e.Appearance.BackColor = Color.Salmon
         e.Appearance.BackColor2 = Color.SeaShell
      End If
   End If
End Sub