About Stephen Jackson Expertise I can help with questions regarding VB.NET syntax and object references, with data interfaces and with the design and creation of robust, data aware object classes. I can also be very helpful with creating distributable applications and provide tricks and tips on .msi creation. I also have extensive experience in designing SQL Server Databases and interfacing them with VB.NET. I try to avoid web specific questions, as that is an area of expertise all its own.
Experience
Experience in the Area: I have been a programmer in Visual Basic since version 1.0 and have worked with VB.NET (which is infinitely more powerful than previous versions) since its initial release and SQL Server, both as a corporate IT professional and professional consultant. I first wrote Basic in 1976 on a TRS 80 and have worked in Visual Basic 1.0 and every subsequent release of Microsoft Visual Basic. I worked for over 7 years as a Senior Level Consultant in the area and currently hold a Project Manager position in IT.
Education and Credentials: MBA in Econometrics, 1983, University of Memphis.
BBA in Financial Management, 1982, Fogelman College of Business and Economics, University of Memphis. Microsoft Certified Professional
Areas of Special Expertise:
My specialty is the design of Object Oriented Solutions with robust, data aware object classes. I generally avoid the classic ‘Three Tier’ model as I find it redundant and cumbersome to maintain. I also specialize in the creation of ‘User Friendly’ User Interfaces which help lessen the need for user training and help prevent user error. I work best with Windows Forms based applications, and while I do work in C# as well, I prefer to limit my questions here to Windows Forms based applications created in Visual Basic.NET and SQL Server. I wil also address questions relating to the distribution and installation of Windows Forms based applications created in VB.NET.
Expert: Stephen Jackson Date: 5/2/2008 Subject: Autogenerating number in vb.net
Question Hi Stephen,
I am trying to write a code for sutogenerating number like
i have table in Sql server 2005 express edition containg field as accountid(acid int) i want to fill it through vb.net like ACO1,ACO2 till ACO9 and after that AC10,AC11 etc
the coding is as follows which i have written in form_load
TextBox1.Enabled = False
Dim num As Integer
Dim i As String = "AC01"
num = 1
While (num < 100)
If num < 10 Then
TextBox1.Text = i & num.ToString
MsgBox(num)
Else
'TextBox1.Text = Microsoft.VisualBasic.Left(i, 2) & num.ToString
'MsgBox(num)
End If
num += 1
End While
I want, everything my form gets loaded it should the next AC number but above coding shows in msgbox 1,2,3...9 and then directly to AC019 in textbox1 but if i don't write msgbox then it directly shows AC99.
So how to stop one by one
like after ACO1 i press into insert button it goes to sql and then after that it shows AC02.
Thanks
Answer Sukhvinder,
This seems a rather odd way of doing things, but if you need to do this, you will need to have a way to get the last value of this number. For Example
cmdLastKey = New SqlCommand("select MAX([NameofField]) from [YourTable]")
strLastKey= cmdLastKey .ExecuteReader
num = Convert.ToInt(Microsoft.VisualBasic.Right(strLastKey, 2))
num += 1
It will get messier if and when you want to start counting up the letters, though. I would really recommend just keeping an auto incrementing numeric key and simply adding the alpha for display (this is what I would do and in fact have done before).
I am a big believer in proper Boyce-Codd Relational Database Design, and purely arbitrary numeric keys are a big part of that for more reasons than I have time to name.
