[RESOLVED] New SQL record

[jkcontra]

Hi

What is the best method to reference a new record added to SQL?

[motil]

can you provide more details?

[Seraph]

A new record that has been added will be the last one in the table so:

SELECT * FROM <table> ORDER BY id DESC LIMIT 1

Should work. Or something similar.

[motil]

more like

INSERT INTO TABLE (columnname) VALUES(value);SELECT SCOPE_IDENTITY

[jkcontra]

Ok the way Im doing it now is to create an ID count table. So when I add a new record I set it's ID to the count table. I add one number to the count table once the record was created for the next record's ID.

Let's use the following example:

Table = Users
Fiield1 = ID
Field2 = Name

Table = IDCount
Field = ID

So when I add a new User, I first reference the IDCounttable

[Negative0]

A new record that has been added will be the last one in the table so:

SELECT * FROM <table> ORDER BY id DESC LIMIT 1

Should work. Or something similar.

That will only work in a single user environment. if there are multiple users adding rows to the database, there is no way to guarantee that the newly added row is the row you just added.

The question is, what do you need from that new row? If you are using some sort of auto incrementing ID, then your best bet is probably a stored proc that does the insert and then returns the ID to you, using a lock to ensure it gets the correct ID.

[jkcontra]

Hi Motil

How will I use it in the following:

Public Shared Sub Add_Camp(ByVal Number As Integer, ByVal Description As String)
Dim myCon As New SqlClient.SqlConnection(strCon)
Dim strSelect As String = "Insert into Camp(CampNr, Description) Values(@field1, @field2)"
Dim myCommand As New SqlClient.SqlCommand(strSelect, myCon)

With myCommand.Parameters
.Add("@field1", SqlDbType.Int).Value = Number
.Add("@field2", SqlDbType.VarChar).Value = Description
End With

myCon.Open()
myCommand.ExecuteNonQuery()

Dim iInt As Integer
myCommand.CommandText = "SELECT SCOPE_IDENTITY() statement"
iInt = myCommand.ExecuteScalar
MsgBox(iInt)

myCon.Close()
myCon.Dispose()
End Sub

[motil]

i just gave an example in this thread:

http://www.vbforums.com/showthread.php?t=580073

[jkcontra]

Motil, you are a real Angel.

Thank you very much!!!!