Cannot Retrieve Output Parameters Until SqlDataReader Is Closed

Apr 13, 2010

While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close.

If the above claim is true, then why is the following method able to retrieve a value from output parameter before the reader is closed:

public int Something()
{
using (SqlConnection con = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("some_procedure", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output;
con.Open();
cmd.ExecuteReader();
return (int)cmd.Parameters["@ID"].Value;
}
}

You can reset the CommandText property and reuse the SqlCommand object. However, you must close the SqlDataReader before you can execute a new or previous command.

Why must you close sqldatareader before you execute new command?

View 2 Replies


Similar Messages:

ADO.NET :: Stored Proc With Output Parameters / Data In The Output Parameters From The Stored Procedure?

Mar 30, 2011

I have a stored procedure that works fine for classic asp, but how can I get my output parameters data back from in /MVC 2 / linq/ entities framework model . I have inported the function also, but when i try to code it i dont get results ...i know this is a matter of lack of / knowledge or exmple because i have been scouring the web for it and cannnot find into that accually works.

here is the stored proc.

[Code]....

Here was the one of the few tries I did:

[Code]....

but it executes fine does not give me an error, but also does not return the info i need to the viewmodel

I keep seeing stuff about "ref" on the net but "ref" shows " arbument # should not be passed with the ref keyword.

So i am not sure if there is a problem with the model, or with my understanding this, Now for your info I can do the same step with returning a dataset from a stored procedure fine, but I dont want a data set I just want excatly the data in the output parameters from the stored procedure.

View 2 Replies

SQL Server :: What If Multiple Output Parameters And Input Parameters And Also Want A Select Table

Feb 16, 2011

[Code]....

When I want to get the output values its okay but I also want returning a table as a result data.But Datareader has no rows.is it possible if I want a returning query result and multiple output values togather ?I wrote a test above.I can get output values as sqlparameters. But Datareader attached to a Gridview is empty.can you detect whats wrong here and it doesnt return a query result.So stored procedure is not standart or ı am doing something wrong.this doesnt raise any exception.but not returning any data.

[code]....

View 8 Replies

How To Get Output Parameters From SQL Server Using ASP

Oct 8, 2010

I had an sqldatasource. I had set its parameter as follows

[Code]....

View 10 Replies

Web Forms :: Cannot Display Output Parameters

Jan 22, 2010

I have a webform which inserts data into a sql table successfully (form is bound to SqlDataSource1). The problem I have is that I need to populate a number of the DataFields based on an entered Ref number from a button click. The Ref is passed into a Stored Procedure as the only input parameter, all other params are output params. The procedure seems to run fine, but I can not seem to display any of the output params in my form. Here is my sproc:

PROC AUMS_RETURN_LOOKUP
(
@REF
@COMPANY
@SALUTATION
@FORENAME
@SURNAME
@ADDRESS1
@ADDRESS2
@ADDRESS3
@ADDRESS4
@ADDRESS5
@ADDRESS6
@CITY
@COUNTRY
@POSTCODE
BIGINT,
char(10)
OUTPUT,
char(20)
OUTPUT,
char(50)
OUTPUT,
char(50)
OUTPUT,
char(50)
OUTPUT,
char(50)
OUTPUT,
char(50)
OUTPUT,
char(50)
OUTPUT,
char(50)
OUTPUT,
char(50)
OUTPUT,
char(30)
OUTPUT,
char(30)
OUTPUT,
char(20)
OUTPUT
)
AS
SET
NOCOUNT ON
SELECT
@SALUTATION
@FORENAME
@SURNAME
@ADDRESS1
@COMPANY =
rtrim([COMPANY]),
=
RTRIM([SALUTATION]),
=
RTRIM([FORENAME]),
=
RTRIM([SURNAME]),
=
RTRIM([ADDRESS1]),
@ADDRESS2 =
RTRIM([ADDRESS2]),
@ADDRESS3 =
RTRIM([ADDRESS3]),
@ADDRESS4 =
RTRIM([ADDRESS4]),
@ADDRESS5 =
RTRIM([ADDRESS5]),
@ADDRESS6 =
RTRIM([ADDRESS6]),
@CITY =
RTRIM([CITY]),
@COUNTRY =
RTRIM[COUNTRY]), @POSTCODE
=
RTRIM([ZIP])
FROM
HEADER
WHERE
REF = REF
Here is my code:
Protected
Sub Button1_Click1(ByVal sender
As
Object,
ByVal e
As System.EventArgs)
Dim Company
As TextBox =
DirectCast(FormView1.FindControl("TextBox9"), TextBox)
Dim Forename
As TextBox =
DirectCast(FormView1.FindControl("TextBox10"), TextBox)
Dim Surname
As TextBox =
DirectCast(FormView1.FindControl("TextBox8"), TextBox)
Dim TESTREFTextBox
As TextBox =
DirectCast(FormView1.FindControl("TxtBox_ref"), TextBox)
Dim TESTREF = Convert.ToInt64(TESTREFTextBox.Text)
Dim CompanyText = Company.Text
Dim strCon
As
String = ConfigurationManager.ConnectionStrings("ConnString").ConnectionString
Dim Con
As
New SqlConnection(strCon)
Dim Cmd
As SqlCommand
Con.Open()
Cmd = New SqlCommand
Cmd.CommandText = "RETURN_LOOKUP"
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add("@REF", SqlDbType.Int)
Cmd.Parameters("@REF").Value = TESTREF
Cmd.Parameters.Add("@COMPANY", SqlDbType.VarChar, 15)
Cmd.Parameters("@COMPANY").Direction = ParameterDirection.Output
Cmd.Parameters.Add("@SALUTATION", SqlDbType.VarChar, 20)
Cmd.Parameters("@SALUTATION").Direction = ParameterDirection.Output
Cmd.Parameters.Add("@FORENAME", SqlDbType.VarChar, 50)
Cmd.Parameters("@FORENAME").Direction = ParameterDirection.Output
Cmd.Parameters.Add("@SURNAME", SqlDbType.VarChar, 50)
Cmd.Parameters("@SURNAME").Direction = ParameterDirection.Output
Cmd.Parameters.Add("@ADDRESS1", SqlDbType.VarChar, 50)
Cmd.Parameters("@ADDRESS1").Direction = ParameterDirection.Output
Cmd.Parameters.Add("@ADDRESS2", SqlDbType.VarChar, 50)
Cmd.Parameters("@ADDRESS2").Direction = ParameterDirection.Output
Cmd.Parameters.Add("@ADDRESS3", SqlDbType.VarChar, 50)
Cmd.Parameters("@ADDRESS3").Direction = ParameterDirection.Output
Cmd.Parameters.Add("@ADDRESS4", SqlDbType.VarChar, 50)
Cmd.Parameters("@ADDRESS4").Direction = ParameterDirection.Output
Cmd.Parameters.Add("@ADDRESS5", SqlDbType.VarChar, 50)
Cmd.Parameters("@ADDRESS5").Direction = ParameterDirection.Output
Cmd.Parameters.Add("@ADDRESS6", SqlDbType.VarChar, 50)
Cmd.Parameters("@ADDRESS6").Direction = ParameterDirection.Output
Cmd.Parameters.Add("@CITY", SqlDbType.VarChar, 30)
Cmd.Parameters("@CITY").Direction = ParameterDirection.Output
Cmd.Parameters.Add("@COUNTRY", SqlDbType.VarChar, 30)
Cmd.Parameters("@COUNTRY").Direction = ParameterDirection.Output
Cmd.Parameters.Add("@POSTCODE", SqlDbType.VarChar, 20)
Cmd.Parameters("@POSTCODE").Direction = ParameterDirection.Output
Cmd.Connection = Con
"@REF", SqlDbType.Int)"@REF").Value = REF"@COMPANY",
SqlDbType.VarChar, 15)"@COMPANY").Direction = ParameterDirection.Output"@SALUTATION",
SqlDbType.VarChar, 20)"@SALUTATION").Direction = ParameterDirection.Output"@FORENAME",
SqlDbType.VarChar, 50)"@FORENAME").Direction = ParameterDirection.Output"@SURNAME",
SqlDbType.VarChar, 50)"@SURNAME").Direction = ParameterDirection.Output"@ADDRESS1",
SqlDbType.VarChar, 50)"@ADDRESS1").Direction = ParameterDirection.Output"@ADDRESS2",
SqlDbType.VarChar, 50)"@ADDRESS2").Direction = ParameterDirection.Output"@ADDRESS3",
SqlDbType.VarChar, 50)"@ADDRESS3").Direction = ParameterDirection.Output"@ADDRESS4",
SqlDbType.VarChar, 50)"@ADDRESS4").Direction = ParameterDirection.Output"@ADDRESS5",
SqlDbType.VarChar, 50)"@ADDRESS5").Direction = ParameterDirection.Output"@ADDRESS6",
SqlDbType.VarChar, 50)"@ADDRESS6").Direction = ParameterDirection.Output"@CITY",
SqlDbType.VarChar, 30)"@CITY").Direction = ParameterDirection.Output"@COUNTRY",
SqlDbType.VarChar, 30)"@COUNTRY").Direction = ParameterDirection.Output"@POSTCODE",
SqlDbType.VarChar, 20)"@POSTCODE").Direction = ParameterDirection.OutputDim
result As
String
Cmd.ExecuteNonQuery()
result = Cmd.Parameters("@COMPANY").Value
CompanyText = result

View 4 Replies

SQL Server :: Stored Procedures Output Parameters?

Mar 16, 2011

I have never used stored proc ouput parameters i want to know what is the use of using output parameters?If possible provide me some code.

View 10 Replies

Web Forms :: Input And Output Parameters For Stored Procedures In VB.NET?

Oct 22, 2010

I have several VB.NET functions which pass and receive values from executed stored procedures using parameters. However, the latest method I am using seems to be a little too specific on the datatype and data length of the parameters for my liking (i.e.

LogActivityCommand.Parameters.Add("@strErrSource", SqlDbType.NVarChar, 300).Value = strErrSource). For example:

[code]....

If I end up changing the datatype and data length properties of the variables in my SQL stored procedures in the future, I am going to have to re-visit my VB code too and alter the parameter settings here as well.

View 4 Replies

ADO.NET :: Pass Input Parameters To SP And Fetch Output Parameter?

Jan 18, 2011

i like to pass two i/p parameters to the SP and fetch 2 output parameters.This should done using simple ADO .NET code.

View 1 Replies

ADO.NET :: How To Define Date Output Parameters For A Stored Procedure

Oct 15, 2010

I have a question. How do we define date output parameters for a stored procedure?

before you answer,I should mention that I am not referring to the sql syntax definition ,which is @mydate datetime output.

