can anybody help me solve this error "OleDbException was unhandled"?

Question

This part of the code is to let lecturer publish consultation schedule, unfortunately i experienced another error as shown in title, after done asking a question at Can anybody help me to solve this error "NullReferenceException was unhandled"? ,and that error is probably resolved. Now, my problem is when i clicked on the publish button which is like saving all the labels(that has the color lime as shown in the form) to an access file, and those labels already declared as(lime = available, red = unavailable).

This is the form

The error is at Dim f As Integer = cmd.ExecuteNonQuery()

    Public Class ConsultationSchedule

Private Sub publishbutton_Click(sender As Object, e As EventArgs) Handles publishbutton.Click
    Dim label As String = "Label"

    Dim time1 As String = vbNull
    Dim time2 As String = vbNull
    Dim time3 As String = vbNull
    Dim time4 As String = vbNull
    Dim time5 As String = vbNull

    Dim day1 As String = vbNull
    Dim day2 As String = vbNull
    Dim day3 As String = vbNull
    Dim day4 As String = vbNull
    Dim day5 As String = vbNull

    Dim available1 As String = vbNull
    Dim available2 As String = vbNull
    Dim available3 As String = vbNull
    Dim available4 As String = vbNull
    Dim available5 As String = vbNull

    For i = 11 To 15
        time1 = Label7.Text

        For k = 16 To 20
            time2 = Label8.Text

            For t = 21 To 25
                time3 = Label9.Text

                For u = 26 To 30
                    time4 = Label10.Text

                    For bg = 33 To 37
                        time5 = Label32.Text

                    Next
                Next
            Next
        Next
    Next

    For i = 11 To 37
        If (i = 11) Then
            day1 = Label2.Text
            If Not Me.Controls(label & i.ToString) Is Nothing Then
                If (Me.Controls(label & i.ToString).BackColor = Color.Lime) Then
                    available1 = "Available"
                Else
                    available1 = "Unavailable"
                End If
            End If

        ElseIf (i = 16) Then
            day1 = Label2.Text
            If Not Me.Controls(label & i.ToString) Is Nothing Then
                If (Me.Controls(label & i.ToString).BackColor = Color.Lime) Then
                    available2 = "Available"
                Else
                    available2 = "Unavailable"
                End If
            End If

        ElseIf (i = 21) Then
            day1 = Label2.Text
            If Not Me.Controls(label & i.ToString) Is Nothing Then
                If (Me.Controls(label & i.ToString).BackColor = Color.Lime) Then
                    available3 = "Available"
                Else
                    available3 = "Unavailable"
                End If
            End If

        ElseIf (i = 26) Then
            day1 = Label2.Text
            If Not Me.Controls(label & i.ToString) Is Nothing Then
                If (Me.Controls(label & i.ToString).BackColor = Color.Lime) Then
                    available4 = "Available"
                Else
                    available4 = "Unavailable"
                End If
            End If

        ElseIf (i = 33) Then
            day1 = Label2.Text
            If Not Me.Controls(label & i.ToString) Is Nothing Then
                If (Me.Controls(label & i.ToString).BackColor = Color.Lime) Then
                    available5 = "Available"
                Else
                    available5 = "Unavailable"
                End If
            End If
        End If
    Next
    For ht = 11 To 37
        If (ht = 11) Then
            day1 = Label2.Text
        ElseIf (ht = 16) Then
            day1 = Label2.Text
        ElseIf (ht = 21) Then
            day1 = Label2.Text
        ElseIf (ht = 26) Then
            day1 = Label2.Text
        ElseIf (ht = 33) Then
            day1 = Label2.Text
        End If
    Next
    For k = 11 To 37
        If (k = 12) Then
            day2 = Label3.Text
        ElseIf (k = 17) Then
            day2 = Label3.Text
        ElseIf (k = 22) Then
            day2 = Label3.Text
        ElseIf (k = 27) Then
            day2 = Label3.Text
        ElseIf (k = 34) Then
            day2 = Label3.Text
        End If
    Next
    For t = 11 To 37
        If (t = 13) Then
            day3 = Label4.Text
        ElseIf (t = 18) Then
            day3 = Label4.Text
        ElseIf (t = 23) Then
            day3 = Label4.Text
        ElseIf (t = 28) Then
            day3 = Label4.Text
        ElseIf (t = 35) Then
            day3 = Label4.Text
        End If
    Next
    For u = 11 To 37
        If (u = 14) Then
            day4 = Label5.Text
        ElseIf (u = 19) Then
            day4 = Label5.Text
        ElseIf (u = 24) Then
            day4 = Label5.Text
        ElseIf (u = 29) Then
            day4 = Label5.Text
        ElseIf (u = 36) Then
            day4 = Label5.Text
        End If
    Next
    For y = 11 To 37
        If (y = 15) Then
            day5 = Label6.Text
        ElseIf (y = 20) Then
            day5 = Label6.Text
        ElseIf (y = 25) Then
            day5 = Label6.Text
        ElseIf (y = 30) Then
            day5 = Label6.Text
        ElseIf (y = 37) Then
            day5 = Label6.Text
        End If
    Next

    Dim sql As String
    Dim sql2 As String
    Dim sql3 As String
    Dim sql4 As String
    Dim sql5 As String

    Dim cmd As OleDbCommand
    Dim cmd2 As OleDbCommand
    Dim cmd3 As OleDbCommand
    Dim cmd4 As OleDbCommand
    Dim cmd5 As OleDbCommand

    Dim conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=scheduledatabase.accdb;Persist Security Info=False;")
    sql = "INSERT INTO consultationschedule([Time], weekDay, Available)VALUES(@time1,@day1,@available1)"
    sql2 = "INSERT INTO consultationschedule([Time], weekDay, Available)VALUES(@time2,@day2,@available2)"
    sql3 = "INSERT INTO consultationschedule([Time], weekDay, Available)VALUES(@time3,@day3,@available3)"
    sql4 = "INSERT INTO consultationschedule([Time], weekDay, Available)VALUES(@time4,@day4,@available4)"
    sql5 = "INSERT INTO consultationschedule([Time], weekDay, Available)VALUES(@time5,@day5,@available5)"

    Using (conn)
        conn.Open()
        cmd = New OleDbCommand(sql, conn)
        cmd.Parameters.Add(New OleDbParameter("@Time", time1))
        cmd.Parameters.Add(New OleDbParameter("@weekDay", day1))
        cmd.Parameters.Add(New OleDbParameter("@Available", available1))
        cmd2 = New OleDbCommand(sql2, conn)
        cmd2.Parameters.Add(New OleDbParameter("@Time", time2))
        cmd2.Parameters.Add(New OleDbParameter("@weekDay", day2))
        cmd2.Parameters.Add(New OleDbParameter("@Available", available2))
        cmd3 = New OleDbCommand(sql3, conn)
        cmd3.Parameters.Add(New OleDbParameter("@Time", time3))
        cmd3.Parameters.Add(New OleDbParameter("@weekDay", day3))
        cmd3.Parameters.Add(New OleDbParameter("@Available", available3))
        cmd4 = New OleDbCommand(sql4, conn)
        cmd4.Parameters.Add(New OleDbParameter("@Time", time4))
        cmd4.Parameters.Add(New OleDbParameter("@weekDay", day4))
        cmd4.Parameters.Add(New OleDbParameter("@Available", available4))
        cmd5 = New OleDbCommand(sql5, conn)
        cmd5.Parameters.Add(New OleDbParameter("@Time", time5))
        cmd5.Parameters.Add(New OleDbParameter("@weekDay", day5))
        cmd5.Parameters.Add(New OleDbParameter("@Available", available5))

        Dim f As Integer = cmd.ExecuteNonQuery()
        If (f >= 1) Then
            ToolStrip1.Text += f.ToString & " record added successfully"
        Else
            ToolStrip1.Text += "Unable to add record"
        End If
    End Using
