SQL Server auto increament number with VB.NET

Question

Hi

When the exisitng records are deleted in the table which has a auto increament number the next available number shows as "0" on the VB.NET Form. But if a record entered directly to the table from then onwards it recognise the auto increment number correctly on the form. Eg : Suppose that we had 5 records in the sql table before delete and despite of deleting the next number would be 6 but in VB.Form it shows as "0".

How to rectify this issue.

Answer 1

please try vb.net of ado.net forum

Answer 2

Hi

When the exisitng records are deleted in the table which has a auto increament number the next available number shows as "0" on the VB.NET Form. But if a record entered directly to the table from then onwards it recognise the auto increment number correctly on the form. Eg : Suppose that we had 5 records in the sql table before delete and despite of deleting the next number would be 6 but in VB.Form it shows as "0".

How to rectify this issue.

Answer 3

Hi wasantha,

Welcome to the MSDN forum.

Would you like to share more information about “the next available number shows as "0" on the VB.NET Form”? In which method you used to connect with SQL server. We need some specific information about your situation.

As for your issue, even the next available number in Vb.net is “0”, but when you want to add a new record to SQL server. It will caused exception or using the record SQL server number automatically.

I look forward your reply.

---

Mark Liu-lxf [MSFT]
MSDN Community Support | Feedback to us

Answer 4

Please check the URL

http://stackoverflow.com/questions/1393208/how-to-update-all-the-autoincrement-columns-in-a-datatable

For better answer please post your sample code.

---

With Thanks and Regards
Sambath Raj.C
click "Proposed As Answer by" if this post solves your problem or "Vote As Helpful" if a post has been useful to you
Happy Programming!

Answer 5

Hi

Yes correct,SQL Server will number automatically. Thing is I have Parent-chiled relation ship in my VB.NET form so in the detail table the corresponding foreign key will not recognise at the time the record is saved to the database and it will caused exception. At the time the record is saved to the database the primary key on the form will be "0" but in the SQL server it will number correctly and which do not have any way to get that number form the parent table to the detail(Chiled) table. Please refere my code as to how I connet with the database.

Thanks.

Public Class frmOrder
   
    Dim OrdersAdapter As New SqlDataAdapter()
    Dim OrdersDetailAdapter As New SqlDataAdapter()
    
    Dim cmdBuilder As New SqlCommandBuilder
    Dim cmdBuilder1 As New SqlCommandBuilder
    Private OrdersBindingSource As New BindingSource()
    Private DetailOrdersBindingSource As New BindingSource()
       
Dim OrdersDataSet As New DataSet()
    Dim OrderSqlCon As New SqlConnection("initial catalog=paymentsmonitoring;integrated security=SSPI;persist security info=false")
Private Sub frmOrder_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
       
        OrderSqlCon.Open()
 OrdersAdapter.SelectCommand = New SqlCommand("SELECT * FROM ORDERR ORDER BY ORDERNO", OrderSqlCon)
        OrdersAdapter.FillSchema(OrdersDataSet, SchemaType.Source, "Orderr")
        OrdersAdapter.Fill(OrdersDataSet, "Orderr")
        OrdersDetailAdapter.SelectCommand = New SqlCommand("SELECT ORDERNO, OrderQty  ,Price ,ITEMID FROM ORDERDETAIL", OrderSqlCon)
        OrdersDetailAdapter.FillSchema(OrdersDataSet, SchemaType.Source, "OrderDetail")
        OrdersDetailAdapter.Fill(OrdersDataSet, "OrderDetail")
Dim relation As New DataRelation("OrdersDetailRel", OrdersDataSet.Tables("Orderr").Columns("OrderNo"), OrdersDataSet.Tables("OrderDetail").Columns("OrderNo"))
        OrdersDataSet.Relations.Add(relation)
        
        OrdersBindingSource.DataSource = OrdersDataSet
        OrdersBindingSource.DataMember = "Orderr"
        DetailOrdersBindingSource.DataSource = OrdersBindingSource
        DetailOrdersBindingSource.DataMember = "OrdersDetailRel"
OrdersDetailDGV.DataSource = DetailOrdersBindingSource
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        
        Try
            If OrdersDataSet.HasChanges(DataRowState.Added) Then
                If OrdersDetailDGV.Rows(1).Cells("Product").Value Is Nothing Then
                    MsgBox("Record is not complete", MsgBoxStyle.MsgBoxHelp, "DGV System")
                    Exit Sub
                Else
                    Me.BindingContext(OrdersDataSet, "Orderr").EndCurrentEdit()
                    cmdBuilder = New SqlCommandBuilder(OrdersAdapter)
                    cmdBuilder1 = New SqlCommandBuilder(OrdersDetailAdapter)
                    Dim MyNewDataSet As DataSet
                    MyNewDataSet = OrdersDataSet.GetChanges
                    OrdersAdapter.Fill(MyNewDataSet)
                    OrdersDetailAdapter.Fill(MyNewDataSet)
                    OrdersAdapter.Update(OrdersDataSet, "Orderr")
                    OrdersDetailAdapter.Update(OrdersDataSet, "OrderDetail")
                    cmbSup.Focus()
                    Call BLC()
                    OrdersBindingSource.AddNew()
                    cmbSup.Focus()
                End If
            End If
 Catch ex As System.Data.SqlClient.SqlException
            MsgBox(ex.Message)
        End Try
    End Sub

Answer 6

or on every delete, you store the value of last id in session or viewstate, then while adding new rows add that value to the id before binding.

regards

joon

Answer 7

Hi Sambath and Joon

Your posts are remedy for the problem but I am wondering and interesting why VB.NET can't identify the next available number in such a situation.

Thanks

Wasantha

Answer 8

Hi Wasantha,

Just a quick scan of you code, I find you have used the command builder in multi-tables. It seems that SQLcommandBuilder is just used in single-table commands. That is the root issue of your code. Here is some information about SqlCommandBuilder Class: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx

Automatically generates single-table commands that are used to reconcile changes made to a DataSet with the associated SQL Server database. This class cannot be inherited.

So I will suggest you to write the SQL command by yourself to resolve this issue.

Hope this helps. 

---

Mark Liu-lxf [MSFT]
MSDN Community Support | Feedback to us

Answer 9

Hi Mark,

I was trying hours and hours to resolve the problem by writing a sql statement but I could not. I could update the tables creating a stored procedure but it was not a solution to the said problem. Could you please give me a sample code/sql statement as how to resolve this.

Thanks.

Wasantha

Answer 10

Hi Wasantha,

If you search on this forum, you will find lots of information about it. Here is some information about this (using different ways):

DataAdapter UpdateCommand: http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/5008cc7e-5e3b-4891-bd4d-2f9583447cc2

problem in update child table in vb.net: http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/d008699b-d2fc-447f-98e9-02135ebac4ad

 Inserting Master-Detail Data into a SQL-Server Compact Edition Database: http://blogs.msdn.com/b/bethmassi/archive/2009/09/15/inserting-master-detail-data-into-a-sql-server-compact-edition-database.aspx

One-To-Many (Master-Detail) Forms with LINQ to SQL: http://blogs.msdn.com/b/bethmassi/archive/2008/02/19/one-to-many-master-detail-forms-with-linq-to-sql.aspx

Hope this helps.

---

Mark Liu-lxf [MSFT]
MSDN Community Support | Feedback to us

Answer 11

Hi mark

The following link posted by you was help me to resolve the problem.

Thanks.

Wasantha

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx