VB.Net increment number in Access 2010 Database and windows form textbox by 1

Good Morning,

I need some help with my VB.Net code.

I have a windows form which is using a DGV control which has a connection to an access database table.

The user selects a single row from the dgv and pushes a button on the first windows form, and the values of what was selected in the dgv is written to a XML file and then the user is directed to another form.

On this second windows form I have the code read the values from the XML and place them in text boxes.

What I am trying to achieve now is when the user pushes a button on this second form, that the “NextChqNum” value will increment by 1 in both the table in the database for this existing row and in the textbox on the form.

In access I have the properties of this column set to “Number”.

I have tried various things, but can’t get it to work. I cant seem to figure out the correct syntax to be used.

My code is below:

Private Sub UpdateChqNum()

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

        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

            sSQL = "SELECT MAX( [NextChqNum] ) as 'NewChqNum' FROM tblUnits WHERE [Unit], [PayeeBank], [Currency]"
            sSQL = sSQL & " VALUES(@NextChqNum, @Unit, @PayeeBank, @Currency)"
            cmd.CommandText = sSQL

            'set paramaters
            cmd.Parameters.Add("@Unit", OleDbType.VarChar).Value = Me.txtMUnit.Text
            cmd.Parameters.Add("@PayeeBank", OleDbType.VarChar).Value = Me.txtMBnkPayee.Text
            cmd.Parameters.Add("@Currency", OleDbType.VarChar).Value = Me.txtMCurr.Text
            cmd.Parameters.Add("@NextChqNum", OleDbType.Integer).Value = Me.txtMChqNum.Text
            cmd.ExecuteNonQuery()

            NewChqNum = Convert.ToInt32(cmd.ExecuteScalar()) + 1

        Catch ex As Exception

            MsgBox(ErrorToString)

        Finally

            conn.Close()

        End Try

        Dim incremChqNum As Integer
        Me.txtMChqNum.Text = incremChqNum + 1

    End Sub

Open in new window


Error I am getting is below:

error
And when I click OK to this error the value of the text box 'txtChqNum' changes from whatever it is (example '5') to a value of '1'.

Also there is no change to the in the database.

Kindly advise,

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.

CodeCruiserCommented:
Change your query to following


 sSQL = "SELECT MAX( [NextChqNum] ) as 'NewChqNum' FROM tblUnits WHERE [Unit]=@Unit AND [PayeeBank]=@PayeeBank AND [Currency]=@Currency"

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
silemoneCommented:
Looks like you have 3 values but passing four params...is that true?
KevinInformation TechnologyAuthor Commented:
CodeCruiser: After adjusting my code to what you said, I am no longer getting an error.

But the NextChqNum value in the database is not being updated, and as said previously the textbox on the form for this value is changing but decrementing to the value of one instead of incrementing.

Any ideas?

Silemone: in that table there are 4 values but i only need to adjust one which is the NextChqNum.
CodeCruiserCommented:
Is it sorted now?
KevinInformation TechnologyAuthor Commented:
Yes thanks.
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.