note getting returned data and asp.net 2

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have this store procedure.
=============================
CREATE PROCEDURE TEST

@TNUMBER NVARCHAR (10) ,
@TRANSNUM NVARCHAR(10) Output,
@OrderCom Nvarchar(500) Output,
@RTask Nvarchar(100) Output
AS
SET @TRANSNUM = @TNUMBER
SET @TRANSNUM = 'TEST'
SET @RTASK = 'TESTTEST'
-- also used SELECT Instead of SET
GO
==============================
and this is the webpage code:

=================================
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>TEST</title>
</head>
<body>
<%
Dim ordercom As String, rtsk As String, tnum as string
Dim cnStr As String
Dim SqlConnection1 As New Data.SqlClient.SqlConnection(cnStr)
Dim GETOLDSHIPRECORD As SqlClient.SqlCommand

cnStr =
ConfigurationManager.ConnectionStrings("SilverQueen_Main_SystemConnectionString1").ConnectionString

GETOLDSHIPRECORD = New SqlClient.SqlCommand
GETOLDSHIPRECORD.CommandType = CommandType.StoredProcedure
GETOLDSHIPRECORD.CommandText = "TEST"
GETOLDSHIPRECORD.Connection = SqlConnection1
GETOLDSHIPRECORD.Parameters.Add("@TNUMBER", SqlDbType.NVarChar).Value =
"493118"
GETOLDSHIPRECORD.Parameters.Add("@TRANSNUM", SqlDbType.NVarChar).Value =
""
GETOLDSHIPRECORD.Parameters.Add("@OrderCom", SqlDbType.NVarChar).Value =
""
GETOLDSHIPRECORD.Parameters.Add("@RTask", SqlDbType.NVarChar).Value = ""
SqlConnection1.Open()
GETOLDSHIPRECORD.ExecuteNonQuery()
tnum = GETOLDSHIPRECORD.Parameters("@TRANSNUM").Value
ordercom = GETOLDSHIPRECORD.Parameters("@TRANSNUM").Value
rtsk = GETOLDSHIPRECORD.Parameters("@RTask").Value
GETOLDSHIPRECORD.Dispose()
SqlConnection1.Dispose()
SqlConnection1.Close()
%>
<form id="form1" runat="server">
<div>
<%=ordercom%>
<%=rtsk %>
</div>
</form>
</body>
</html>

========================

but the results do not return to the page, I am still new to using asp.net 2
for this so maybe i am leaving out a step.

Tdar
 
Hi Tdar,

Thanks for your code. In the code, you also have to set the parameter
direction to output for the TRANSNUM, OrderCom and RTask.

GETOLDSHIPRECORD.Parameters("@TRANSNUM").Direction =
ParameterDirection.Output
GETOLDSHIPRECORD.Parameters("@OrderCom").Direction =
ParameterDirection.Output
GETOLDSHIPRECORD.Parameters("@RTask").Direction = ParameterDirection.Output

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hi,
I did what you said and there still is no output, and there is output in
QA.
================================================
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>TEST</title>
</head>
<body>
<%
Dim ordercom As String, rtsk As String, tnum as string

Dim cnStr As String
cnStr =
ConfigurationManager.ConnectionStrings("SilverQueen_Main_SystemConnectionString1").ConnectionString
Dim SqlConnection1 As New Data.SqlClient.SqlConnection(cnStr)

Dim GETOLDSHIPRECORD As SqlClient.SqlCommand