This is ok,what i am asking is HOW DO I SPECIFY IT IN LINQ WHEN I NEED TO CALL THE STORED PROCEDURE IN THE SERVER SIDE CODE. I am repeating that I do I am not referring to the sql definition .

View 1 Replies

Retrieve The Full Output From A Web Application With MSBuild

Jan 12, 2010

I have a misunderstanding with MSBuild. I want to build a web application (with a traditional .csproj project file) and get the "entire output" of the build in a new, clean output folder - including all the website files that are included in the project with <Content Include="....

The AspNetCompiler utility makes this easy for "web site" style projects - you specify the input folder and the output folder, and they can be entirely different.

So far I've only had success specifying the OutoutPath folder, i.e. moving the bin folder - but this only gives me the binaries and ignores aspx, image files, etc.

View 1 Replies

ADO.NET :: How To Call A Stored Procedures With Varchar Output Parameters In LINQ

Oct 15, 2010

I have a stored procedure with the following structure:

[Code]....

View 4 Replies

DataSource Controls :: Deprecated Types Not Supported As Output Parameters

Jan 21, 2010

i am trying to do: Parameters.Add("@ProfileHtml", SqlDbType.Text, MAX_TEXT).Direction = ParameterDirection.Output but i keep getting this error. MAX_TEXT is integer = 2147483647 Data type 0x23 is a deprecated large object, or LOB, but is marked as output parameter. Deprecated types are not supported as output parameters. Use current large object types instead.

