Results 1 to 14 of 14

Thread: Syntax error VB/MS Access

  1. #1

    Thread Starter
    Junior Member
    Join Date
    May 2018
    Posts
    16

    Question Syntax error VB/MS Access

    I NEED HELP FOR FIX SOME ERRORS. I WANT TO SAVE MY DETAILS INTO MS ACCESS DATABASE. BUT WHEN I CLICK THE SAVE BUTTON ITS SHOW Syntax error (missing operator) in query expression . I ALREADY FIND THE GOOGLE AND CANT FIX IT MYSELF. SO PLZ HELP ME TO FIX THIS.
    Code:
    Dim strText() As String
            Dim strDate As String
            strDate = Me.date1.Text
            strText = Split(Me.cmbSSelect.Text, vbCrLf)
            Dim cmd As New OleDb.OleDbCommand
    
    
            If Not rs.State = ConnectionState.Open Then
                'open connection if it not yet open
                rs.Open()
            End If
    
            cmd.Connection = rs
            'add data to table
            cmd.CommandText = "INSERT INTO dpdata(DPNo, DPDate, Supplier, Des, Amount) " & _
                              " VALUES(" & Me.TextBox2.Text & ",'" & strDate & ",'" & strText(0) & ",'" & Me.TextBox7.Text & ",'" & Me.TextBox4.Text & "')"
            cmd.ExecuteNonQuery()
    
            'close connection
            rs.Close()
            MsgBox("Record Added Successfully")
    Attached Images Attached Images  

  2. #2
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Syntax error VB/MS Access

    First off please refrain from using all upper case in your post. In regards to your issue, it's unwise to string concatenate a query of any kind as you have done. Instead use OleDbParameters as shown below. The last parameter to the function requires a Integer declared, passed in and on success returns the new primary key (that is if there is a primary key that is auto-incrementing). How the SQL statement is created requires targeting Framework 3.5 or higher using XML Literals, otherwise place the statement into a string. Lastly, the transaction is optional but is good to use more so when working with relational data, I left this in as it's a stock code sample I use for answering forum questions.

    Code:
    Public Function AddNewRow(
        ByVal pName As String,
        ByVal pContact As String,
        ByVal pContactTitle As String,
        ByVal pJoinDate As Date,
        ByRef pIdentfier As Integer) As Boolean
    
        Dim Success As Boolean = True
    
        Using cn As New OleDbConnection("TODO")
            Dim transaction As OleDbTransaction = Nothing
            Using cmd As New OleDbCommand With {.Connection = cn}
                cmd.CommandText =
                    <SQL>
                        INSERT INTO Customer (CompanyName,ContactName,ContactTitle,JoinDate) 
                        Values (@CompanyName,@ContactName,@ContactTitle,@JoinDate)
                    </SQL>.Value
    
                cmd.Parameters.AddWithValue("@CompanyName", pName)
                cmd.Parameters.AddWithValue("@ContactName", pContact)
                cmd.Parameters.AddWithValue("@ContactTitle", pContactTitle)
                cmd.Parameters.AddWithValue("@JoinDate", pJoinDate)
    
                Try
                    cn.Open()
                    transaction = cn.BeginTransaction()
                    cmd.Transaction = transaction
                    cmd.ExecuteNonQuery()
                    cmd.CommandText = "Select @@Identity"
                    pIdentfier = CInt(cmd.ExecuteScalar)
                Catch sqlex As OleDbException
                    transaction.Rollback()
                Catch ex As Exception
                    mHasException = True
                    mLastException = ex
                    Success = False
                End Try
            End Using
        End Using
    
    
        Return Success
    
    End Function
    EDIT: Without XML literals
    Code:
    Public Function AddNewRow(
                                ByVal pName As String,
                                ByVal pContact As String,
                                ByVal pContactTitle As String,
                                ByVal pJoinDate As Date,
                                ByRef pIdentfier As Integer) As Boolean
    
        Dim Success As Boolean = True
    
        Using cn As New OleDbConnection("TODO")
            Dim transaction As OleDbTransaction = Nothing
            Using cmd As New OleDbCommand With {.Connection = cn}
                cmd.CommandText = "INSERT INTO Customer (CompanyName,ContactName,ContactTitle,JoinDate) Values (@CompanyName,@ContactName,@ContactTitle,@JoinDate)"
    
                cmd.Parameters.AddWithValue("@CompanyName", pName)
                cmd.Parameters.AddWithValue("@ContactName", pContact)
                cmd.Parameters.AddWithValue("@ContactTitle", pContactTitle)
                cmd.Parameters.AddWithValue("@JoinDate", pJoinDate)
    
                Try
                    cn.Open()
                    transaction = cn.BeginTransaction()
                    cmd.Transaction = transaction
                    cmd.ExecuteNonQuery()
                    cmd.CommandText = "Select @@Identity"
                    pIdentfier = CInt(cmd.ExecuteScalar)
                Catch sqlex As OleDbException
                    transaction.Rollback()
                Catch ex As Exception
                    Success = False
                End Try
            End Using
        End Using
    
    
        Return Success
    
    End Function
    Last edited by kareninstructor; Jul 1st, 2018 at 06:02 AM. Reason: added second code sample

  3. #3

    Thread Starter
    Junior Member
    Join Date
    May 2018
    Posts
    16

    Re: Syntax error VB/MS Access

    thanks for the fast response. but can I use SQL while using access database.?

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Syntax error VB/MS Access

    You can, and you already were... INSERT INTO is an SQL statement.

    As for the <SQL> tag, that has the limitations Karen mentioned (you need to be targeting .Net Framework 3.5 or higher). Parameters do not have that limitation.


    For further information on why you should be using parameters (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of the Database Development forum).


    By the way, I notice that you are using "Classic" ADO with recordsets etc (which was current 20 years ago), ideally you should switch to ADO.Net as Karen showed (which is still current).

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Syntax error VB/MS Access

    Quote Originally Posted by shammis View Post
    thanks for the fast response. but can I use SQL while using access database.?
    Of course you can. You already are. You're just not doing it properly. If you had actually looked at your SQL code, rather than just the code that constructed it, you probably would have seen the actual issue. Use parameters a demonstrated and the issue should go away.

  6. #6
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Syntax error VB/MS Access

    Quote Originally Posted by shammis View Post
    thanks for the fast response. but can I use SQL while using access database.?
    See second code sample in my post

  7. #7

    Thread Starter
    Junior Member
    Join Date
    May 2018
    Posts
    16

    Re: Syntax error VB/MS Access

    Quote Originally Posted by kareninstructor View Post
    See second code sample in my post
    Thanks again karen. But i still cant understand how to get text boxes value to @CompanyName . example: How insert TextBox1.Text to CompanyName . Sorry I am still new for these things. I am self learner. watching videos and try to learn my self. when i face a some error i try to fix it if couldn't i ask in here. so anyone can explain a little bit. sorry for the my bad English.

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Syntax error VB/MS Access

    When you call Parameters.AddWithValue, you specify the parameter name and the value. If the value is in TextBox1.Text then that's what you provide.

    If you're going to use the AddNewRow method that kareninstructor has written then when you call that method you pass the values you want to insert as arguments when you call it. Again, if TextBox1.Text is one of those values then that's what you pass when you call the method. This is simply a case of calling a method and passing an argument. If you don't know how to do that then you should probably spend some time on the fundamentals first.

  9. #9

    Thread Starter
    Junior Member
    Join Date
    May 2018
    Posts
    16

    Re: Syntax error VB/MS Access

    Quote Originally Posted by jmcilhinney View Post
    When you call Parameters.AddWithValue, you specify the parameter name and the value. If the value is in TextBox1.Text then that's what you provide.

    If you're going to use the AddNewRow method that kareninstructor has written then when you call that method you pass the values you want to insert as arguments when you call it. Again, if TextBox1.Text is one of those values then that's what you pass when you call the method. This is simply a case of calling a method and passing an argument. If you don't know how to do that then you should probably spend some time on the fundamentals first.
    i think i found way how to do it. But i cant understand what is pIdentfier and what was doing it.?
    Code:
    Public Function AddNewRow(
                                    ByVal Name1 As String,
                                    ByVal Contact As String,
                                    ByVal ContactTitle As String,
                                    ByVal JoinDate As Date,
                                    ByRef pIdentfier As Integer) As Boolean
    
            Dim Success As Boolean = True
    
    
            Using cn As New OleDbConnection("TODO")
                Dim transaction As OleDbTransaction = Nothing
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "INSERT INTO Customer (CompanyName,ContactName,ContactTitle,JoinDate) Values (@CompanyName,@ContactName,@ContactTitle,@JoinDate)"
    
                    cmd.Parameters.AddWithValue("@CompanyName", Name1)
                    cmd.Parameters.AddWithValue("@ContactName", Contact)
                    cmd.Parameters.AddWithValue("@ContactTitle", ContactTitle)
                    cmd.Parameters.AddWithValue("@JoinDate", JoinDate)
    
                    Try
                        cn.Open()
                        transaction = cn.BeginTransaction()
                        cmd.Transaction = transaction
                        cmd.ExecuteNonQuery()
                        cmd.CommandText = "Select @@Identity"
                        pIdentfier = CInt(cmd.ExecuteScalar)
                    Catch sqlex As OleDbException
                        transaction.Rollback()
                    Catch ex As Exception
                        Success = False
                    End Try
                End Using
            End Using
    
    
            Return Success
        End Function
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim tst As New Form1
            tst.AddNewRow(Name1.Text, Contact.Text, ContactTitle.Text, JoinDate.Text, pIdentfier)
        End Sub

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Syntax error VB/MS Access

    You probably don't need pIdentfier

    It is there so that if the database table has an AutoNumber/Identity field, you can automatically detect the value for the record you just added.

    As that doesn't seem relevant in this case, you can remove it from the parameters list:
    Code:
                                    ByVal JoinDate As Date) As Boolean
    ...and remove the two lines for using it:
    Code:
                        cmd.ExecuteNonQuery()
                    Catch sqlex As OleDbException

  11. #11

    Thread Starter
    Junior Member
    Join Date
    May 2018
    Posts
    16

    Re: Syntax error VB/MS Access

    Quote Originally Posted by si_the_geek View Post
    You probably don't need pIdentfier

    It is there so that if the database table has an AutoNumber/Identity field, you can automatically detect the value for the record you just added.

    As that doesn't seem relevant in this case, you can remove it from the parameters list:
    Code:
                                    ByVal JoinDate As Date) As Boolean
    ...and remove the two lines for using it:
    Code:
                        cmd.ExecuteNonQuery()
                    Catch sqlex As OleDbException
    Finally all errors gone.. Thanks to all . but now when i click on button it was nothing happened. i checked the database. it was not updated.
    here is the codes i used.

    Code:
    Public Function AddNewRow(
                                  ByVal ID As String,
                                    ByVal Name1 As String,
                                    ByVal Contact As String,
                                    ByVal ContactTitle As String,
                                    ByVal JoinDate As Date) As Boolean
    
            Dim Success As Boolean = True
    
    
            Using cn As New OleDbConnection
                cn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=" & Application.StartupPath & "\Database1.accdb"
                Dim transaction As OleDbTransaction = Nothing
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "INSERT INTO Customer (ID,CompanyName,ContactName,ContactTitle,JoinDate) Values (@ID,@CompanyName,@ContactName,@ContactTitle,@JoinDate)"
    
                    cmd.Parameters.AddWithValue("@ID", ID)
                    cmd.Parameters.AddWithValue("@CompanyName", Name1)
                    cmd.Parameters.AddWithValue("@ContactName", Contact)
                    cmd.Parameters.AddWithValue("@ContactTitle", ContactTitle)
                    cmd.Parameters.AddWithValue("@JoinDate", JoinDate)
    
                    Try
                        cn.Open()
                        transaction = cn.BeginTransaction()
                        cmd.Transaction = transaction
                        cmd.ExecuteNonQuery()
                        transaction.Rollback()
                    Catch ex As Exception
                        Success = False
                    End Try
                End Using
            End Using
    
    
            Return Success
        End Function
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    
            AddNewRow(ID.Text, Name1.Text, Contact.Text, ContactTitle.Text, JoinDate.Text)
        End Sub

  12. #12
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Syntax error VB/MS Access

    How would you even know whether it's working or not? In that AddNewRow method you simply return False if an exception is thrown but you never actually test what value is returned when you call AddNewRow. An exception could be being thrown and you'd never know. Have you stepped through the code in the debugger to see whether it succeeds or not? That is the first thing to do.

    If it does succeed, you should then test the value returned by that ExecuteNonQuery call. If that's not zero then the code is working exactly as it should and you're just not looking for the data properly, which is not unusual. In that case, follow the first link in my signature to learn how local data files are managed.

  13. #13

    Thread Starter
    Junior Member
    Join Date
    May 2018
    Posts
    16

    Re: Syntax error VB/MS Access

    Quote Originally Posted by jmcilhinney View Post
    How would you even know whether it's working or not? In that AddNewRow method you simply return False if an exception is thrown but you never actually test what value is returned when you call AddNewRow. An exception could be being thrown and you'd never know. Have you stepped through the code in the debugger to see whether it succeeds or not? That is the first thing to do.

    If it does succeed, you should then test the value returned by that ExecuteNonQuery call. If that's not zero then the code is working exactly as it should and you're just not looking for the data properly, which is not unusual. In that case, follow the first link in my signature to learn how local data files are managed.
    I try step by step as you instruct and found error in database format . then i create new one as database format as .mdb then again run debugger and its run to
    Code:
     transaction.Rollback()
    and show the form1 and nothing happened and nothing to added to database.

  14. #14
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Syntax error VB/MS Access

    You need to start making an effort to understand the code you're using. Do you know what transactions do or how they work? If not, have you tried to find out? Have you read the relevant documentation and/or searched the web for information?

    You've changed how the one in your code is being used and broken it as a result. The point of a transaction is to allow you to ensure that multiple database operations are either all performed successfully or none of them are, so you don't end up with an intermediate, indeterminate state. The original code rolled back the transaction, i.e. undid any database changes, if and only if there was an OleDbException thrown. You've removed that Catch statement but retained the Rollback call, which means that you're always going to undo your changes if they are made successfully. Does that make sense? Of course not. You only want to rollback a transaction if something goes wrong partway through. You were told in the first place that the transaction was optional. Don't use it if it's not appropriate to do so.

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