note getting returned data and asp.net 2

5 Jan 2006 5:40 PM

TdarTdar

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

6 Jan 2006 2:14 AM

Kevin Yu [MSFT]

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."

6 Jan 2006 3:57 PM

TdarTdar

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

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

Show quote

"Kevin Yu [MSFT]" wrote:

> 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."
>
>

9 Jan 2006 5:14 AM

Kevin Yu [MSFT]

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."

10 Jan 2006 5:41 PM

TdarTdar

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>

Show quote

"Kevin Yu [MSFT]" wrote:

> 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."
>
>

11 Jan 2006 8:41 AM

Kevin Yu [MSFT]

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."