code บันทึก แก้ไข ลบ ข้อมูล ของ vb2010 + sql server 2008

Dim Conn As OleDbConnection
Dim cmd As OleDbCommand
Dim sql as string

Conn = New OleDbConnection("Provider=sqloledb;server=" & SVName & ";database=" & DBName & ";uid=" & UID & ";pwd=")
Conn.Open()

sql = "insert into tablename(id,name) select (select isnull(max(id),0) + 1 from tablename),me.txtname.text"

sql = "update tablename set name='xxx' where id="& me.txtid.text

sql = "delete tablename where id="& me.txtid.text

Try

cmd = New OleDbCommand(Sql, Conn)
cmd.ExecuteNonQuery()

Catch ex As Exception
MessageBox.Show("ets_mc_plan Insert Data Incomplete as" & ex.Message, "Insert Data Error !!!", MessageBoxButtons.OK, MessageBoxIcon.Warning)

End Try

 Dim sqlADD As String 'เก็บคำสั่ง sql สำหรับเพิ่มข้อมูล
        Dim cm As New SqlCommand

        If txtid.Text = "" Or txtname.Text = "" Or txtsur.Text = "" Or txtno.Text = "" Or txtmoo.Text = "" Or txttumbon.Text = "" Or txtdistrict.Text = "" Or txtprovince.Text = "" Or txtzip.Text = "" Or mttel.Text = "" Or txtex.Text = "" Then
            MessageBox.Show("กรุณากรอกข้อมูลให้ครบด้วยครับ..", "คำเตือน", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning)
        Else
            MessageBox.Show("คุณต้องการบันทึกข้อมูลใช่หรือไม่?", "ยืนยัน", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question)

            sqlADD = "INSERT INTO teacher(t_id, t_name, t_sur, t_no, t_moo, t_road, t_tumbon, t_district, t_province, t_zip, t_tel, t_exp, t_pic)"
            sqlADD &= " VALUE ( '" & txtid.Text & "','" & txtname.Text & "' , '" & txtsur.Text & "' , '" & txtno.Text & "','" & txtmoo.Text & "','" & txttumbon.Text & "', '" & txtdistrict.Text & "','" & txtprovince.Text & "','" & txtzip.Text & "','" & mttel.Text & "','" & txtex.Text & "')"
            With cm
                .CommandType = CommandType.Text
                .CommandText = sqlADD
                '.Connection = Conn
                .ExecuteNonQuery()
            End With
            MessageBox.Show("เพิ่มข้อมูลเรียบร้อยแล้ว", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)

        End If

