Get Primary Key

[gaaz]

Please could some1 help.

 

I have a OleDbConnection, to an access database (jet4) OleDbCommand then than Inserts a record - which works.

 

For each record a Autonumber primary key is created. Now what i want is for the newly created id to get brought back and showed on the form.?

 

Heres my code to insert.

 

 

 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

 

 

 

'-----------------------------------------------

OleDbCommand1.Parameters("Name").Value = TextBox.Text

 

 

'-----------------------------------------------

Dim SQLreturn As Integer

'OPEN THE CONNECTION AND EXECUTE THE COMMAND

Try

OleDbConnection1.Open()

SQLreturn = OleDbCommand1.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

 

'END - SHOW EX MESSAGE IF NEED BE

'-----------------------------------------------

 

 

'-----------------------------------------------

'TEST IF STUFF WENT IN

If SQLreturn <> 1 Then

MessageBox.Show("Could not execute the INSERT query", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

Else

MessageBox.Show("Database Update Sucsesfull", "Confirm", MessageBoxButtons.OK, MessageBoxIcon.Information)

End If

OleDbConnection1.Close()

'END ME TEST - CLOSE THE CONNECTION

'-----------------------------------------------

 

End Sub

[wessamzeidan]

I had that same problem, what I did was, after I insert the record, I use the values just inserted in a select statement to retrieve the generated ID

[gaaz]

Hiya

 

I had that same problem' date=' what I did was, after I insert the record, I use the values just inserted in a select statement to retrieve the generated ID[/quote']

 

 

How woudl I do that?

 

Could you paste in the code?

[wessamzeidan]

       Dim qry As String = "insert into Media(Name,Description,Path,TypeID,FileType,location) values(?,?,?,?,?,3)"

       Dim parName As New OleDb.OleDbParameter("Name", OleDb.OleDbType.LongVarChar)
       Dim parDescription As New OleDb.OleDbParameter("Description", OleDb.OleDbType.LongVarChar)
       Dim parPath As New OleDb.OleDbParameter("Path", OleDb.OleDbType.LongVarChar)
       Dim parTypeID As New OleDb.OleDbParameter("TypeID", OleDb.OleDbType.Numeric)
       Dim parFileType As New OleDb.OleDbParameter("FileType", OleDb.OleDbType.LongVarChar)

       Dim cmm As New OleDb.OleDbCommand(qry)
       Dim idselect As New OleDb.OleDbCommand
       Dim qry1 As String = "select id from media where name=? and description=? and path=? and typeid=? and filetype=?"

       cmm.Connection = cn
       cmm.Parameters.Add(parName)
       cmm.Parameters.Add(parDescription)
       cmm.Parameters.Add(parPath)
       cmm.Parameters.Add(parTypeID)
       cmm.Parameters.Add(parFileType)

       cmm.Parameters("Name").Value = mName
       cmm.Parameters("Description").Value = description
       cmm.Parameters("Path").Value = mypath
       cmm.Parameters("TypeID").Value = Me.cmbTypes.SelectedValue
       cmm.Parameters("FileType").Value = ext
       cn.Close()
       cn.ConnectionString = conString
       Try
           cn.Open()

           cmm.ExecuteNonQuery()

           cn.Close()
       Catch ex As Exception
           MessageBox.Show(ex.ToString, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
           Exit Sub
       End Try
       
       cmm.CommandText = qry1
       Dim dr As OleDb.OleDbDataReader
       cn.Open()
       dr = cmm.ExecuteReader
       Dim id As String

       If dr.Read Then
           id = dr.Item("ID")
       End If
       dr.Close()
       cn.Close()