Connection my sql

Imports MySql.Data.MySqlClient

Public Class clsConnecetion
    Public ConMy As MySqlConnection = New MySqlConnection

    'Dim Hostname As String = "172.00.00.000"
    'Dim DBName As String = "XXX"
    'Dim SID As String = "admin"
    'Dim Password As String = "1234567890"

    Public Sub ConnectOpen()
        ConMy.Close()
        Try
            With ConMy
                If .State = ConnectionState.Open Then .Close()
                .ConnectionString = "Data Source=" & Hostname & ";Database=" & DBName & ";User ID=" & SID & ";Password=" & Password & ";CharSet=tis620"
                .Open()
            End With
        Catch ex As Exception
        End Try
    End Sub

    Public Sub ConnectClose()
        Try
            ConMy.Close()
        Catch ex As Exception
        End Try
    End Sub

End Class

Read More

จัด format ตัวเลข

จัด format ตัวเลข
Dim num As Integer = 10
Dim id As String =  num.ToString.PadLeft(5, "0")
'  id = 00010


Dim num As Integer = 10
Dim id As String =  Microsoft.VisualBasic.Right("00001", 5)
'  id = 00010

Read More

Create Text Editor by VB.Net

สร้าง Text Editor เอง ด้วย VB.Net

หน้าจอหลักๆ ของ Text Editor

ค้นหาคำ

แสดงโค้ด HTML

Download Semple Coding in vb.net Project

ภาษา C#

http://www.mediafire.com/download/r6k66lg6zol3jx1/TextControlSource.zip

Credit by : www.codeproject.com

ภาษา VB converse จาก C#

http://www.mediafire.com/download/6do555ybq43gt01/Editor.zip

Credit by : NuyingNaja

Read More

ตัวอย่างคำสั่ง SQL ที่ใช้ง่ายบ่อยๆ

ตัวอย่างคำสั่ง SQL ที่ใช้ง่ายบ่อยๆ

ตัวอย่าง database ดังนี้

Book_id	Book_name	Book_price
1	Vb.net 2008	50
2	Vb.net 2010	100
3	Vb.net 2011	200
4	Vb.net 2012	500
5	Vb.net 2013	500

1. คำสั่ง SQL นี้  ค้นหาข้อมูลง่ายๆ ที่ book_id  = 1
" SELECT * FROM  tbl_book  WHERE  book_id  = 1 "

Book_id	Book_name	Book_price
1	Vb.net 2008	50

2. คำสั่ง SQL นี้  ค้นหาข้อมูล เรียงลำดับข้อมูล น้อยไปมาก
" SELECT * FROM  tbl_book  ORDER BY  book_id "

Book_id	Book_name	Book_price
1	Vb.net 2008	50
2	Vb.net 2010	100
3	Vb.net 2011	200
4	Vb.net 2012	500
5	Vb.net 2013	500

3. คำสั่ง SQL นี้  ค้นหาข้อมูล เรียงลำดับข้อมูล มากไปน้อย
" SELECT * FROM  tbl_book  ORDER BY  book_id  DESC "

Book_id	Book_name	Book_price
5	Vb.net 2013	500
4	Vb.net 2012	500
3	Vb.net 2011	200
2	Vb.net 2010	100
1	Vb.net 2008	50

4. คำสั่ง SQL นี้  ค้นหาข้อมูล จัดกลุ่มข้อมูล
" SELECT  book_price  FROM  tbl_book  GROUP BY  book_price "

book_price

50

100

200

500

5. คำสั่ง SQL นี้ เพิ่มข้อมูล อีก 1 row
" INSERT INTO  tbl_book  (book_id , book_name, book_price) VALUES (6, 'AAA',20) "

Book_id	Book_name	Book_price
1	Vb.net 2008	50
2	Vb.net 2010	100
3	Vb.net 2011	200
4	Vb.net 2012	500
5	Vb.net 2013	500
6	AAA	20

6. คำสั่ง SQL  แก้ไขข้อมูล
" UPDATE  tbl_book  SET
book_name = 'Vb.net' ,
book_price = 111
WHERE book_id = 1 "

