Using the WITH statement in code

Create an connection to the db.
set conn = server.createobject("adodb.connection")
conn.open "dsn=sqlsvr;database=pubs;uid=sa;pwd=;"

'Set local variables equals to the form data

strCompanyName = request.form("CompanyName")
strCompanyAddress1 = request.form("CompanyAddress1")
strCompanyAddress2 = request.form("CompanyAddress2")
strCompanyCity = request.form("CompanyCity")
strCompanyState =request.form("CompanyState")
strCompanyZip = request.form("CompanyZip")
strCompanyCountry =request.form("CompanyCountry")
strPrice = request.form("Price")
strCompanyID = request.form("CompanyID")

Create Command Object

set cmd1 = server.createobject("adodb.command")

'Start Your WITH statement
'Notice after that you can just put a period then the method your calling
With cmd1
    .activeconnection = strconn
    .commandtext = "sp_update"
    .CommandType = acCmdStoredProc
    .parameters.append .createparameter("@CompanyName", adVarChar, adParamInput, 150, strCompanyName)
    .parameters.append .createparameter("@CompanyAddress1", adVarChar, adParamInput, 255 , strCompanyAddress1)
    .parameters.append .createparameter("@CompanyAddress2", adVarChar, adParamInput, 255 , strCompanyAddress2)
    .parameters.append .createparameter("@CompanyCity", adVarChar, adParamInput, 150 , strCompanyCity)
    .parameters.append .createparameter("@CompanyState", adVarChar, adParamInput, 50 , strCompanyState)
    .parameters.append .createparameter("@CompanyZip", adVarChar, adParamInput, 50 , strCompanyZip)
    .parameters.append .createparameter("@CompanyCountry", adVarChar, adParamInput, 100 , strCompanyCountry)
    .parameters.append .createparameter("@Price", adVarChar, adParamInput, 50 , strPrice)
    .parameters.append .createparameter("@companyID", adInteger, adParamInput, 0 , strCompanyID)
    .execute lngRecs, , adExecuteNoRecords
'Once done put End WITH
End With
set cmd1 = nothing

blog comments powered by Disqus