Not getting all data from Stored Procedure.
From: Thomas Scheiderich (tfs_at_deltanet.com)
Date: 07/21/04
- Next message: Thomas Scheiderich: "Is vs is not"
- Previous message: Bryant Hankins: "Re: validation controls"
- Next in thread: Greg Burns: "Re: Not getting all data from Stored Procedure."
- Reply: Greg Burns: "Re: Not getting all data from Stored Procedure."
- Reply: Darren Clark: "Re: Not getting all data from Stored Procedure."
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 21 Jul 2004 13:09:32 -0700
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.
- Next message: Thomas Scheiderich: "Is vs is not"
- Previous message: Bryant Hankins: "Re: validation controls"
- Next in thread: Greg Burns: "Re: Not getting all data from Stored Procedure."
- Reply: Greg Burns: "Re: Not getting all data from Stored Procedure."
- Reply: Darren Clark: "Re: Not getting all data from Stored Procedure."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
