incrementing from sql with vb.net

Question

i want to read student no from sql and increment it by 1 example student no. is ACT001 it will becomes ACT002 and ACT003 etc.

Satyapriya Nayak · Answer

Imports System.Collections.Generic Imports System.ComponentModel Imports System.Data Imports System.Drawing Imports System.Linq Imports System.Text Imports System.Windows.Forms Imports System.Data.OleDb Namespace Dynamic_generate_id Public Partial Class Form1 Inherits Form Private ConnectionString As String = System.Configuration.ConfigurationSettings.AppSettings("dsn") Private com As OleDbCommand Private str As String Private count As Integer Public Sub New() InitializeComponent() End Sub Private Sub btn_save_Click(sender As Object, e As EventArgs) Dim con As New OleDbConnection(ConnectionString) con.Open() str = "insert into caste(cast_code,cast_desc) values('" & lbl_castecode.Text.Trim() & "','" & txt_castename.Text.Trim() & "')" com = New OleDbCommand(str, con) com.ExecuteNonQuery() con.Close() MessageBox.Show("Records successfully Inserted") txt_castename.Text = "" autogenerated() End Sub Private Sub autogenerated() Dim con As New OleDbConnection(ConnectionString) str = "select count(*) from caste" com = New OleDbCommand(str, con) con.Open() count = Convert.ToInt16(com.ExecuteScalar()) + 1 lbl_castecode.Text = "ACT00" & count.ToString() lbl_castecode.Enabled = False con.Close() End Sub Private Sub Form1_Load(sender As Object, e As EventArgs) autogenerated() End Sub End Class End Namespace

Jignesh Trivedi · Answer

hi, here if prefix 'ACT' is fix than we can write following query to meet your requirement. select 'ACT' + RIGHT('000' + cast(cast(substring('ACT003',4,LEN('ACT003')) as int) + 1 as varchar) ,3) replace your column name as well as write logic for finding last row. but good idea is table has one column type int and from this column you have to generate required student no. hope this will help you.

incrementing from sql with vb.net

Answers (2)