p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

proasp_codeclinic thread: How do I send long parameters to sproc


Message #1 by "Walter Burrough" <lists@c...> on Wed, 7 Feb 2001 17:49:30 -0000
Walter, the adLongVarChar type describes *parameters* as well as
fields.

You can load up your parameter and then call your sp with it.  This
will work:

With objCommand
  .CommandType = adCmdStoredProc
  .CommandText = strStoredProcName
  .Parameters.Append .CreateParameter(strParamName, _
    adLongVarchar,adParamInput,MAXDATALENGTH, _
    strLongTextData)
  Set .ActiveConnection = objConnect
  Set rsRecordset = .Execute
End With


----- Original Message -----
From: "Walter Burrough" <lists@c...>
To: "Code Clinic" <proasp_codeclinic@p...>
Sent: Thursday, February 08, 2001 9:21 AM
Subject: RE: How do I send long parameters to sproc


> Thanks, I took a look but as in every other article I've seen
they're using
> appendChunk to add data to a recordset field rather than to send a
> parameter.
>
> In answer to your "why?"-type query: It's not user input and maybe
there is
> a better way to do it. What's happening is the script
> is lifting a text string off another website. It's a sort of 2D
array -
> columns delimited by ~ and rows by commas (e.g.
>
"value1~value2~value3~value4,value1~value2~value3~value4,value1~value2
> ~value3~value4....").
>
> I'm doing some processing on it and then outputting the results to
my sproc
> in the same format to insert all the rows into a table. Guess you
couldcall
> it a bulk insert.
>
> I suppose I could run through the rows in my script and send each
one tothe
> sproc but that seems really wasteful when I should be able to send
the
> whole lot together.
>
> Thanks
> walter
>
>
> -----Original Message-----
> From: Draco Associates Ltd [mailto:jmail@d...]
> Sent: 08 February 2001 13:21
> To: Code Clinic
> Subject: Re: How do I send long parameters to sproc
>
>
> Walter, there isn't an adText type for a parameter, but there is an
> adLongVarChar type which describes a TEXT type field.  You can use
the
> "Chunk" methods to populate adLongVarChar parameters/fields.  Have a
> look at Q194975.
>
> I guess your users are going to create these >8000 character blocks
of
> text in a large textbox?  If they are being assembled any other way
> (i.e., in pieces, or from existing data) I'd write the pieces or the
> keys to existing data to a temporary place in the database, and pass
> the key(s) to that to the sp.
>
> HTH
>
> Mark
>
> ----- Original Message -----
> From: "Walter Burrough" <lists@c...>
> To: "Code Clinic" <proasp_codeclinic@p...>
> Sent: Thursday, February 08, 2001 7:29 AM
> Subject: RE: How do I send long parameters to sproc
>
>
> > Thanks, it is 2.1
> > walter
> >
> > -----Original Message-----
> > From: Daniel [mailto:danielw@w...]
> > Sent: 08 February 2001 12:14
> > To: Code Clinic
> > Subject: RE: How do I send long parameters to sproc
> >
> >
> > Walter,
> >    These lines will return your ADO's version number:
> >
> >          Dim objConn
> >          Set objConn = Server.CreateObject("ADODB.Connection")
> >          Response.Write(objConn.Version)
> >
> >    HTH
> >    Daniel Walker
> >    Wrox Press
> >
> > > I think I must be using ADO2.1 as I'm on win98 and use Office
> 2000.
> > >
> > > I'm only SENDING a long parameter, not recieving a recordset at
> all. So
> > > will I still have problems?
> > >
> > > Thanks
> > > Walter
> > >
> > > -----Original Message-----
> > > From: Ian Barker [mailto:Ian.Barker@b...]
> > > Sent: 08 February 2001 08:42
> > > To: Code Clinic
> > > Subject: Re: How do I send long parameters to sproc
> > >
> > >
> > > What version of ADO do you have? There were some issues in ADO
> 2.x?
> > > about passing long arguments... the resulting recordset would
> raise
> > > an error if it was over 8K... ANd then there were the problems
> marshalling
> > > betwen processes... Check MSDN for more info
> > >
> > > > Subject: Re: How do I send long parameters to sproc
> > > > From: "Richard Xin" <richardxin@h...>
> > > > Date: Wed, 07 Feb 2001 11:10:32 -0800
> > > > X-Message-Number: 13
> > > >
> > > > Just use Text datatype
> > > >
> > > >
> > > > >From: "Walter Burrough" <lists@c...>
> > > > >Reply-To: "Code Clinic" <proasp_codeclinic@p...>
> > > > >To: "Code Clinic" <proasp_codeclinic@p...>
> > > > >Subject: How do I send long parameters to sproc
> > > > >Date: Wed, 07 Feb 2001 19:12:34 -0800
> > > > >
> > > > >Hi,
> > > > >I need to send a long text parameter to a sproc. I can't use
a
> varchar
> > > > >because it is quite possible that they will be longer than
> > > > >8000 characters.
> > > > >I can't find any good explaination anywhere on how to do it.
> Can anyone
> > > > >help please.
> > > > >At the moment I'm using a varchar(8000) like this:
> > > > >
> > > > >------------------------
> > > > >dim objCommLoadCts
> > > > >dim  objParamProcList
> > > > >set objCommLoadCts = server.CreateObject("ADODB.Command")
> > > > >objCommLoadCts.ActiveConnection = strConnect
> > > > >objCommLoadCts.CommandText = "isp_procList"
> > > > >objCommLoadCts.CommandType = adCmdStoredProc
> > > > >
> > > > >set objParamFarmerId 
> > > >
> >objCommLoadCts.CreateParameter("farmerID",adInteger,adParamInput)
> > > > >set objParamProcList 
> > > >
>
>objCommLoadCts.CreateParameter("procList",adVarChar,adParamInput,8000
> )
> > > > >objCommLoadCts.Parameters.Append objParamFarmerId
> > > > >objCommLoadCts.Parameters.Append objParamProcList
> > > > >
> > > > >objCommLoadCts.Parameters("farmerId") = session("farmerID")
> > > > >objCommLoadCts.Parameters("procList") = mid(strProcList,2)
> > > > >objCommLoadCts.Execute adExecuteNoRecords
> > > > >set objParamFarmerId = nothing
> > > > >set objParamProcList = nothing
> > > > >set objCommLoadCts = nothing
> > > > >-----------------------------
> > > > >I just want to do the same thing without the 8000 character
> limit.
> > > > >
> > > > >Thanks
> > > > >walter
> > > > >
> > 

  Return to Index