0

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 :)

4
Contributors
6
Replies
7
Views
5 Years
Discussion Span
Last Post by JorgeM
0

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
0

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 :)

0

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 & "

0

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) & ";"

0

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

Thanks again :D

0

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.