There Is Already An Open DataReader Associated With This Command Which Must Be Closed
Oct 15, 2007
I have gathered from reading online that I need to create a 2nd connection to SQL Server if I want to insert records into the database while in a "while (reader.Read())" loop.
I am trying to make my code as generic as possible, and I don't want to have to re-input the connection string at this point in my code. Is there a way to generate a new connection based on the existing open one? Or should I just create 2 connections up front and carry them around with me like I do for the 1 connection now?
Thanks.
View 7 Replies
ADVERTISEMENT
Jun 26, 2007
This is my code:1 If Session("ctr") = False Then
2
3 Connect()
4
5 SQL = "SELECT * FROM counter"
6 SQL = SQL & " WHERE ipaddress='" & Request.ServerVariables("REMOTE_ADDR") & "'"
7 dbRead()
8
9 If dbReader.HasRows = True Then
10
11 dbReader.Read()
12 hits = dbReader("hits")
13 hits = hits + 1
14 dbClose()
15
16 SQL = "UPDATE counter SET hits=" & hits
17 SQL = SQL & " WHERE ipaddress='" & Request.ServerVariables("REMOTE_ADDR") & "'"
18 dbExecute()
19
20 Else
21
22 SQL = "INSERT INTO counter(ipaddress,hits)"
23 SQL = SQL & " VALUES('" & Request.ServerVariables("REMOTE_ADDR") & "',1)"
24 dbExecute()
25
26 End If
27
28 Session("ctr") = True
29
30 End If
1 Public Sub Connect()
2 Conn = New SqlConnection("Initial Catalog=NURSETEST;User Id=sa;Password=sa;Data Source=KSNCRUZ")
3 If Conn.State = ConnectionState.Open Then
4 Conn.Close()
5 End If
6 Conn.Open()
7 End Sub
8
9 Public Sub Close()
10 Conn.Close()
11 Conn = Nothing
12 End Sub
13
14 Public Sub dbExecute()
15 dbCommand = New SqlCommand(SQL, Conn)
16 dbCommand.ExecuteNonQuery()
17 End Sub
18
19 Public Sub dbRead()
20 dbCommand = New SqlCommand(SQL, Conn)
21 dbReader = dbCommand.ExecuteReader
22 End Sub
23
24 Public Sub dbClose()
25 SQL = ""
26 dbReader.Close()
27 End Sub
View 2 Replies
View Related
May 3, 2008
the class code:
Dataase.cs: using System; using System.Data; using System.Configuration; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Xml.Linq; using System.Data.SqlClient; using System.Data.Common; using System.Web; /// <summary> /// Summary description for DataBase /// </summary> public class DataBase { private SqlConnection con=new SqlConnection(); private void Open() { if (con==null) { con = new SqlConnection("Data Source=58.17.30.81;Initial Catalog=a1230192748;Persist Security Info=True;User ID=a1230192748;Password=***"); } if (con.State == System.Data.ConnectionState.Closed) { con.ConnectionString = "Data Source=58.17.30.81;Initial Catalog=a1230192748;Persist Security Info=True;User ID=a1230192748;Password=****"; con.Open(); } } public void Close() { if (con != null && con.State != System.Data.ConnectionState.Open) con.Close(); } public DataBase() { // // TODO: Add constructor logic here // } public string liuyan(string id,string sign) { string com=string.Empty; switch(sign) { case "xiaobiaoti": com="Select subject from liuyan where liuyanid='"+id+"'"; break; case "def_message": com="Select message from liuyan where liuyanid='"+id+"'"; break; } SqlCommand myCommand=new SqlCommand(com,con); Open(); try { SqlDataReader sdr=myCommand.ExecuteReader(); if (sdr.Read()) { return sdr[0].ToString(); } else { return ""; } sdr.Close(); //what i have written.} catch (Exception ex) { HttpContext.Current.Response.Write("<script>alert('error:" + ex.Message + "')</script>"); return ""; } finally { myCommand.Dispose(); Close(); } } }
it was instantiated once in aspx.cs code.I invoke liuyan(string id,string sign) twice.The first one is OK and the second one makes an exception.
View 3 Replies
View Related
Nov 1, 2006
HiI'm trying to loop through all the records in a recordset and perform a database update within the loop. The problem is that you can't have more than one datareader open at the same time. How should I be doing this? cmdPhoto = New SqlCommand("select AuthorityID,AuthorityName,PREF From qryStaffSearch where AuthorityType='User' Order by AuthorityName", conWhitt)conWhitt.Open()dtrPhoto = cmdPhoto.ExecuteReaderWhile dtrPhoto.Read()If Not File.Exists("D:WhittNetLiveWebimagesstaffphotospat_images_resize" & dtrPhoto("PRef") & ".jpg") ThencmdUpdate = New SqlCommand("Update tblAuthority Set NoPhoto = 1 Where AuthorityID =" & dtrPhoto("AuthorityID"), conWhitt)cmdUpdate.ExecuteNonQuery()End IfEnd WhileThanks
View 7 Replies
View Related
May 2, 2008
"There is already an open datareader associated with this command which must be closed first."
I have received this same error before, but I'm not sure why I'm getting it here.'Create a Connection object.
MyConnection = New SqlConnection("...............................")
'Check whether a TMPTABLE_QUERY stored procedure already exists.
MyCommand = New SqlCommand("...", MyConnection)
With MyCommand
'Set the command type that you will run.
.CommandType = CommandType.Text
'Open the connection.
.Connection.Open()
'Run the SQL statement, and then get the returned rows to the DataReader.
MyDataReader = .ExecuteReader()
'Try to create the stored procedure only if it does not exist.
If Not MyDataReader.Read() Then
.CommandText = "create procedure tmptable_query as select * from #temp_table"
MyDataReader.Close()
.ExecuteNonQuery()
Else
MyDataReader.Close()
End If
.Dispose() 'Dispose of the Command object.
MyConnection.Close() 'Close the connection.
End With
As you can see, the connection is opened and closed, and the datareader is closed. Here's what comes next...'Create another Connection object.
ESOConnection = New SqlConnection("...")
If tx_lastname.Text <> "" Then
If (InStr(sqlwhere, "where")) Then
sqlwhere = sqlwhere & " AND lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'"
Else
sqlwhere = " where lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'"
End If
End If
If tx_firstname.Text <> "" Then
If (InStr(sqlwhere, "where")) Then
sqlwhere = sqlwhere & " AND fname like '" & Replace(tx_firstname.Text, "'", "''") & "%'"
Else
sqlwhere = " where fname like '" & Replace(tx_firstname.Text, "'", "''") & "%'"
End If
End If
dynamic_con = sqlwhere & " order by arr_date desc "
'create the temporary table on esosql.
CreateCommand = New SqlCommand("CREATE TABLE #TEMP_TABLE (".............", ESOConnection)
With CreateCommand
'Set the command type that you will run.
.CommandType = CommandType.Text
'Open the connection to betaserv.
ESOConnection.Open()
'Run the SQL statement.
.ExecuteNonQuery()
End With
'query our side
ESOCommand = New SqlCommand("SELECT * FROM [arrest_index]" & dynamic_con, ESOConnection)
'execute query
ESODataReader = ESOCommand.ExecuteReader()
'loop through recordset and populate temp table
While ESODataReader.Read()
MyInsert = New SqlCommand("INSERT INTO #TEMP_TABLE VALUES("......", ESOConnection)
'Set the command type that you will run.
MyInsert.CommandType = CommandType.Text
'Run the SQL statement.
MyInsert.ExecuteNonQuery()
End While
ESODataReader.Close() 'Create a DataAdapter, and then provide the name of the stored procedure.
MyDataAdapter = New SqlDataAdapter("TMPTABLE_QUERY", ESOConnection)
'Set the command type as StoredProcedure.
MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
'Create a new DataSet to hold the records and fill it with the rows returned from stored procedure.
DS = New DataSet()
MyDataAdapter.Fill(DS, "arrindex")
'Assign the recordset to the gridview and bind it.
If DS.Tables(0).Rows.Count > 0 Then
GridView1.DataSource = DS
GridView1.DataBind()
End If
'Dispose of the DataAdapter
MyDataAdapter.Dispose()
'Close server connection
ESOConnection.Close() Again, a separate connection is open and closed.I've read you can only have 1 datareader available per connection. Isn't that what I have here? The error is returned on this line: MyInsert.ExecuteNonQuery()
Help is appreciated.
View 3 Replies
View Related
Jul 20, 2005
I am debugging an app which blocks many processes in a SQL7 server DB.The app log writes every transaction "open" and "close".The weird thing is : when the app logfile says the transaction isdropped (object closed) the db keeps showing the process "running", ina sleeping mode, with open_tran in 2 or even 3 and in an awaitingcommand status.We are working on NT4.0, SP6a (all fixes up to date, MDAC 2.7SP1) onIIS side, an equal box for MTS (same patches and updates) and a SQLServer 7 (on NT4, same fixes ans SPs) database on another box.Is this normal? Those sleeping processes are blocking other apps andeverything gets slow and messy....the only solution is to kill thoseblocking processes.Thanks!
View 1 Replies
View Related
Apr 16, 2008
Looking at the below code you can see that I have a separate Connection for each Insert Statement.'OPEN CONNECTION TO ESOSQL
MyConnection = New SqlConnection("......")
MyConnection.Open()
'check if there are existing charges for this person. The user must enter in atleast 1 charge before proceeding with arrest insert.
MyCheck = New SqlCommand("SELECT * FROM ARREST_CHARGES WHERE ARRESTNO = '" & Session("uid") & "'", MyConnection)
MyCheck.CommandType = CommandType.Text
MyDataReader = MyCheck.ExecuteReader
If MyDataReader.HasRows Then
MyConnection1 = New SqlConnection("...")
MyConnection1.Open()
MyInsert = New SqlCommand("INSERT INTO ARREST_INDEX (ARRESTNO, NOTES) VALUES ('" & Session("uid") & "','" & tx_notes.Text & "')", MyConnection1)
MyInsert.CommandType = CommandType.Text
MyInsert.ExecuteNonQuery()
MyConnection1.Close()
MyConnection2 = New SqlConnection("....")
MyConnection2.Open()
MyOtherInsert = New SqlCommand("INSERT INTO ARREST_COMMENTS (ARRESTNO, NOTES) VALUES ('" & Session("uid") & "','" & tx_notes.Text & "')", MyConnection2)
MyOtherInsert.CommandType = CommandType.Text
MyOtherInsert.ExecuteNonQuery()
MyConnection2.Close()
Label1.Text = ""
Else
div1.Style.Add("display", "block")
Label1.Text = "You must enter charges before proceeding."
End If
MyConnection.Close()One would think I should only have to use one connection. However, if I use only one I get this error:There is already an open DataReader associated with this Command which must be closed first. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.Source Error: Line 60:
Line 61: MyInsert.CommandType = CommandType.Text
Line 62: MyInsert.ExecuteNonQuery()
Line 63:
Line 64: ' MyConnection1.Close()The only way I could get rid of it was to encapsulate each individual INSERT statement within its own connection. This seems to me as very inefficient. Can anyone explain why I couldn't just use one connection?Thanks.
View 5 Replies
View Related
Aug 1, 2004
Hi all!
I basically need to get some records from a table and while looping through them i need to insert some records on other table.
I keep getting this error:
There is already an open DataReader associated with this connection which must be closed first.
The piece of code that I have is like this:
...
SqlCommand sqlCmd2 = new SqlCommand(sqlString2, dbConn);
sqlCmd2.Transaction = trans;
SqlDataReader dr = sqlCmd2.ExecuteReader(CommandBehavior.CloseConnection);
//loop through dr
while (dr.Read())
{
string sqlStr = "insert into prodQtyPrice (typeQtyId, prodId, typeId) values(28," + dr["prodId"] + "," + dr["typeId"] +")";
SqlCommand sqlCmd3 = new SqlCommand(sqlStr, dbConn);
//sqlCmd3.Transaction = trans;
sqlCmd3.ExecuteNonQuery();
}
...
Also I would like to have the insertions in the same transaction as the previous sql commands.
Thanks a million!
LAM
View 4 Replies
View Related
Dec 13, 2007
Hi,
I hope someone can help me with this.
I have created a data reader to loop thru a table and build an update statement.
Now I need to execute this statement in the datareader loop but the connection object is in use.
I tried creating another connection object but get an error:
"The context connection is already in use."
Is there a way to open another connection while the main context connection is open.
Just to make it clear, I do not want to read all the rows into memory first and then call update, it has to
happen for each iteration of the datareader.
Thanks.
--
With Regards
Shailen Sukul
Software Architect/Developer/Consultant
(BSc | Mcts (Biztalk (IP)) | Mcpd | Mcts (Web, Win, Dist Apps) | Mcsd.NET | Mcsd | Mcad)
Ashlen Consulting Services Pty Ltd
(http://www.ashlen.com.au)
MSN | Skype | GTalk Id: shailensukul
Ph: +61 0421 277 812
Fax: +61 3 9011 9732
Linked In: http://www.linkedin.com/in/shailensukul
View 7 Replies
View Related
Jan 9, 2007
I am getting the following error when running some of my reports that use a Report Model on a recently built Windows 2003 R2 server with SQL Server 2005 SP2 intalled. The reports run fine our SQL Server 2005 RTM server.
W3wp!webserver!7!01/09/2007-12:57:58:: e
ERROR: Reporting Services
error Microsoft.ReportingServices.Diagnostics.Utilities.RSException:
An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> System.InvalidOperationException:
There is already an open DataReader associated with this Command which must be closed first.
Any help would be appreciated.
Rick
View 2 Replies
View Related
Jul 31, 2007
I have the following SQL statement in the DataReader.SQLCommand.
select * from myTable where DataEdited > 'some hard coded date data'
I want to read the above 'some hard coded date data' from a table sitting in different database which is not the same server where the DataReader is connected.
How to do that?
I can populate the 'some hard coded date data' to a SSIS variable but do not know how to embed that in the SQL statement.
Thank you for your thoughts.
Smith
View 9 Replies
View Related
Feb 1, 2007
This is my page_loads event code and iam getting the Exception pasted below the code.
-----------------------------------------------------------------------------------------------------------------------------------------------------
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
If Not Page.IsPostBack Then
Dim myconnection As New SqlConnection("Data Source=localhostSQLEXPRESS;initial catalog = safetydata.mdf;Integrated Security=True;User Instance=True")
Dim strSQL As String = "SELECT Incident_Id,Emp_No From Report_Incident"
Dim mycommand As New SqlCommand(strSQL, myconnection)
myconnection.Open()
Dim reader As SqlDataReader = mycommand.ExecuteReader()
Dim chart As New PieChart
chart.DataSource = reader
chart.DataXValueField = "Incident_id"
chart.DataYValueField = "Emp_No"
chart.DataBind()
chart.DataLabels.Visible = True
ChartControl1.Charts.Add(chart)
ChartControl1.RedrawChart()
myconnection.Open()
End If
End Sub
-------------------------------------------------------------------------------------------------------------------------------
EXCEPTION IS BELOW
Cannot open database "mydatabase.mdf" requested by the login. The login failed.Login failed for user 'myusername'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot open database "safetydata.mdf" requested by the login. The login failed.Login failed for user 'myusername'.Source Error:
Line 18: Dim strSQL As String = "SELECT Incident_Id,Emp_No From Report_Incident"
Line 19: Dim mycommand As New SqlCommand(strSQL, myconnection)
Line 20: myconnection.Open()
Line 21: Dim reader As SqlDataReader = mycommand.ExecuteReader()
Line 22: Source File: C:Incident Reporting System--Trial VersionWebChart.aspx Line: 20 Stack Trace:
[SqlException (0x80131904): Cannot open database "safetydata.mdf" requested by the login. The login failed.
Login failed for user 'myusername'.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +171
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2305
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +34
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +606
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +193
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +501
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +429
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +70
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +512
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +85
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +89
System.Data.SqlClient.SqlConnection.Open() +160
ASP.webchart_aspx.Page_Load(Object sender, EventArgs e) in C:Incident Reporting System--Trial VersionWebChart.aspx:20
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45
System.Web.UI.Control.OnLoad(EventArgs e) +80
System.Web.UI.Control.LoadRecursive() +49
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3745
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.
View 1 Replies
View Related
Apr 2, 2008
I have a multiple form project for a Windows CE 5.0 device (Symbol MC3000) written in VB.NET (Visual Studio 2005) using SQL Server Compact 3.5.
If I create the database on the SD Card, the program works fine until the unit is "suspended". When the power resumes, I can still continue to work and save/access data in the database as long as I do not go to another form. If I go to another form, I get an access violation error (0xC0000005).
If the database is installed in main memory the prorgam works properly even after the power is cycled.
Unfortunately, due to the memory size restrictions on the device, there are cases where the database needs to be stored on the SD Card.
Currently the program uses a single "global" SQLCeConnection and the connection is closed prior to the device being powered off. However, each procedure/event in the program uses it's own SQLCeCommand and SQLCeDataReader objects (which are created and closed/disposed prior to the end of the procedure).
I have tried everything I can think of, including OS updates, .NET Compact Framework 2.0 updates and modifications to the program to correct this, but the results are the same.
Would creating a global SQLDataReader and SQLCeCommand object help with this issue or should the way that this is currently being handled work?
View 4 Replies
View Related
Apr 26, 2007
I am accessing SQL2005 with C# code using OleDbConnection.
A try and catch block catches the following error once a while between the Open() and Close() of the connection:
ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
I do not even have any idea where to start to debug this. The ExecuteNonQuery() runs a delete SQL query. It works 99.9% of the time. I do not see anything wrong when this error happens.
Any hint would be greatly appreciated.
View 9 Replies
View Related
Sep 21, 2006
how to open enterprise manager using keyboard shortcut?
and how to open enterprise manager using command prompt?
Regards
Satish
View 4 Replies
View Related
Sep 4, 2015
During our DR drill we found that the same code which used to run perfectly fine on our Primary Data Centre is running very slow on Disaster Recovery DB Server and there are Lot's of open transaction with sleeping status and waittype as 'AWAITING COMMAND'.CPU, Memory and disk utilization are good. The ping reply between the app server and the DB server is well within the limit's even blocking is not their, also nothing is reported in the error logs. We are using SQL server 2014 STD 64 BIT on Windows Server 2012.
View 6 Replies
View Related
Aug 30, 2006
HI!
as far as I know from docs and forum datareader is for .NET data in memory. So if a use a complex dataflow to build up some data and want to use this in other dataflow componens - could i use data datareader source in the fist dataflow and then use a datareader souce in the second dataflow do read the inmemoty data from fist transform to do fursther cals ?
how to pass in memory data from one dataflow to the next one (i do not want to rebuild the logic in each dataflow to build up data data ?
Is there a way to do this ? and is the datareader the proper component ? (because its the one and only inmemory i guess, utherwise i need to write to temp table and read from temp table in next step) (I have only found examples fro .NET VB or C# programms to read a datareader, but how to do this in SSIS directly in the next dataflow?
THANKS, HANNES
View 7 Replies
View Related
Sep 7, 2007
I get the below error only when my IDE open. It connects well when it is found closed.
[SqlException (0x80131904): Cannot open user default database. Login failed.Login failed for user 'JPASPNET'.]
I could solve this by giving the logged in windows user to impersonate under IIS window > WEBSITE > ASP.NET tab > EDIT CONFIG > APPLICATION tab
But I wish someone could give me the proper solution.
I almost tried all from giving ASPNET user as a administrator to configuring the same in Express management tool.
Environment: XP pro, VWD and SQL Express
View 3 Replies
View Related
Jun 7, 2006
Hi there,
I got an approach like that:
1) Read something from DB - check the value, if true stop if false go on2) Read the second Value (another SQL Statement) - check the value etc.
Now I could open the connection at 1) and if I have to go to 2) I leave the connection open and use the same connection at 2). Is it ok to do that?
The other scenario would be opening a connection at 1), immediately close it after I read the value and open a new connection at 2).
Thanks for the input!
View 4 Replies
View Related
Nov 3, 2014
Post installation of SQL Server 2014 Express edition, I am able to connect to the Database Instance.
But while opening a new query window in SSMS or opening a table getting the error:
Package 'RadLangSvc.Package, RadLangSvc, Version 12.0.0.0, Culture=Neutral, Public Token=89845dcd8080cc91' failed to load
Object reference not set to an instance of an object. (mscorlib)..Have already tried installing the componentsDACProjectSystemSetup_enu.msi, TSql LanguageService_enu.msi, DACFramework_enu.msi from path VS 2010 WCU DAC.
View 5 Replies
View Related
Sep 12, 2007
thx but this code not needed anymore
sry
View 1 Replies
View Related
Jun 7, 2008
I have an sp, which has 2 select statements, so iam using a sqldatareader and binding the data to a dropdown.
the first binding is fine, but when i say dataReader.NextResult(), It is null.It says the reader is closed. Can any one tell a work around for this.
thanx in advance,
Anil Kumar.
View 6 Replies
View Related
May 5, 2004
i am running a java program in tomcat to connect SQL Server,using the Microsoft's jdbc driver ,as the following code :
import java.sql.*;
class Bkjz{
ResultSet rs=null;
String spde;
String condition;
Connection con=null;
Statement sql;
public String getSpde(){
return spde;
}
public void setSpde(String spde){
this.spde=spde;
}
public ResultSet Searchsjk(){
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con=DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;databasename=zkbm","zkbm","zkbm");
sql=con.createStatement(ResultSet.TYPE_SCROLL_SENS ITIVE,ResultSet.CONCUR_READ_ONLY);
if(spde.equals(""))
condition="select CRCC,CRNM,SPDE,SPNM from SPCR where EMTP='5'group by SPDE,SPNM,CRCC,CRNM";
else
condition="select CRCC,CRNM,SPDE,SPNM from SPCR where SPDE='"+spde+"'"+"and EMTP='5' group by SPDE,SPNM,CRCC,CRNM";
rs=sql.executeQuery(condition);
//con.close();
if(!rs.next())
{
return null;
}
else
{
rs.previous();
return rs;
}
}
catch(Exception e){
message="exception!!! "+e.toString();
System.out.println(e);
return null;
}
}
}
public class Bk{
public static void main(String args[]){
Bkjz bbb=new Bkjz();
bbb.setSpde("1020110");
try{
ResultSet rr=bbb.Searchsjk();
while(rr.next()){
System.out.println(rr.getString("CRCC"));
}
}
catch(Exception e){
System.out.println(e);
}
}
}
without con.close(),it can return Resultset ,but when includeing con.close(),an Exception tell me:Object has been closed, in other programms i've close connection,but it never throws this Exception,why? thanks
View 1 Replies
View Related
Jan 27, 2007
I have the Function, that fires from onLoad even of one of the asp:Label controls on my main page.
Here is it's code:
SqlConnection conn = new SqlConnection(); conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["UkraineConnectionString"].ToString(); SqlCommand comm = new SqlCommand("SELECT [Greeting] FROM [Misc]", conn);
try { conn.Open(); } catch { Response.Write("Error opening connection in Page_Load of default.aspx to retrieve the greetings"); }
string MyGreet = (string)comm.ExecuteScalar();
Greetings.Text = MyGreet;
try { conn.Close(); } catch { Response.Write("Error closing connection in Page_Load of default.aspx after retrieving the greetings"); } }
When it gests to conn.Open() in the debugging mode I see that the ServerVersion = 'conn.ServerVersion' threw an exception of type 'System.InvalidOperationException'.
The most interesting thing is that it used to work before.
Here is the connection string it retrieves fine.
"Data Source=MDM1;AttachDbFilename=|DataDirectory|Ukraine.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"
As I said it used to work, but one day I tried to access the web site and it said this error that I get, that the connection is closed. Then I was using the SQL Server Express. When in the Visual studio if I would run this same site in debug, everything was working fine. I decided to uninstall the SQL Express and installed the SQL Server.
If I open the SQL Server Managment Studio in the Server name field I see MDM1(this is the name of the PC, but probably it is the same name for the Server. Well, in the MAnagment Studio it conects fine to the MDM1 so it is no probably the naming problem. Ithink it has something to do with permisssions.
If someon can - please help. Thanks.
View 3 Replies
View Related
Jan 30, 2007
Hello,
I have built a system, that uses a .dll file for all SQL operations. So a program looks something like this:
using myDLL;
....
SQLDBCON mSQL = new SQLDBCON();
and here comes the rest of the program.
My question is, the dll file has all stored procedures and when you declare mSQL as shown above, then it opens a thread to the database for that user.
Is that thread properly closed when the site has finished loading?
in my .aspx page i dont have a function like mSQL.CloseDB();
and if i try to add something like this to the dll file
~SQLDBCON {
m_local_con.Close();m_local_con.dispose();
}
i get a error message says something that this is not allowed.
Just want to know if my thread in the dll file is properly closed?
View 9 Replies
View Related
Jan 13, 2000
That is an app ACCESS 2000 wih Named Pipe ODBC to SQL Server 6.5.
After 10 minutes without use this app, the connection closed !
Have-you idea for correct this probleme ?
Regards
Alain
View 2 Replies
View Related
May 3, 2006
I'm getting object closed when returning a recordset from a stored procedure. I've tested the select statement in Query Analyzer and a value is getting returned. So, I'm not sure if my code for my stored procedure is incorrect?
Here is the code for the procedure:
CREATE Procedure dbo.GetRepEmailByZip
@sessionid varchar(50),
@zip varchar(5)
AS
Begin Transaction
INSERT INTO dbo.SupportRequests(firstname,lastname,schoolname, address,city,state,zip,phone,email,currentcustomer ,implementationtype,producttype,comment)
(SELECT FirstName,LastName,SchoolName,Address,City,State,Z ip,Phone,Email,CurrentCustomer,ImplementationType, ProductType,Comment
FROM dbo.Temp_ContactInfo
WHERE sessionid = @sessionid)
--If Transacation fails, stop execution of procedure, return error code and Rollback Transaction
IF @@ERROR<>0 OR @@RowCount = 0
BEGIN
ROLLBACK TRANSACTION
--return value
RETURN 1
END
--If Transaction succeeds, commit transaction, continue and process the select statement
COMMIT TRANSACTION
SELECT r.email
FROM PostalCodes p
INNER JOIN TerritoryList z ON p.ZipID = z.ZipID
INNER JOIN RepList r ON r.RepID = z.RepID
WHERE p.ZipCode = @zip
GO
This is the code I'm calling to execute the procedure and return the recordset:
set GetRepEmail = Server.CreateObject("ADODB.Command")
With GetRepEmail
.ActiveConnection = MM_DBConn_STRING
.CommandText = "dbo.GetRepEmailByZip"
.CommandType = 4
.CommandTimeout = 0
.Prepared = true
.Parameters.Append .CreateParameter("@RETURN_VALUE", 3, 4)
.Parameters.Append .CreateParameter("@sessionid", 200, 1,50,usrid)
.Parameters.Append .CreateParameter("@zip", 200, 1,5,zip)
set RepEmail = .Execute()
End With
Dim x, y
x = RepEmail.RecordCount
y = RepEmail.State
Response.Write(x)
Response.Write("<br>")
Response.Write(y)
'Response.Write(RepEmail("email"))
Response.End()
I'm getting "Operation is not allowed when the object is closed", which is occuring on the following line:
x = RepEmail.RecordCount
I'm trying to determine if this is problem within my procedure or in the application code.
Thanks in advance for any help.
View 4 Replies
View Related
Feb 22, 2007
In a previous post I asked the question relating to moving a file assoicated with a connection. It appears I need to close the connection first.
On advice, in a script task I created the following:
Dim dtsConnection As Microsoft.SqlServer.Dts.Runtime.ConnectionManager
For Each dtsConnection In Dts.Connections
Dim LiveConnection As Object = dtsConnection.AcquireConnection(Nothing)
Dts.Events.FireInformation(0, "", "Connection Name : " + dtsConnection.Name, "", 0, False)
dtsConnection.ReleaseConnection(LiveConnection)
dtsConnection.Dispose()
Next
Dts.TaskResult = Dts.Results.Success
RetainSameConnection is set to true.
The dispose line is something I've added just to try - I've tried it without this line as well.
The next component then moves the file and fails complaining that the file is in use.
What can I do?
Regards
Guy
View 2 Replies
View Related
Jan 5, 2006
Wierd problem here
As one user, when i select * from sys.conversation_endpoints I can see all (I assume) conversations in all states specifically DO, DI and CD
However when I change to another user I see only DI
Why is this?
If it is a permissions issue what permission do I have to grant to a user to see all conversations in sys.conversation.endpoints?
View 1 Replies
View Related
Apr 16, 2007
I have a page that I have 3 connections. I've made sure that each of these are closed when they are not being used and opened just right before being used. I keep getting the error "There is already an open DataReader associated with this Command which must be closed first." This error might show up as being produced by a dataadapter or sqldatareader...I have many. I've even tried to make separate connections as some have mentioned for each...leaving me with 15+ connections. I have added "MultipleActiveResultSets=True" to the connection strings as some have mentioned. I just don't know where to go from here...
Is it possible that the problem lies in multiple instances of this page being opened? Also, the data refreshes every 15 seconds. I really need this to work, but I have no clue on how to fix this problem. The error is easy to reproduce by opening up multiple instances, but some of the times is doesn't give an error at all?!
View 4 Replies
View Related
Jun 18, 2001
Hello all,
I'm writing some stored procedures that first do an Insert or an Update, and then also do a Select to return a Recordset back to an ADO client. However an Insert or Update causes a closed recordset to be produced in ADO. I can skip over the closed recordset with the NextRecordset method.
So the question is, is there any way of stopping the closed recordset being returned? I don't want to have to call the NextRecordset method from ADO as this would mean that the client would need to know about the implementation of the stored procedure.
Has anyone got any ideas on how to get round this?
Thanks
Dave Sykes
View 2 Replies
View Related
Jan 5, 2013
Finding the court cases where all children associated with that case have a programClosureDate. I can run this query:
CaseInfo Table
CaseID,
CaseNumber,
CaseName
CaseChild Table
CaseID, FK to CaseInfo
ChildPartyID, FK to PartyID in Party table
ProgramClosureDate
Party Table
ID,
PartyID,
Firstname,
LastName
SELECT ci.CaseNumber, ci.CaseName, p.firstname+' '+p.lastname AS child, cc.programClosureDate
FROM CaseInfo ci JOIN
CaseChild cc ON ci.CaseID = cc.CaseID JOIN
Party p ON cc.ChildPartyID = p.PartyID
WHERE cc.ProgramClosureDate IS NOT NULL
ORDER BY ci.CaseName
But this does not give me the cases where all the children have programCLosureDate IS NOT NULL.
View 5 Replies
View Related
Jul 23, 2005
I have written a stored proceedure for MSSQL that needs to run for hours ata time. I need to execute it from C++ code. The current code does:nRet = SQLDIRECTEXEC(hstmt, "exec stored_proc", SQL_NTS)followed shortly after by aFree_Stmt_Handle(hstmt) //roughlyThe stored proc currently dies with the statement handle, not fullypopulating the table I need it to.I need to either know when the proc finishes so I can close the handle afterthat, or allow the proc to run independently on the server no matter whatthe program is doing (is exited, etc), either of these is fine.Please Help! Thanks in advance!Joseph
View 2 Replies
View Related
