Executing SQL Server procedures from asp question

[aolb]

I want to execute SQL Server stored procedures from ASP which I have done. I have included the code for this if anyone's interested.

My question is that one of the parameter fields is an SQL Server 'Text' field which I am to understand is like an Access Memo field.

When a parameter is appended to the command object I have to define the data type of the parameter and I cannot find what the syntax for a SQL Server 'text' data type is. I suspect there isn't one and I will have to define the parameter as a large varchar(which would be 'adVarChar').

Can anyone confirm this please, then I can stop looking.



<%
Dim objConn, objDBConn, objCmd, rs, strSQL, strServer, strUID, strPWD

set objConn = Server.CreateObject(&quot;ADODB.Connection&quot

strServer = Application(&quot;SERVER&quot
strUID = Application(&quot;UID&quot
strPWD = Application(&quot;PWD&quot

objDBConn= &quot;DRIVER={SQL Server};SERVER=&quot; & strServer & &quot;;UID=&quot; & strUID & &quot;;PWD=&quot; & strPWD
objConn.Open objDBConn


' *** Recordset for Customer details
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.ActiveConnection = objConn

Set objobjCmd = Server.CreateObject (&quot;ADODB.Command&quot

objCmd.ActiveConnection = objConn
objCmd.CommandText = &quot;uspInsertSurvey&quot;
objCmd.CommandType = adobjCmdStoredProc

objCmd.Parameters.Append objCmd.CreateParameter (&quot;@Su_Id&quot;,adVarChar,adParamInput,10)

objCmd.Parameters.Append objCmd.CreateParameter (&quot;@Su_RemedyDesc&quot;,adVarChar,adParamInput,100)
objCmd.Parameters.Append objCmd.CreateParameter (&quot;@Su_Cu_Id&quot;,adInteger,adParamInput)
objCmd.Parameters.Append objCmd.CreateParameter (&quot;@Su_IE_Id&quot;,adInteger,adParamInput)
objCmd.Parameters.Append objCmd.CreateParameter (&quot;@Su_SMd_Id&quot;,adInteger,adParamInput)
objCmd.Parameters.Append objCmd.CreateParameter (&quot;@Su_ST_Id&quot;,adInteger,adParamInput)

objCmd.Parameters(&quot;@Su_Id&quot = &quot;fancy&quot;
objCmd.Parameters(&quot;@Su_RemedyDesc&quot = &quot;pants&quot;
objCmd.Parameters(&quot;@Su_Cu_Id&quot = 1
objCmd.Parameters(&quot;@Su_IE_Id&quot = 2
objCmd.Parameters(&quot;@Su_SMd_Id&quot = 1
objCmd.Parameters(&quot;@Su_ST_Id&quot = 2

Set rs = objCmd.Execute

set objCmd = nothing
%>

 

[PatrickGreen]

aolb:

I use text fields to store xml....here is what I do:

.Parameters.Append .CreateParameter(&quot;txtXMLString&quot;, adLongVarWChar, adParamInput, Len(strSQL))
.Parameters.Item(&quot;txtXMLString&quot.AppendChunk strSQL

I hope this helps,

Patrick

 

[mwolf00]

try using less info when you create the parameters....

with objCmd
.commandText = &quot;usp_rentEstSearch&quot;
.CommandType = adCmdStoredProc
.parameters(1) = request(&quot;locCode&quot
.parameters(2) = request(&quot;agCode&quot
.parameters(3) = request(&quot;abCode&quot
.parameters(4) = request(&quot;leaseNum&quot
.parameters(5) = request(&quot;rga&quot
.parameters(6) = request(&quot;teamCode&quot
.parameters(7) = request(&quot;orderBy&quot
End With Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048
Tek-Tips Best Practices: FAQ183-3179

 

[aolb]

Thanks for the input.

What is the largest string I can pass through as a parameted and what is the DataTypeEnum, is it adVarChar or
adLongVarChar or something else?

 

[mwolf00]

I believe that you can pass at least 4000 characters with the following...

objCmd.Parameters.Append objCmd.CreateParameter (&quot;@Su_RemedyDesc&quot;,adVarChar,adParamInput,4000) Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048
Tek-Tips Best Practices: FAQ183-3179

 

[aolb]

thank for the help, I can get upto 8000 characters in a parameter

objCmd.Parameters.Append objCmd.CreateParameter (&quot;@Su_RemedyDesc&quot;,adVarChar,adParamInput,Len(Trim(Request.Form(&quot;txtRemedyDesc&quot)))