by David Koopman (11 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Thu 27th December 2001
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
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
VBcoders
'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
No comments have been posted about Insert a new record to SQL Server using ADO and returns the newly created ID as an output parameter. Why not be the first to post a comment about Insert a new record to SQL Server using ADO and returns the newly created ID as an output parameter.
