|
Yes, the connection definitely remains open unless you specifically close it (either by calling the Close or Dispose method of the connection).
If you don't close the connection, it will remain open until it makes a timeout, which happens after a few minutes. If too many connections remain open, the database will not allow any more connections, so it will effectively stop working until the next connection makes a timeout.
An Access database has a limit of 64 connections, so it will die rather quickly. Database servers have a higher limit, but it's still there.
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
as what every one has told already... you need to close the connection
There is just one small information which i will like to add..
Unlike the "recordset" in VB6, the result from DB is stored in the datareader in VB.net.
If you are using a common function to interact with DB, in that function only open the Connection and close it before you leave the function.
Regards
Nishkarsh
|
|
|
|
|
nishkarsh_k wrote: Unlike the "recordset" in VB6, the result from DB is stored in the datareader in VB.net.
Actually, as the name suggests, the DataReader is used to read the data rather than storing the data.
A DataReader is similar to a forward-only read-only Recordset.
A DataTable is similar to a disconnected Recordset.
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
but i am using a central function to open and close the connection and it returns a data reader object , i can close the reader object whilst i am leaving the function but i cant close the connection coz i dont have that opened connection in this method .
any help ?
hello
|
|
|
|
|
In which case you need to refactor your code to allow it 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
i think you must close the connection,thus; if you didn't do that you may get deadlock
|
|
|
|
|
I am working in Visual Studio 2005, and am running a model that is meant to run through 500000 clients, and project things for them 25 years in the future.
In each years loop, different dll functions are called and values obtained.
The model runs though around 300000 clients succcessfully before crashing with the "OutOfMemoryException" message. Under numerous runs, it has been seen that no particular borrower, or no particlaur dll function is the source of the problem, but it always crashes on a dll funcion.
I have tried to clear memory by using GC.Collect(), but it has made no difference.
Is there anything I can be looking at to clear memory, help the programme run though?
Cheers
Juan Patrick
|
|
|
|
|
Does your memory usage steadily climb ? Look for objects you create which have a Dispose method, which you don't call. You should use using statements where-ever possible. It's possible a dll is what's leaking memory, from what you've said. in that case, GC.Collect will do nothing, but you should not have to call it anyhow, you should just make sure you manage your own memory,
Do you create any bitmaps or objects like that in your code ? They are a very likely culprit.
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Hi Christian,
I can't remember what was happening with the memory usage, so will run model and check out again.
I was just reading up about the Dispose method when you sent this through (I haven't struck it before). Still a bit unclear on it at the moment. Where you have said "Look for objects you create which have a Dispose method, which you don't call," should I be calling the dispose method on these?
I don't create any bitmaps, but do have a number of objects - some are used as arrays - probably five, of which, two are repopulated every new client i.e. ideally 500000 times during the model. Should these be cleared (dispose) after each client, or can they simply be re-written over, which I assume i was doing? The only other reference to objects is simply as part of some other funcationality e.g.
For a button to quit the programme
Private Sub btnQuit_Click(ByVal sender As System.Object,...
Anyway, I will set the model running, and check it out for the memory.
|
|
|
|
|
If you create an object that has a dispose method, you should call it. I realise now this is the VB.NET forum, I don;t think VB supports the using construct, which basically is a bit of syntactic sugar for local objects, that calls dispose for you.
I wrote a video jukebox and it plain died because I had not realsied in those early days of my .NET usage that the GC is not very efficient with large objects that get used for several minutes before being discarded.
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Christian Graus wrote: I don;t think VB supports the using construct
It does, since VB 8 (Framework 2.0 / Visual Studo 2005).
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
Hi Christian.
I am still unsure on the Dispose methods. Are these something I will need to add in to the code when creating the objects, or are they automatically in some objects?
Yes, the GC Garbage Collect has made no difference whatsoever.
I checked the memeory when I ran the mdoel again last night. It starting off with around 133M of Available Physical Memory and as the model went through to about an hour and a half before crashing the Available Memory steadily decreased down to 86, 32, 22. Likewise the Committ Charge increased during the same time.
J.P
|
|
|
|
|
Juan Patrick wrote: Are these something I will need to add in to the code when creating the objects, or are they automatically in some objects?
If the object has a Dispose method, you should call that when you no longer need the object, then set it to null ( just to be sure you don't ever use a disposed object )
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Rather simple windows service that has a timer set to 1 minute from NOW and when that time is reached, writes an event log entry. That was the idea, but nothing happens.
Anybody know what i am doing wrong?
<br />
Public Class Service1<br />
Private alarmTime As Date<br />
Protected Overrides Sub OnStart(ByVal args() As String)<br />
' Add code here to start your service. This method should set things<br />
' in motion so your service can do its work.<br />
'Dim a As TimeSpan = Date.Now.Subtract(Date.Today.AddDays(1))<br />
'Dim tomorrow As Date = New Date<br />
'tomorrow = Date.Now.AddDays(1)<br />
<br />
'Me.alarmTime = New Date(tomorrow.Year, tomorrow.Month, tomorrow.Day, 0, 1, 1, 1)<br />
Me.alarmTime = Date.Now.AddMinutes(1)<br />
Me.Timer1.Start()<br />
End Sub<br />
<br />
Protected Overrides Sub OnStop()<br />
' Add code here to perform any tear-down necessary to stop your service.<br />
End Sub<br />
Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs)<br />
If alarmTime < Date.Now Then<br />
Me.Timer1.Stop()<br />
Dim MyLog As New EventLog() ' create a new event log<br />
' Check if the the Event Log Exists<br />
If Not Diagnostics.EventLog.SourceExists("SQLBackup") Then<br />
Diagnostics.EventLog.CreateEventSource("SQLBackup", "SQLBackup") ' Create Log<br />
End If<br />
MyLog.Source = "SQLBackup"<br />
' Write to the Log<br />
Diagnostics.EventLog.WriteEntry("SQLBackup", "Event fired at " & CStr(TimeOfDay), EventLogEntryType.Information)<br />
Else<br />
Dim remainingTime As TimeSpan = Me.alarmTime.Subtract(Date.Now)<br />
<br />
'Me.Label1.Text = String.Format("{0}:{1:d2}:{2:d2}", _<br />
'remainingTime.Hours, _<br />
'remainingTime.Minutes, _<br />
'remainingTime.Seconds)<br />
End If<br />
End Sub<br />
End Class<br />
|
|
|
|
|
hello
I have just started learning sql 2005 and i have create small table student with 3 columns studentID,firstname and lastname it's works fine i can insert,delete without any problem but when i delete any entry it dosent refresh the page i mean it delete record from sql database but it shows deleted entry in text boxes but if i close application and restart it then it dosent show deleted entry any help this is the code for delete
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click<br />
Dim SQL As String = Nothing<br />
SQL = "DELETE FROM Student WHERE StudentID = " & TextBox3.Text<br />
If MessageBox.Show("Are you sure you want to delete", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) = MsgBoxResult.Yes Then<br />
conStudent = New Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Projects\SQL DataApplication\SQL DataApplication\Student.mdf;Integrated Security=True;User Instance=True")<br />
conStudent.Open()<br />
Try<br />
commStudent = New SqlClient.SqlCommand(SQL, conStudent)<br />
commStudent.ExecuteNonQuery()<br />
<br />
MsgBox("Item deleted", MsgBoxStyle.Information)<br />
Catch ex As Exception<br />
MsgBox(ex.Message)<br />
End Try<br />
conStudent.Close()<br />
conStudent.Dispose()<br />
Else<br />
Exit Sub<br />
End If<br />
End Sub
any help  
waiting for your kind rep.
have a nice time
thanks
modified on Tuesday, April 29, 2008 4:00 PM
|
|
|
|
|
Just deleting the data form the database doe snot cause your display page to refresh. Depending on how you are displaying the data you need to handle that yourself after the delete is done.
Your query just removes the data form the DB. You have to either requery to display the data without that record now or at least refresh your page and clear out the data that is displayed.
|
|
|
|
|
Hello sir
first of all thanks for your rep.
sir i am new in sql and this is my first attempt to do some thing with sql server so can you please tell me more how get on with it i dont want code and copy past code does not solve the problem i want to understant it
and still i am trying
waiting for your kind rep.
have a nice day
thanks again
|
|
|
|
|
My suggesting is that once you do the query to delete the record you need to manually refresh the interface to show this.
It depends on bit on how you are filling your interface also. My guess is that if you are doing data binding then you may just be able to refresh the binding-manager to do this. If you are manually reading a record and placing data form the fields into the text properties of your UI then you have to at very least go back through the UI and clear out those text properties to show that the record has been deleted.
If you have a client side record-set that you have used to populate your UI from that is not really going to reflect the changes made int eh DB though until you have redone that read query so your record-set on the client may need to be flushed and refilled to show the change also.
There are so many different ways to do all of this, I can;t really cover them ALL here. I might suggest that you concentrate on searching how to do this keeping in mind the method you are using to read the data and display it in the first place.
|
|
|
|
|
thanks for your rep.
i have tried few things but still i am no way near
this is what i have done
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click<br />
Dim SQL As String = Nothing<br />
SQL = "DELETE FROM Student WHERE StudentID = " & TextBox3.Text<br />
If MessageBox.Show("Are you sure you want to delete", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) = MsgBoxResult.Yes Then<br />
conStudent = New Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\bablu\My Documents\Visual Studio 2005\Projects\SQL DataApplication\SQL DataApplication\Student.mdf;Integrated Security=True;User Instance=True")<br />
<br />
conStudent.Open()<br />
Try<br />
commStudent = New SqlClient.SqlCommand(SQL, conStudent)<br />
commStudent.ExecuteNonQuery()<br />
dsStudent.Clear() :confused:<br />
<br />
daStudent.Fill(dsStudent) :confused:<br />
<br />
MsgBox("Itemdeleted successfully", MsgBoxStyle.Information)<br />
Catch ex As Exception<br />
MsgBox(ex.Message)<br />
End Try<br />
conStudent.Close()<br />
Else<br />
Exit Sub<br />
End If<br />
End Sub
and i have also read tool tip when i select from IDE but now i dont know what to do
waiting for your kind help
have a good day sir
thanks
|
|
|
|
|
I think your problem here is that the dsStudent that you are using still has the data form when that record existed. You need to repopulate that data-set like you did originally once the database is changed. You are not removing data form the data-set, you are removing it by running a query against the database itself. Once you aer done with the delete clear your data-set and repopulate it from the database.
|
|
|
|
|
thank you very much for you help and finally job done 
this is the final code and it's working fine
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click<br />
Dim SQL As String = Nothing<br />
conStudent = New Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\SQL DataApplication\SQL DataApplication\Student.mdf;Integrated Security=True;User Instance=True")<br />
SQL = "DELETE FROM Student WHERE StudentID = " & TextBox3.Text<br />
If MessageBox.Show("Are you sure you want to delete", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) = MsgBoxResult.Yes Then<br />
conStudent.Open()<br />
Try<br />
commStudent = New SqlClient.SqlCommand(SQL, conStudent)<br />
commStudent.ExecuteNonQuery()<br />
MsgBox("Itemdeleted successfully", MsgBoxStyle.Information)<br />
Catch ex As Exception<br />
MsgBox(ex.Message)<br />
End Try<br />
conStudent.Close()<br />
Else<br />
Exit Sub<br />
End If<br />
dsStudent.Clear()<br />
Call LoadData()<br />
<br />
End Sub<br />
<br />
<br />
<br />
Private Sub LoadData()<br />
conStudent = New Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\SQL DataApplication\SQL DataApplication\Student.mdf;Integrated Security=True;User Instance=True")<br />
<br />
If conStudent.State = ConnectionState.Open Then<br />
conStudent.Close()<br />
End If<br />
<br />
Try<br />
conStudent.Open()<br />
daStudent.Fill(dsStudent, "Student")<br />
Label1.Text = Me.StudentBindingSource.Position<br />
Catch ex As Exception<br />
MsgBox(ex.Message)<br />
End Try<br />
conStudent.Close()<br />
conStudent.Dispose()<br />
<br />
End Sub
i am going to make few changes like connection here i am using connecting string more then one time so i can make sub for it and call it when ever it's needed
but the method i am using is this the one of the right method ? I just want to know your views as an expert and still
thanks again sir for your help
have a nice week end
take care ![Rose | [Rose]](https://codeproject.freetls.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
Without seeing the entire solution its hard to tell if this is the BEST way But I say that if it gets your job done then it is a step in the right direction.
My rules are make it work then make it pretty. You now have the 'works' part, now it is up to you to see if the way it works fits well with your overall design and if not, make it pretty I would assume that you are using the LoadData() sub already to load your data in the first place. If not then you might want to consider some refactoring in that area there.
Good luck, glad I could help a bit.
|
|
|
|
|
I'm trying to have my program display the PrintDialog and then print out some text. I assumed it would work similar to the open and save dialogs but when I try to double click on the printdialog it refuses to produce an event for when the person clicks the print button.
Can someone explain what is going wrong and how I can print a string of text. thanks
Mike
|
|
|
|
|
You need a printdialog and a printdocument. When printdialog's dialogresult=okay use printdocument.print to call the printdocument.printpage-event. There you have to use graphics.drawstring-method to print your string.
"I love deadlines. I like the whooshing sound they make as they fly by." (DNA)
|
|
|
|
|
Thank you for your help. But I have a follow up question, when I double click on the printdocument I dragged and dropped into the program it creates a subroutine. However, when I run the program and click the print button of the printdialog it doesn't trigger the event. What is the event that the print button triggers?
thanks again,
Mike
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
