|
 |
asp_web_howto thread: Problem accessing data returned from a stored procedure
Message #1 by "Kathy Egan" <kathy.egan@h...> on Tue, 27 Nov 2001 22:44:31
|
|
I'm having difficulty accessing the data returned from a SQL Server stored
procedure. My stored procedure takes one argument and is a "select"
statement linking 9 tables together. I've verified within SQL Server
that the stored procedure is returning the correct data. When I execute
my stored procedure from within ASP, I can loop through all the fields and
display their contents by doing this:
do while not recTemp.EOF
for i = 0 to 18
response.write (recTemp.fields(i)) & "<br>"
next
recTemp.moveNext
loop
However, when I attempt to store the data in local variables within the
ASP code, some of the values are missing and my local variables are set
to "":
strField1 = trim(recTemp.fields("field1"))
In addition, when I attempt to store a data type "text" field into a local
variable, I get an "exception" error.
I'm invoking the stored procedure within the ASP code like this (error
checking has been omitted for this example):
'create object
set connConnection = Server.CreateObject("ADODB.CONNECTION")
'open connection to the database
connConnection.Open(application("applConnection"))
'create command objects
set cmdTemp = Server.CreateObject("ADODB.Command")
'create recordset
set recTemp = Server.CreateObject("ADODB.Recordset")
'create the command to run the stored procedure to add the record
cmdTemp.CommandText = "sp1"
cmdTemp.CommandType = 4
set cmdTemp.ActiveConnection = connConnection
'add parameter
cmdTemp.Parameters.Append cmdTemp.CreateParameter("@id", 3, 1, , id)
'run the stored procedure
set recTemp = cmdTemp.Execute
'get the record field values
strField1 = trim(recTemp.fields("field1"))
What am I doing wrong?
Message #2 by "Bailey, Mark" <MBailey@m...> on Tue, 27 Nov 2001 18:31:16 -0500
|
|
More than likely your field contains a null value.
Try appending an empty screen to the field value.
e.g)
strField1 = recTemp.fields("field1") & ""
that should fix it!
-----Original Message-----
From: Kathy Egan [mailto:kathy.egan@h...]
Sent: Tuesday, November 27, 2001 5:45 PM
To: ASP Web HowTo
Subject: [asp_web_howto] Problem accessing data returned from a stored
procedure
I'm having difficulty accessing the data returned from a SQL Server stored
procedure. My stored procedure takes one argument and is a "select"
statement linking 9 tables together. I've verified within SQL Server
that the stored procedure is returning the correct data. When I execute
my stored procedure from within ASP, I can loop through all the fields and
display their contents by doing this:
do while not recTemp.EOF
for i = 0 to 18
response.write (recTemp.fields(i)) & "<br>"
next
recTemp.moveNext
loop
However, when I attempt to store the data in local variables within the
ASP code, some of the values are missing and my local variables are set
to "":
strField1 = trim(recTemp.fields("field1"))
In addition, when I attempt to store a data type "text" field into a local
variable, I get an "exception" error.
I'm invoking the stored procedure within the ASP code like this (error
checking has been omitted for this example):
'create object
set connConnection = Server.CreateObject("ADODB.CONNECTION")
'open connection to the database
connConnection.Open(application("applConnection"))
'create command objects
set cmdTemp = Server.CreateObject("ADODB.Command")
'create recordset
set recTemp = Server.CreateObject("ADODB.Recordset")
'create the command to run the stored procedure to add the record
cmdTemp.CommandText = "sp1"
cmdTemp.CommandType = 4
set cmdTemp.ActiveConnection = connConnection
'add parameter
cmdTemp.Parameters.Append cmdTemp.CreateParameter("@id", 3, 1, , id)
'run the stored procedure
set recTemp = cmdTemp.Execute
'get the record field values
strField1 = trim(recTemp.fields("field1"))
What am I doing wrong?
$subst('Email.Unsub')
Read the future with ebooks at B&N
http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&categoryid
=rn_ebooks
Message #3 by "Ken Schaefer" <ken@a...> on Wed, 28 Nov 2001 11:33:11 +1100
|
|
Read:
http://support.microsoft.com/support/kb/articles/Q200/1/24.ASP
Accessing Text or Memo Fields in ASP returns No Data
http://support.microsoft.com/support/kb/articles/Q175/2/39.ASP
80020009 Error When Retrieving Data From SQL Server
Then read:
http://support.microsoft.com/support/kb/articles/Q194/9/75.ASP
Howto: Read and Write BLOBs using GetChunk and AppendChunk
HTH
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Kathy Egan" <kathy.egan@h...>
Subject: [asp_web_howto] Problem accessing data returned from a stored
procedure
: I'm having difficulty accessing the data returned from a SQL Server stored
: procedure. My stored procedure takes one argument and is a "select"
: statement linking 9 tables together. I've verified within SQL Server
: that the stored procedure is returning the correct data. When I execute
: my stored procedure from within ASP, I can loop through all the fields and
: display their contents by doing this:
:
: do while not recTemp.EOF
: for i = 0 to 18
: response.write (recTemp.fields(i)) & "<br>"
: next
: recTemp.moveNext
: loop
:
: However, when I attempt to store the data in local variables within the
: ASP code, some of the values are missing and my local variables are set
: to "":
:
: strField1 = trim(recTemp.fields("field1"))
:
: In addition, when I attempt to store a data type "text" field into a local
: variable, I get an "exception" error.
:
: I'm invoking the stored procedure within the ASP code like this (error
: checking has been omitted for this example):
:
: 'create object
: set connConnection = Server.CreateObject("ADODB.CONNECTION")
:
: 'open connection to the database
: connConnection.Open(application("applConnection"))
:
: 'create command objects
: set cmdTemp = Server.CreateObject("ADODB.Command")
:
: 'create recordset
: set recTemp = Server.CreateObject("ADODB.Recordset")
:
: 'create the command to run the stored procedure to add the record
: cmdTemp.CommandText = "sp1"
: cmdTemp.CommandType = 4
: set cmdTemp.ActiveConnection = connConnection
:
: 'add parameter
: cmdTemp.Parameters.Append cmdTemp.CreateParameter("@id", 3, 1, , id)
:
: 'run the stored procedure
: set recTemp = cmdTemp.Execute
:
: 'get the record field values
: strField1 = trim(recTemp.fields("field1"))
:
: What am I doing wrong?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #4 by "Kathy Egan" <kathy.egan@h...> on Wed, 28 Nov 2001 19:40:20
|
|
Thanks. This article helped the most:
> http://support.microsoft.com/support/kb/articles/Q175/2/39.ASP
> 80020009 Error When Retrieving Data From SQL Server
I moved my text field to the last field in the Select statement and that solved my problem.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Read:
> http://support.microsoft.com/support/kb/articles/Q200/1/24.ASP
> Accessing Text or Memo Fields in ASP returns No Data
>
> http://support.microsoft.com/support/kb/articles/Q175/2/39.ASP
> 80020009 Error When Retrieving Data From SQL Server
>
> Then read:
> http://support.microsoft.com/support/kb/articles/Q194/9/75.ASP
> Howto: Read and Write BLOBs using GetChunk and AppendChunk
>
> HTH
>
> Cheers
> Ken
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: "Kathy Egan" <kathy.egan@h...>
> Subject: [asp_web_howto] Problem accessing data returned from a stored
> procedure
>
>
> : I'm having difficulty accessing the data returned from a SQL Server
stored
> : procedure. My stored procedure takes one argument and is a "select"
> : statement linking 9 tables together. I've verified within SQL Server
> : that the stored procedure is returning the correct data. When I
execute
> : my stored procedure from within ASP, I can loop through all the fields
and
> : display their contents by doing this:
> :
> : do while not recTemp.EOF
> : for i = 0 to 18
> : response.write (recTemp.fields(i)) & "<br>"
> : next
> : recTemp.moveNext
> : loop
> :
> : However, when I attempt to store the data in local variables within the
> : ASP code, some of the values are missing and my local variables are set
> : to "":
> :
> : strField1 = trim(recTemp.fields("field1"))
> :
> : In addition, when I attempt to store a data type "text" field into a
local
> : variable, I get an "exception" error.
> :
> : I'm invoking the stored procedure within the ASP code like this (error
> : checking has been omitted for this example):
> :
> : 'create object
> : set connConnection = Server.CreateObject("ADODB.CONNECTION")
> :
> : 'open connection to the database
> : connConnection.Open(application("applConnection"))
> :
> : 'create command objects
> : set cmdTemp = Server.CreateObject("ADODB.Command")
> :
> : 'create recordset
> : set recTemp = Server.CreateObject("ADODB.Recordset")
> :
> : 'create the command to run the stored procedure to add the record
> : cmdTemp.CommandText = "sp1"
> : cmdTemp.CommandType = 4
> : set cmdTemp.ActiveConnection = connConnection
> :
> : 'add parameter
> : cmdTemp.Parameters.Append cmdTemp.CreateParameter("@id", 3, 1, , id)
> :
> : 'run the stored procedure
> : set recTemp = cmdTemp.Execute
> :
> : 'get the record field values
> : strField1 = trim(recTemp.fields("field1"))
> :
> : What am I doing wrong?
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
|
|
|
View Cart
