Question Assign Sequence NextVal to textbox

[rajthampi]

Hi guys

I am designing a simple windows form application using typed datasets. My requirement is to assign a sequential value from a database SEQUENCE to one of the textboxes available with the form while default navigator "Save" button is clicked. My Database level Sequence name is "OrderHeader_seq"


My current .vb code as attached

Public Class Form1

    Dim Header_Id As Integer


    Private Sub OrdersBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles OrdersBindingNavigatorSaveItem.Click
       
     ' I want to replace the below static assignment with dynamic values generated from database level Sequences


      ' If Me.Header_idTextBox.Text = "" Then
       '     Me.Header_idTextBox.Text = 1000
       ' End If

        Me.Validate()
        Me.OrdersBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.POSDataSet)

    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Dim Conn As String = My.Settings.POSConnectionString

        MsgBox(Conn)

        Me.OrdersBindingSource.AddNew()

    End Sub

    Private Sub Order_DetailsDataGridView_Enter(sender As Object, e As EventArgs) Handles Order_DetailsDataGridView.Enter

        If Me.Header_idTextBox.Text = "" Then
            MsgBox("Nothing here")
        End If

    End Sub
End Class

Please help

Regards,

raj

 

[jmcilhinney]

Check this out:

Retrieve Access AutoNumber Value After Insert

It was written for Access but I think that it should work for any RDBMS. It may not be required for some databases that may have a simpler specific mechanism but you haven't told us what database you're using so I'm pointing to something generic.

 

[rajthampi]

Check this out:

".. but you haven't told us what database you're using so I'm pointing to something generic.

Dear jmcilhinney

I'm using SQL 2014 Express edition for the learning purpose. Why I asked you for SEQUENCES is because we will be moving from Oracle ERP to Microsoft Dynamics and for many applications we would require to generate different series of numbers based on a certain organization parameters ,ie,

if org = 'abc' then the sequences must start with 300, else for 'xyz' should start with 500 etc

Hence, for the business requirements, Sequences are better approach for us. Please forgive my ignorance about the SQL environment as my whole 15+ years of development experiences are Oracle centric.

regards,

raj

 

[jmcilhinney]

SQL Server doesn't have sequences. It uses identities for auto-generating sequential IDs. They don't have the flexibility that you're relying on so people have to get a bit creative. You should do a bit of reading on SQL Server identities to get up to speed.

 

[rajthampi]

SQL Server doesn't have sequences. It uses identities for auto-generating sequential IDs. They don't have the flexibility that you're relying on so people have to get a bit creative. You should do a bit of reading on SQL Server identities to get up to speed.

Dear jmcilhinney

Correct me if I am wrong, I have clearly created SEQUENCE using the following SQL

CREATE SEQUENCE dbo.OrderHeaderId_Seq
        START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 99999999999999
    CYCLE
    CACHE 10
;

and could retrieve values by using

SELECT NEXT VALUE FOR OrderHeaderId_Seq;

Now all I need is a method to retrieve the next value from the Sequence into a Windows form textbox!

regards,

 

[jmcilhinney]

I meant to add a qualifier to my previous post to the effect of "unless they added it in 2014" but I neglected to. I just had a look and now I see that it was actually added in SQL Server 2012, so I'm rather behind the times. My apologies for the bum steer.

 

[rajthampi]

Dear jmcilhinney

That's all good Now, as you know the sequences already exist and the values from SEQUENCES could be retrieved using simple SQL, can you please guide me how I could achieve my goal with the "Save" button click?

Private Sub OrdersBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles OrdersBindingNavigatorSaveItem.Click

'       
        Me.Validate()
        Me.OrdersBindingSource.EndEdit()

' This area I want to add some kind of coding and assign the value to textbox

        Me.TableAdapterManager.UpdateAll(Me.POSDataSet)

    End Sub

Thanks again


raj

 

[jmcilhinney]

It is interesting that you're asking me to tell you what to do when I just indicated that you actually knew more about sequences than I did. If I'm going to find out about using sequences then I'm going to have to do a bit of research. That's exactly what I did and now I know what to do. I have to wonder why you couldn't do the same research. All I did was look up CREATE SEQUENCE on MSDN, read a bit and then follow a couple of links. Now I know what to do. You should do the same.

 

[rajthampi]

It is interesting that you're asking me to tell you what to do when I just indicated that you actually knew more about sequences than I did. If I'm going to find out about using sequences then I'm going to have to do a bit of research. That's exactly what I did and now I know what to do. I have to wonder why you couldn't do the same research. All I did was look up CREATE SEQUENCE on MSDN, read a bit and then follow a couple of links. Now I know what to do. You should do the same.

Dear jmcilhinney

Did I mention that I started learning VB.Net just few days back? I asked my first question after almost 6 years about Typed Datasets and security and you were the one who answered me. Why I kept getting back to you was just because I really don't know what to write within that Save button code, say, I don't know whether I should initiate a connection, execute a SQL statement, if yes, how the construct should be

Anyway I have started doing more researches as you asked and hopefully come across something that helps.

regards,

raj

 

[rajthampi]

Getting it done!

Hey Guys

I'm sure, this is not the way to achieve what I wanted, however, I have managed something. Copying the code, if the method at all is applicable, please do let me know

Imports System.Data.SqlClient

Public Class Form1

    Dim Header_Id As Integer


    Private Sub OrdersBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles OrdersBindingNavigatorSaveItem.Click

        Dim conn As New SqlClient.SqlConnection(My.Settings.POSConnectionString)
        Dim CmdTxt As String = "SELECT NEXT VALUE FOR OrderHeaderId_Seq"

        Dim objCmda As SqlCommand = New SqlCommand(CmdTxt, conn)
        Dim dra As SqlDataReader

        objCmda.CommandType = CommandType.Text
        conn.Open()
        dra = objCmda.ExecuteReader()

        Do While dra.Read()
            Me.Header_idTextBox.Text = dra(0)
        Loop
conn.close()


        Me.Validate()
        Me.OrdersBindingSource.EndEdit()
        Me.Order_DetailsBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.POSDataSet)

    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        'Dim Conn As String = My.Settings.POSConnectionString

        'MsgBox(Conn)

        Me.OrdersBindingSource.AddNew()

    End Sub

    Private Sub Order_DetailsDataGridView_Enter(sender As Object, e As EventArgs) Handles Order_DetailsDataGridView.Enter

        'If Me.Header_idTextBox.Text = "" Then
        '    MsgBox("Nothing here")
        'End If

    End Sub
End Class

regards,

raj

 

[jmcilhinney]

The NEXT VALUE FOR part is exactly what I was implying that you should have been able to find for yourself and you've done exactly that. That's exactly why you should be looking for yourself first and then asking questions if and only if you can't what you need or understand what you find.

Anyway, what you have is not wrong but not ideal. If you're using a typed DataSet then you should stick to using a typed DataSet. By default, it will include queries for just the tables in your database but you can add your own custom queries and then execute them by calling methods on table adapters. You should start here:

TableAdapter Overview

You'll see in the index that there is a topic dedicated specifically to adding queries to table adapters.

 

[rajthampi]

TableAdapter Overview
You'll see in the index that there is a topic dedicated specifically to adding queries to table adapters.

Dear jmcilhinney

That's what I really wanted jmcilhinney. Thank you so much! I would like to post the sample for some newbies like me who are trying to switch from other development scenarios to SQL + VB.Net. Please let me know

regards,

raj