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?
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.
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.
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
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.
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 .
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.
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.
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:
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:.................................
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.
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.
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:
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
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.
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).
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
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?