insert record with bind parameters and get the new id on mssql

[Costas]

Function AddTeam(user)
    Dim rs, sql, cmd
    dim adCmdText, adInteger, adVarWChar, adDBTimeStamp, adParamInput, adParamOutput
 
    adInteger = 3
    adCmdText = 1
    adVarWChar = 202
    adParamInput = 1
    adParamOutput = 2
    adDBTimeStamp = 135

    'dummy sql to get the ActiveConnection
    set rs = GetRecordset("select top 1 id from Tournament")

    sql = "INSERT INTO [Tournament]([user_op],[date_start],[date_end])VALUES(?, ?, ?); SET ?= SCOPE_IDENTITY();"

    'instantiate COMMAND and pass the RS connection
    Set cmd = Server.CreateObject("ADODB.Command")
 
    with cmd

        .ActiveConnection = rs.ActiveConnection
        .CommandText = sql
        .CommandType = adCmdText
   
        .Parameters.Append(.CreateParameter("@user_op", adVarWChar, adParamInput, 50))
        .Parameters("@user_op").Value = user
   
        .Parameters.Append(.CreateParameter("@date_start", adDBTimeStamp, adParamInput))
        .Parameters("@date_start").Value = Now()
   
        .Parameters.Append(.CreateParameter("@date_end", adDBTimeStamp, adParamInput))
        .Parameters("@date_end").Value = Null

        .Parameters.Append(.CreateParameter("@newID", adInteger, adParamOutput))

        .Execute
 
        Dim newID
        newID = .Parameters("@newID").Value

    End With

    If Not rs Is Nothing Then
        rs.ActiveConnection.Close
        Set rs.ActiveConnection = Nothing
    End If
                       
    AddTeam = newID
End Function

Public Function GetRecordset(sql)
        Dim objConn
        Dim rsServer
   
        Set objConn = Server.CreateObject("ADODB.Connection")
        objConn.ConnectionString = application("connection_string")
        objConn.Open   

        set rsServer = Server.CreateObject("ADODB.Recordset")
        rsServer.CursorLocation = 3 'adUseClient
        rsServer.open sql,objconn, 3, 1 'adLockReadOnly
   
        Set GetRecordset = rsServer
End function

• see the project on github
• more - https://www.pipiscrew.com/threads/internet-explorer-11-vs-edge.144511

---

references :
https://www.w3schools.com/asp/prop_comm_commandtype.asp
https://www.w3schools.com/asp/met_comm_createparameter.asp
https://stackoverflow.com/q/16478821
https://stackoverflow.com/q/28682604
https://stackoverflow.com/a/22037613
https://stackoverflow.com/a/30325853
https://www.vbforums.com/showthread.php?a&p=5092001

#asp #classic #newid