Getting back the autonumber value for a new inserted record RRS feed

  • Question

  • User-800224740 posted
    All: I am trying to use the code below to get back the record id number for record inserted. All i keep getting back in my variable "CusID" is a Zero? Any help/insight would be greatly appreciated. Also, I would prefer not to have to issue another SQL stmt just to get the rec id nunber. TIA, Ash. My Code: SQLCmd.CommandText = "Insert into CustomerMaster (CM_FirstName,CM_LastName,CM_Address1,CM_Address2,CM_City,CM_State,CM_Zip,CM_Phone,CM_Email) Values(?,?,?,?,?,?,?,?,?)" SQLCmd.Parameters.Add(New OleDbParameter("@FN", OleDbType.VarChar)) SQLCmd.Parameters.Add(New OleDbParameter("@LN", OleDbType.VarChar)) SQLCmd.Parameters.Add(New OleDbParameter("@AD1", OleDbType.VarChar)) SQLCmd.Parameters.Add(New OleDbParameter("@AD2", OleDbType.VarChar)) SQLCmd.Parameters.Add(New OleDbParameter("@CT", OleDbType.VarChar)) SQLCmd.Parameters.Add(New OleDbParameter("@ST", OleDbType.VarChar)) SQLCmd.Parameters.Add(New OleDbParameter("@ZP", OleDbType.VarChar)) SQLCmd.Parameters.Add(New OleDbParameter("@PH", OleDbType.VarChar)) SQLCmd.Parameters.Add(New OleDbParameter("@EM", OleDbType.VarChar)) SQLCmd.Parameters("@FN").Value = TxtFName.Text SQLCmd.Parameters("@LN").Value = TxtLName.Text SQLCmd.Parameters("@AD1").Value = TxtAddress1.Text SQLCmd.Parameters("@AD2").Value = TxtAddress2.Text SQLCmd.Parameters("@CT").Value = TxtCity.Text SQLCmd.Parameters("@ST").Value = DDLState.SelectedItem.Value SQLCmd.Parameters("@ZP").Value = TxtZip.Text SQLCmd.Parameters("@PH").Value = TxtPhone.Text SQLCmd.Parameters("@EM").Value = TxtEmail.Text Dim CusID As Integer DBConnection.Open() Try CusID = SQLCmd.ExecuteScalar Show_Controls("Y") LblCustomerNo.Text = Convert.ToString(CusID) Catch ex As Exception Response.Write(Err.Description) Response.End() End Try DBConnection.Close()
    Sunday, October 3, 2004 6:27 PM

All replies

  • User-1856965531 posted
    Hi, This article covers SQL Server and Access. This shows a typical DataSet/DataAdapter approach. If you are using Access but not a DataSet you could: (Use ExecuteNonQuery() for the insert, not ExecuteScalar() as in your code). After the Insert, you can do something like: ... SQLCmd.Parameters.Clear() SQLCmd.CommandText = "SELECT @@IDENTITY" SQLCmd.CommandType = CommandType.Text Dim id as Integer = SQLCmd.ExecuteScalar() ... If you are using SQL Server, use SCOPE_IDENTITY() instead, or if you use stored procedures use another parameter with ParameterDirection.Output, and change your stored proc to include something like SET @IdentityOut = SCOPE_IDENTITY() after the insert. (You can use "SELECT @@IDENTITY" also with SQL Server, as long as you are not using insert triggers. See also MSDN.) For completeness... If you were using Oracle, you could use: "SELECT SequenceName.CURRVAL FROM DUAL" or "SELECT SequenceName.NEXTVAL FROM DUAL" If you were using MySQL, you could use: "LAST_INSERT_ID()" Of course there is SELECT MAX(<primaryKey>) FROM <tablename>, which is not reliable in a multiuser environment, but does nonetheless work when you have an integer primary key and a single user system. Yet other (pretty dodgy and not-recommended!) approach for single user Access stuff, is as follows: 1. Create this table in Access: CREATE TABLE DUAL (ID Long NOT NULL); 2. Insert a value: INSERT INTO DUAL VALUES (0); 3. Per insert, call an Access _function_ instead using SELECT, such as: SELECT InsertRecord("MyString", myInteger) FROM DUAL; 4. Write a VBA function to do the insert using ADO recordset. Public Function InsertRecord(ByRef aString As String, ByRef anInteger As Integer) As Long On Error GoTo ErrorHandler ' declare variables Dim rs As ADODB.Recordset Dim con As ADODB.Connection Dim sSQL As String Dim newID As Long ' instance recordset and connection Set rs = New ADODB.Recordset Set con = CurrentProject.Connection ' open rs, start ADO trans, insert, update, commit Call rs.Open("TableName", con, adOpenKeyset, adLockPessimistic, adCmdTable) con.BeginTrans rs.AddNew rs("StringColumn") = aString rs("IntegerColumn") = anInteger rs.Update newID = rs("TableNameID") con.CommitTrans ' tidy Set con = Nothing Set rs = Nothing ' return InsertRecord = newID Exit Function ' error handler ErrorHandler: con.RollbackTrans Set con = Nothing Set rs = Nothing End Function Anthony.
    Monday, October 4, 2004 8:15 AM