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")

---

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

---

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

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).

Quote:

---

Originally Posted by shammis

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.

Quote:

---

Originally Posted by shammis

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

---

See second code sample in my post

Quote:

---

Originally Posted by kareninstructor

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.

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.

Quote:

---

Originally Posted by jmcilhinney

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

---

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

---

Quote:

---

Originally Posted by si_the_geek

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

---

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.

Quote:

---

Originally Posted by jmcilhinney

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.

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.