How do we handle autonumbers in VBNet?

Do we include it in our Select Statements?
if Yes:
  What happens when we update it?
If No:
  How do we get the values


Proud To Be Pinoy

__________________
Proud To Be Pinoy

I'm not sure what you mean. Are you asking how to retrieve them?

Just like any other program that deals with a database system that allows autonumbered fields, you have to use and insert statement that does not specify a value for that field. When you update, you can not update that field (which you normally would never do anyway because usually the autonumber field is the or part of a key to a record).

You can select from the autonumber field normally.

Dear reyboy!

You must not assign a value to an AutoNumber field in your inserts or updates. Doing so will surely throw an exeption for you 2 catch.

You can select an AutoNumber field as you would fields that were not AutoNumber. If you are using DataAdapter and DataSet in VB.Net then there's something you should be aware of. Here it goes:

In order to make your DataSet synchronized with the underlying Data Source in regard to a AutoNumber field you will need to use a stored procedure for your insert which has an output parameter that selects the AutoNumber just generated by the most recent insert. In code it looks like this

assume u have this stored proc in Northwind databse (SQL Server) for the products table. the productid is an AutoNumber field:

CREATE PROC spAddProduct
    @ProductName NVARCHAR(40),
    @ProductID INT OUTPUT
AS
INSERT INTO Products (ProductName)
VALUES (@ProductName)

SELECT @ProductID = @@Identity
GO

Now from VB.Net, here is how u'd configure a data set and a data adapter

Dim productsAdapter As New SqlDataAdapter
Dim nwindDataSet As New DataSet

' configure select cmd....

' configure update cmd....

' configure delete cmd....

' configure INSERT cmd....
Dim prm As SqlParameter
Dim cmdInsert As New SqlCommand
cmdInsert = conn.CreateCommand()
cmdInsert.CommandText = "spAddProduct"
cmdInsert.CommandType = CommandType.StoredProcedure
prm = cmdInsert.Parameters.Add("@ProductName", SqlDbType.NVarChar, 40, "ProductName")
prm = cmdInsert.Parameters.Add("@ProductID", SqlDbType.Int)
prm.SourceColumn = "ProductID"
prm.Direction = ParameterDirection.Output
productsAdapter.InsertCommand = cmdInsert

' now fill the data set
productsAdapter.Fill(nwindDataSet, "Products")

' display results in data grid
productsDataGrid.DataSource = nwindDataSet
productsDataGrid.DataMember = "Products"

And after you add new rows to the data grid and save them back the data grid will automatically sho you the new AutoNumber productID.
To save data back use this code

productsAdapter.Update(nwindDataSet)

Plz notice how we configured the insert command. we explicitly told the data adapter that the @ProductID parameter was an output parameter by using this statement

prm.Direction = ParameterDirection.Output

This ensures that the DataAdapter passes the newly generated ProductID (which is an AutoNumber) back to the DataSet.which is finally displayed in the DataGrid.

I hope it would help u learn how to use AutoNumber with DataAdapter, DataSet and DataGrid.



ejan