4 Replies - 4228 Views - Last Post: 16 April 2009 - 07:10 AM Rate Topic: -----

#1 ktooof  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 14-September 08

how can I create autonumber in oracle and use it in vb.net

Posted 14 April 2009 - 11:34 PM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: how can I create autonumber in oracle and use it in vb.net

#2 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,290
  • Joined: 12-March 09

Re: how can I create autonumber in oracle and use it in vb.net

Posted 15 April 2009 - 01:12 AM

use the query

Quote

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

Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5901
  • View blog
  • Posts: 12,805
  • Joined: 16-October 07

Re: how can I create autonumber in oracle and use it in vb.net

Posted 15 April 2009 - 03:54 AM

View Postnoorahmad, 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.
Was This Post Helpful? 0
  • +
  • -

#4 ktooof  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 14-September 08

Re: how can I create autonumber in oracle and use it in vb.net

Posted 15 April 2009 - 10:28 PM

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
Was This Post Helpful? 0
  • +
  • -

#5 Goethals  Icon User is offline

  • D.I.C Head

Reputation: 25
  • View blog
  • Posts: 145
  • Joined: 14-April 09

Re: how can I create autonumber in oracle and use it in vb.net

Posted 16 April 2009 - 07:10 AM

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


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1