[RESOLVED] Unsure if SQL or VB issue

The error I'm receiving is a syntax issue in the SQL INSERT INTO statement but I can't find anything wrong with it.

Code:

---

Imports System.Data.OleDb

Module taxiIE

    Dim input As String = ""
    Dim quit As Boolean = False
    Dim failedMenu_attempts As Integer = 0

    Structure expensesData
        Dim jobDate As Date
        Dim gas As Double
        Dim food As Double
        Dim carWash As Double
        Dim vacuum As Double
        Dim parts As Double
        Dim misc As Double
        Dim expenseTotal As Double
    End Structure

    Dim expenses As expensesData

    Dim command As OleDbCommand
    Dim connection As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Taxi.accdb")

    Sub Main()
        Do
            Select Case menu()
                Case "0"
                    quit = True
                Case "1"
                    failedMenu_attempts = 0
                    Console.WriteLine("1")
                    Console.ReadLine()
                    Console.Clear()
                Case "2"
                    failedMenu_attempts = 0
                    Console.WriteLine("2")
                    Console.ReadLine()
                    Console.Clear()
                Case "3"
                    failedMenu_attempts = 0
                    Console.WriteLine("2")
                    Console.ReadLine()
                    Console.Clear()
                Case "4"
                    failedMenu_attempts = 0
                    Console.WriteLine("4")
                    Console.ReadLine()
                    Console.Clear()
                Case "5"
                    Console.Clear()
                    failedMenu_attempts = 0
                    enterExpenses()
                    submitExpenses()
                Case "6"
                    failedMenu_attempts = 0
                    Console.WriteLine("6")
                    Console.ReadLine()
                    Console.Clear()
                Case "7"
                    failedMenu_attempts = 0
                    Console.WriteLine("7")
                    Console.ReadLine()
                    Console.Clear()
                Case "8"
                    failedMenu_attempts = 0
                    Console.WriteLine("8")
                    Console.ReadLine()
                    Console.Clear()
                Case Else
                    failedMenu_attempts += 1
                    Console.Clear()
                    Console.WriteLine("You failed to enter a valid input " & failedMenu_attempts & " of 3 attempts. The application will close after 3 consecutive failed attempts." & vbCrLf)
                    If failedMenu_attempts = 3 Then
                        Console.ReadLine()
                        quit = True
                    End If
            End Select
        Loop Until quit = True
    End Sub

    Sub submitExpenses()
        Dim SQL As String = "INSERT INTO Expenses (Day, Gas, Food, carWash, Vacuum, Parts, Misc, Total) VALUES (@Day, @Gas, @Food, @carWash, @Vacuum, @Parts, @Misc, @Total)" ' This SQL string has a syntax error
        command = New OleDbCommand(SQL, connection)
        connection.Open()
        command.Parameters.AddWithValue("@Day", expenses.jobDate)
        command.Parameters.AddWithValue("@Gas", expenses.gas)
        command.Parameters.AddWithValue("@Food", expenses.food)
        command.Parameters.AddWithValue("@carWash", expenses.carWash)
        command.Parameters.AddWithValue("@Vacuum", expenses.vacuum)
        command.Parameters.AddWithValue("@Parts", expenses.parts)
        command.Parameters.AddWithValue("@Misc", expenses.misc)
        command.Parameters.AddWithValue("@Total", expenses.expenseTotal)
        command.ExecuteNonQuery()
        connection.Close()
    End Sub

    Function enterExpenses() As Double
        Console.WriteLine("Please enter the value corrosponding to the expense labeled. Currency should be in decimal form and dates should be in short date form (Ex. 1/2/3456). All values will be converted to two decimal places when submitted." & vbCrLf)
        Try
            Console.Write("Job Date: ")
            expenses.jobDate = CDate(Console.ReadLine())
        Catch
            Try
                Console.WriteLine("Try again. Attempt 2")
                Console.Write("Job Date: ")
                expenses.jobDate = CDate(Console.ReadLine())
            Catch
                Try
                    Console.WriteLine("Try 1 more time. Attempt 3")
                    Console.Write("Job Date: ")
                    expenses.jobDate = CDate(Console.ReadLine())
                Catch
                    Return 0
                    Exit Function
                End Try
            End Try
        End Try
        Try
            Console.Write("Gas: $")
            expenses.gas = CDbl(Console.ReadLine())
        Catch
            Try
                Console.WriteLine("Try again. Attempt 2")
                Console.Write("Gas: $")
                expenses.gas = CDbl(Console.ReadLine())
            Catch
                Try
                    Console.WriteLine("Try 1 more time. Attempt 3")
                    Console.Write("Gas: $")
                    expenses.gas = CDbl(Console.ReadLine())
                Catch
                    Return 0
                    Exit Function
                End Try
            End Try
        End Try
        Try
            Console.Write("Food: $")
            expenses.food = CDbl(Console.ReadLine())
        Catch
            Try
                Console.WriteLine("Try again. Attempt 2")
                Console.Write("Food: $")
                expenses.food = CDbl(Console.ReadLine())
            Catch
                Try
                    Console.WriteLine("Try 1 more time. Attempt 3")
                    Console.Write("Food: $")
                    expenses.food = CDbl(Console.ReadLine())
                Catch
                    Return 0
                    Exit Function
                End Try
            End Try
        End Try
        Try
            Console.Write("Car Wash: $")
            expenses.carWash = CDbl(Console.ReadLine())
        Catch
            Try
                Console.WriteLine("Try again. Attempt 2")
                Console.Write("Car Wash: $")
                expenses.carWash = CDbl(Console.ReadLine())
            Catch
                Try
                    Console.WriteLine("Try 1 more time. Attempt 3")
                    Console.Write("Car Wash: $")
                    expenses.carWash = CDbl(Console.ReadLine())
                Catch
                    Return 0
                    Exit Function
                End Try
            End Try
        End Try
        Try
            Console.Write("Vacuum: $")
            expenses.vacuum = CDbl(Console.ReadLine())
        Catch
            Try
                Console.WriteLine("Try again. Attempt 2")
                Console.Write("Vacuum: $")
                expenses.vacuum = CDbl(Console.ReadLine())
            Catch
                Try
                    Console.WriteLine("Try 1 more time. Attempt 3")
                    Console.Write("Vacuum: $")
                    expenses.vacuum = CDbl(Console.ReadLine())
                Catch
                    Return 0
                    Exit Function
                End Try
            End Try
        End Try
        Try
            Console.Write("Parts: $")
            expenses.parts = CDbl(Console.ReadLine())
        Catch
            Try
                Console.WriteLine("Try again. Attempt 2")
                Console.Write("Parts: $")
                expenses.parts = CDbl(Console.ReadLine())
            Catch
                Try
                    Console.WriteLine("Try 1 more time. Attempt 3")
                    Console.Write("Parts: $")
                    expenses.parts = CDbl(Console.ReadLine())
                Catch
                    Return 0
                    Exit Function
                End Try
            End Try
        End Try
        Try
            Console.Write("Misc: $")
            expenses.misc = CDbl(Console.ReadLine())
        Catch
            Try
                Console.WriteLine("Try again. Attempt 2")
                Console.Write("Misc: $")
                expenses.misc = CDbl(Console.ReadLine())
            Catch
                Try
                    Console.WriteLine("Try 1 more time. Attempt 3")
                    Console.Write("Misc: $")
                    expenses.misc = CDbl(Console.ReadLine())
                Catch
                    Return 0
                    Exit Function
                End Try
            End Try
        End Try
        expenses.expenseTotal = Math.Round(expenses.gas + expenses.food + expenses.carWash + expenses.vacuum + expenses.parts + expenses.misc, 2)
        Console.WriteLine(vbCrLf & "Your total expenses for " & expenses.jobDate & " is: $" & expenses.expenseTotal & vbCrLf)
        Return 0
    End Function


    Function exitString() As String
        If String.Equals(input, "EXIT", StringComparison.CurrentCultureIgnoreCase) Then
            Return "0"
        Else
            Return input
        End If
    End Function

    Function menu() As String
        Console.WriteLine("Please enter a menu option.")
        Console.WriteLine("You may enter 0 or EXIT to quit.")
        Console.WriteLine(vbCrLf & "1 - Enter Income (Not Yet Supported)" & vbCrLf & "2 - View Income (Not Yet Supported)" & vbCrLf & "3 - Edit Income (Not Yet Supported)" & vbCrLf & "4 - Delete Income (Not Yet Supported)" & vbCrLf & "5 - Enter Expenses (Experimental)" & vbCrLf & "6 - View Expenses (Not Yet Supported)" & vbCrLf & "7 - Edit Expenses (Not Yet Supoorted)" & vbCrLf & "8 - Delete Expenses (Not Yet Supported" & vbCrLf)
        Console.Write("Option: ")
        input = Console.ReadLine()
        input = exitString()
        Return input
    End Function