Book_id	Book_name	Book_price
1	Vb.net	111

7. คำสั่ง SQL  ลบข้อมูล
" DELETE FROM  tbl_book  WHERE  book_id = 5 "

Book_num	Book_name	Book_price
1	Vb.net 2008	50
2	Vb.net 2010	100
3	Vb.net 2011	200
4	Vb.net 2012	500
6	AAA	20

8. คำสั่ง SQL นี้ สามารถหาผลรวมที่ต้องการได้
" SELECT * FROM  tbl_book WHERE (book_id & 7) =  book_id "

Book_num	Book_name	Book_price
1	Vb.net 2008	50
2	Vb.net 2010	100
4	Vb.net 2012	300

9. คำสั่ง SQL นี้  count นับจำนวน row

" SELECT  count(*) as count_book FROM  tbl_book "

count_book

5

10. คำสั่ง SQL นี้ MAX 

" SELECT  max(book_price) as book_price FROM  tbl_book "

count_book

500

11. คำสั่ง SQL นี้ Update data and Select Data  ถ้าจะใช้กับ Insert data and Select Data ก็ได้

" UPDATE  tbl_book  SET
book_name = 'Vb.net' ,
book_price = (SELECT  max(book_price) as book_price FROM  tbl_book)
WHERE book_id = 1 "

12. คำสั่ง SQL  insert date time database
 "INSERT INTO tbl_book
(book_date)
 VALUES
(
 CURRENT_TIMESTAMP
 )"

12. คำสั่ง SQL  insert date time database for vb.net
 Dim date_now As Date = "#01/12/2014 11:11:11#"
 "INSERT INTO tbl_book
(book_date)
 VALUES
(
  IIf( date_now = Date.MinValue, "Null", "STR_TO_DATE('" & Format(date_now , "M/d/y h:m:s") & "', '%m/%d/%Y %h:%i:%s')")
 )"

13. ตัดตัวอักษร SQL Oracle
SELECT MAX(substr(book_id,2)) AS ID
FROM tbl_book ;

14. ตัดตัวอักษร SQL server
SELECT  MAX(RIGHT(book_id,2)) AS ID
FROM tbl_book
GROUP BY RIGHT(book_id,2);

Read More

ให้ Textbox พิมพ์ค่าได้แต่ตัวเลขเท่านั้น

กำหนดให้ TextBox รับค่าจาก Keyboard เป็นตัวเลขเท่านั้น
 
Private Sub txt_price_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_price.KeyPress
        Select Case Asc(e.KeyChar)
            Case 48 To 57
                e.Handled = False
            Case 8, 3, 22   ' Backspace = 8, Enter = 13, Delete = 46 ,Ctrl+C=3,Ctrl+V=22
                e.Handled = False
            Case 13
                e.Handled = True 'SendKeys.Send("{TAB}")
            Case 46         'Delete = 46 ,.
                If txt_price.Text.IndexOf(".") <> -1 Then
                    e.Handled = True
                Else
                    e.Handled = False
                End If
            Case Else
                e.Handled = True
        End Select 
End Sub

Private Sub txt_price_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_price.Leave
        If txt_price.Text <> "" Then
            txt_price.Text = Convert.ToDouble(txt_price.Text).ToString("#,###.#0")
        End If
End Sub

Read More

FTP Update Delete Download File in Server

Dim FtpHostname As String = '192.XX.XX.XXX'
Dim FtpHostname As String = "172.16.80.185"
Dim FtpUserName As String = "username"   
Dim FtpPassWord As String = "password"   
Dim FtpPort As String = "21"

Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click
       Dim response As Boolean
       Dim folderServer As String = '/Newfolder/'
       Dim pathFullName As String = 'D:\1234.jpg'
       Dim filename As String = '1234.jpg'
       Dim filenameNew As String = 'pic.jpg'


        Try 
            ' ====== Upload =======
            response = cSftp.FTPuploadFile(folderServer , pathFullName , filename )

            ' ====== Delete =======
            response = cSftp.FTPDeleteFile(folderServer & filename)

            ' ====== Rename =======
            response = cSftp.FTPuploadFile(folderServer & filename,  filenameNew )
 






        Catch ex As Exception
        End Try