View 4 Replies

SQL Server :: Retrieving Two Output Parameters In A Stored Proc Called From C#?

Mar 29, 2011

I've modified an existing strored procedure. It originally had one ouput parameter in the stored proc and it was set up as follows in the c# code that called it:

cmd.Parameters.Add("@Var1Param", SqlDbType.BigInt);
if (Var1 == 0)
cmd.Parameters["@Var1"].Value = DBNull.Value;
else
cmd.Parameters["@Var"].Value = Var1;
cmd.Parameters["@Var1"].Direction = ParameterDirection.InputOutput;
cmd.ExecuteNonQuery();
// Get Var1
Var1= dataMorph.ToInt64(cmd.Parameters["@Var1"].Value.ToString());

Just to clarify, this parameter was declared as OUTPUT in the stored proc but as inputoutput in the C# code. This worked fine. I have added another parameter to the same stored procedure and I want to retrieve both from the C# code. The C# code now is as follows:.................................

View 3 Replies

Xslt - How To Retrieve Xsl:output Media-type Value From Xsl File

Mar 9, 2010

Is there a way to retrieve the media-type value? e.g. like OutputSettings.OutputMethod used to get xsl:output method.

View 2 Replies

Visual Studio :: The Underlying Connection Was Closed: A Connection That Was Expected To Be Kept Alive Was Closed?

Feb 3, 2011

I have created a webservice in 2010. And when I call it from my web application sometimes,It generates the follwoing errors:-1. The underlying connection was closed: A connection that was expected to be kept alive was closed by the server.2. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.I have already tried increasing excutonTimeOut etc.

View 2 Replies

WCF / ASMX :: The Underlying Connection Was Closed: The Connection Was Closed Unexpectedly?

Oct 14, 2010

This error stopping my work in my crucial time. The error is "The underlying connection was closed: The connection was closed unexpectedly.".There are multiple WCF service I created in my project. But when I am requesting only one service this error is coming. I compare with other service, no difference. The service works fine in the morning, but afternoon it stops working and raising error.

I installed Silverlight 3.0 Tools for 2008 Sp1, Silverlight 3.0 Developer, WCF RIA Beta and Silverlight 4.0 runtime in my system in the morning. After getting this error I uninstalled WCF RIA Beta. But same issue.Could not find any solution. Please assist me how to solve this issue.

View 2 Replies

The Underlying Connection Was Closed: The Connection Was Closed Unexpectedly?

Nov 19, 2010

I have my wcf service and it runs fine. Then from my asp.net application I am trying to connect to this service. Everything is ok, request from asp.net is received in service (as I can debug code) and then when it returns to client I have the following error:underlying connection was closed: The connection was closed unexpectedlyMy contract on WCF service is as follows:

[OperationContract()]
WCFResponseGetAllProducts GetAllProducts(WCFRequestGetAllProducts request);

And WCFResponseGetAllProducts , WCFRequestGetAllProducts classes have [DataContract] attributes. Members of those classes have [DataMember] attribute. However when I added another method to my contract:

[OperationContract()]
int Test();

then I can execute it from asp.net mvc application without a problem.
PS. I host my wcf service in default web server in visual studio 2010 at the moment

View 3 Replies

Web Forms :: Browser Output Doesn't Look Like Web Developer Output Using Nested Master Pages And Css Stylesheets

Apr 18, 2010

First off, suggest better ways if you want rather than patch up this code. I am just starting this project and it is first time I have tried to code a web site so anything you suggest is very much welcomed. I have spent the last 2 and 1/2 days trying to find workable answers to this but none I have found and tried seem to fit. If needed I can email screens shots or code. I am trying to construct a website that will have the same main theme throughout as far as the header, navbars sitemap, and footer go. Naturally the content will vary from page to page. I want to use a single master page and css stylesheet for this main theme and I will change the content format as needed per page because each page may vary somewhat.

