ADO Command Object and Parameters

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  March 11, 2004 at 2:42 am

  #84245

  Hi there,

  apparently I'm kinda brain dead this morning, but how do I append two parameters to an ADO command object that fires a s_proc?

  Set rsCompany = Server.CreateObject("ADODB.Recordset")

  Set cmdCompany = Server.CreateObject("ADODB.Command")

  Set cmdCompany.ActiveConnection = cnn

  cmdCompany.CommandText="portfolio_manuals"

  cmdCompany.CommandType = adCmdStoredProc

  .....

  Set rsCompany = cmdCompany.Execute

  ???

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  March 11, 2004 at 5:56 am

  #498250

  Ok, a little bit further now.

  With cm

  .ActiveConnection = cnn

  .commandText = "kapitalanlage_insert"

  .CommandType= adCmdStoredProc

  .Prepared=True

  .Parameters.Append .CreateParameter("@bezeichnung",adVarChar,adParamInput,100)

  .Parameters.Append .CreateParameter("@riskID", adInteger, adParamInput)

  .Parameters.Append .CreateParameter("@issue_date",adDate,adParamInput)

  .Parameters.Append .CreateParameter("@maturity", adDate,adParamInput)

  .Parameters.Append .CreateParameter("@coupon", adVarChar, adParamInput,10)

  .Parameters.Append .CreateParameter("@bSpezialfonds", adVarChar,adParamInput,1)

  .Parameters.Append .CreateParameter("@bManualUpdate", adVarChar, adParamInput,1)

  .Parameters.Append .CreateParameter("@bCashPosition", adVarChar, adParamInput,1)

  .Parameters("@bezeichnung") = Request("bezeichnung")

  .Parameters("@riskID") = CInt(Request("Risiko"))

  .Parameters("@issue_date") = Request("issuedate")

  .Parameters("@maturity") = Request("maturity")

  .Parameters("@coupon") = Request("coupon")

  .Parameters("@bSpezialfonds") = Request("spezialfonds")

  .Parameters("@bManualUpdate") = Request("manualupdate")

  .Parameters("@bCashPosition") = Request("cashposition")

  .Execute

  End With

  results in

  Error Type:

  Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)

  [Microsoft][ODBC SQL Server Driver]Optional feature not implemented

  /fai/asp/assetnew.asp, line 33

  ????

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  March 11, 2004 at 6:10 am

  #498257

  Got it finally !!!

  WTF doesn't MS products speak the same language ????

  Changing

  .Parameters.Append .CreateParameter("@issue_date",adDate,adParamInput)

  .Parameters.Append .CreateParameter("@maturity", adDate,adParamInput)

  to

  .Parameters.Append .CreateParameter("@issue_date",adVarChar,adParamInput,12)

  .Parameters.Append .CreateParameter("@maturity", adVarChar,adParamInput,12)

  did the trick.

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• tymberwyld

  SSCertifiable

  Points: 7810

  More actions

  March 12, 2004 at 6:36 am

  #498488

  Hmm...there's an easier way...

  Once you have given the Connection Object to the ADO Command, it can auto-magically retrieve the parameters from the DataSource (however, this does not work with Sybase databases).

  Ex.

  Dim adoCon As ADODB.Connection

  Dim adoCmd As ADODB.Command

  Dim rsData As ADODB.Recordset

      

       Set adoCon = New ADODB.Connection: adoCon.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDB;Integrated Security=SSPI"

       Set adoCmd = New ADODB.Command

       With adoCmd

            Set .Connection = adoCon

            .CommandType = adStoredProc

            .CommandText = "sp_MyProc"

            .Parameters("@Param1").Value = 1

            .Parameters("@Param2").Value = "This is some text"

       End With

      

       'If you DO NOT want to Return a Recordset

       Call adoCmd.Execute(, , adExecuteNoRecords)

      

       'If you DO want to Return a Recordset

       Set rsData = adoCmd.Execute()

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  March 12, 2004 at 6:49 am

  #498491

  Now that's a nice way to smartly save some typing. Thanks, I'll try it!

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• lionfan91

  SSCrazy Eights

  Points: 8794

  More actions

  March 12, 2004 at 6:59 am

  #498494

  I do this all the time.  However, you don't even need a Connection object, just a connection string.  I have an INCLUDE file (for ASP) that contains my connection string.

  e.g.

  connString = "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDB;Integrated Security=SSPI"

  and then the code could be

  Dim adoCmd As ADODB.Command

  Dim rsData As ADODB.Recordset

       

  Set adoCmd = New ADODB.Command

  With adoCmd

            Set .Connection = connString

            .CommandType = adCmdStoredProc

            .CommandText = "sp_MyProc"

            .Parameters("@Param1").Value = 1

            .Parameters("@Param2").Value = "This is some text"

  End With

  ...    

  works like a champ...

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  March 12, 2004 at 7:06 am

  #498499

  That one I have already. Next will be to have an include file I pass an array of values (parameters) that returns the recordset to work with.

  Does ASP support ParamArray?

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]