Syntax errors for statements in VB.Net code

Hello,

I’m getting a syntax error in both my “UPDATE” and “INSERT INTO” statements, but I cannot find the issue as I am not getting any error when debugging. I am using VS 2012, coding in VB.NET and using an Access 2010 database, which is being accessed via OleDB.

Private Sub Save_Record()

        Dim conn As New OleDbConnection
        Dim cmd As New OleDbCommand
        Dim sSQL As String = String.Empty

        Try
            'get connection string declared in the modFunctions.vb and assing it to conn variable
            conn = New OleDbConnection(sConnString)
            conn.Open()
            cmd.Connection = conn
            cmd.CommandType = CommandType.Text

            'Using textbox tag property to identify if the data is new or existing.
            If Me.txtBUDUnit.Tag = 0 Then
                sSQL = "INSERT INTO tblUnits ( Unit, PayeeBank, Currency, CreditAccNum, NextChqNum)"
                sSQL = sSQL & "  VALUES(@Unit, @PayeeBank, @Currency, @CreditAccNum, @NextChqNum)"
                cmd.CommandText = sSQL
            Else
                sSQL = "UPDATE tblUnits SET Unit = @Unit, PayeeBank =  @PayeeBank, Currency =  @Currency, CreditAccNum = @CreditAccNum, NextChqNum = @NextChqNum WHERE UnitID = @id"
                cmd.CommandText = sSQL
            End If

            'set paramaters
            cmd.Parameters.Add("@Unit", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtBUDUnit.Text)) > 0, Me.txtBUDUnit.Text, DBNull.Value)
            cmd.Parameters.Add("@PayeeBank", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtBUDPayeeBank.Text)) > 0, Me.txtBUDPayeeBank.Text, DBNull.Value)
            cmd.Parameters.Add("@Currency", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtBUDCurr.Text)) > 0, Me.txtBUDCurr.Text, DBNull.Value)
            cmd.Parameters.Add("@CreditAccNum", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtBUDCreditAccNum.Text)) > 0, Me.txtBUDCreditAccNum.Text, DBNull.Value)
            cmd.Parameters.Add("@NextChqNum", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtBUDNxtChqNum.Text)) > 0, Me.txtBUDNxtChqNum.Text, DBNull.Value)
            cmd.Parameters.Add("@id", OleDbType.Numeric).Value = Me.txtBUDUnit.Tag
            cmd.ExecuteNonQuery()

            'If record is new then get its ID so that it can be edited right away after the insertion.

            If Me.txtBUDUnit.Tag = 0 Then
                cmd.CommandText = "Select @@Identity"

                'Set textbox tag property with the ID of new record
                Me.txtBUDUnit.Tag = cmd.ExecuteScalar()
            End If

            MsgBox("Unit has been save.")

        Catch ex As Exception
            MsgBox(ErrorToString)
        Finally
            conn.Close()
        End Try

    End Sub

Open in new window


Any help with what I am doing wrong would be most appreciated.

Kind Regards,
N
LVL 1
KevinInformation TechnologyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jacques Bourgeois (James Burger)PresidentCommented:
I pasted your code in VS, and there is no syntax error in lines 15 to 22 where you have your 2 commands. Is the error somewhere else?

And how can you say "not getting any error when debugging". You cannot debug if there is a syntax error, because Visual Studio cannot compile the application.
KevinInformation TechnologyAuthor Commented:
Hi James,

That is correct, I am "not getting any error when debugging". My application is able to compile fine, without any errors.

I should have stated this in my initial post, but the error is coming from my application, I'm assuming "Line 44" when it catches the exception.

I have attached both errors that I receive when running the above code. I am still a beginner programer and would appreciate any tips on how I can resolve the issue that I'm having.

Please let me know if additional information is needed.

error1error2
Appreciate the help.

Kind Regards,
N
Éric MoreauSenior .Net ConsultantCommented:
not sure that IIF and DBNull.Value are a good fit.

Can you try to replace all the DBNull.Value with test values like this:
cmd.Parameters.Add("@Unit", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtBUDUnit.Text)) > 0, Me.txtBUDUnit.Text, "ValidValue")

Open in new window


If it is working, you will need to use a different syntax like this:
If Len(Trim(Me.txtBUDUnit.Text)) = 0 then
   cmd.Parameters.Add("@Unit", OleDbType.VarChar).Value = DBNull.Value
else
   cmd.Parameters.Add("@Unit", OleDbType.VarChar).Value = Me.txtBUDUnit.Text
end if

Open in new window


BTW, "If Len(Trim(Me.txtBUDUnit.Text)) = 0 then" can be replaced with "If string.isnullorwhitespace(Me.txtBUDUnit.Text) then"
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

KevinInformation TechnologyAuthor Commented:
Thanks for your response Eric. However, doing the test did not work.