Private Sub btsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btsave.Click
        Dim sqlADD As String 'เก็บคำสั่ง sql สำหรับเพิ่มข้อมูล
        Dim cm As New SqlCommand

        
        If txtid.Text = "" Or txtname.Text = "" Or txtsur.Text = "" Or txtno.Text = "" Or txtmoo.Text = "" Or txttumbon.Text = "" Or txtdistrict.Text = "" Or txtprovince.Text = "" Or txtzip.Text = "" Or mttel.Text = "" Or txtex.Text = "" Then
            MessageBox.Show("กรุณากรอกข้อมูลให้ครบด้วยครับ..", "คำเตือน", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning)
            clear()

        Else
            MessageBox.Show("คุณต้องการบันทึกข้อมูลใช่หรือไม่?", "ยืนยัน", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question)

            sqlADD = "INSERT INTO teacher(t_id, t_name, t_sur, t_no, t_moo, t_road, t_tumbon, t_district, t_province, t_zip, t_tel, t_exp, t_pic)"
            sqlADD &= " VALUES ( '" & txtid.Text & "','" & txtname.Text & "' , '" & txtsur.Text & "' , '" & txtno.Text & "','" & txtmoo.Text & "','" & txttumbon.Text & "', '" & txtdistrict.Text & "','" & txtprovince.Text & "','" & txtzip.Text & "','" & mttel.Text & "','" & txtex.Text & "')"
            'Dim com As New SqlCommand(sqlADD, conn)
            With cm
                .CommandType = CommandType.Text
                .CommandText = sqlADD
                .Connection = conn
               .ExecuteNonQuery()
            End With
            MessageBox.Show("เพิ่มข้อมูลเรียบร้อยแล้ว", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
            clear()


        End If



    End Sub

Dim sqlEmployee As String = ""
        Dim comEmployee As SqlCommand = New SqlCommand
        Dim tmpMSG As String = ""

        If (txtEmpID.Text = "") Or (txtEmpName.Text = "") Or (txtEmpPosition.Text = "") Or (txtEmpTel.Text = "") Or (txtUser.Text = "") Or (txtPass.Text = "") Then
            MessageBox.Show("กรุณาป้อนข้อมูลสมาชิกให้ครบ !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            txtEmpID.Focus()
            Exit Sub
        End If

        Try
            Select Case ActionFlag
                Case "Add"
                    sqlEmployee = "INSERT INTO Employee (EmployeeID,Emp_Name,Emp_Tel,"
                    sqlEmployee &= " Emp_Position, Username, Password)"
                    sqlEmployee &= " VALUES ('" & txtEmpID.Text & "',"
                    sqlEmployee &= "'" & txtEmpName.Text & "',"

                    If OptMale.Checked = True Then
                        sqlEmployee &= "'M',"
                    ElseIf OptFeMale.Checked = True Then
                        sqlEmployee &= "'F',"
                    End If

                    sqlEmployee &= "'" & txtEmpPosition.Text & "',"
                    sqlEmployee &= "'" & txtEmpTel.Text & "',"
                    sqlEmployee &= "'" & txtUser.Text & "',"
                    sqlEmployee &= "'" & txtPass.Text & "')"
With Conn
                If .State = ConnectionState.Open Then .Close()
                .ConnectionString = strConn
                .Open()
            End With

            If ActionFlag = "Add" Then
                tmpMSG = "คุณต้องการเพิ่มข้อมูล ใช่หรือไม่ ?"
            ElseIf ActionFlag = "Edit" Then
                tmpMSG = "คุณต้องการแก้ไขข้อมูล ใช่หรือไม่ ?"
            End If

            If MessageBox.Show(tmpMSG, "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes Then
                With comEmployee
                    .CommandType = CommandType.Text
                    .CommandText = sqlEmployee
                    .Connection = Conn
                    .ExecuteNonQuery()
                End With
            End If
        Catch ErrProcess As Exception
            MessageBox.Show("ไม่สามารถเพิ่มสมาชิกใหม่ได้ เนื่องจาก " & ErrProcess.Message, "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            Exit Sub
        End Try

        MessageBox.Show("บันทึกลงฐานข้อมูล เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim sqlMember As String = ""
        Dim comMember As SqlCommand = Conn.CreateCommand()
        Dim tmpMSG As String = ""


        If (txtMemberID.Text = "") Or (txtName.Text = "") Or (txtAddress.Text = "") Or (txtTel.Text = "") Then
            MessageBox.Show("กรุณาป้อนข้อมูลสมาชิกให้ครบ !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            txtMemberID.Focus()
            Exit Sub
        End If

        If cboPosition.Text = "" Then
            MessageBox.Show("กรุณาระบุประเภทสมาชิก  !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            cboPosition.Focus()
            Exit Sub
        End If

        Try
            Select Case ActionFlag
                Case "Add"
                    sqlMember = "INSERT INTO Member (MemberID,Mem_Num,Mem_Name,Address,Sex,Tel,PositionID)"
                    sqlMember &= " VALUES ('" & lblMemID.Text & "',"
                    sqlMember &= "'" & txtMemberID.Text & "',"
                    sqlMember &= "'" & txtName.Text & "',"
                    sqlMember &= "'" & txtAddress.Text & "',"

                    If OptMale.Checked = True Then
                        sqlMember &= "'M',"
                    ElseIf OptFemale.Checked = True Then
                        sqlMember &= "'F',"
                    End If

                    sqlMember &= "'" & txtTel.Text & "',"
                    sqlMember &= "'" & CStr(cboPosition.SelectedValue) & "',"

                    If CStr(cboPosition.SelectedValue) = "1" Or CStr(cboPosition.SelectedValue) = "2" Then
                        sqlMember &= "'0','1')"
                    ElseIf CStr(cboPosition.SelectedValue) = "0" Then
                        sqlMember &= "'1','0')"
                    End If

                Case "Edit"
                    sqlMember = "UPDATE Member "
                    sqlMember &= " SET Mem_Num='" & txtMemberID.Text & "',"
                    sqlMember &= " Mem_Name='" & txtName.Text & "',"
                    sqlMember &= " Address='" & txtAddress.Text & "',"
                    If OptMale.Checked = True Then
                        sqlMember &= " Sex='M',"
                    ElseIf OptFemale.Checked = True Then
                        sqlMember &= " Sex='F',"
                    End If

                    sqlMember &= " Tel='" & txtTel.Text & "',"
                    sqlMember &= " PositionID='" & CStr(cboPosition.SelectedValue) & "'"
                    sqlMember &= " WHERE (MemberID='" & lblMemID.Text & "')"
            End Select

            With Conn
                If .State = ConnectionState.Open Then .Close()
                .ConnectionString = strConn
                .Open()
            End With


            If ActionFlag = "Add" Then
                tmpMSG = "คุณต้องการเพิ่มข้อมูล ใช่หรือไม่ ?"
            ElseIf ActionFlag = "Edit" Then
                tmpMSG = "คุณต้องการแก้ไขข้อมูล ใช่หรือไม่ ?"
            End If

            If MessageBox.Show(tmpMSG, "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes Then
                With comMember
                    .CommandType = CommandType.Text
                    .CommandText = sqlMember
                    .Connection = Conn
                    .ExecuteNonQuery()
                End With
            End If
        Catch ErrProcess As Exception
            MessageBox.Show("ไม่สามารถเพิ่มสมาชิกใหม่ได้ เนื่องจาก " & ErrProcess.Message, "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            Exit Sub
        End Try

        MessageBox.Show("บันทึกลงฐานข้อมูล เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)

        grbSearch.Enabled = True

        ControlStatus(False)

        btnAdd.Enabled = False
        btnEdit.Enabled = False

        btnSave.Visible = False
        btnCancel.Visible = False
        ClearData()
        grdEquipNotReturn.DataSource = Nothing

        btnPrint.Enabled = False

        cmdMoveFirst.Enabled = False
        cmdMovePrevious.Enabled = False
        cmdMoveNext.Enabled = False
        cmdMoveLast.Enabled = False

        lblShowPositions.Text = ""
        txtSearch.Text = ""
        txtSearch.Focus()
    End Sub

dim sql as string
sql = "INSERT INTO OT(No_OT,Date_OT)"
sql &= " VALUES('{0}','{1}'"
sql = String.Format(sql,lblNo_OT.Text,Format(Otdate.text,"MM/dd/yyyy")

dim sql as string
sql = "INSERT INTO OT(No_OT,Date_OT)"
sql &= " VALUES("
sql &= "'" & lblNo_OT.Text & "'"
sql &= ",'" & Format(Otdate.text,"MM/dd/yyyy") & "')"

        Dim dtset As New DataSet()
        If code.Text = "" Then

            bname.Text = ""
            gtype.Text = ""
            gday.Text = ""
            gnum.Text = ""
            price.Text = ""

            MsgBox("กรุณากรอกข้อมูลให้ครบด้วยทุกช่องด้วย", MsgBoxStyle.Information, "ระะบบแจ้งเตือน")

        Else
            ds.Clear()
            Dim cn As New OleDbCommandBuilder(da)
            objcurrencymanager.EndCurrentEdit()
            Dim irecordchanged As Integer = da.Update(ds, "st_save")
            MsgBox("บันทึกข้อมูลสมาชิกลงฐานข้อมูลเรียบร้อยแล้ว" & ControlChars.Lf & _
                    "จำนวนเรคคอร์ดที่มีการเปลี่ยนแปลงเท่ากับ " & irecordchanged & " เรคคอร์ด", _
                    , "บันทึกข้อมูลสมาชิก")
            dtset.Clear()
            code.Text = ""
            bname.Text = ""
            gtype.Text = ""
            gday.Text = ""
            gnum.Text = ""
            price.Text = ""
        End If

Dim sqlADD As String 'เก็บคำสั่ง sql สำหรับเพิ่มข้อมูล
Dim cm As New StdPicture
sqlADD = "INSERT INTO "
sqlADD = "INSERT INTO vacancies_Job(code,order,Product_Name,number,Tel,send,mail,address)"
sqlADD = "VALUES ('" & code.Text & "','" & order.Text & "','" & Product_Name.Text & "','" & Tel.Text & "','" & send.Text & "','" & mail.Text & "','" & address.Text & "',)"
'ใช้ oledbcommand ในการเพิ่มข้อมูล
With cm
    .CommandType = CommandType.Text
    .CommandText = sqlADD
    .Connection = Conn
    .Transaction = tr
    .Execute NonQuery()
End With

 Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click

        Try

            If txtCusID.Text = "" Then
                MessageBox.Show("Please Select Data", "Update", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                txtCusID.Focus()
                Exit Sub
            End If
            cm = New OleDbCommand("UPDATE tbFile SET FileNo=@FileNo,Date=@Date,CustomerID=@CustomerID,CustomerName=@CustomerName,TypeName=@TypeName,FileID=@FileID,FileName=@FileName,FilePath1=@FilePath1,FilePath2=@FilePath2,FilePath3=@FilePath3,Remark=@Remark Where FileNo=@FileNo", conn)
            cm.Parameters.AddWithValue("@FileNo", txtFileNo.Text)
            cm.Parameters.AddWithValue("@Date", Format(CDate(dtpDate.Text), "yyyy-MM-dd"))
            cm.Parameters.AddWithValue("@CustomerID", txtCusID.Text)
            cm.Parameters.AddWithValue("@CustomerName", txtCusName.Text)
            cm.Parameters.AddWithValue("@TypeName", cbType.Text)
            cm.Parameters.AddWithValue("@FileID", txtFileID.Text)
            cm.Parameters.AddWithValue("@FileName", txtFileName.Text)
            cm.Parameters.AddWithValue("@FilePath1", txtSave1.Text)
            cm.Parameters.AddWithValue("@FilePath2", txtSave2.Text)
            cm.Parameters.AddWithValue("@FilePath3", txtSave3.Text)
            cm.Parameters.AddWithValue("@Remark", txtRemark.Text)
            cm.ExecuteNonQuery()


            MessageBox.Show("Update successful", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information)

            ShowFile()
            AutoFileNo()
            AllClear()
        Catch ex As Exception

        End Try
    End Sub