GETOLDSHIPRECORD = New SqlClient.SqlCommand
GETOLDSHIPRECORD.CommandType = CommandType.StoredProcedure
GETOLDSHIPRECORD.CommandText = "TEST"
GETOLDSHIPRECORD.Connection = SqlConnection1
GETOLDSHIPRECORD.Parameters.Add("@TNUMBER", SqlDbType.NVarChar).Value =
"493118"
GETOLDSHIPRECORD.Parameters.Add("@TRANSNUM", SqlDbType.NVarChar).Value =
""
GETOLDSHIPRECORD.Parameters.Add("@OrderCom", SqlDbType.NVarChar).Value =
""
GETOLDSHIPRECORD.Parameters.Add("@RTask", SqlDbType.NVarChar).Value = ""
GETOLDSHIPRECORD.Parameters("@TRANSNUM").Direction =
ParameterDirection.Output
GETOLDSHIPRECORD.Parameters("@OrderCom").Direction =
ParameterDirection.Output
GETOLDSHIPRECORD.Parameters("@RTask").Direction =
ParameterDirection.Output
SqlConnection1.Open()
GETOLDSHIPRECORD.ExecuteNonQuery()
tnum = GETOLDSHIPRECORD.Parameters("@TRANSNUM").Value
ordercom = GETOLDSHIPRECORD.Parameters("@TRANSNUM").Value
rtsk = GETOLDSHIPRECORD.Parameters("@RTask").Value
GETOLDSHIPRECORD.Dispose()
SqlConnection1.Dispose()
SqlConnection1.Close()
%>
<form id="form1" runat="server">
<div>
<%=tnum %>
<%=ordercom%>
<%=rtsk %>
</div>
</form>
</body>
</html>

============================


CREATE PROCEDURE TEST

@TNUMBER NVARCHAR (10) ,
@TRANSNUM NVARCHAR(10) Output,
@OrderCom Nvarchar(500) Output,
@RTask Nvarchar(100) Output

AS
SELECT @TRANSNUM = @TNUMBER
SELECT @OrderCom = 'TEST'
SELECT @RTASK = 'TESTTEST'
GO


===============================
 
Hi Tdar,

When setting the type of the parameter, we should also set the size of it.
Because the default size for the parameter is 0. That's why you cannot get
anything from the output parameter. Changing to the following makes the
code work fine.

GETOLDSHIPRECORD.Parameters.Add("@TNUMBER", SqlDbType.NVarChar,
10).Value = "493118"
GETOLDSHIPRECORD.Parameters.Add("@TRANSNUM", SqlDbType.NVarChar,
10).Value = ""
GETOLDSHIPRECORD.Parameters.Add("@OrderCom", SqlDbType.NVarChar,
500).Value = ""
GETOLDSHIPRECORD.Parameters.Add("@RTask", SqlDbType.NVarChar,
100).Value = ""

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
That worked however for some reason the var lost its data when getting to the
body of the message, because when i steped thru the code the data was now
getting from the store procedure to the var rtsk. when i get down to the
place where it is outputing it to the page Response.Write("<br>rtsk:" &
rtsk)
when i break the code it says "var rtsk is used before it is assigned a
value..."
I am going to try this same come in a new project since it should be working..
here is the current code:(it does this for all the vars, its like after it
gets the body message i loss the data in the var)

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient"%>
<%
Dim user As MembershipUser, username As String, userid As String

user = Membership.GetUser(True)
username = user.UserName
userid = user.ProviderUserKey.ToString
Dim ordercom As String, rtsk As String, tnum As String

Dim cnStr As String
cnStr =
ConfigurationManager.ConnectionStrings("SilverQueen_Main_SystemConnectionString1").ConnectionString
Dim SqlConnection1 As New Data.SqlClient.SqlConnection(cnStr)
SqlConnection1.Open()
Dim GETOLDSHIPRECORD As SqlClient.SqlCommand

GETOLDSHIPRECORD = New SqlClient.SqlCommand
GETOLDSHIPRECORD.CommandType = CommandType.StoredProcedure
GETOLDSHIPRECORD.CommandText = "TEST"
GETOLDSHIPRECORD.Connection = SqlConnection1

GETOLDSHIPRECORD.Parameters.Add("@TNUMBER", SqlDbType.NVarChar,
10).Value = "493118"
GETOLDSHIPRECORD.Parameters.Add("@TRANSNUM", SqlDbType.NVarChar,
10).Value = ""
GETOLDSHIPRECORD.Parameters.Add("@OrderCom", SqlDbType.NVarChar,
500).Value = ""
GETOLDSHIPRECORD.Parameters.Add("@RTask", SqlDbType.NVarChar, 100).Value
= ""

