[ktooof]

I have a table hasn't primary key in oracle 10g and I need to add autonumber to table and use this autonumber in vb.net 2005 to control in update ,delete command in vb.net
thanks.

[noorahmad]

use the query

Quote

dim strQuery as string
strQuery = "Select Max(ID)+1 from tblem"

[baavgai]

noorahmad, on 15 Apr, 2009 - 02:12 AM, said:

use the query

Quote

dim strQuery as string
strQuery = "Select Max(ID)+1 from tblem"

No, no, no, for the love of God, no! When two people post at the same time, you'll go down in flames.

There is not database side autonumber in Oracle, which is a bit of a pain. However, there is a database object called Sequence. A sequence is a wonderful thing, because it guarantees a unique value. In Oracle, the standard way to have an "identity" column is to just have a number column and populate it with a value from a sequence for new inserts.

In your code, grab a value from a sequence before you do an insert. The dataset should just hold a basic int.

[ktooof]

thanks for warning, but how can I generate a sequence in oracle and use it in vb.net
this my connection function

Private Shared conn As OracleConnection
Dim str As String = ""
		conn = New OracleConnection("Data Source=orcl;Persist Security Info=True;User ID=scott;password=tiger;Unicode=True")
		Try
			If conn.State = ConnectionState.Closed Then
				conn.Open()

			End If

		Catch ex As Exception
			str = ex.Message
		End Try

		Return str

this is my dml function

Dim cmd As OracleCommand
		dbconn()
		cmd = New OracleCommand(sql, conn)
		Try
			cmd.ExecuteNonQuery()

		Catch ex As Exception
			MessageBox.Show(ex.Message)

		End Try
		Return Nothing

and thanks for help

[Goethals]

Use the following to define a sequence

DROP SEQUENCE AUTONUMBERING;
CREATE SEQUENCE AUTONUMBERING
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;

U can use the sequence as a value like this:

AUTONUMBERING.NEXTVALUE

as in

INSERT INTO RANDOM_TABLE (ID) VALUES (AUTONUMBERING.NEXTVALUE)

U could also google a bit to make oracle do that automaticly,
by assigning a trigger to do it for you, but I dont advise it,
it makes you lose quite some flexibility.

You could also select the sequence value manually if you want,
and assign it manually.

SELECT AUTONUMBERING.NEXTVALUE FROM DUAL