Link to home
Start Free TrialLog in
Avatar of Zaki Moustapha
Zaki Moustapha

asked on

VB.NET DELETE QUERY

Hello Expert,

I have a windows form that contains a CheckedListBox Which Display all employees in my Database Table (MS-Access 2007). When the Form is load my CheckedListBox Display Employees with no errors.

I want the user to delete more than employee at a time so here is my error message:
System.Data.OleDb.OleDbException: 'Characters found after end of SQL statement.'

Please check the code:

 Private Sub DeleteRecord()

        If MsgBox("Les employés sélectionnés seront supprimés! Es-tu sur de vouloir continuer?", MsgBoxStyle.YesNo, "Guarde Security Service - Supprimer") = MsgBoxResult.Yes Then
           

            Dim Counter As Integer
            Dim DelString As String = "" 

            For Each index As String In cbLstEmployee.CheckedItems

                OLEDBControl.AddParams("@empName" & Counter, index)

                DelString += "DELETE FROM Employees WHERE empName=@empName" & Counter & ";"
                Counter += 1
            Next

            OLEDBControl.ExecQuery(DelString)

            'Report and Abort Error...
            'If OLEDBControl.HasException Then Exit Sub

            MsgBox("La suppression a été effectuée avec succès!", MsgBoxStyle.Information, "Guarde Security Service - Supprimer")

            'Refresh CheckedListBox...
            FetchEmployees()
        End If
    End Sub

Kindly note that if the user Checked Only One Employee the Code execute successfully without errors
otherwise I got the error message mentioned above.

Many thanks in advance for your precious time and considertion.
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

The error message is true; don't append the SQL DelString but reset it each time:

DelString = "DELETE FROM Employees WHERE empName=@empName" & Counter & ";"

Open in new window

Avatar of Zaki Moustapha
Zaki Moustapha

ASKER

how to reset it please
dear expert give me a detailed example please
Use the code line I posted.
I used the code line you posted but It Deletes only One Record it is not Deleting multiple record when I checked more than one employee
I'm not sure. It is not the same code line as the one you posted.
my dear I swear that I did exactly why you asked me to do but it's not working.

if you wont i can send you the project but tell me how to send it to you
Can I replace the Parameter @empId=@empId with something like this:

 Where empId = '" & txtBox1.text &"' but I dont know with checkedlistbox how to do it

I think the problem now is the parameter
Sir I am really hopeless :(
You can put  executeQuery inside foreach block otherwise only delete one record
SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Mr. Gustav it Works Perfect....I am so happy!
Great!
My comment corrected Gustav's solution. Some credit would have waited.