Results 1 to 6 of 6

Thread: [RESOLVED] Unsure if SQL or VB issue

Threaded View

  1. #1

    Thread Starter
    Junior Member
    Join Date
    May 2015
    Posts
    23

    Resolved [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
    Last edited by fatalerror0x00; Jun 17th, 2015 at 01:13 PM. Reason: Error reason found

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width