Post reply

ADO Command Object and Parameters

  • 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]

  • 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]

  • 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]

  • 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()

  • 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]

  • 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...

  • 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]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply