Auto number increment

[vivsm]

Hi,

I set the auto increment in SQL server for CustNo. How do I display this CustNo in the customer form?? How to retrieve the last CustNo + 1 ??? i am using VB.NET.

Thank you in advance!!

viv

[balmerch]

So you are using an autoincrementing value on the sql database, for a primary key? You can return @@IDENTITY in the sql procedure to get the incremented primary key of the record when you create it.

Chris

[vivsm]

Hi there,

still quite blur.

Yes, the incrementing number is a primary key. How to return @@IDENTITY in the sql procedure to get the incremented primary key of the record ? What i wanted is to display the last number in the front end.

Someone suggested me to use the key word lastResult, hows the syntax like??

Any suggestion or example is appreciated.

viv

[davidrobin]

If I am not mistaken your autoincrement field will have a name. I may have got this wrong but can't you just use the field name.

How are you retrieving the records?
Why do you need CustNo+1?

[taxes]

Hi vivsm.

Do you know how to connect to a database. If so, do it.

Then, do you know how to use datasets etc? If so, what is your specific problem?

If not, you are going to have to read up on working with databases (there is plenty of guidance in the MSDN Help files) and then bring specific problems to this forum.

[balmerch]

So you want the number of rows then? From your last post, this is how it sounded, or did you want the last ID value, because if you delete rows or use a seed the ID incremented value won't sync with the row count. To get your row count though you would use:

SELECT Count(anyColumnName)
FROM yourTable

Now this is purely off the top of my head and its been a while since I had to write sql, so keep that in mind but I'll double check it when I get to my office later today.

Chris

[vivsm]

yes, i want the last ID value, because if i delete rows or use a seed the ID incremented value won't sync with the row count. Thats tha problem i am facing.

viv

[davidrobin]

Does this help

Syntax
IDENT_CURRENT('table_name')

Arguments
table_name

Is the name of the table whose identity value will be returned. table_name is varchar, with no default.

Return Types
sql_variant

Remarks
IDENT_CURRENT is similar to the Microsoft® SQL Server™ 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which 'last' is defined in each of these functions differ.

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.


@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.


SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.


Just add 1 to the returned value

[Andy]

the best thing to do is create a stored procedure. in that, declare a variable such as @ReturnNum and at the end of the stored procedure, do this @ReturnNum = @@Identity.

now, in your vb code, you want to do this:

'InsertCmd is a predefined sqlclient.SqlCommand
    Dim A_Number As SqlParameter = InsertCmd.Parameters.Add("@ReturnNum", SqlDbType.Int)
        ticketNumber.Direction = ParameterDirection.Output

then, you can do something like this:

messagebox.show("The number returned is " & A_number)

hope that helps.