Not getting all data from Stored Procedure.

T

Thomas Scheiderich

I am trying to get the identity of a row I add in my stored procedure. I
have tried all kinds of ways to get it passed to my ASP.NET page. It works
fine when I run it from Sql Query.

Here is the asp.net page:

****************************************************************************
*
<%@ Page Language="VB" ContentType="text/html" trace="false"
ResponseEncoding="iso-8859-1" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<%@ import Namespace="System.IO" %>

<html>
<head>
<title>Temp display</title>
<script runat="server">

Dim ConnectionString as String
Dim objConn as SqlConnection
Dim CommandText as String
Dim objCmd as SqlCommand
Dim objCommand as SqlCommand
Dim objDataReader as SqlDataReader
Dim ServerName as String
Dim Login as String
Dim Password as String

Sub Page_Load(sender as Object, e as EventArgs)
if not isPostBack then
ConnectionString
=System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRIN
G_Contour_Server")
ServerName
=System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_Serve
r")
Login
=System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_LOGIN
")
Password
=System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_PASSW
ORD")
objConn = New SqlConnection (ConnectionString)
objConn.Open()
end if

Call moveQAToSql("c:\QA Docs\QPM 4.1.doc")
objConn.Close()
End Sub

Sub moveQAToSql (fileName as String)
Dim sResults As String
CommandText = "exec openworx..sp_textcopy '" & ServerName & "','" & Login
& "','" & Password & "','openworx','qadocs','document','" _
& filename & "','Where fullFileName = @filename','I','" &
Path.GetFileName(fileName) & "','" &
Path.GetFileNameWithoutExtension(fileName) & "','" &
Mid(Path.GetExtension(fileName),2) & "'"

objCommand = new SqlCommand(CommandText, objConn)
objDataReader = objCommand.ExecuteReader()

while (objDataReader.Read() = true)
if(objDataReader(0) is System.DBNull.value) then
else
response.Write("In While loop return from sp_textcopy - " &
objDataReader(0) + "<br>")
end if
end while
objDataReader.Close()
End Sub
</script>

</head>
<body>

</body>
</html>
***********************************************************************

When I execute the reader, it executes the stored procedure fine. But when
I am reading the results in my "while loop", it doesn't seem to get
everything back.

Here is what I get back on my page:
***********************************************************************
In While loop return from sp_textcopy - TEXTCOPY Version 1.0
In While loop return from sp_textcopy - DB-Library version 8.00.194
In While loop return from sp_textcopy - Data copied into SQL Server image
column from file 'c:\QA Docs\QPM 4.1.doc'.
***********************************************************************

When I run it from Sql Query, here is the results I get back:
*************************************************************************
c:\textcopy /S Raptor /U tfs /P tfstom /D openworx /T qadocs /C document /W
"Where fullFileName = 'c:\QA Docs\QPM 5.3.doc'" /F "c:\QA Docs\QPM 5.3.doc"
/I/Z
output

------------------------------------------
TEXTCOPY Version 1.0
DB-Library version 8.00.194
Data copied into SQL Server image column from file 'c:\QA Docs\QPM 5.3.doc'.
NULL

(4 row(s) affected)

----------
193

(1 row(s) affected)

at the end of the routine 193
****************************************************************************
*******

As you can see, it displays the textcopy string it is going to execute,
lines sent from textcopy (which is all it seems to get), and a couple lines
with the identity number.

Here is the stored procedure:
****************************************************************************
*************
CREATE PROCEDURE sp_textcopy (
@srvname varchar (30),
@login varchar (30),
@password varchar (30),
@dbname varchar (30),
@tbname varchar (30),
@colname varchar (30),
@filename varchar (30),
@whereclause varchar (40),
@direction char(1),
@fileNameLong varchar(40),
@fileNameShort varchar(40),
@fileExtension varchar(20))
AS
DECLARE @exec_str varchar (255),
@lastIdentity integer

insert qadocs
(fullFileName,document,fileNameLong,fileNameShort,fileExtension) values
(@filename,@filename,@fileNameLong,@fileNameShort,@fileExtension)

select @lastIdentity = @@identity


SELECT @exec_str =
'c:\textcopy /S ' + @srvname +
' /U ' + @login +
' /P ' + @password +
' /D ' + @dbname +
' /T ' + @tbname +
' /C ' + @colname +
' /W "Where fullFileName = ' + "'" + @filename + "'" +
'" /F "' + @filename +
'" /' + @direction +
'/Z'

print @exec_str

EXEC master..xp_cmdshell @exec_str

select @lastIdentity = scope_identity()
select STR(scope_identity())
print "at the end of the routine " + STR(@lastIdentity)

return @lastIdentity

GO
****************************************************************************
**************

Why doesn't the asp.net page have all the data that is passed back?

Thanks,

Tom.
 
G

Greg Burns

You may want to try using parameters here. I took the liberty of modifing
your code to do that, but haven't really changed the logic of what you were
doing before. (sorry)

If you sproc is returning mutliple result sets, I would check into the
NextResult property of the datareader. Maybe somebody else will take a stab.

HTH,
Greg

(untested)


Option Strict On
Imports System.Data.SqlClient

Sub moveQAToSql(ByVal fileName As String)

Dim sResults As String

Dim objCommand As New SqlCommand("openworx..sp_textcopy", objConn)
objCommand.CommandType = CommandType.StoredProcedure