I am getting the same errors as before   :(

Do you have any other ideas?

Kindly advise.

Regards,
N
Éric MoreauSenior .Net ConsultantCommented:
All your fields are varchar in your database?

have you tried an hardcoded query (of course you need to provide correct values for your table):

                sSQL = "INSERT INTO tblUnits ( Unit, PayeeBank, Currency, CreditAccNum, NextChqNum)"
                sSQL = sSQL & "  VALUES('Unit', 'Bank', 'Currency', 'CreditAccNum', 'NextChqNum')"

Open in new window

Éric MoreauSenior .Net ConsultantCommented:
Also, in your Catch, can you paste here the full message of ex?
Jacques Bourgeois (James Burger)PresidentCommented:
What are the values of your parameters? Is is possible that there are quotes in there? This can break the SQL because a quote is seen as an end of string in SQL.
KevinInformation TechnologyAuthor Commented:
Regarding the full exception message that is catched. I would code “MsgBox(ex.ToString)” correct?

If so it can be seen below for both “INSERT INTO” and “UPDATE” statements. If this message is not what you are looking for please let me know what to adjust in my code to retrieve this for the message.

insertinto1
update1
I've noticed from these more detailed errors that it's referring to line 86 which is the "cmd.ExecuteNonQuery()", so I am wondering if I am using the correct syntax to run the query. I have tried enclosing the table in [ ] (example [tblUnits]) but this doesn't seem to do anything.

What I understand from using “varchar” is that it can contain mixed characters, both numbers and letters. So I chose this to be on the safe side, as even though two of my fields (UnitID and NextChqNum) in the database contain only digits, they do not need to be used for any sort of calculation so this is why I chose varchar.

Within my Access 2010 database my fields type are:

UnitID = AutoNumber
Unit = Text
PayeeBank = Text
Currency = Text
CreditAccNum = Text
NextChqNum = Number

However I did try and change the type of  “NextChqNum” to use Numeric instead:

So from this:

cmd.Parameters.Add("@NextChqNum", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtBUDNxtChqNum.Text)) > 0, Me.txtBUDNxtChqNum.Text, DBNull.Value)

Open in new window


to this:

cmd.Parameters.Add("@NextChqNum", OleDbType.Numeric).Value = IIf(Len(Trim(Me.txtBUDNxtChqNum.Text)) > 0, Me.txtBUDNxtChqNum.Text, DBNull.Value)

Open in new window


Doing so however gave the same errors as seen above.

I then tried the hardcoded query as suggested, but get the same error.

Do note that there are no quotes in my values. An example of the values in the access database can be seen below:

data1
Kindly advise,

Regards,
N
Éric MoreauSenior .Net ConsultantCommented:
The ex.Message is not giving us details. Place a breakpoint in the Catch and dig into the ex variable to see if you can find anything else (maybe in the InnerExceptions).

Have you tried to hardcode the values?
Éric MoreauSenior .Net ConsultantCommented:
You can also try to remove your Try..Catch. This can help you find where the error really is.
Jacques Bourgeois (James Burger)PresidentCommented:
You are showing us your sample values from a grid, but you get them from TextBoxes when running the query.

This is not good. The values passed to the query might be different from what you think. When you debug, you must assume nothing and try to get the facts as they really are. You need to look at the real values assigned to the parameters when you execute the query, not at values that you assume are there.
KevinInformation TechnologyAuthor Commented:
Bare with me James, as I said previously I am still a beginner with programming, learning new things everyday.

I will look at it more tonight and advise.

Kind Regards,
N
CodeCruiserCommented:
Try enclosing column names in brackets

                sSQL = "INSERT INTO tblUnits ( [Unit], [PayeeBank], [Currency], [CreditAccNum], [NextChqNum])"
                sSQL = sSQL & "  VALUES(@Unit, @PayeeBank, @Currency, @CreditAccNum, @NextChqNum)"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jacques Bourgeois (James Burger)PresidentCommented:
I am bearing with you. Just trying to guide you into the proper ways of going through a debugging session. You need to look precisely at what happens, not at what you think is there.

Could you show us your hard coded query? This would help us because problems such as this one are hard to pinpoint when you are not in front of the debugger as we are.
KevinInformation TechnologyAuthor Commented:
Thank you very much for posting your solution CodeCruiser, I enclosed all of the column names in "[ ]" brackets on both Line 16 for the "INSERT INTO" statement and Line 20 for the "UPDATE" statement and now the codes query syntax is working as it should. While researching my problem on stackoverflow I ran in to a few post of other peoples who recommended putting these brackets on only the table names. So this is what I attempted but it failed to work.

Are you able to give me an explanation as to why these brackets are needed on the column names? Is it because I am using an Access 2010 database (accdb) rather than an Access 2000-2003 database (mdb)? I was reading somewhere that the accdb format is newer and offers more features for Access and that the syntax for each format may be a little different from the mdb format. Would this be the reasoning behind the brackets on the column names?

Eric and James, thank you also for your efforts, I appreciate very much the tips on debugging as they will help me to be a better programmer in locating where an error is and not to assume anything.

Your assistance has been most appreciated.

Kind Regards,
Nobushi Sugahara.

PS. Eric, BTW, removing the try and catch I was able to see a more detailed report of the exception, looking through the report there was a column labeled "InnerExceptions" the value for this however was set to "Nothing".
Éric MoreauSenior .Net ConsultantCommented:
The brackets are normally required only when you have a space in your field name or when you are using a reserved word.
KevinInformation TechnologyAuthor Commented:
Thank you Eric, well the space in the field name is not applicable in my case. Can you explain further what you mean by reserved word?

Would you perhaps mean a reserved word in the .Net code, such as "If" or "then" etc?
Éric MoreauSenior .Net ConsultantCommented:
I meant reserved word in Access. I wonder if Unit or Currency could be reserved word. Isn't Currency a data type in Access?
KevinInformation TechnologyAuthor Commented:
Yes currency is a data type.

Ahhh... I see what you mean.

Thank you so much for your response.

Take Care,
N
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.