|
 |
asp_databases thread: What is the right type for stored procedure parameters??
Message #1 by Imar Spaanjaars <Imar@S...> on Fri, 15 Sep 2000 09:56:07 +0200
|
|
Hi there,
Can someone tell me what type of parameter I have to use in the
Command.CreateParameter method when my Stored Procedure expects a Text type??
I tried the Refresh command on the Parameters collection, and then printed
out the type of the parameter.
I seems to return 200 for ntext and 202 for a nvarchar.
When I look up theses values in adovbs.inc, they give me:
Const adVarChar = 200 ' Is the ntext parameter in the Stored Procedure.
Const adVarWChar = 202 ' Is a normal nvarchar in the Stored Procedure.
Is this correct?? Is the text / ntext an adVarChar in ADO?? And a nvarchar
an adVarWChar??
This is the source I have
Dim objCommand
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = sMyConnectionString
objCommand.CommandText = "spInsertSomething"
objCommand.CommandType = adCmdStoredProc
objCommand.Parameters.Append(objCommand.CreateParameter("Return",
adInteger, adParamReturnValue, 4))
objCommand.Parameters.Append(objCommand.CreateParameter("sDescription",
adVarChar, adParamInput, 100, sDescription))
objCommand.Parameters.Append(objCommand.CreateParameter("sText", ad???????,
adParamInput, ????, sText))
When I don't add the parameters manually but use the refresh command and a
for next loop I get the 200 and 202 results
objCommand.Parameters.Refresh
for each objParam in objCommand.Parameters
Response.Write(objParam.Name & " has type " & objParam.Type & "<BR>")
next
This results in
RETURN_VALUE has type 3
sDescription has type 202
sText has type 200
Any suggestions??
Imar
Message #2 by "Asmadi Ahmad" <chloro@e...> on Fri, 15 Sep 2000 19:15:55 +0800
|
|
I use adchar but I must put the size of the parameter . This is an example
of stored procedure using Access97.
Maybe this could help..
example source code:
strcnn = "DSN=myDSN;User ID=;Password=;"
cn.Open strcnn
cn.CursorLocation = adUseClient
Set cmd1 = New ADODB.Command
cmd1.CommandText = "qryPatient"
cmd1.CommandType = adCmdUnknown
Set param1 = cmd1.CreateParameter("prmName", adChar, adParamInput)
param1.Size = 30
cmd1.Parameters.Append param1
param1.Value = txtNameSearch.Text
Set cmd1.ActiveConnection = cn
Set re1 = cmd1.Execute
----- Original Message -----
From: Imar Spaanjaars <Imar@S...>
To: ASP Databases <asp_databases@p...>
Sent: Friday, 15 September, 2000 3:56 PM
Subject: [asp_databases] What is the right type for stored procedure
parameters??
> Hi there,
>
> Can someone tell me what type of parameter I have to use in the
> Command.CreateParameter method when my Stored Procedure expects a Text
type??
>
> I tried the Refresh command on the Parameters collection, and then printed
> out the type of the parameter.
> I seems to return 200 for ntext and 202 for a nvarchar.
>
> When I look up theses values in adovbs.inc, they give me:
>
> Const adVarChar = 200 ' Is the ntext parameter in the Stored Procedure.
> Const adVarWChar = 202 ' Is a normal nvarchar in the Stored Procedure.
>
> Is this correct?? Is the text / ntext an adVarChar in ADO?? And a nvarchar
> an adVarWChar??
>
>
> This is the source I have
>
> Dim objCommand
> Set objCommand = Server.CreateObject("ADODB.Command")
> objCommand.ActiveConnection = sMyConnectionString
> objCommand.CommandText = "spInsertSomething"
> objCommand.CommandType = adCmdStoredProc
> objCommand.Parameters.Append(objCommand.CreateParameter("Return",
> adInteger, adParamReturnValue, 4))
> objCommand.Parameters.Append(objCommand.CreateParameter("sDescription",
> adVarChar, adParamInput, 100, sDescription))
> objCommand.Parameters.Append(objCommand.CreateParameter("sText",
ad???????,
> adParamInput, ????, sText))
>
> When I don't add the parameters manually but use the refresh command and a
> for next loop I get the 200 and 202 results
>
> objCommand.Parameters.Refresh
> for each objParam in objCommand.Parameters
> Response.Write(objParam.Name & " has type " & objParam.Type & "<BR>")
> next
>
> This results in
>
> RETURN_VALUE has type 3
> sDescription has type 202
> sText has type 200
>
>
> Any suggestions??
>
>
> Imar
>
>
Message #3 by Imar Spaanjaars <Imar@S...> on Fri, 15 Sep 2000 13:57:00 +0200
|
|
OK, but will this work for text?? Theoretically, my text could be very
large, like thousands and thousands of characters.
Imar
At 07:15 PM 9/15/2000 +0800, you wrote:
>I use adchar but I must put the size of the parameter . This is an example
>of stored procedure using Access97.
>Maybe this could help..
>example source code:
>
>strcnn = "DSN=myDSN;User ID=;Password=;"
>cn.Open strcnn
>cn.CursorLocation = adUseClient
>Set cmd1 = New ADODB.Command
>cmd1.CommandText = "qryPatient"
>cmd1.CommandType = adCmdUnknown
>Set param1 = cmd1.CreateParameter("prmName", adChar, adParamInput)
>param1.Size = 30
>cmd1.Parameters.Append param1
>param1.Value = txtNameSearch.Text
>Set cmd1.ActiveConnection = cn
>Set re1 = cmd1.Execute
>
>
>
>----- Original Message -----
>From: Imar Spaanjaars <Imar@S...>
>To: ASP Databases <asp_databases@p...>
>Sent: Friday, 15 September, 2000 3:56 PM
>Subject: [asp_databases] What is the right type for stored procedure
>parameters??
>
>
> > Hi there,
> >
> > Can someone tell me what type of parameter I have to use in the
> > Command.CreateParameter method when my Stored Procedure expects a Text
>type??
> >
> > I tried the Refresh command on the Parameters collection, and then printed
> > out the type of the parameter.
> > I seems to return 200 for ntext and 202 for a nvarchar.
> >
> > When I look up theses values in adovbs.inc, they give me:
> >
> > Const adVarChar = 200 ' Is the ntext parameter in the Stored Procedure.
> > Const adVarWChar = 202 ' Is a normal nvarchar in the Stored Procedure.
> >
> > Is this correct?? Is the text / ntext an adVarChar in ADO?? And a nvarchar
> > an adVarWChar??
> >
> >
> > This is the source I have
> >
> > Dim objCommand
> > Set objCommand = Server.CreateObject("ADODB.Command")
> > objCommand.ActiveConnection = sMyConnectionString
> > objCommand.CommandText = "spInsertSomething"
> > objCommand.CommandType = adCmdStoredProc
> > objCommand.Parameters.Append(objCommand.CreateParameter("Return",
> > adInteger, adParamReturnValue, 4))
> > objCommand.Parameters.Append(objCommand.CreateParameter("sDescription",
> > adVarChar, adParamInput, 100, sDescription))
> > objCommand.Parameters.Append(objCommand.CreateParameter("sText",
>ad???????,
> > adParamInput, ????, sText))
> >
> > When I don't add the parameters manually but use the refresh command and a
> > for next loop I get the 200 and 202 results
> >
> > objCommand.Parameters.Refresh
> > for each objParam in objCommand.Parameters
> > Response.Write(objParam.Name & " has type " & objParam.Type & "<BR>")
> > next
> >
> > This results in
> >
> > RETURN_VALUE has type 3
> > sDescription has type 202
> > sText has type 200
> >
> >
> > Any suggestions??
> >
> >
> > Imar
> >
> >
>
Message #4 by "Asmadi Ahmad" <chloro@e...> on Sat, 16 Sep 2000 09:28:35 +0800
|
|
Why would your parameter text be thousand and thousands of characters? My
parameters are usually small in size supplied just to get the stored query
running. Are u using a memo field content as your parameter? I cannot
possibly imagine why you would want to do that. Sorry but I cannot
understand your usage of parameter. Maybe our definition of 'text' is
different. My definition of text is the field type 'text' which is limited
to 255 characters.
asmadi
www.effitech.com
----- Original Message -----
From: Imar Spaanjaars <Imar@S...>
To: ASP Databases <asp_databases@p...>
Sent: Friday, 15 September, 2000 7:57 PM
Subject: [asp_databases] Re: What is the right type for stored procedure
parameters??
> OK, but will this work for text?? Theoretically, my text could be very
> large, like thousands and thousands of characters.
>
> Imar
>
>
> At 07:15 PM 9/15/2000 +0800, you wrote:
> >I use adchar but I must put the size of the parameter . This is an
example
> >of stored procedure using Access97.
> >Maybe this could help..
> >example source code:
> >
> >strcnn = "DSN=myDSN;User ID=;Password=;"
> >cn.Open strcnn
> >cn.CursorLocation = adUseClient
> >Set cmd1 = New ADODB.Command
> >cmd1.CommandText = "qryPatient"
> >cmd1.CommandType = adCmdUnknown
> >Set param1 = cmd1.CreateParameter("prmName", adChar, adParamInput)
> >param1.Size = 30
> >cmd1.Parameters.Append param1
> >param1.Value = txtNameSearch.Text
> >Set cmd1.ActiveConnection = cn
> >Set re1 = cmd1.Execute
> >
> >
> >
> >----- Original Message -----
> >From: Imar Spaanjaars <Imar@S...>
> >To: ASP Databases <asp_databases@p...>
> >Sent: Friday, 15 September, 2000 3:56 PM
> >Subject: [asp_databases] What is the right type for stored procedure
> >parameters??
> >
> >
> > > Hi there,
> > >
> > > Can someone tell me what type of parameter I have to use in the
> > > Command.CreateParameter method when my Stored Procedure expects a Text
> >type??
> > >
> > > I tried the Refresh command on the Parameters collection, and then
printed
> > > out the type of the parameter.
> > > I seems to return 200 for ntext and 202 for a nvarchar.
> > >
> > > When I look up theses values in adovbs.inc, they give me:
> > >
> > > Const adVarChar = 200 ' Is the ntext parameter in the Stored
Procedure.
> > > Const adVarWChar = 202 ' Is a normal nvarchar in the Stored Procedure.
> > >
> > > Is this correct?? Is the text / ntext an adVarChar in ADO?? And a
nvarchar
> > > an adVarWChar??
> > >
> > >
> > > This is the source I have
> > >
> > > Dim objCommand
> > > Set objCommand = Server.CreateObject("ADODB.Command")
> > > objCommand.ActiveConnection = sMyConnectionString
> > > objCommand.CommandText = "spInsertSomething"
> > > objCommand.CommandType = adCmdStoredProc
> > > objCommand.Parameters.Append(objCommand.CreateParameter("Return",
> > > adInteger, adParamReturnValue, 4))
> > >
objCommand.Parameters.Append(objCommand.CreateParameter("sDescription",
> > > adVarChar, adParamInput, 100, sDescription))
> > > objCommand.Parameters.Append(objCommand.CreateParameter("sText",
> >ad???????,
> > > adParamInput, ????, sText))
> > >
> > > When I don't add the parameters manually but use the refresh command
and a
> > > for next loop I get the 200 and 202 results
> > >
> > > objCommand.Parameters.Refresh
> > > for each objParam in objCommand.Parameters
> > > Response.Write(objParam.Name & " has type " & objParam.Type & "<BR>")
> > > next
> > >
> > > This results in
> > >
> > > RETURN_VALUE has type 3
> > > sDescription has type 202
> > > sText has type 200
> > >
> > >
> > > Any suggestions??
> > >
> > >
> > > Imar
> > >
> > >
> >
>
>
Message #5 by Imar Spaanjaars <Imar@S...> on Sun, 17 Sep 2000 14:48:48 +0200
|
|
Yes, but what you say is true for Microsoft Access. I use SQL server
(sorry, forgot to tell that) and the fields "text" or "ntext" (for Unicode
text) in SQL server map to the Memo field in Access.
What is wrong with using the memo field (ntext in my case) in a stored
procedure?? My application allows users to insert rather lengthy documents
through a web interface. I could of course use a direct INSERT statement or
use a recordset and the addNew method but a stored procedure is
considerably faster (based on tests we ran), and besides that, I need the
auto ID of the newly inserted record which is returned by the stored
procedure.
Microsoft recommends against using the text/ntext field as they are slow to
process, and slow down the backing up procedures. They advice to chop the
text in small pieces and insert multiple records for one large text.
However, our application is not big enough to go through all this extra work.
If you have better suggestions how to do this, please let me know.
Imar
At 09:28 AM 9/16/2000 +0800, you wrote:
>Why would your parameter text be thousand and thousands of characters? My
>parameters are usually small in size supplied just to get the stored query
>running. Are u using a memo field content as your parameter? I cannot
>possibly imagine why you would want to do that. Sorry but I cannot
>understand your usage of parameter. Maybe our definition of 'text' is
>different. My definition of text is the field type 'text' which is limited
>to 255 characters.
>
>asmadi
>www.effitech.com
>
>
>----- Original Message -----
>From: Imar Spaanjaars <Imar@S...>
>To: ASP Databases <asp_databases@p...>
>Sent: Friday, 15 September, 2000 7:57 PM
>Subject: [asp_databases] Re: What is the right type for stored procedure
>parameters??
>
>
> > OK, but will this work for text?? Theoretically, my text could be very
> > large, like thousands and thousands of characters.
> >
> > Imar
> >
> >
> > At 07:15 PM 9/15/2000 +0800, you wrote:
> > >I use adchar but I must put the size of the parameter . This is an
>example
> > >of stored procedure using Access97.
> > >Maybe this could help..
> > >example source code:
> > >
> > >strcnn = "DSN=myDSN;User ID=;Password=;"
> > >cn.Open strcnn
> > >cn.CursorLocation = adUseClient
> > >Set cmd1 = New ADODB.Command
> > >cmd1.CommandText = "qryPatient"
> > >cmd1.CommandType = adCmdUnknown
> > >Set param1 = cmd1.CreateParameter("prmName", adChar, adParamInput)
> > >param1.Size = 30
> > >cmd1.Parameters.Append param1
> > >param1.Value = txtNameSearch.Text
> > >Set cmd1.ActiveConnection = cn
> > >Set re1 = cmd1.Execute
> > >
> > >
> > >
> > >----- Original Message -----
> > >From: Imar Spaanjaars <Imar@S...>
> > >To: ASP Databases <asp_databases@p...>
> > >Sent: Friday, 15 September, 2000 3:56 PM
> > >Subject: [asp_databases] What is the right type for stored procedure
> > >parameters??
> > >
> > >
> > > > Hi there,
> > > >
> > > > Can someone tell me what type of parameter I have to use in the
> > > > Command.CreateParameter method when my Stored Procedure expects a Text
> > >type??
> > > >
> > > > I tried the Refresh command on the Parameters collection, and then
>printed
> > > > out the type of the parameter.
> > > > I seems to return 200 for ntext and 202 for a nvarchar.
> > > >
> > > > When I look up theses values in adovbs.inc, they give me:
> > > >
> > > > Const adVarChar = 200 ' Is the ntext parameter in the Stored
>Procedure.
> > > > Const adVarWChar = 202 ' Is a normal nvarchar in the Stored Procedure.
> > > >
> > > > Is this correct?? Is the text / ntext an adVarChar in ADO?? And a
>nvarchar
> > > > an adVarWChar??
> > > >
> > > >
> > > > This is the source I have
> > > >
> > > > Dim objCommand
> > > > Set objCommand = Server.CreateObject("ADODB.Command")
> > > > objCommand.ActiveConnection = sMyConnectionString
> > > > objCommand.CommandText = "spInsertSomething"
> > > > objCommand.CommandType = adCmdStoredProc
> > > > objCommand.Parameters.Append(objCommand.CreateParameter("Return",
> > > > adInteger, adParamReturnValue, 4))
> > > >
>objCommand.Parameters.Append(objCommand.CreateParameter("sDescription",
> > > > adVarChar, adParamInput, 100, sDescription))
> > > > objCommand.Parameters.Append(objCommand.CreateParameter("sText",
> > >ad???????,
> > > > adParamInput, ????, sText))
> > > >
> > > > When I don't add the parameters manually but use the refresh command
>and a
> > > > for next loop I get the 200 and 202 results
> > > >
> > > > objCommand.Parameters.Refresh
> > > > for each objParam in objCommand.Parameters
> > > > Response.Write(objParam.Name & " has type " & objParam.Type & "<BR>")
> > > > next
> > > >
> > > > This results in
> > > >
> > > > RETURN_VALUE has type 3
> > > > sDescription has type 202
> > > > sText has type 200
> > > >
> > > >
> > > > Any suggestions??
> > > >
> > > >
> > > > Imar
> > > >
> > > >
> > >
> >
|
|
|
View Cart