However, about 15 of the pages will all use the same format for their content and this format will differ from the rest of the site but the format of the main theme will stay the same. So I am trying to create a nested master page to use to format the just the content area for all these pages while retaining the main theme for the header, etc.. I believe I should use a separate css file with the nested master page to handle the formatting of the content areas for these 15 pages. I have code that looks like it works when viewed in web developer but design view but does not work when viewd through a browser (IE, Chrome, Firefox). So far I have the following done in web developer.

If it helps the code and screen shots follow. Master Page called "Parent.master". For all theme throughout site Nested Master Page called "Lab.master". For the 15 like pages Primary stylesheet file for main theme called "StylesheetNew.css" For site theme Secondary stylesheet file to syle the 15 like formatted pages. It is called Labmaster.css Labs.apsx file to use as first of the 15 like pages.
Finally screens shots from web developer and browser. Sorry try as I might I could not capture screenshots and put them into this post. The problem is that web developer shows all the area (many lines high) with gray background and with the XXX text that I want to allocate for content in the 15 pages. Yet all the browser show is one line of text o a white background followed by the footer. It looks like the LabStyleSheet works in Web Developer but not in a browser.

Parent.master
<%@ Master Language="VB" CodeFile="Parent.master.vb" Inherits="MasterPage" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL]">
<html xmlns="[URL]">
<head id="ParentHeader" runat="server">
<title>HX5</title>
<asp:ContentPlaceHolder runat="server" id="headerPlaceHolder" />
<link href="~/StyleSheetNew.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form id="form1" runat="server">
<div id="wrapper" >
<div id="Header" >
<table class="hdrtbl1" >
<tr>
<td id="hdrtd1" style="width:175px; height:100px;" >
<img id="logo" alt="Logo" src="../Images/logo.png" style="width: 136px; height: 87px" />
</td>
</tr>
</table >
<table class="hdrtbl2" >
<tr><td id="hdrtbl2td1"><b> Providing Professional Technical, Manangement and Business Solution</b></td></tr>
<tr><td id="hdrtbl2td2"> <b> Services since 2004 </b></td></tr>
<tr> <td id="hdrtbl2td3" align="center" ><img id="rdln" alt="RedLine" style=" height:3px;" src="../Images/RedLine.png" /></td></tr>
<tr><td id="hdrtbl2td4" > <b>Committed to Excellence In All We Do</b></td></tr>
</table>
</div>
<div id="navbardiv">
<ul id="topnav">
<li><a href="../pages/about.aspx" >About Us <img style=" border: none; " src="../Images/arrow-down.gif" /></a>
<!--Subnav Starts Here-->
<span>
<a style="font-weight:lighter; " href="#" >Who We Are </a> |
<a href="#">Locations</a> |
<a href="#">Business Classifications</a> |
<a href="#">Contact Us</a> |
</span>
</li>
<!--Subnav Ends Here-->
<li><a href="../pages/services.aspx">Services <img alt="arrow" style="border: none; " src="../Images/arrow-down.gif" /></a>
<!--Subnav Starts Here-->
<span>
<a style="font-weight:lighter; " href="#" >Capabilities</a> |
</span>
<!--Subnav Ends Here-->
</li>
<li><a href="#">Customers <img style=" border: none; " src="../Images/arrow-down.gif" /></a>
<span>
<a style="font-weight:lighter; " href="#" >Subnav Link</a> |
<a href="#">Customer Locations</a> |
<a href="../pages/labs.aspx">Labs</a> |
</span>
</li>
<li><a href="#">Careers <img style=" border: none; " src="../Images/arrow-down.gif" /></a>
<!--Subnav Starts Here-->
<span>
<a style="font-weight:lighter; " href="#" >Joining the HX5 Team</a> |
<a href="#">Current Opportunities</a> |
</span>
</li>
</ul>
</div>
<div id="subnavbar" >
<!-- <asp:SiteMapPath ID="SiteMapPath" runat="server">
</asp:SiteMapPath> -->
</div>
<div id="topContent" style="border-bottom:solid 1px #191970;">
<table>
<tr><td style="height:30px; width:900px;" >
<asp:ContentPlaceHolder id="TopPlaceHolder" runat="server">
</asp:ContentPlaceHolder>
</td>
</tr>
</table>
</div>
<!-- <div id="mainContent"> -->
<asp:ContentPlaceHolder id="MainPlaceHolder" runat="server" />
<!-- </div> -->
<!-- <div id="lowerContent"> -->
<asp:ContentPlaceHolder id="LowerPlaceHolder" runat="server">
</asp:ContentPlaceHolder>
<!-- </div> -->
<div id="footerContent" style=" height:50px; width:900px; text-align: right; background-color: #ccc; ">
<span style=" font-family: Arial, Helvetica, sans-serif; font-size: xx-small; font-weight: bold; color: #000080; margin: 30px 20px 0px 0px;"> Copyright 2010 HX5, LLC All rights reserved</span></div>
</div>
</form>
</body>
</html>