End Module

---

To me, that code looks rough and unmaintainbale. Nested Try Catch, CDbl()? You say you are getting an error? When and where is the error occurring?

You should be accepting and validating input. Once that is done, then you should be able to do whatever you want with the values. have validation and rejection as a step, so there are no "attempts", you can only perform an action once the data passes validation period.

So:

1) User Enters Data
2) System Validates Data - Reject or Accept
3) If data is acceptable, process data with no worry of conversion of entry problems

Yeah I agree, the code is very maintainable and could be shortened to more than 75% of what it is now.

I get the code is messy but this is by no means meant to be anything special it's me throwing something together for someone there is a syntax error where marked in the code and as I mention. But I see no syntax errors compared to INSERT INTO statements I've used in my other programs which I pulled that from. This code could be made so much better but I'm in a rush and nothing special is needed like I said.

Always wrap database activity, such as the ExecuteNonQuery() in Try...Catch blocks. DB exceptions might be real exceptions, though most will end up being bugs to fix, as this one is. However, the information you get with the exception might be more useful than the generic message you are currently getting. SQL errors are kind of a pain to diagnose because you get the error later than you might like, and it is often cryptically worded when, in fact, the DB engine knows more about the problem than is revealed by the message. That just makes it more of a challenge for you.

There are a couple possible issues:

