VS 2013 [RESOLVED] Unsure if SQL or VB issue-VBForums
Results 1 to 6 of 6

Thread: [RESOLVED] Unsure if SQL or VB issue

  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

  2. #2
    Frenzied Member
    Join Date
    Oct 2012
    Location
    Tampa, FL
    Posts
    1,173

    Re: Unsure if SQL or VB issue

    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

  3. #3
    Fanatic Member Toph's Avatar
    Join Date
    Oct 2014
    Posts
    655

    Re: Unsure if SQL or VB issue

    Yeah I agree, the code is very maintainable and could be shortened to more than 75% of what it is now.
    If you find my contributions helpful then rate them.

  4. #4

    Thread Starter
    Junior Member
    Join Date
    May 2015
    Posts
    23

    Re: Unsure if SQL or VB issue

    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.

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    30,830

    Re: Unsure if SQL or VB issue

    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.
    My usual boring signature: Nothing

  6. #6
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,158

    Re: [RESOLVED] Unsure if SQL or VB issue

    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.

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
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.