How to get last agreement no from SQL server database (VB.NET)

Question

0.00/5 (No votes)

See more:

I'm using sql server database, AgreementDB table. In that table i have a column called "AgreementNo" - Data Type "nvarchar(50)". My agreement no sample is like "ABC/AGR/001"

I want to get this agreement no last saved record + 1 ("ABC/AGR/001" + 1 = ABC/AGR/002) to textbox called txtAgreementNo. Below I show the codes that I used and when running the program there is an error "Input string was not in a correct format" with highlighting this code row

VB

txtAgreementNo.Text = Convert.ToInt64(cmd.ExecuteScalar()) + 1

please give me a solution

What I have tried:

VB

Private Sub GetLastAgreementNo()
        MysqlConn = New SqlConnection
        MysqlConn.ConnectionString = Local(0)

        Dim query = "SELECT max(AgreementNo) FROM AgreementDB"
        Dim cmd As New SqlCommand(query, MysqlConn)
        MysqlConn.Open()
        txtAgreementNo.Text = Convert.ToInt64(cmd.ExecuteScalar()) + 1
    End Sub

Posted 24-Mar-18 18:53pm

Member 13745089

Updated 25-Mar-18 6:26am

Maciej Los

v2

Add a Solution

Comments

A_Griffin 25-Mar-18 4:11am

What happens when you get to ABC/AGR/999 ?

Member 13745089 25-Mar-18 12:38pm

shoulld be - ABC/AGR/1000

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2018-03-24T23:38:00

Solution 1

That's because the data you retrieve from the DB isn;t a number: you said yourself it's in the format "ABC/AGR/001" which cannot be directly converted to a number.

You need to process the data string that is returned (or better, change your DB to include a numeric field and a computed column) using either string.Substring or a Regular Expression.

But ... if you are doing this so you can generate a "free number" for the next record you want to add, I'd strongly suggest you rethink this whole design: SQL Server is a multiuser system, so generating "new" values from the latest plus one is a recipe for intermittent problems when two or more users try to do it all at once. You can seriously corrupt your database integrity that way, and it can take a huge amount of human input to sort it all out.

Posted 24-Mar-18 23:38pm

OriginalGriff

Comments

Maciej Los 25-Mar-18 12:25pm

5ed!

Member 13745089 25-Mar-18 12:38pm

Can you give me a solution code for this matter?

OriginalGriff 25-Mar-18 14:12pm

Did you read any of the words that I wrote above?

Member 13745089 27-Mar-18 5:09am

I agreed what you telling here. The only option is I have to create 2 columns for agreement No, one for the text field and other for the numeric field. then it will easy to generate. but what will happen when saving data to sql server. I mean how to divide text field and numeric field when inserting into sql server database?

OriginalGriff 27-Mar-18 5:13am

Do it in your presentation language, where you generate the value. You have much better string manipulation facilities there - and you probably have both halves separated to start with...

Maciej Los · Accepted Answer · 2018-03-25T06:27:00

Solution 2

Check this: Auto Incremented Column With VARCHAR and NVARCHAR Data Type in SQL[^]

Posted 25-Mar-18 6:27am

Maciej Los

Comments

Member 13745089 25-Mar-18 12:38pm

Can you give me vb.net solution for this?

Maciej Los 25-Mar-18 13:19pm

No, i can't for set of reason, but the main reason is: in multi-user environment, you'll get duplicated numbers. You have to generate AgreementNo on database level.