Hi nirmal90,
Before Inserting the data you need to check the max value for the column. Then, check for the condition if column value is null insert 1 else increment by 1.
Please refer below sample.
Namespaces
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Code
C#
private void Form1_Load(object sender, EventArgs e)
{
BindDataGrid();
}
private void BindDataGrid()
{
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("Select * from Customers_NoAuto", con))
{
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
dataGridView1.DataSource = dt;
}
}
}
}
}
private void button1_Click(object sender, EventArgs e)
{
string lastId = string.Empty;
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("Select MAX(CustomerId) from Customers_NoAuto", con))
{
con.Open();
lastId = Convert.ToString(cmd.ExecuteScalar());
con.Close();
}
}
int customerId = !string.IsNullOrEmpty(lastId) ? (Int32.Parse(lastId) + 1):1;
string name = txtName.Text;
string country = txtCountry.Text;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO Customers_NoAuto VALUES (@Id, @Name, @Country)", con))
{
cmd.Parameters.AddWithValue("@Id", customerId);
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@Country", country);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
this.BindDataGrid();
}
VB.Net
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
BindDataGrid()
End Sub
Private Sub BindDataGrid()
Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constring)
Using cmd As SqlCommand = New SqlCommand("Select * from Customers_NoAuto", con)
Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
Using dt As DataTable = New DataTable()
sda.Fill(dt)
DataGridView1.DataSource = dt
End Using
End Using
End Using
End Using
End Sub
Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnInsert.Click
Dim lastId As String = String.Empty
Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constring)
Using cmd As SqlCommand = New SqlCommand("Select MAX(CustomerId) from Customers_NoAuto", con)
con.Open()
lastId = Convert.ToString(cmd.ExecuteScalar())
con.Close()
End Using
End Using
Dim customerId As Integer = If(Not String.IsNullOrEmpty(lastId), (Int32.Parse(lastId) + 1), 1)
Dim name As String = txtName.Text
Dim country As String = txtCountry.Text
Using con As SqlConnection = New SqlConnection(constring)
Using cmd As SqlCommand = New SqlCommand("INSERT INTO Customers_NoAuto VALUES (@Id, @Name, @Country)", con)
cmd.Parameters.AddWithValue("@Id", customerId)
cmd.Parameters.AddWithValue("@Name", name)
cmd.Parameters.AddWithValue("@Country", country)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
Me.BindDataGrid()
End Sub
Screenshot