Delete row using ms access

[Ameer89]

Hello all :)

I am still a beginner at ASP.NET and VB.NET so I could use all the guidance I can get. So thanks in advance :)

I am building a web application using Visual Web Developer as the IDE and Microsoft Access as the database. The application allows users to delete records. I have a text box which lets users type in the record they want to delete and a delete button which performs the deletion. However wheneve I click the delete button I get a "missing or error in syntax" message at this line: Employee ID = " &TextBox1.Text & ";"

Here is the page load code:

   <script runat="server" language="VB">



       Protected Sub Page_Load(sender As Object, e As System.EventArgs)
           Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Users\toshiba\Documents\Employees.mdb;User Id=admin;Password=;")
           Try
               cn.Open()

               Dim da As New OleDbDataAdapter("select * from Employees", cn)
               Dim dt As New DataTable
               da.Fill(dt)
               da.Dispose()

               cn.Dispose()
               Me.GridViewAdmin.DataSource = dt
               Me.GridViewAdmin.DataBind()
               GridViewAdmin.DataSource = dt.DefaultView

           Catch sqlex As OleDbException

               Response.Write(sqlex.Message & " - " & sqlex.Source)

           Catch ex As Exception
               Response.Write(ex.Message & " - " & ex.Source)


           Finally
               cn.Close()
           End Try
       End Sub

and here is the delete code:

       Protected Sub Button1_Click(sender As Object, e As System.EventArgs)

           Dim cnnOLEDB As New OleDbConnection

           Dim cmdOLEDB As New OleDbCommand

           Dim cmdInsert As New OleDbCommand

           Dim cmdUpdate As New OleDbCommand

           Dim cmdDelete As New OleDbCommand
           Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Users\toshiba\Documents\Employees.mdb;User Id=admin;Password=;"
           cnnOLEDB.ConnectionString = strConnectionString

           cnnOLEDB.Open()


           If TextBox1.Text <> "" Then

               cmdDelete.CommandText = "DELETE FROM Employees WHERE Employee ID = " &
                   TextBox1.Text & ";"

               'MsgBox(cmdDelete.CommandText)

               cmdDelete.CommandType = CommandType.Text

               cmdDelete.Connection = cnnOLEDB

               cmdDelete.ExecuteNonQuery()

               MsgBox(TextBox1.Text = "Record deleted.")



               cmdDelete.Dispose()

           Else

               MsgBox("Enter the required values:")

           End If


       End Sub

thank you :)

[kvprajapati]

Wrap the page_load event code in IsPostBack block

 Protected Sub Page_Load(sender As Object, e As System.EventArgs)
   IF Not IsPostBack Then
    Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:
    ....
   End IF
End Sub

[Ameer89]

Heey avd :)
Thanks for replying.

I tried your solution and now I have a different error at a different line.
The error is: Syntax error (missing operator) in query expression 'Employee ID = TextBox1.Text & ";".
The error is from this line: cmdDelete.ExecuteNonQuery()

Am I missing something?

Thanks again :)

[JorgeM]

DELETE FROM Employees WHERE Employee ID

If your SQL field has a space between two words, you need to wrap the name within brackets. For example,

DELETE FROM Employees WHERE [Employee ID] = " & TextBox1.Text & "

[GrabItHard]

Assuming that the employee id field is a number field the

cmdDelete.CommandText = "DELETE FROM Employees WHERE Employee ID = " &
TextBox1.Text & ";"

should read cmdDelete.CommandText = "DELETE FROM Employees WHERE Employee ID = " & Val(TextBox1.Text) & ";"

[Ameer89]

IT WORKED! :D
Thank you sooo much guys for all your help :) I tried everything you told me and it worked :)

Thanks again :D

[JorgeM]

It would help others if you can elaborate on what you actually did that worked for you. there were several suggestions provided in this thread.