GETOLDSHIPRECORD.Parameters("@TRANSNUM").Direction =
ParameterDirection.Output
GETOLDSHIPRECORD.Parameters("@OrderCom").Direction =
ParameterDirection.Output
GETOLDSHIPRECORD.Parameters("@RTask").Direction =
ParameterDirection.Output
GETOLDSHIPRECORD.Parameters("@RTask").Size = 500
GETOLDSHIPRECORD.Parameters("@OrderCom").Size = 500

GETOLDSHIPRECORD.ExecuteNonQuery()
tnum = GETOLDSHIPRECORD.Parameters("@TRANSNUM").Value
ordercom = GETOLDSHIPRECORD.Parameters("@TRANSNUM").Value
rtsk = GETOLDSHIPRECORD.Parameters("@RTask").Value
'GETOLDSHIPRECORD.Dispose()
'SqlConnection1.Dispose()
SqlConnection1.Close()
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>TEST</title>
</head>
<body>

<form id="form1" runat="server">
<div>

<%
Response.Write("<br>Username: " & username)
Response.Write("<br>ProviderUserKey: " & userid)
Response.Write("<br>ordercom:" & Ordercom)
Response.Write("<br>tnum:" & Tnum)
Response.Write("<br>rtsk:" & rtsk)
%>
</div>
</form>
</body>
</html>
 
Hi Tdar,

You can try to put the code into one code block. So that the vars will be
recognized as defined ones.

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient"%>
<%

%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>TEST</title>
</head>
<body>

<form id="form1" runat="server">
<div>

<%
Dim user As MembershipUser, username As String, userid As String

user = Membership.GetUser(True)
username = user.UserName
userid = user.ProviderUserKey.ToString
Dim ordercom As String, rtsk As String, tnum As String

Dim cnStr As String
cnStr = "Persist Security Info=False;User ID=sa;Initial
Catalog=Test;Data Source=sha-kevy-new;Password=sapass"

Dim SqlConnection1 As New Data.SqlClient.SqlConnection(cnStr)
SqlConnection1.Open()
Dim GETOLDSHIPRECORD As SqlClient.SqlCommand

GETOLDSHIPRECORD = New SqlClient.SqlCommand
GETOLDSHIPRECORD.CommandType = CommandType.StoredProcedure
GETOLDSHIPRECORD.CommandText = "TEST"
GETOLDSHIPRECORD.Connection = SqlConnection1

GETOLDSHIPRECORD.Parameters.Add("@TNUMBER", SqlDbType.NVarChar,
10).Value = "493118"
GETOLDSHIPRECORD.Parameters.Add("@TRANSNUM", SqlDbType.NVarChar,
10).Value = ""
GETOLDSHIPRECORD.Parameters.Add("@OrderCom", SqlDbType.NVarChar,
500).Value = ""
GETOLDSHIPRECORD.Parameters.Add("@RTask", SqlDbType.NVarChar,
100).Value = ""

GETOLDSHIPRECORD.Parameters("@TRANSNUM").Direction =
ParameterDirection.Output
GETOLDSHIPRECORD.Parameters("@OrderCom").Direction =
ParameterDirection.Output
GETOLDSHIPRECORD.Parameters("@RTask").Direction =
ParameterDirection.Output
GETOLDSHIPRECORD.Parameters("@RTask").Size = 500
GETOLDSHIPRECORD.Parameters("@OrderCom").Size = 500

GETOLDSHIPRECORD.ExecuteNonQuery()
tnum = GETOLDSHIPRECORD.Parameters("@TRANSNUM").Value
ordercom = GETOLDSHIPRECORD.Parameters("@TRANSNUM").Value
rtsk = GETOLDSHIPRECORD.Parameters("@RTask").Value
'GETOLDSHIPRECORD.Dispose()
'SqlConnection1.Dispose()
SqlConnection1.Close()

Response.Write("<br>Username: " & username)
Response.Write("<br>ProviderUserKey: " & userid)
Response.Write("<br>ordercom:" & Ordercom)
Response.Write("<br>tnum:" & Tnum)
Response.Write("<br>rtsk:" & rtsk)
%>
</div>
</form>
</body>
</html>

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top