1) It may be that you are using a reserved word. There are two field names in your query that seem like they might be functions, and would therefore be reserved words. Any reserved word would have to be enclosed in square brackets []. The Day field is the most likely one to cause trouble, while Total also seems like a candidate. For this reason, I always like to have my field names NOT be single words. For example, you have carWash, which is always going to be safe. Using compound field names like that is always safe, while using single words, as you have for most of those fields, means that you have to know that you aren't conflicting with a reserved word.

B) You are using named parameters, but Access doesn't allow named parameters. So, I believe the Values should just be (?,?,?,?,?,?,?,?). The names used in the parameters can be left as they are, as they are ignored. The only thing that matters for the parameters is that they are supplied in the order that they show up in the query.

iii) The other alternative is that you are passing in a bad value.

As suggested, your submitExpenses sub should be wrapped in a Try/Catch block:

Code:

---

Sub submitExpenses()
        Try
                'Open the connection
                connection.Open()
               
                'Create a new command
                Using cmd As OleDbCommand = New OleDbCommand("INSERT INTO [Expenses] ([Day], [Gas], [Food], [carWash], [Vacuum], [Parts], [Misc], [Total]) VALUES (@day, @gas, @food, @carWash, @vacuum, @parts, @misc, @total);")
                        'Parameterize the query
                        With cmd.Parameters
                                .Add("@day", OleDbType.Date).Value = expense.jobDate.Date
                                .AddWithValue("@gas", expenses.gas)
                                .AddWithValue("@food", expenses.food)
                                .AddWithValue("@carWash", expenses.carWash)
                                .AddWithValue("@vacuum", expenses.vacuum)
                                .AddWithValue("@parts", expenses.parts)
                                .AddWithValue("@misc", expenses.misc)
                                .AddWithValue("@total", expenses.expenseTotal)
                        End With
                       
                        'Execute the query
                        cmd.ExecuteNonQuery()
                       
                        'Close the connection
                        connection.Close()
                End Using
        Catch ex As Exception
                'Display the error
                Console.WriteLine(ex.ToString())
        Finally
                'Close the connection if it was left open
                If connection IsNot Nothing AndAlso connection.State = ConnectionState.Open Then
                        connection.Close()
                End If
        End Try
 
End Sub

---

Notice how I use the Using keyword for the OleDbCommand, this is because OleDbCommand implements IDisposable. Another thing to notice is how I'm adding the Day parameter, if all you want to add is the Date and not the Date and Time then you need to first specify the OleDbType and then set the Value to just the Date otherwise it will return a mismatch error.