Why done work is it some thing worng i only wan edit one or delete one of it?

Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click

        If String.IsNullOrWhiteSpace(txtadmin.Text) Or String.IsNullOrWhiteSpace(txtname.Text) Or String.IsNullOrWhiteSpace(txtcourse.Text) Or String.IsNullOrWhiteSpace(txtic.Text) Or String.IsNullOrWhiteSpace(txtgender.Text) Or String.IsNullOrWhiteSpace(txtaddress.Text) Or String.IsNullOrWhiteSpace(DateTimePicker1.Value) Or String.IsNullOrWhiteSpace(txttel.Text) Or String.IsNullOrWhiteSpace(txtemail.Text) Or String.IsNullOrWhiteSpace(txttype.Text) Then
            MessageBox.Show("Please complete the on the box.", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End If

        Try
            Dim conn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\Database1.accdb")


            Dim sql As String = "UPDATE tbl_datain SET UserName='" & txtname.Text & "' AdminNo = '" & txtadmin.Text & "'Course Title = '" & txtcourse.Text & "'""'IC No = '" & txtic.Text & "'""'Gender = '" & txtgender.Text & "'""'Address= '" & txtaddress.Text & "'""'[Date of Birth] = '" & txtbirth.Text & "'Tel No = '" & txttel.Text & "'""'Email = '" & txtemail.Text & "''Personl = '" & txttype.Text & "'"


            Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)

            sqlCom.Connection = conn
            conn.Open()

            Dim sqlRead As System.Data.OleDb.OleDbDataReader = sqlCom.ExecuteReader()

            If sqlRead.Read() Then

                MessageBox.Show("one eidt is Successfully'")

            Else

                MessageBox.Show("Have a great day..!!", "Authentication Failure", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

                txtname.Text = ""
                txtadmin.Text = ""
                txtcourse.Text = ""
                txtic.Text = ""
                txtgender.Text = ""
                txtaddress.Text = ""
                txtbirth.Text = ""
                txttel.Text = ""
                txtemail.Text = ""
                txttype.Text = ""

                txtadmin.Focus()
            End If

        Catch ex As Exception
            MessageBox.Show("Failed to connect to Database..", "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try


    End Sub





 Private Sub btnremove_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnremove.Click

        If String.IsNullOrWhiteSpace(txtadmin.Text) Or String.IsNullOrWhiteSpace(txtname.Text) Or String.IsNullOrWhiteSpace(txtcourse.Text) Or String.IsNullOrWhiteSpace(txtic.Text) Or String.IsNullOrWhiteSpace(txtgender.Text) Or String.IsNullOrWhiteSpace(txtaddress.Text) Or String.IsNullOrWhiteSpace(DateTimePicker1.Value) Or String.IsNullOrWhiteSpace(txttel.Text) Or String.IsNullOrWhiteSpace(txtemail.Text) Or String.IsNullOrWhiteSpace(txttype.Text) Then
            MessageBox.Show("Please complete the on the box.", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End If

        Try
            Dim conn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\Database1.accdb")


            Dim sql As String = "DELETE * FROM tbl_datain WHERE UserName='" & txtname.Text & "' AdminNo = '" & txtadmin.Text & "'Course Title = '" & txtcourse.Text & "'""'IC No = '" & txtic.Text & "'""'Gender = '" & txtgender.Text & "'""'Address= '" & txtaddress.Text & "'""'[Date of Birth] = '" & txtbirth.Text & "'Tel No = '" & txttel.Text & "'""'Email = '" & txtemail.Text & "''Personl = '" & txttype.Text & "'"


            Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)

            sqlCom.Connection = conn
            conn.Open()

            Dim sqlRead As System.Data.OleDb.OleDbDataReader = sqlCom.ExecuteReader()

            If sqlRead.Read() Then

                MessageBox.Show("one info has delete")

            Else

                MessageBox.Show("Have a great day..!!", "Authentication Failure", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

                txtname.Text = ""
                txtadmin.Text = ""
                txtcourse.Text = ""
                txtic.Text = ""
                txtgender.Text = ""
                txtaddress.Text = ""
                txtbirth.Text = ""
                txttel.Text = ""
                txtemail.Text = ""
                txttype.Text = ""

                txtadmin.Focus()
            End If

        Catch ex As Exception
            MessageBox.Show("Failed to connect to Database..", "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub
End Class

Recommended Answers

All 3 Replies

Wrong SQL Statement and syntax for both operation

For UpDate it should be

            Dim sql As String = "UPDATE tbl_datain SET UserName='" & txtname.Text & "' AdminNo ='" & txtadmin.Text & "' [Course Title] ='" & txtcourse.Text & "' [IC No] ='" & txtic.Text & "' Gender ='" & txtgender.Text & "' Address='" & txtaddress.Text & "' [Date of Birth] ='" & txtbirth.Text & "' [Tel No] ='" & txttel.Text & "' Email ='" & txtemail.Text & "' Personl ='" & txttype.Text & "' Where UserID ='" & txtuserid.Text & "'"

            Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)
            sqlCom.Connection = conn
            conn.Open()
            Dim xedt As Integer = sqlCom.ExecuteReader()
            sqlCom.Dispose()
            conn.Close()

            If xedt>0 Then
                MessageBox.Show("one eidt is Successfully'")
            Else
                MessageBox.Show("Have a great day..!!", "Authentication Failure", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                txtname.Text = ""
                txtadmin.Text = ""
                txtcourse.Text = ""
                txtic.Text = ""
                txtgender.Text = ""
                txtaddress.Text = ""
                txtbirth.Text = ""
                txttel.Text = ""
                txtemail.Text = ""
                txttype.Text = ""
                txtadmin.Focus()
            End If

For Delete it should be

Dim sql As String = "DELETE FROM tbl_datain WHERE UserName='" & txtname.Text & "' And UserID ='" & txtuserid.Text & "'"
            Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)
            sqlCom.Connection = conn
            conn.Open()
            Dim xdel as Integer = sqlCom.ExecuteReader()
            sqlCom.Dispose()
            conn.Close()
            If xdel > 0 Then
                MessageBox.Show("one info has delete")
            Else
                MessageBox.Show("Have a great day..!!", "Authentication Failure", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                txtname.Text = ""
                txtadmin.Text = ""
                txtcourse.Text = ""
                txtic.Text = ""
                txtgender.Text = ""
                txtaddress.Text = ""
                txtbirth.Text = ""
                txttel.Text = ""
                txtemail.Text = ""
                txttype.Text = ""
                txtadmin.Focus()
            End If

here have error

Dim xedt As Integer = sqlCom.ExecuteReader()
Value of type 'System.Data.OleDb.OleDbDataReader' cannot be converted to 'Integer'.

then i change to

Dim xedit As System.Data.OleDb.OleDbDataReader = sqlCom.ExecuteReader()

   *   *  **If xedit > 0 Then****
            MessageBox.Show("one eidt is Successfully'")
        Else
            MessageBox.Show("Have a great day..!!", "Authentication Failure", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtname.Text = ""
            txtadmin.Text = ""
            txtcourse.Text = ""
            txtic.Text = ""
            txtgender.Text = ""
            txtaddress.Text = ""
            txtbirth.Text = ""
            txttel.Text = ""
            txtemail.Text = ""
            txttype.Text = ""
            txtadmin.Focus()
        End If

        sqlCom.Dispose()
        conn.Close()

**Error 1 Operator '>' is not defined for types 'System.Data.OleDb.OleDbDataReader' and 'Integer'. **

Sorry! I did some mistake to edit your codes. It will be sqlCom.ExecuteNonQuery() in lieu of sqlCom.ExecuteReader() in both procedures.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.