View 4 Replies

"The Underlying Connection Was Closed: The Connection Was Closed Unexpectedly"?

Oct 12, 2010

Im working with this Arquitecture : Web Service -> DLL(Xml Translator) -> Asp.net.Everything works fine, but,i have a process that takes about 23 minutes, in that time i show a "loading screen" that avoid the user to do anything, but after 20 mins while the web service is working and after debug my code, i get this error :

"The underlying connection was closed: The connection was closed unexpectedly"

Then, my "loading screen" keep visible until the web service completes the work, and after that, the "loading" hides and the web form shows my gridview empty.(its should show data).

View 3 Replies

SQLDataReader Find Value Of Each Row?

Nov 10, 2010

I used to use datasets instead of sqldatareaders and I used to be able to do something like this

If dataset.tables(0).Rows(0)(1).ToString()) = "N" Then
lbl.Text = dataset.tables(0).Rows(0)(2).ToString())
Else
'Do Nothing
End If

This obviously doesn't work with sqldatareaders.I have code to see if the SQLDatareader has any rows but was wondering if there was a way to get the value of each rowI'm guessing this is possible and i've had a look around but can't seem to find anything

Dim conn As SqlConnection = New SqlConnection("server='h'; user id='w'; password='w'; database='w'; pooling='false'")
conn.Open()
Dim query As New SqlCommand("DECLARE @investor varchar(10), @sql varchar(1000) Select @investor = 69836 select @sql = 'SELECT * FROM OPENQUERY(db,''SELECT * FROM table WHERE investor = ''''' + @investor + ''''''')' EXEC(@sql)", conn)
Dim oDR As SqlDataReader = query.ExecuteReader()
If oDR.HasRows or dataset.tables(0).Rows(0)(1).ToString()) = "N" Then
lbl.Text = dataset.tables(0).Rows(0)(2).ToString())
Else
'Do Nothing
End If

That is the code I have at the moment which obviously doesn't work

View 2 Replies

Using SqlDataReader In Linq?

Sep 7, 2010

In normal way of write the code as follows in DAL.

DataSet ds=db.executedataset();
IEnumerable<datarow>= ds.tables[0].asEnumerable();

For better performance can use datareader in Linq instead of DataSet

View 11 Replies

SqlDataReader To String[]?

Mar 20, 2011

why the following code doesn't work?

internal static string[] GetToolsForRole(string selectedRole) {
string[] tempStr;
ArrayList myAL = new ArrayList();

[code]...

View 2 Replies

ADO.NET :: How To Get The Number Of Rows From Sqldatareader

Mar 29, 2011

how to get the number of rows from sqldatareader

View 2 Replies

C# - Read Data From SqlDataReader?

Oct 25, 2010

I have a SQL Server 2008 database and I am working on it in the backend. I am working on asp.net/C#

SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
//how do I read strings here????
}

I know that the reader has values. My SQL command is to select just 1 column from a table. The column contains strings ONLY. I want to read the strings (rows) in the reader one by one. How do I do this?

View 5 Replies

ADO.NET :: Load List From Sqldatareader?

Feb 26, 2011

How can you load a List<> object from a Datareader?I tried to do it but for some reason it is not working. Below is the code that I tried to use:

using System;
using System.Data;
using System.Configuration;

[code]...

View 1 Replies







Copyrights 2005-15 www.BigResource.com, All rights reserved