I have a Formview on ASP page that insert records to mdb file.

Eg the table has 2 col: ID, Name

I want the ID field to be automatically be populated as the next largest number on the existing ID values when I add  record via FormView.

Eg the table now has 2 rows

1, John
2, Mary

I want FormView only display Name field when keying in and inserting record but oninsert, it add value of 3 (ie max of existing +1) and save to ID filed as user insert record of the names.

What is the syntax of max +1 and where to put it ?

Thanks

If you set the ID column in your access db to be an AutoNumber field type, then it will increment automatically.

Thanks. Yes but I also need to delete the records on ASP gridview and setting it to autonumber would causes error in deleting.

I tried to write this script and there is an error. How to do it ? Thanks

Protected Sub AccessDataSource1_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)
        e.Command.Parameters("ID").Value = Val(SELECT max(ID) FROM TableA)+1

End Sub

Any expert can help ?

hkbeer:

Yes but I also need to delete the records on ASP gridview and setting it to autonumber would causes error in deleting.

That doesn't make any sense.  There should be no error as a result of using the autonumber feature.  What error do you get? Perhaps we can solve that?  Using MAX(ID) + 3 (or similar) will cause problems if two people are inserting at the same time.

In fact if I make that ID as autonumber, when inserting record through FormView, I get this error message

System.Data.OleDb.OleDbException: You tried to assign the Null value to a variable that is not a Variant data type.

 Seems autonumber only works in inserting record within Access but not from ASP page control ?

Anyone can help ?
Or, anyone use Autonumber with no problem including deleting records through Gridview?

hkbeer:

System.Data.OleDb.OleDbException: You tried to assign the Null value to a variable that is not a Variant data type.

I suspect that the ID column is included in the InsertCommand and/or there is a parameter for this field.  Delete them boith.

Thanks Mike, yes I have added that in insert or delete parameters. By default it is there. ok I will try deleting them. Thanks again Mike. Great !

Hi Mike, it works !! Thanks !!