objCommand.Parameters.Add("RETURN_VALUE", SqlDbType.Int).Direction =
ParameterDirection.ReturnValue
objCommand.Parameters.Add("@srvname", SqlDbType.VarChar, 30).Value =
ServerName
objCommand.Parameters.Add("@login", SqlDbType.VarChar, 30).Value =
Login
objCommand.Parameters.Add("@password", SqlDbType.VarChar, 30).Value
= Password
objCommand.Parameters.Add("@dbname", SqlDbType.VarChar, 30).Value =
"openworx"
objCommand.Parameters.Add("@tbname", SqlDbType.VarChar, 30).Value =
"qadocs"
objCommand.Parameters.Add("@colname", SqlDbType.VarChar, 30).Value =
"document"
objCommand.Parameters.Add("@filename", SqlDbType.VarChar, 30).Value
= fileName
objCommand.Parameters.Add("@whereclause", SqlDbType.VarChar,
40).Value = "Where fullFileName = @filename"
objCommand.Parameters.Add("@direction", SqlDbType.Char, 1).Value =
"I"
objCommand.Parameters.Add("@fileNameLong", SqlDbType.VarChar,
40).Value = Path.GetFileName(fileName)
objCommand.Parameters.Add("@fileNameShort", SqlDbType.VarChar,
40).Value = Path.GetFileNameWithoutExtension(fileName)
objCommand.Parameters.Add("@fileExtension", SqlDbType.VarChar,
20).Value = Mid(Path.GetExtension(fileName), 2)

Dim objDataReader As SqlDataReader
Try
objDataReader = objCommand.ExecuteReader()

While objDataReader.Read()
If Not (objDataReader(0) Is System.DBNull.Value) Then
response.Write("In While loop return from sp_textcopy -
" & objDataReader(0) + "<br")
End If
End While
objDataReader.Close()
Dim i As Integer =
CInt(objCommand.Parameters("RETURN_VALUE").Value)
Finally

If Not objDataReader Is Nothing AndAlso Not
objDataReader.IsClosed Then objDataReader.Close()

End Try
End Sub
 
D

Darren Clark

If inside your SP you are doing a multistep operation....

you need to use the following
CREATE PROCEDURE sp_textcopy (
@srvname varchar (30),
@login varchar (30),
@password varchar (30),
@dbname varchar (30),
@tbname varchar (30),
@colname varchar (30),
@filename varchar (30),
@whereclause varchar (40),
@direction char(1),
@fileNameLong varchar(40),
@fileNameShort varchar(40),
@fileExtension varchar(20))
AS
Click to expand...
--- this will allow you to do a multi step operation... and still return the
results to ADO
set nocount on

DECLARE @exec_str varchar (255),
@lastIdentity integer

insert qadocs
(fullFileName,document,fileNameLong,fileNameShort,fileExtension) values
(@filename,@filename,@fileNameLong,@fileNameShort,@fileExtension)

select @lastIdentity = @@identity


SELECT @exec_str =
'c:\textcopy /S ' + @srvname +
' /U ' + @login +
' /P ' + @password +
' /D ' + @dbname +
' /T ' + @tbname +
' /C ' + @colname +
' /W "Where fullFileName = ' + "'" + @filename + "'" +
'" /F "' + @filename +
'" /' + @direction +
'/Z'

print @exec_str

EXEC master..xp_cmdshell @exec_str

select @lastIdentity = scope_identity()
select STR(scope_identity())
print "at the end of the routine " + STR(@lastIdentity)

return @lastIdentity
Click to expand...

set nocount off
GO
Click to expand...



ACTUALLY!!!!!

After just having a quick look at your code....
it seems that you are just returning a return code right?
Then why are you tryhing to fill a datareader?
why not just get the value from a sql parameter
 
T

Thomas Scheiderich

Greg Burns said:
You may want to try using parameters here. I took the liberty of modifing
your code to do that, but haven't really changed the logic of what you were
doing before. (sorry)

If you sproc is returning mutliple result sets, I would check into the
NextResult property of the datareader. Maybe somebody else will take a
Click to expand...
stab.

That sounds reasonable.
How would I check to see if there is another set?

Thanks,

Tom.
 
G

Greg Burns

How would I check to see if there is another set?
Thanks,

Tom.
Click to expand...

Code like this will read each result set in turn:

Try
cn.Open()

dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

Do While dr.Read
x= GetSqlString(0).Value
Loop

dr.NextResult()

Do While dr.Read()
y = dr.GetSqlString(0).Value
Loop

dr.NextResult()

Do While dr.Read()
z = dr.GetSqlString(0).Value
Loop

Catch ex As SqlException
Throw

Finally

If Not dr Is Nothing AndAlso Not dr.IsClosed Then dr.Close()

cn.Close()

End Try

Greg
 
T

Thomas Scheiderich

Greg Burns said:
Code like this will read each result set in turn:

Try
cn.Open()

dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

Do While dr.Read
x= GetSqlString(0).Value
Loop

dr.NextResult()

Do While dr.Read()
y = dr.GetSqlString(0).Value
Loop

dr.NextResult()

Do While dr.Read()
z = dr.GetSqlString(0).Value
Loop

Catch ex As SqlException
Throw

Finally

If Not dr Is Nothing AndAlso Not dr.IsClosed Then dr.Close()

cn.Close()

End Try
Click to expand...

This would assume you know how many sets there are.

Is there a way to do a check to see if there actually is another set
waiting?

Thanks,

Tom.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem retrieving data from Stored Procedure from SQL 2005 2
Using layers won't return value from stored procedure 0
Basic question: returning @@IDENTITY from stored procedure... 5
Pass NULL To Stored Procedure 2
Running Stored Procedure in a Loop 7
returning output vars from nested stored procedure 1
Output value stored procedure dotnet 1
How to return value from Stored Procedure to label control? 0

Members online

No members online now.
Total: 24 (members: 0, guests: 24)
Robots: 449

Forum statistics

Threads
473,769
Messages
2,569,580
Members
45,054
Latest member
TrimKetoBoost

Latest Threads

Top