How To Open A Second Connection While In A Datareader
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
ADVERTISEMENT
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
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
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
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
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
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
View Related
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
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
Jul 31, 2006
Please see following code :
SqlConnection conn=new SqlConnection(@"something....;");SqlCommand comm=new SqlCommand("Select TOP 10 * FROM TableReaderTest WITH (HOLDLOCK) ",conn);
conn.Open();SqlDataReader rd;conn=null;
try{ rd = comm.ExecuteReader(); rd.Close();}catch (Exception ex){ MessageBox.Show(ex.Message);}
This Code works fine. I have set conn=null, still datareader is able to read the data. Why?
Thank you.
View 6 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
Mar 16, 2006
Hi,
I am trying to use the DataReader Source to import a table from a PostgresSQL database into a new table in SQL 2005 database. It works for all tables except one, which has over 80,000 records with long text columns. When I limit the import to fraction of records (3,000 to 4,000 records) it works fine but when I try to get all it generates the following errors:
Source: DataReader using ADO.NET and ODBC driver to access PostgresSQL table
Destination: OLE DB Destination - new table in SQL 2005
(BTW - successful import with DTS packagein SQL 2000)
---Errors
Error: 0x80070050 at Import File, DTS.Pipeline: The file exists.
Error: 0xC0048019 at Import File, DTS.Pipeline: The buffer manager could not get a temporary file name. The call to GetTempFileName failed.
Error: 0xC0048013 at Import File, DTS.Pipeline: The buffer manager could not create a temporary file on the path "C:Documents and SettingsmichaelshLocal SettingsTemp". The path will not be considered for temporary storage again.
Error: 0xC0047070 at Import File, DTS.Pipeline: The buffer manager cannot create a file to spool a long object on the directories named in the BLOBTempStoragePath property. Either an incorrect file name was provided, or there are no permissions.
Error: 0xC0209029 at Import File, DataReader Source - Articles [1]: The "component "DataReader Source - Articles" (1)" failed because error code 0x80004005 occurred, and the error row disposition on "output column "probsumm" (1639)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC02090F5 at Import File, DataReader Source - Articles [1]: The component "DataReader Source - Articles" (1) was unable to process the data.
Error: 0xC0047038 at Import File, DTS.Pipeline: The PrimeOutput method on component "DataReader Source - Articles" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
---End
Any idea why it can't create a temp file or why it complains about the "The File exists", which file, where, etc. Any help or alternative suggestions are greatly appreciated. What I am missing or doing wrong here?
Best,
Michael Sh
View 11 Replies
View Related
Jan 13, 2006
Synopsis:
Attempting to create a data flow task to copy data from AS/400 (DB2) to SQL2005, using an existing System DSN ODBC connection defined on the SQL2005 host.
Problem:
When adding the DataReader Source component to the package, I cannot assign the Connection Manager. Designer issues the error message:
"The runtime connection manager with the ID "" cannot be found. Verify that the connection manager collection has a connection manager with that ID."
Editing the DataReaderSrc component shows only one row under the Connection Managers tab:
Name=IDbConnection
Connection Manager=blank
Description=Managed connection manager
The datareadersrc component editor displays the warning message: "Not all connection managers have been set. Set all connection managers.". Clicking the Refresh button causes the error message to be displayed "The runtime connection manager with the ID "" cannot be found. Verify that the connection manager collection has a connection manager with that ID."
I am prevented from assigning my Connection Manager object the DataReaderSrc.
The package already contains one Connect Manager object:
Provider: .Net Providers/Odbc Data Provider
System DSN
Test Connection operation succeeds
Any help would be appreciated.
Fraser.
View 3 Replies
View Related
Sep 22, 2006
I'm trying to create a DataReader source object using a working ADO.NET Oracle Client connection object that exists in the connection manager.
Problem:
When I open the editor the DataReaderSrc component shows only one row under the Connection Managers tab:
Name=IDbConnection
Connection Manager=blank
Description=Managed connection manager
I cannot assign the ADO.NET connection (or any connections). I see a warning at the bottom of the Connection Managers tab:
"Not all connection managers have been set. Set all connection managers."
When I click refresh the warning message changes to:
"The runtime connection manager with the ID "" cannot be found. Verify that the connection manager collection has a connection manager with that ID."
I am prevented from removing the IDbConnection or assigning my Connection Manager object to the DataReader Source.
Thanks, -- Mike
View 9 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
Dec 11, 2006
In my ssis package,
I have a DSN connection like this: "DSN=myDSNname". Which decide from i have to pull the data.
By using OLE DB Source Editor, I want to assign that ODBC Connection to it.
By data source Reader i can achive this but where i have to pass the hard-code SQL Query that i don't want.
i'm using the variable for dynamic SQL command.
Thanks.
Manoj
View 5 Replies
View Related
Apr 12, 2007
Hi, I had an old web application created during asp.net 1.1 and it have a connection problem with the sql server 2005 when it is mirgrated to a new webserver with dotnet framework 2.0 platform. I have enabled the remote access(TCP/IP and named pipes) in sql server 2005, did all the neccessary things, check whether the TCP/IP is enabled, named pipe is enabled... I created another web application using VS 2005. The database connection works perfectly well.This are the connectionString from the old web application.<appSettings> <add key="ConnectionString" value="Server=127.0.0.1;Database=somedb;User id=user; Password=somepassword; Trusted_Connection=False; POOLING=FALSE"/></appSettings> Thankyou in advance!
View 4 Replies
View Related
Nov 1, 2007
Hello everybody I would like to know more about the number of possible connection to a sql server is it by pool ? or there is max for all the database ? all the server ? how I can get the number of connection open ? Thx in advance
View 2 Replies
View Related
Sep 22, 2007
Hi,
I hope this is right place to ask for help.If it is not, Please show me where and if it is please Help me
I using Window Vista bussiness, Microsoft visual 2005 professional edition, Microsoft SQL server express edtion
I have created table on Database.
My sql server name is KYAW-HQ
**but in the bar it shown like as KYAW-HQSQLEXPRESS
My data base name is kyaw
I have already Enabled Name Pipes and TCP/IP setting in sql studio management tool express
I using window authentication to log in to sql server management studio express.
My coding for c# is as follow:
private void button4_Click(object sender, EventArgs e)
{
//connect sql connection
SqlConnection con = new SqlConnection("Server=KYAW-HQ;Database=kyaw;UID=;PWD=;");
//open sql conection
con.Open();
SqlDataAdapter ad = new SqlDataAdapter("Select * From kyaw", con);
DataSet ds = new DataSet();
ad.Fill(ds, "kyaw");
dataGridView1.DataSource = ds.Tables[0].DefaultView;
con.Close();
}
After I click the button the error has shown like below
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server).
Please tell me what i have to do.
If someone read me, please tell me and advice me how to fix above problem.
Thanks
View 9 Replies
View Related
Jan 22, 2008
Hello!
This code sample stops when I try to open a connection (see below). Ther error message reads "pipe provider , error 40". The help suggests that the credentials are wrong. I donīt think so, could there be another problem? Tough question but, I would appreciate any help.
System.Data.SqlClient.SqlConnection sqlConnection1 =
new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["SwimAdminConnectionString"].ConnectionString);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string sql1 = Session["txtFirstName"].ToString();
string sql2 = Session["txtLastName"].ToString();
cmd.CommandText = "INSERT dbo.Member (mbrFirstName, mbrLastName) VALUES (sql1, sql2)";
cmd.Connection = sqlConnection1;
sqlConnection1.Open(); //EXCEPTION HERE!!!
cmd.ExecuteNonQuery();
sqlConnection1.Close();
View 26 Replies
View Related
Nov 25, 2007
Hi, Has somebody had a problem like this before? Exception Details: System.Data.SqlClient.SqlException:
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not
open a connection to SQL Server) That's part of the code: SqlConnection con; string sql; SqlCommand cmd; con=new SqlConnection("Data Source=elf/SQLEXPRESS;Initial Catalog=logos;Integrated Security=SSPI;"); sql="SELECT max(id_kandydat) from Kandydat"; con.Open(); cmd=new SqlCommand(sql,con); int id = Convert.ToInt32(cmd.ExecuteScalar()); con.Close(); SqlDataSource7.InsertCommand="INSERT INTO Kandydat_na_Kierunek(id_kandydat, id_kierunku, id_stan) VALUES ("+"'"+id+"'"+","+"'"+DropDownList1.SelectedValue+"'"+","+'1'+");"; I don't really get it. I'm nearly 100% sure that before some changes in my project this had worked perfectly. Does anybody have any idea how to make it work again? I would be very grateful for any help. And, yesy, I know it is quite common error, but proposed solution found didn't help.Regards,N.
View 5 Replies
View Related
Mar 20, 2008
Hello, I have always closed my db connections asap after opening them and have always thought this was the best practice. But now I am wondering if I can just leave the connection open.
I created a windows service which runs on a timer and executes every 5 seconds. The problem is that the code takes longer than that to process and the code may be executing three times at once. Im worried that since I use the same connection each time the code executes that a connection opened by the first run through may be closed or reopened by the second or third running of the code resulting in an error. The connection is opened and closed about 10 times each time the timer event fires. Should I open the connection once and leave it open when the service is started and close it when it is stopped or on any error?
Thanks
View 2 Replies
View Related
Jun 3, 2008
i m getting this exception
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
i want to know that this exception is from my end or on Server End. because 5 to 6 month my application works fine but in these days i m getting this exception 5 to 20 times a day
your help will really appricate...
View 5 Replies
View Related
Jun 14, 2006
hi to all,
I create a web application in asp.net with sql server 2000, I use always close connection in .net code as my consern but when run my application then visit page to page give a error "General network error " message . then i check lots of connection is sleeping state in process info in sql manegment(current activity) Now i open the particular processId in Process info show open connection with any storeprocedure name and show .net sqlclient data provoider referance as open connection which is fetch the data from sql then i check in code find really the connection which is got data by that store procedure is not close properly and i close it. This is ok but one problem is that some procesId show blank info with .net sql client data provoider. so how can i find where these conneciton is open in my applicaton because this processId doesn't give any clue for it , it show nothing only blank but status is sleeping. If any help have this context then quick feedback.
Thanks
View 2 Replies
View Related
Feb 20, 2006
Hi,
One issue has got me stuck while getting to build an application(ASP.NET/SQLSERVER). Whenever I try to connect to SQL Server 2005 (installed on local host) using Visual Web Developer 2005 Databse Explorer, I get the following error message:
"An error has occured while establishing a connection to the server.
when connection to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - could not open a connection to SQL Server)"
NB:
- on SQL Servers' Surface Area Configuration, Remote connections are set to "Local and Remote connnections" and "Using both TCP/IP and named pipes".
ANyone with a solution? pse help
View 3 Replies
View Related
May 8, 2007
I am developing a web service that uses a sql data connection.
The sql database is on a server, and the development version of my web service is on my pc.
I can connect/access the data accross my network when I run the service on my PC, but as soon as I put the web service onto the server(with the sql) it displays, the service wont function - error: 40 - could not open a connection....
I presume that sql is configured correctly as I am recieving data back when I request it accross the network. I dont understand why it wont work when I have the service on the server with the sql?
Any help will be appreciated.
View 4 Replies
View Related
Jan 25, 2008
tried to install SQL Server 2005 in a fresh installed Windows 2003 SP2. And this server is a workgroup member, not a member of a domain.
The services (Database service, integration service, reporting services and so on) work fines, but I could not connect to server
I checked the log, I found the following error,error :40-Could not connect to SQL Server(Microsoft SQL Server,Error-1231).
I never had this problem and this time, I really got the trouble and do not have solution.
Who have ever encounter this problem? Or someone can help me?
Thanks.
View 1 Replies
View Related
Sep 21, 2006
I'm trying to update an SQL DB from data in Quickbooks. I am trying to find if I can open a dsn connection in a stored procedure, called from an ASP page, to the quickbooks db using odbc. The driver I have for odbc does not allow for linked servers. I'm very new to stored procedures and such so please bear with me. Thxs.
View 3 Replies
View Related
Apr 17, 2008
hello ,i write this code
string sql="SELECT * FROM Customers";
SqlConnection connection = CollectionManager.getHotelConnection();
SqlCommand command = new SqlCommand(sql,connection);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
result = command.ExecuteScalar();
connection.Open();
...
and when the debug goes to
connection.Open ()
it throws me this
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
but i have all enabled ,browser running normally ,name pipes tcp ip enable all ,but it makes me this thsi all the time what mayb e huppens there?
View 4 Replies
View Related
Feb 9, 2006
Try
Dim l_connString As String
l_connString = "Server=kangalert;database=Order;user id=sa;password=123;"
m_cn = New SqlConnection(l_connString)
m_cn.Open()
Catch ex As SqlException
Dim l_sqlerr As SqlError
For Each l_sqlerr In ex.Errors
MsgBox(l_sqlerr.Message)
Next
End Try
i got this in my mobile application, which try to open a connection directly to the sql server, but i got the following message
General Network error, check your network documentation.
View 7 Replies
View Related
Feb 12, 2007
Hi,
I've created an "ASP.NET Web Application" project in Visual Studio and I want to simply open a connection to a database in SQL Server. All I have done is adding these 3 lines below without modifying any other generated codes by Visual Studio.(I've done this within code behind in Page_Load() not by <script> tag within HTML code)
public class WebForm1 : System.Web.UI.Page { private void Page_Load(object sender, System.EventArgs e) { // Put user code to initialize the page here SqlConnection c = new SqlConnection(); c.ConnectionString = "workstation id=BABAK;integrated security=SSPI;initial catalog=db1;persist security info=False"; c.Open(); } ... }
But I get this error :
Server Error in '/projects/fortest/tDB2' Application.--------------------------------------------------------------------------------
Login failed for user 'BABAKASPNET'.
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: Login failed for user 'BABAKASPNET'.
Source Error:
Line 24: SqlConnection c = new SqlConnection();Line 25: c.ConnectionString = "workstation id=BABAK;integrated security=SSPI;initial catalog=db1;persist security info=False";Line 26: c.Open();Line 27: }Line 28: Source File: c:inetpubwwwrootprojectsfortest db2webform1.aspx.cs Line: 26
What's wrong ?
View 2 Replies
View Related
