How to generate auto ID

Question

0.00/5 (No votes)

See more:

Please Help to solve one kind problem
I want generate an auto id like this where ARA = ARA then display result like ARA004+1= ARA005 but displayed an error conversion from string "ARA001" to type 'Integer' is not valid. See details below

Shop_No      Block
ARA001      ARA
ARA002      ARA
ARA003      ARA
ARA004      ARA
BRA001      BRA
BRA002      BRA
BRA003      BRA
BRA004      BRA
CRA001      CRA
CRA002      CRA
CRA003      CRA
CRA004      CRA
CRA005      CRA

Shop_No Field set for text in access database and I change my dim to string still error displayed conversion from string "ARA001" to type 'String' is not valid

What I have tried:

below is my tried codes

VB

Dim IncrID As Integer
CustAutoSqlstr = "Select *From tblRevenueRegister WHERE Block='" & cbBlock.Text & "'"
CustAutodr = CustAutocmd.ExecuteReader()
        If IncrID < CustAutodr.Item(0) Then
                    IncrID = CustAutodr("Shop_No").ToString
                End If
 txtShopNo.Text = IncrID + 1

Posted 26-Feb-18 2:31am

Dauda Muhammad

Updated 26-Feb-18 3:38am

Maciej Los

v2

Add a Solution

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)

Richard Deeming · Answer 1 · 2018-02-26T03:14:00

VB.NET

Dim maxValue As Object
Dim block As String = cbBlock.Text

Using con As New SqlConnection("...")
    Using cmd As New SqlCommand("SELECT Max(Shop_No) FROM tblRevenueRegister WHERE Block = @Block")
        cmd.Parameters.AddWithValue("@Block", block)
        
        con.Open()
        maxValue = cmd.ExecuteScalar()
    End Using
End Using

If maxValue Is Nothing OrElse Convert.IsDBNull(maxValue) Then
    txtShopNo.Text = "1"
Else
    Dim numberPart As String = Convert.ToString(maxValue).Substring(block.Length)
    Dim shopNumber As Integer = Convert.ToInt32(numberPart)
    txtShopNo.Text = (shopNumber + 1).ToString()
End If

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Maciej Los · Answer 2 · 2018-02-26T03:39:00

In multiuser environment your code will generate duplicated Shop_no's. Stop doing this that way! You have to create custom-auto-generated sequence on server side!
See: Custom Auto-Generated Sequences with SQL Server - SQLTeam.com[^]

More at Google: sql autonumber based on another field - Google Search[^]

How to generate auto ID

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0