• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Insert a new record to SQL Server using ADO and returns the newly created ID as an output parameter

Insert a new record to SQL Server using ADO and returns the newly created ID as an output parameter

Insert a new record to SQL Server using ADO and returns the newly created ID as an output parameter. Very useful when a customer is signing up

Rate Insert a new record to SQL Server using ADO and returns the newly created ID as an output parameter

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

'I am passing parameters to from the front end. Must
'reference Microsoft ActiveX Data Objects Library.

Public Function InsertCand(ByVal FName As String, ByVal MI As String, _
ByVal LName As String, ByVal Add1 As String, _
ByVal Add2 As String, ByVal City As String, _
ByVal State As String, ByVal Zip As String, _
ByVal Hphone As String, ByVal Wphone As String, _
ByVal Offer As Integer, _
ByVal Office As String, _
Optional ByVal SDate As Date) As Long
                           
Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim oPrm As ADODB.Parameter

On Error GoTo InsertCandErr
'Make Connection         
Set oConn = New ADODB.Connection
    oConn.ConnectionString = "your SQL Server connection string"
    oConn.CursorLocation = adUseClient
    oConn.Open

'Link connection with command object and use Stored Procedure
Set oCmd = New ADODB.Command
    oCmd.CommandType = adCmdStoredProc
    oCmd.CommandText = "sp_InsertCandidate1"
Set oCmd.ActiveConnection = oConn

'Pass parameters in which Stored Procedure is looking for
Set oPrm = New ADODB.Parameter
Set oPrm = oCmd.CreateParameter("@fname", adVarChar, adParamInput, 15, FName)
    oCmd.Parameters.Append oPrm
Set oPrm = oCmd.CreateParameter("@mi", adVarChar, adParamInput, 4, MI)
    oCmd.Parameters.Append oPrm
Set oPrm = oCmd.CreateParameter("@lname", adVarChar, adParamInput, 20, LName)
    oCmd.Parameters.Append oPrm
Set oPrm = oCmd.CreateParameter("@add1", adVarChar, adParamInput, 50, Add1)
    oCmd.Parameters.Append oPrm
Set oPrm = oCmd.CreateParameter("@add2", adVarChar, adParamInput, 50, Add2)
    oCmd.Parameters.Append oPrm
Set oPrm = oCmd.CreateParameter("@city", adVarChar, adParamInput, 50, City)
    oCmd.Parameters.Append oPrm
Set oPrm = oCmd.CreateParameter("@state", adVarChar, adParamInput, 20, State)
    oCmd.Parameters.Append oPrm
Set oPrm = oCmd.CreateParameter("@zip", adVarChar, adParamInput, 10, Zip)
    oCmd.Parameters.Append oPrm
Set oPrm = oCmd.CreateParameter("@hphone", adVarChar, adParamInput, 24, Hphone)
    oCmd.Parameters.Append oPrm
Set oPrm = oCmd.CreateParameter("@wphone", adVarChar, adParamInput, 24, Wphone)
    oCmd.Parameters.Append oPrm
Set oPrm = oCmd.CreateParameter("@offer", adInteger, adParamInput, 1, Offer)
    oCmd.Parameters.Append oPrm
Set oPrm = oCmd.CreateParameter("@office", adVarChar, adParamInput, 50, Office)
    oCmd.Parameters.Append oPrm
Set oPrm = oCmd.CreateParameter("@sdate", adDate, adParamInput, 8, SDate)
    oCmd.Parameters.Append oPrm
Set oPrm = oCmd.CreateParameter("@candid", adInteger, adParamOutput, 4) 'Notice direction of ParamOutput
    oCmd.Parameters.Append oPrm
            
    oCmd.Execute
    'Make function equal to Output Parameter to pass back to front end
    InsertCand = oCmd.Parameters("@candid")

InsertCandExit:
'Cleanup
 oConn.Close
 Set oPrm = Nothing
 Set oCmd = Nothing
 Set oConn = Nothing
 Exit Function

InsertCandErr:
   InsertCand = 0
     modErrorLogging.LogError Err.Number, Err.Description, _
     "clsInsertCandidate", "InsertCandidate"
   Resume InsertCandExit

End Function


Stored Procedure will look something like this--
CREATE PROCEDURE [sp_Insert_ADOExample]
	(
	 @FName_2 	[nvarchar](50),
	 @LName_3 	[nvarchar](50),
	 @Address_4 	[nvarchar](50),
	 @City_5 	[char](20),
	 @State_6 	[char](2),
	 @Zip_7 	[char](10),
	 @ID [int] OUTPUT)

AS INSERT INTO [Database].[dbo].[Table] 
	 (
	 [FName],
	 [LName],
	 [Address],
	 [City],
	 [State],
	 [Zip]) 
 
VALUES 
	( 
	 @FName_2,
	 @LName_3,
	 @Address_4,
	 @City_5,
	 @State_6,
	 @Zip_7)
	Select @ID = @@IDENTITY //Output parameter passed back
GO