Hi, I've created a simple hit counter in my Session_Start event...
Dim myDataSource As New SqlDataSource
myDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString
myDataSource.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.
Hi all,
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.....
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
View 10 Replies View Related
Hello all,
I have a DB on my WM2005 device.
I'm using a program which connect to the DB using a CN.
On the constructor of the DB Class I use the line
static string connString = "Data Source='" + strDB + "'; LCID=1033; Password=" + strPass + "; Encrypt = FALSE;";
SqlCeConnection cn = new SqlCeConnection(connString);
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.
Thanks in advance..
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,
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?
MA.
I designed a DTS package with eleven different connections. When I try to see the proberties of a connection the enterprise manager immediatly closes without giving me a warning. This happens only with two connections (in my example M1 and M2). This mistake has no influence to the execution of the package. In my opinion it is a fault of the DTS package desinger but may be that I made something wrong.
Is there anybody who knows this mistake? Thanks for your help.
I have the next code in my proyect:
If CurrentProject.IsConnected Then
MsgBox "Base ya Conectada.Procedemos a cerrarla primero", vbOKOnly
If SQLConexion.State = adStateOpen Then SQLConexion.Close
SQLConexion.Close
End If
SQLConexion.Open SQLSentencia
AbreConexionSQLSERVER = 0
If I detect that the connection is opened while I am initializazing varriables, I want to close always the connection and then begins my aplicattion always opening the connection, but it always returns a error 91..
suggestions?
The following error message was displayed in the SQL server event log when the client application attempted to connect to sql server
"The Tabular Data Stream (TDS) version 0x72020002 of the client library used to open the connection is unsupported or unknown. The connection has been closed. [CLIENT: XX.XX.XX.XX]"
Can anyone tell me what this means?
Thanks
Please i have a question,
I want to use temporary tables to handle my forms child tables data.
My question is after i load the data from #temporary table to my dataset then i will close my database connection.
Does my temporary tables gets dropped off as soon as i do the connection.close.
or do i need to drop the #temporary table by code.
Which one is right.
I know #temporary tables are specific to the users(session)
Thank you very much in advance.
we have roughly 22 people connected to one database. But after a while, their applications begin to drag due to in and out communication with the server. When i check the active connections on the sql server, some times i see 157 active connections, please how to i set a timeout or connection interval close, so as reduce the heavy load being put on the server. Or how can i automatically close connections when they get higher than 50 connections.
This settings should be sql server 2008 related.
Hi,
My question is if I close the sqlreader i am using. will that close the connection it uses or the connection will still remain open?
Syed
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!
string ConnectionString = ConfigurationManager.ConnectionStrings["myConnString"].ConnectionString;
SqlConnection connection = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(@"INSERT INTO CreditAppFile (GI_RB_Div) VALUES(@GI_RB_Div) SELECT @@idientity as 'NewID'", connection);
cmd.Parameters.Add("@ID", SqlDbType.BigInt, 8).Value = NewID.Text;
.
.
Response.Redirect(DisplayPage.aspx?ID=@ID);
i want the ID of the most recently added record to be passed to another page to show all entered data from the prior page... am i close?
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++; } }
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(); } } } }
View 2 Replies View RelatedHi guys.
is there anyway I can find a cursor that is open
so that i can close it?
I have a procedure running daily (across servers). that stopped suddenly with this error.
A cursor with the name 'xyz' already exists.
I tried closing and deallocating on destination server. I am getting the error like "cursor doesnot exist"
I need to run this procedure. i dont want to recycle the destination server.
any ideas?
-MAK
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
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
View 4 Replies View RelatedHi,since yesterday I have this message everytime I try to open Sql Server"Enterprise manager"; I reinstalled the outfit completely without successany solutionthanksFernand
View 5 Replies View RelatedHaiIs it possiable to close the current connection screen in the queryanalyser , not to close the query analyser.With ThanksRaghu
View 3 Replies View RelatedProblem: 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),
SITE=N'Win2003.ent.corp.net', SSL_PORT = 443, COMPRESSION=DISABLED)
FOR SOAP (
WEBMETHOD 'PutTestResponse'( NAME=N'[UserDb].[schema].[usp_test]'
, SCHEMA=DEFAULT
, FORMAT=ALL_RESULTS),
BATCHES=DISABLED,
WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]',
SESSIONS=ENABLED, SESSION_TIMEOUT=10,
DATABASE=N'UserDb',
NAMESPACE=N'http://tempTestEndpoint.org',
SCHEMA=STANDARD, CHARACTER_SET=XML)
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.
Gary
is it possible to close automatically report preview after user's print
Noticed there is event print but it fires *before* printers dialog show
I need somethink like AfterPrint
Plz help
Hi,
This is VERY VERY simple.
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.
Is there someone to explain this behaviour ?
Thank you
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.
suggestions ?
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
View 8 Replies View RelatedI 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?
View 1 Replies View Related