End Sub

The message is telling you that an exception was thrown that you didn't catch. *That exception* contains the information about the error that you're looking for. Catch the exception and find out what that information is. (If I were to *guess*... You're adding 15 parameters to a query that has *3*.) — David, Apr 23 '17 at 11:07
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll Additional information: Syntax error in INSERT INTO statement. Is that the message you're talking about? — bwraths, Apr 23 '17 at 11:11
If Im adding 15 parameters to a query that has 3, does that mean i need to add more different query with different name to it including my database? — bwraths, Apr 23 '17 at 11:18
If you want to add 5 records then I imagine you'd execute this query 5 times, each with the corresponding parameters. — David, Apr 23 '17 at 11:23
The important part of that message is `"Syntax error in INSERT"` statement, probably because OLE and Access do not support named parameters. You have to use `?` placeholders and set parameters in the order you need them. — Joel Coehoorn, Apr 23 '17 at 14:07
I've just edited and removed that withdraw part of the code. It shouldn't be there now. — bwraths, Apr 23 '17 at 16:33

Wai Fung Au Yeung · Accepted Answer · 2017-04-23T12:58:12.343

2

I suggest you create a new method for creating a record:

 Private Sub AddRecords(ByVal time As String, ByVal day As String, ByVal available As String)

        Dim sql As String
        Dim cmd As OleDbCommand

        Dim conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=scheduledatabase.accdb;Persist Security Info=False;")
        sql = "INSERT INTO consultationschedule(Time, weekDay, Available)VALUES(@Time,@weekDay,@Available)"

        Using (conn)
            conn.Open
            cmd = New OleDbCommand(sql, conn)
            cmd.Parameters.AddWithValue("@Time", time)
            cmd.Parameters.AddWithValue("@weekDay", day)
            cmd.Parameters.AddWithValue("@Available", available)
            Dim f As Integer = cmd.ExecuteNonQuery()
            If (f >= 1) Then
                ToolStrip1.Text += f.ToString & " record added successfully"
            Else
                ToolStrip1.Text += "Unable to add record"

            End If
            MessageBox.Show("User Widrawed", "Widrawed", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Using
    End Sub

After that you could call the method for adding records

AddRecords(time1, day1, available1)
AddRecords(time2, day2, available2)
AddRecords(time3, day3, available3)
AddRecords(time4, day4, available4)
AddRecords(time5, day5, available5)

edited Apr 23 '17 at 12:58

answered Apr 23 '17 at 12:47

Wai Fung Au Yeung

131
4

Unfortunately I have no luck in solving the error, I've used the code that you provided, and replaced it on the whole method, and i also added additional sql2 to 5. Kindly check out at the edited at my post above. – bwraths Apr 23 '17 at 16:29
You could try this : cmd.Parameters.Add(new OleDbParameter("@Time", time)) – Wai Fung Au Yeung Apr 23 '17 at 16:45
Just edited, however the error still persists at : Dim f As Integer = cmd.ExecuteNonQuery() >>An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll Additional information: Syntax error in INSERT INTO statement.< – bwraths Apr 23 '17 at 16:55
Oke I think I have found the error! 'Time' is a reserved word , the correct usage should be "INSERT INTO consultationschedule([Time], weekDay, Available)VALUES(@Time,@weekDay,@Available)" – Wai Fung Au Yeung Apr 23 '17 at 17:16
Omg, the error finally disappears, now I can save into my database. Thanks a lot @Wai Fung Au Yeung !! – bwraths Apr 23 '17 at 17:24
Okay, so the data can be saved into the database, but my data shows the same value in each row. Would you be able to guide me on where should I look into? I've just edited the code at my post above. – bwraths Apr 23 '17 at 17:54
If you are still using your code , it will indeed show the same data everytime you try to add a record. If you want the easy way, you should do cmd.ExecuteNonQuery() cmd2.ExecuteNonQuery() cmd3.ExecuteNonQuery() cmd4.ExecuteNonQuery() cmd5.ExecuteNonQuery() Though this is not the best practice – Wai Fung Au Yeung Apr 23 '17 at 18:01

can anybody help me solve this error "OleDbException was unhandled"?

1 Answers1

Linked