Here's a code test I did place in the Form_Load of my C#.NET app.
SqlConnection cnn = new SqlConnection();
cnn.ConnectionString = "bla bla...";
cnn.Open();
cnn.Close();
cnn.Dispose();
In SQL SERVER PROFILER, the Audit Login takes place as soon as I hit the .Open()
but when I hit the .Close() OR the .Dispose() there's no Audit Logout. The Audit Logout is issued only
when I quit the application.
I am inheriting all my webpages from a BasePage which is storing my sqlconnection to the database. I open the connection in the "protected override void OnInit()" method (is this right?) but I don't know where I should write the close the connection. Can anyone give me any suggestions? Regards EO
Hello all, Actually i want to known, is their any method or property in SqlConnection class which will return some value, so that through which we become confirm that connection has established.
Hi, We are using ReportViewer control to show SSRS 2005 Reports in a Windows Form. Now, in some Reports the close button is not working. Even user clicks on the Close button in the right top corner of the Report Viewer the Report cannot be closed. Please help me to understand and resolve this problem.
Hi, I've created a simple hit counter in my Session_Start event... Dim myDataSource As New SqlDataSourcemyDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToStringmyDataSource.UpdateCommand = "UPDATE Stats SET Hits = Hits + 1"myDataSource.Update() It works fine but do I need to close the connection after I've finished with it or is this ok? Thanks for your help.
I am trying to select rows from a SQL2000 database table and then write a random number back into each row. I have already looked into do it all in a SP but there are well documented limitations for the SQL RAND function when called in the same batch, so I need to somehow do in .Net what I already have working in classic ASP. I can't get the UPDATE (part two section) to compile. I don't know how to call the stored procedure inside the 'foreach' loop or extract the SP parameters. I have it working in classic asp but am having a lot of trouble converting to .Net 2.0. Is the below even close to working? // stored procedure to write externally generated random number value into database PROCEDURE RandomizeLinks@L_ID int,@L_Rank intASUPDATE Links SET L_Rank = @L_RankWHERE (L_ID = @L_ID) // Part One select links that need random number inserted. public DataTable GetRandLinks() { SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1A"].ConnectionString); SqlCommand cmd = new SqlCommand("RandomizerSelect001", con); cmd.CommandType = CommandType.StoredProcedure; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet(); try { da.Fill(ds, "Random001"); return ds.Tables["Random001"]; } catch { throw new ApplicationException("Data error"); } } // Part Two I need two write a random number back into each row protected void Button1_Click(object sender, EventArgs e) { GetRandLinks(); int LinkID; // this generates unassigned local variable "LinkID' error int LRank; // this generates unassigned local variable "LRank' error SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1A"].ConnectionString); SqlCommand cmd2 = new SqlCommand("RandomizeLinks", con2); cmd2.CommandType = CommandType.StoredProcedure; cmd2.Parameters.AddWithValue("@L_ID", LinkID); cmd2.Parameters.AddWithValue("@L_Rank", LRank); SqlDataAdapter da2 = new SqlDataAdapter(); int RowIncrement; RowIncrement = 0; DataTable dt = GetRandLinks(); foreach (DataRow row in dt.Rows) { System.Random myRandom = new System.Random(); int LinkRank = myRandom.Next(25, 250); LRank = LinkRank; da2.UpdateCommand = cmd2; RowIncrement++; } }
If this is used eg. Return selectCommand.ExecuteReader(CommandBehaviour.Default) Here do i need to close the connection in finally explicitly???? Are there any links which can answer this kind of question ??? Thanks for any help in advance.....
Hello, I'm trying to run the following snippet as Console app, and I get an error: "a reader is already open for this connection, and should be closed first" I tried to manually say: SqlDataReader.Close() in the begining of the code, but still get the error, Any suggecstions how to manually close the reader? thank you ---------- here's the code -----------using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; namespace ADO.NET { /// <summary> /// Summary description for Class1. /// </summary> class Class1 { /// <summary> /// The main entry point for the application. /// </summary> [STAThread] static void Main(string[] args) { SqlConnection cn = new SqlConnection ("SERVER=MyServer; INTEGRATED SECURITY=TRUE;" + "DATABASE=AdventureWorks"); SqlCommand cmd1 = new SqlCommand ("Select * from HumanResources.Department", cn); cmd1.CommandType = CommandType.Text; try { cn.Open(); SqlDataReader rdr = cmd1.ExecuteReader(); while (rdr.Read()) { if (rdr["Name"].ToString() == "Production") { SqlCommand cmd2 = new SqlCommand("SELECT * FROM " + "HumanResources.Employee WHERE DepartmentID = 7", cn); cmd2.CommandType = CommandType.Text; SqlDataReader rdr2 = cmd2.ExecuteReader(); while (rdr2.Read()) { } rdr2.Close(); } } rdr.Close(); } catch (Exception ex) { Console.WriteLine (ex.Message); } finally { cn.Close(); } } } }
How do i close a current connection to a database using t-sql?I fail some time to drop the database getting messages that it'scurrently in use.Using the wizard to delete the database, i could check the option toclose all connections to the db, but how do i do it using t-sql?best regards
Then I Open the Connection. Now, in some ways I need to drop a Table. Before I do so, I disconnect the DB but I'm using only the line
cn.Close();
I saw om the Watch attributes that its not a real disconnect to the DB. When I try to Drop the Table I get this message: The system timed out waiting for a lock. [ Session id = 25,Thread id = -2096711882,Process id = -2090594918,Table name = Movies,Conflict type = x lock (s blocks),Resource = DDL ]
And the Table doesn't drop.
Is it related to the constructor line I made? What is the best solution to Drop a table..and then to get connected again with the DB.
Hi everybody,How is it possible to close any open connections from the connection pool explicitly like on the log off page? So when the users log off from the application I want to close all connections that were opened during the use of application.asp.net forums is the best....thanks,Murthy here
How do you close the connection at the SQL Server? I have a backup I want to resotre to SQLExpress using SQL Management Studio Express. When I try, the SQL tells me he can't because the database is in use. I don't realy care about this I still want to restore the database. So I figured it's because there are some active connection to the database. My gues is I have to close those, but how? I also know I can restart SQLExpress and the connection will drop, but this server has more then one database, so I cant always do that. I'm looking for some T-SQL like DROP ALL CONNECTION. Thanks for the help.
Hi,since yesterday I have this message everytime I try to open Sql Server"Enterprise manager"; I reinstalled the outfit completely without successany solutionthanksFernand
Problem: Connections to SOAP endpoints never close even if app initiating call was shutdown several days earlier. Create Endpoint statement included at bottom
Questions: 1 Why? 2. Is there a configuration option I need to set?
[1] Client side: Multiple clients from XMLSpy running on XP Pro to WebSphere Process Server running on AIX
[2] Server side:
What is the MS SQL version? SQL Server 2005 SP2
What is the SKU of MS SQL? Enterprise
What is the SQL Server Protocol enabled? Shared Memory | and TCPIP
Does the server start successfully? YES If SQL Server is a named instance, is the SQL browser enabled? YES
What is the account that the SQL Server is running under? Domain Account Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? NO
Do you make firewall exception for SQL Browser UDP port 1434? In SQL2000, you still need to make firewall exception for UDP port 1434 in order to support named instance. NO [2a] Tool Used to Connect: Multiple clients from XMLSpy running on XP Pro to WebSphere Process Server running on AIX
[3] Platform:
What is the OS version?Windows 2003 SP2 Do you have third party antivirus, anti-spareware software installed? McAfee. Exceptions have been set in accordance with MS SQL Server documentation
ENDPOINT:
CREATE ENDPOINT [TestEndpoint]
AUTHORIZATION [AuthID]
STATE=STARTED
AS HTTP (PATH=N'/SqlSSL/testEndpoint', PORTS = (SSL), AUTHENTICATION = (BASIC),
Given that the conversation states are as follows: (Thanks Rushi!)
Event Initiator Endpoint state Target Endpoint state
BEGIN DIALOG SO -- from Initiator to Target
SEND message(s) CO -- from Initiator to Target
Target receives a fragment CO SI of the first message sent or receives out of order message
Target received entire CO CO first message
END conversation at CO DO target
Initiator receives EndDialog DI DO message from target
Target receives ACK for the DI CD EndDialog message sent
END conversation at CD CD Initiator
When does the 30 minute timer start for clearing the conversation from the sys.conversation_handles table? Is it the same for both sides (initiator and Target) ie, the end conversation at the Initiator. I guess it must be just in case a resend is necessary.
I write a .NET Windows Form that connect to SQLExpress datafile. After updating data, I want to zip the .mdf file and send email. However, I got an exeption that the .mdf file is used by other thread so I cant zip. Even I try to close all connection, I still cant zip.
Is there any way to detach/unlock .mdf file connecting by SQLExpress?
Hi I have double checked my code and cannot pin down why I am getting the error "There is already an open DataReader associated with this Command which must be closed first. " on the lne: Line 95: DR_IndJobPostings = oComm_IndPostings.ExecuteReader(); I have closed the DR_IndJobPostings object after every use of it as see n on line number 175
----------- Code--------------1 /// <summary> 2 /// Will generate and email job alerts based on the frequency 3 /// </summary> 4 /// <param name="frequency">WEEKLY or MONTHLY</param> 5 /// <param name="oServerIN">Instance of Server from ASPX page</param> 6 public void hk_DoAlertByFreq(string frequency, HttpServerUtility oServerIN) 7 { 8 SqlConnection oConn = new SqlConnection(ConfigurationSettings.AppSettings["CString"]); 9 oConn.Open(); 10 11 SqlCommand oComm; 12 13 emailSystems oEmail = new emailSystems(); 14 HttpServerUtility oServer = oServerIN; 15 16 bool validCall = false; 17 bool industryHasPostings = false; 18 string sEmail = ""; 19 string sEmailTemplate = ""; 20 string sVacListForEmail = ""; 21 22 int IJPost_VacId = 0; 23 int IJPost_EmpId = 0; 24 string IJPost_Req = ""; 25 string IJPost_KeyRes = ""; 26 string IJPost_VacTitle = ""; 27 string IJPost_VacJobTitle = ""; 28 string IJPost_VacUrl = ""; 29 30 int loopCounter1 = 0; 31 32 string CandEmailAddress = ""; 33 34 oComm = new SqlCommand(); 35 oComm.Connection = oConn; 36 oComm.CommandType = CommandType.Text; 37 38 SqlCommand oComm_IndPostings = new SqlCommand(); 39 oComm_IndPostings.Connection = oConn; 40 41 SqlDataReader DR_Industries; 42 SqlDataReader DR_IndJobPostings; 43 SqlDataReader DR_AlertList; 44 45 if (frequency == "WEEKLY" || frequency == "MONTHLY") 46 { 47 validCall = true; 48 } 49 50 if (validCall) 51 { 52 if (frequency == "WEEKLY") 53 { 54 sEmailTemplate = oEmail.readTextFile("/email_templates/weeklyJobAlert.txt"); 55 } 56 57 if (frequency == "MONTHLY") 58 { 59 sEmailTemplate = oEmail.readTextFile("/email_templates/monthlyJobAlert.txt"); 60 } 61 62 sSql = "" + 63 "SELECT [id],[industry] FROM S_Utils_Industries " + 64 "WHERE [active] = 1"; 65 oComm.CommandText = sSql; 66 DR_Industries = oComm.ExecuteReader(); 67 68 if (DR_Industries.HasRows) 69 { 70 // 71 // Loop through each active industry 72 // 73 while (DR_Industries.Read()) 74 { 75 industryHasPostings = false; 76 iCurrentIndustryId = (int)DR_Industries.GetSqlInt32(0); 77 sCurrentIndustryText = DR_Industries.GetSqlString(1).ToString(); 78 79 // Get all active vacancy postings for this 80 // industry 81 sSql = "SELECT [id]," + 82 "[emp_id], " + 83 "[vac_Requirements]," + 84 "[vac_KeyResp]," + 85 "[vac_VacTitle]," + 86 "[vac_VacJobTitle]," + 87 "FROM [S_Vacancies] " + 88 "WHERE [vac_VacIndustry_Id] = " + iCurrentIndustryId.ToString() + " AND " + 89 "[status] = 1 AND " + 90 "[vac_ListingStart] >= '" + gf.SqlDateTimeFormat(DateTime.Today,1) + "' AND " + 91 "[vac_ListingEnd] < '" + gf.SqlDateTimeFormat(DateTime.Today, 1) + "'"; 92 93 oComm_IndPostings.CommandText = sSql; 94 95 DR_IndJobPostings = oComm_IndPostings.ExecuteReader(); 96 97 // 98 // If there are job vacancy postings for the industries 99 // 100 if (DR_IndJobPostings.HasRows) 101 { 102 industryHasPostings = true; 103 sEmail = sEmailTemplate; 104 105 // 106 // Loop through the job postings for this industry 107 // 108 while (DR_IndJobPostings.Read()) 109 { 110 IJPost_VacId = (int)DR_IndJobPostings.GetSqlInt32(0); 111 IJPost_EmpId = (int)DR_IndJobPostings.GetSqlInt32(1); 112 IJPost_Req = DR_IndJobPostings.GetSqlString(2).ToString(); 113 IJPost_KeyRes = DR_IndJobPostings.GetSqlString(3).ToString(); 114 IJPost_VacTitle = DR_IndJobPostings.GetSqlString(4).ToString(); 115 IJPost_VacJobTitle = DR_IndJobPostings.GetSqlString(5).ToString(); 116 IJPost_VacUrl = "http://www.mann-power.net/vJDetails_FromFront.aspx?vid=" + IJPost_VacId.ToString() + "&from=myjobs"; 117 118 sVacListForEmail += IJPost_VacTitle + @" 119 120 Job title: " + IJPost_VacJobTitle + @" 121 122 Key Responsibilities 123 " + IJPost_KeyRes + @" 124 125 Requirements 126 " + IJPost_Req + @" 127 128 " + IJPost_VacUrl + @" 129 130 ============================================================ 131 132 "; 133 } 134 135 136 sEmail = sEmail.Replace("{VACANCYLIST}", sVacListForEmail); 137 138 // If there are job postings for this industry 139 // get all the people who signed up for a job alert 140 if (industryHasPostings) 141 { 142 sSql = "SELECT [S_JobAlerts].[IndustryId]," + 143 "[S_JobAlerts].[candidateEmail] " + 144 "[S_Cv_Status].[Cv_Online], " + 145 "[S_Cv_Status].[usingShortResume], " + 146 "[S_Cv_Status].[iHasHadIntro] " + 147 "FROM [S_JobAlerts] " + 148 "INNER JOIN [S_Cv_Status] ON " + 149 "[S_Cv_Status].[user_id] = [S_JobAlerts].[candidateUserId] " + 150 "WHERE ([S_JobAlerts].[frequency] = '" + frequency + "') AND " + 151 "[S_JobAlerts].[IndustryId] = " + iCurrentIndustryId.ToString() + ""; 152 153 oComm.CommandText = sSql; 154 155 DR_AlertList = oComm.ExecuteReader(); 156 157 // If there are candidates who signed up 158 // for a job alert 159 if (DR_AlertList.HasRows) 160 { 161 // 162 // Loop through each job alert for this industry 163 // 164 while (DR_AlertList.Read()) 165 { 166 CandEmailAddress = DR_AlertList.GetSqlString(1).ToString(); 167 oEmail.sendSingleMail("john.cogan@staffmann.co.za", "Mann-power Job alert", sEmail); 168 } 169 170 } 171 DR_AlertList.Close(); 172 173 } 174 } 175 DR_IndJobPostings.Close(); 176 177 } 178 179 } 180 DR_Industries.Close(); 181 182 183 oConn.Close(); 184 } // END: if (validCall) 185 186 }
should u always close everys sqldatasource connection string and the dataset associated with it, if so how do u do that (ie the proper way) and is it possible to to do it in the session area of global.aspx, NEt 2.0 or should it be done when exiting the page containing the data stuff, also same question for NET 1.0 datasets and adapters and strings,
Lets say I am selling parts for cars, trucks, suvs, vans, etc. I want a user to search for a part by selecting a vehicle type, then a vehicle make, and then a vehicle model. So Vehicle Makes have to be related to Vehicle Types (I.E: Land Rover doesn’t make a car) and vehicle models need to be related to vehicle make (I.E Toyato doesn’t make a mustang Ford does). So I am okay with those relationships individually but what is confusing me is how to ensure that a Mustang doesn’t show up as a Ford Car.
Lets say I have: vehicle_typevehicle_type_cdvehicle_type_desc
vehicle_makevehicle_make_cdvehicle_make_descvehicle_modelvehicle_model_cdvehicle_model_descThen I might have an intersect between vehicle_type and vehicle_make because certain makes don’t have certain types of vehicles. BMW doesn’t have a truck, Lexus doesn’t have a truck, so when a user selects truck as a make lexus nad BMW shouldn’t show up in the drop down.Vehicle_type_makeVehicle_type_make_keyVehicle_type_cdVehicle_make_cd I might have the same relationship between make and model:
keyboard shortcuts for management studio http://msdn2.microsoft.com/en-us/library/ms174205.aspx
Anyone know of a way to setup a keyboard shortcut to close the current window your working in ? Normally with other editors (such as visual studio) I use ctl-w to close the current window. Cant do that with sql studio.
In SQL Server 2005 (Developer Edition) I can't figure out how to close the extra sub-tabs (by default within and below the current query tab) that show the query results and messages.
I know in SQL Server 2000 there was an icon button in the toolbar that let you do this. Surely there is some similar way to close that in 2005?
Does anyone know how?
===================================== f u cn rd ths, u cn gt a gd jb n prgrmng
Hi allI have a large data set of points situated in 3d space. I have a simpleprimary key and an x, y and z value.What I would like is an efficient method for finding the group ofpoints within a threshold.So far I have tested the following however it is very slow.---------------select *from locations a full outer join locations bon a.ID < b.ID and a.X-b.X<2 and a.Y-b.Y<2 and a.Z-b.Z<2where a.ID is not null and b.ID is not null---------------If anyone knows of a more efficient method to arrive at this results itwould be most appreciated.Thanks in advanceBevan
I am using VB6 with SQL server 2005. In order to implement password expiry properly I need to know how to find out when a user's password is due to expire so that I can output a message to prompt him to change his password. How can I interrogate this information?
Public Conn As New SqlConnection("Data Source=localhost;Initial Catalog=tblUsers;UID=XXXX;pwd=XXXX")Public Conn As New SqlConnection("Data Source=XX.XXX.XX.XXX;Initial Catalog=tblUsers;UID=XXXX;pwd=XXXX") This two commands are in my application. I switch between the two so I can test my application. When I put my application on the server, I use the localhost SQLCONNECTION. The other is commented out. However, the application continues to connect to the server with the address that is commented out. THIS IS VERY FRUSTRATING... HELP!!!!
Hi, Someone can explain to me what happend in this situation...i have this and want to call a procedure with connection parameters protected void Button1_Click(object sender, EventArgs e) {
SqlConnection edo = new SqlConnection(); edo.ConnectionString = "server=myserver; uid=admin; pwd=; " + "database=name1"; SqlCommand com = edo.CreateCommand(); com.CommandType = CommandType.Text; My_proc(edo, com); } protected void My_proc(SqlConnection edo , SqlCommand com) { try {edo.Open(); } catch{} finally{ edo.close();} }
Well what happend with the SqlCommand and SqlConnection when return to Button_Click() Are they alive? Can be used in Buton_Click like parameters for another procedure? or i have to create it again?Thx in advance..
please help me !!!?I have a problem with sqlconnection definition when I add the sqlconnection from toolbox :like this: Imports System.Data.SqlClientPartial Class _Default Inherits System.Web.UI.Page Private Sub InitializeComponent() Me.sqlConnection1 = New System.Data.SqlClient.SqlConnection Me.sqlConnection1.ConnectionString = "Data Source=server;Initial Catalog=masterstd;User ID=sa" Me.sqlConnection1.FireInfoMessageEventOnUserErrors = False End Sub Private WithEvents sqlConnection1 As System.Data.SqlClient.SqlConnection Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not IsPostBack Then Dim cmduniversity As SqlCommand Dim dtruniversity As SqlDataReader cmduniversity = New SqlCommand("select * from university", sqlConnection1) sqlConnection1.Open() dtruniversity = cmduniversity.ExecuteReader dtruniversity.Close() sqlConnection1.Close() End If End SubEnd Classin the sqlconnection.open the nullrefrence exception occures butwhen i define myself this exception dosn't occure.like this: Imports System.Data.SqlClientPartial Class _Default Inherits System.Web.UI.Page Private Sub InitializeComponent() End Sub Private WithEvents sqlConnection1 As System.Data.SqlClient.SqlConnection Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not IsPostBack Then sqlConnection1 = New SqlConnection sqlConnection1.ConnectionString = "Data Source=server;Initial Catalog=masterstd;User ID=sa" sqlConnection1.FireInfoMessageEventOnUserErrors = False Dim cmduniversity As SqlCommand Dim dtruniversity As SqlDataReader cmduniversity = New SqlCommand("select * from university", sqlConnection1) sqlConnection1.Open() dtruniversity = cmduniversity.ExecuteReader dtruniversity.Close() sqlConnection1.Close() End If End Subwhere is the problem?
Can someone help me out? I am trying to establish a SQL server connection in my C# code. My C# code crasheds though. Can someone tell me what Iam doing wrong? Here is the C# code:
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["TheDividerConnectionString"]); Here is the connection string as defined in the web.config file: <connectionStrings> <add name="TheDividerConnectionString" connectionString="Data Source=BVCOMPUTERSQLEXPRESS;Initial Catalog=TheDivider;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings>