End Sub

Function FTPuploadFile(ByVal folderServer As String, ByVal pathFullName As String, ByVal filename As String) As Boolean
        ' folderServer = '/Newfolder/'
        ' pathFullName = 'D:\1234.jpg'
        ' filename = '1234.jpg'
        Try
            Dim ftp As String = "ftp://" & FtpHostname & folderServer
            Dim Request As System.Net.FtpWebRequest = DirectCast(System.Net.WebRequest.Create(ftp & filename), System.Net.FtpWebRequest)

            Request.Credentials = New System.Net.NetworkCredential(FtpUserName, FtpPassWord)
            Request.Method = System.Net.WebRequestMethods.Ftp.UploadFile

            'Read File
            Dim File() As Byte = System.IO.File.ReadAllBytes(pathFullName)

            'Upload
            Dim Strz As System.IO.Stream = Request.GetRequestStream()
            Strz.Write(File, 0, File.Length)
            Strz.Close()
            Strz.Dispose()
            Return True
        Catch ex As Exception
            'MessageBox.Show("เกิดความผิดพลาดของไฟล์: " & _pathName & vbNewLine & ex.Message, "Messsage")
            Return False
        End Try 
End Function
 
' Delete File in Server Past FTP
 Function FTPDeleteFile(ByVal folderServerFilename As String) As Boolean
        Try
            Dim filename As String = "ftp://" & FtpHostname & "/" & folderServerFilename
            Dim ftpReq As FtpWebRequest = WebRequest.Create(filename)
            ftpReq.Method = WebRequestMethods.Ftp.DeleteFile
            ftpReq.Credentials = New NetworkCredential(FtpUserName, FtpPassWord)

            Dim ftpResp As FtpWebResponse = ftpReq.GetResponse
            Return True
        Catch ex As Exception
            'MsgBox("Error " + ex.ToString)
            Return False
        End Try 
End Function

' Rename File in Server Past FTP
Private Sub RenameFileWord(ByVal NameOld As String, ByVal NameNew As String)
        Dim reqFTP As FtpWebRequest = Nothing
        Dim ftpStream As Stream = Nothing
        reqFTP = DirectCast(FtpWebRequest.Create(New Uri(FtpHostname & NameOld)), FtpWebRequest)
        reqFTP.Method = WebRequestMethods.Ftp.Rename
        reqFTP.RenameTo = NameNew
        reqFTP.UseBinary = True
        reqFTP.Credentials = New NetworkCredential(FtpUserName, FtpPassWord)
        Dim response As FtpWebResponse = DirectCast(reqFTP.GetResponse(), FtpWebResponse)
        ftpStream = response.GetResponseStream()
        ftpStream.Close()
        response.Close()
 End Sub

Read More

Read TextFile or Notepad

Private Sub btnRead_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRead.Click   
        Dim sContents As String = GetFileContents("C:\note.txt")
        If sContents = "" Then
            Return
        Else
            Me.txtUserName.Text = Mid(sContents, 1, InStr(sContents, "|") - 1)
            Me.txtPassword.Text = Mid(sContents, InStr(sContents, "|") + 1, 900)
        End If
 End Sub


Private Sub btnOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOk.Click   
        SaveTextToFile(txtUserName.Text & "|" & Me.txtPassword.Text, "C:\note.txt")
 End Sub

Public Sub GetFileContents(ByVal FullPath As String) As String

        Dim strContents As String = ""
        Dim objReader As StreamReader
        Try
            objReader = New StreamReader(FullPath)
            strContents = objReader.ReadToEnd()
            objReader.Close()
            Return strContents
        Catch Ex As Exception
        End Try
        Return strContentsEnd Sub

Public Function SaveTextToFile(ByVal strData As String, ByVal FullPath As String) As Boolean

        Dim Contents As String = ""
        Dim bAns As Boolean = False
        Dim objReader As StreamWriter
        Try
            objReader = New StreamWriter(FullPath)
            objReader.Write(strData)
            objReader.Close()
            bAns = True
        Catch Ex As Exception
        End Try
        Return bAns

    End Function

Read More