|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
check database connection/openconnection error to user in message box The code is like: Dim objConnection As New SqlConnection _ ("Server=server1; database=db1;user id=user1;password=whatever") Dim objDataAdapter As New SqlDataAdapter() Dim objDataSet objDataAdapter.SelectCommand = New SqlCommand() objDataAdapter.SelectCommand.Connection = objConnection cmdtext = "Select parameter from table1" objDataAdapter.SelectCommand.CommandText = cmdtext objDataAdapter.SelectCommand.CommandType = CommandType.Text objConnection.Open() objDataAdapter.Fill(objDataSet, "table1") objConnection.Close() So if connection problem, it will post error masseage like below MessageBox.Show("Ths is Database connenction issue", "DataBase Connection problem") My question is which code line is used to check if db connection/open or not, it is objConnection.Open()? if it is, how to check? Thanks martin1 wrote:
Show quoteHide quote > All, I want to check wether db connection/open or not, if not it will post db Seems like all you need to do is put the .Fill method call inside of a> connection error to user in message box > > The code is like: > > Dim objConnection As New SqlConnection _ > ("Server=server1; database=db1;user > id=user1;password=whatever") > Dim objDataAdapter As New SqlDataAdapter() > Dim objDataSet > > objDataAdapter.SelectCommand = New SqlCommand() > objDataAdapter.SelectCommand.Connection = objConnection > cmdtext = "Select parameter from table1" > > objDataAdapter.SelectCommand.CommandText = cmdtext > objDataAdapter.SelectCommand.CommandType = CommandType.Text > > objConnection.Open() > objDataAdapter.Fill(objDataSet, "table1") > objConnection.Close() > > So if connection problem, it will post error masseage like below > > MessageBox.Show("Ths is Database connenction issue", "DataBase Connection > problem") > > My question is which code line is used to check if db connection/open or > not, it is objConnection.Open()? if it is, how to check? Thanks Try/Catch block. Connections have the "state" property, which you can check.
-tom martin1 ha scritto: Show quoteHide quote > All, I want to check wether db connection/open or not, if not it will post db > connection error to user in message box > > The code is like: > > Dim objConnection As New SqlConnection _ > ("Server=server1; database=db1;user > id=user1;password=whatever") > Dim objDataAdapter As New SqlDataAdapter() > Dim objDataSet > > objDataAdapter.SelectCommand = New SqlCommand() > objDataAdapter.SelectCommand.Connection = objConnection > cmdtext = "Select parameter from table1" > > objDataAdapter.SelectCommand.CommandText = cmdtext > objDataAdapter.SelectCommand.CommandType = CommandType.Text > > objConnection.Open() > objDataAdapter.Fill(objDataSet, "table1") > objConnection.Close() > > So if connection problem, it will post error masseage like below > > MessageBox.Show("Ths is Database connenction issue", "DataBase Connection > problem") > > My question is which code line is used to check if db connection/open or > not, it is objConnection.Open()? if it is, how to check? Thanks Thank you. Tom
it works Show quoteHide quote "tommaso.gasta***@uniroma1.it" wrote: > Connections have the "state" property, which you can check. > > -tom > > martin1 ha scritto: > > > All, I want to check wether db connection/open or not, if not it will post db > > connection error to user in message box > > > > The code is like: > > > > Dim objConnection As New SqlConnection _ > > ("Server=server1; database=db1;user > > id=user1;password=whatever") > > Dim objDataAdapter As New SqlDataAdapter() > > Dim objDataSet > > > > objDataAdapter.SelectCommand = New SqlCommand() > > objDataAdapter.SelectCommand.Connection = objConnection > > cmdtext = "Select parameter from table1" > > > > objDataAdapter.SelectCommand.CommandText = cmdtext > > objDataAdapter.SelectCommand.CommandType = CommandType.Text > > > > objConnection.Open() > > objDataAdapter.Fill(objDataSet, "table1") > > objConnection.Close() > > > > So if connection problem, it will post error masseage like below > > > > MessageBox.Show("Ths is Database connenction issue", "DataBase Connection > > problem") > > > > My question is which code line is used to check if db connection/open or > > not, it is objConnection.Open()? if it is, how to check? Thanks > > Martin,
In this construction you don't need to open and close the connection. The dataadapter does that intrensic. Cor Show quoteHide quote "martin1" <mart***@discussions.microsoft.com> schreef in bericht news:0F21B492-45DF-4FDC-AC3A-5F323E793D5B@microsoft.com... > All, I want to check wether db connection/open or not, if not it will post > db > connection error to user in message box > > The code is like: > > Dim objConnection As New SqlConnection _ > ("Server=server1; database=db1;user > id=user1;password=whatever") > Dim objDataAdapter As New SqlDataAdapter() > Dim objDataSet > > objDataAdapter.SelectCommand = New SqlCommand() > objDataAdapter.SelectCommand.Connection = objConnection > cmdtext = "Select parameter from table1" > > objDataAdapter.SelectCommand.CommandText = cmdtext > objDataAdapter.SelectCommand.CommandType = CommandType.Text > > objConnection.Open() > objDataAdapter.Fill(objDataSet, "table1") > objConnection.Close() > > So if connection problem, it will post error masseage like below > > MessageBox.Show("Ths is Database connenction issue", "DataBase Connection > problem") > > My question is which code line is used to check if db connection/open or > not, it is objConnection.Open()? if it is, how to check? Thanks > > > > > > Hello martin1,
Three points: 1. The user doesn't give a flying rats ass whether or not your connection is already open or not. So there's no point in telling them. 2. The connection.State property will tell you about the connections current state (imagine that). So in accordance with point #1.. check the state and if it's doing something naughty.. then correct it and move on. You should only bother the user if the world is coming to an end. 3. You should always call connection.Open and .Close even if the DataAdapter does this work for you. It mokes the code more readable and you are less likely to forget when you use other methods for reading the data which do not perform this functionality for you. -Boo Show quoteHide quote > All, I want to check wether db connection/open or not, if not it will > post db connection error to user in message box > > The code is like: > > Dim objConnection As New SqlConnection _ > ("Server=server1; database=db1;user > id=user1;password=whatever") > Dim objDataAdapter As New SqlDataAdapter() > Dim objDataSet > objDataAdapter.SelectCommand = New SqlCommand() > objDataAdapter.SelectCommand.Connection = objConnection cmdtext = > "Select parameter from table1" > > objDataAdapter.SelectCommand.CommandText = cmdtext > objDataAdapter.SelectCommand.CommandType = CommandType.Text > > objConnection.Open() > objDataAdapter.Fill(objDataSet, "table1") > objConnection.Close() > So if connection problem, it will post error masseage like below > > MessageBox.Show("Ths is Database connenction issue", "DataBase > Connection problem") > > My question is which code line is used to check if db connection/open > or not, it is objConnection.Open()? if it is, how to check? Thanks > GhostInAk
> I assume that this is a very personal opinion from you. I find this a > 3. You should always call connection.Open and .Close even if the > DataAdapter does this work for you. It mokes the code more readable and > you are less likely to forget when you use other methods for reading the > data which do not perform this functionality for you. > beginners approach, that should be avoided as soon as possible. Cor Hello Cor Ligthert [MVP],
Well, when MS changes the way the .Fill method works so it doesn't automatically open the connection.. you al will be cryin and I'll be sittin in Maui sippin Rum n Coke. -Boo Show quoteHide quote > GhostInAk > >> 3. You should always call connection.Open and .Close even if the >> DataAdapter does this work for you. It mokes the code more readable >> and you are less likely to forget when you use other methods for >> reading the data which do not perform this functionality for you. >> > I assume that this is a very personal opinion from you. I find this a > beginners approach, that should be avoided as soon as possible. > > Cor > Hi GhostInAK
but... if we wanted to foresee all the things that ms might change in the future, our programs would definitely grow without limits and eventually get stack :) I do use in several occasion a boolean function a call "OpenConnectionIfNecessary" (opening an open connection would rise error). But I apply it to cases where it is expected the connection to be open (not here for instance). Anyway, the idea of Rum n Coke at Maui it's not bad :) -t GhostInAK ha scritto: Show quoteHide quote > Hello Cor Ligthert [MVP], > > Well, when MS changes the way the .Fill method works so it doesn't automatically > open the connection.. you al will be cryin and I'll be sittin in Maui sippin > Rum n Coke. > > -Boo > > > GhostInAk > > > >> 3. You should always call connection.Open and .Close even if the > >> DataAdapter does this work for you. It mokes the code more readable > >> and you are less likely to forget when you use other methods for > >> reading the data which do not perform this functionality for you. > >> > > I assume that this is a very personal opinion from you. I find this a > > beginners approach, that should be avoided as soon as possible. > > > > Cor > > GhostInAk,
See the answer from Thomasso, Microsoft will never create these breaking changes if they know this, and this is an obvious one. However, but let us take the not imaginable situation as well, that Microsoft would stop checking in the dataadapter if the connection is already open. Who is than sittin in Maui sipping Rum n Coke. Definitely not you. :-) CorShow quoteHide quote "GhostInAK" <ghosti***@gmail.com> schreef in bericht news:c71747b4291e68c87997223d6ae4@news.microsoft.com... > Hello Cor Ligthert [MVP], > > Well, when MS changes the way the .Fill method works so it doesn't > automatically open the connection.. you al will be cryin and I'll be > sittin in Maui sippin Rum n Coke. > > -Boo > >> GhostInAk >> >>> 3. You should always call connection.Open and .Close even if the >>> DataAdapter does this work for you. It mokes the code more readable >>> and you are less likely to forget when you use other methods for >>> reading the data which do not perform this functionality for you. >>> >> I assume that this is a very personal opinion from you. I find this a >> beginners approach, that should be avoided as soon as possible. >> >> Cor >> > > Hello Cor Ligthert [MVP],
You are most likely correct in that MS will not change the behaviour of the DataAdapter.Fill method. However.. Seeing code that does something with a connection whose state you are not in direct control of at design time leaves one to guess at state. Whereas if instead you implicitly set the state (both open and close) before doing something with the connection, the guessing (at least as far as connection state is concerned) is eliminated. It's just good technique to make sure you are in complete control of your environment. -Boo Show quoteHide quote > GhostInAk, > > See the answer from Thomasso, Microsoft will never create these > breaking changes if they know this, and this is an obvious one. > > However, but let us take the not imaginable situation as well, that > Microsoft would stop checking in the dataadapter if the connection is > already open. Who is than sittin in Maui sipping Rum n Coke. > Definitely not you. > > :-) > > Cor > > "GhostInAK" <ghosti***@gmail.com> schreef in bericht > news:c71747b4291e68c87997223d6ae4@news.microsoft.com... > >> Hello Cor Ligthert [MVP], >> >> Well, when MS changes the way the .Fill method works so it doesn't >> automatically open the connection.. you al will be cryin and I'll be >> sittin in Maui sippin Rum n Coke. >> >> -Boo >> >>> GhostInAk >>> >>>> 3. You should always call connection.Open and .Close even if the >>>> DataAdapter does this work for you. It mokes the code more >>>> readable and you are less likely to forget when you use other >>>> methods for reading the data which do not perform this >>>> functionality for you. >>>> >>> I assume that this is a very personal opinion from you. I find this >>> a beginners approach, that should be avoided as soon as possible. >>> >>> Cor >>> Boo,
In my opinion can there be in a good written program be not more guessing than that dim C as integer = 1 + 1' C will be 2. It is not something as this dim c as string = 1 + 1 'while some users thinks that this will give 11 while it will give used in this way still forever be 2. The dataadapter has a very fine mechanisme that opens and closes if that is not done implicitly. But just my opinion. Cor Show quoteHide quote "GhostInAK" <ghosti***@gmail.com> schreef in bericht news:be1391bffa8e8c87a1b7eb3f9a6@news.microsoft.com... > Hello Cor Ligthert [MVP], > > You are most likely correct in that MS will not change the behaviour of > the DataAdapter.Fill method. However.. Seeing code that does something > with a connection whose state you are not in direct control of at design > time leaves one to guess at state. Whereas if instead you implicitly set > the state (both open and close) before doing something with the > connection, the guessing (at least as far as connection state is > concerned) is eliminated. It's just good technique to make sure you are in > complete control of your environment. > > -Boo > >> GhostInAk, >> >> See the answer from Thomasso, Microsoft will never create these >> breaking changes if they know this, and this is an obvious one. >> >> However, but let us take the not imaginable situation as well, that >> Microsoft would stop checking in the dataadapter if the connection is >> already open. Who is than sittin in Maui sipping Rum n Coke. >> Definitely not you. >> >> :-) >> >> Cor >> >> "GhostInAK" <ghosti***@gmail.com> schreef in bericht >> news:c71747b4291e68c87997223d6ae4@news.microsoft.com... >> >>> Hello Cor Ligthert [MVP], >>> >>> Well, when MS changes the way the .Fill method works so it doesn't >>> automatically open the connection.. you al will be cryin and I'll be >>> sittin in Maui sippin Rum n Coke. >>> >>> -Boo >>> >>>> GhostInAk >>>> >>>>> 3. You should always call connection.Open and .Close even if the >>>>> DataAdapter does this work for you. It mokes the code more >>>>> readable and you are less likely to forget when you use other >>>>> methods for reading the data which do not perform this >>>>> functionality for you. >>>>> >>>> I assume that this is a very personal opinion from you. I find this >>>> a beginners approach, that should be avoided as soon as possible. >>>> >>>> Cor >>>> > > Cor and Boo ... ,
Thank you, this is very good discussion. I tried to check connection property, if it is not 1 ( not sure) which means connection failed, then close application. I use try-catch and put connection.open() in the try block and if conenction failed and close app using me.close in catch block, but the app window did Not colse and still point to someline in debug mode. any more advice? Show quoteHide quote "Cor Ligthert [MVP]" wrote: > Boo, > > In my opinion can there be in a good written program be not more guessing > than that > > dim C as integer = 1 + 1' C will be 2. > > It is not something as this > > dim c as string = 1 + 1 'while some users thinks that this will give 11 > while it will give used in this way still forever be 2. > > The dataadapter has a very fine mechanisme that opens and closes if that is > not done implicitly. > > But just my opinion. > > Cor > > > "GhostInAK" <ghosti***@gmail.com> schreef in bericht > news:be1391bffa8e8c87a1b7eb3f9a6@news.microsoft.com... > > Hello Cor Ligthert [MVP], > > > > You are most likely correct in that MS will not change the behaviour of > > the DataAdapter.Fill method. However.. Seeing code that does something > > with a connection whose state you are not in direct control of at design > > time leaves one to guess at state. Whereas if instead you implicitly set > > the state (both open and close) before doing something with the > > connection, the guessing (at least as far as connection state is > > concerned) is eliminated. It's just good technique to make sure you are in > > complete control of your environment. > > > > -Boo > > > >> GhostInAk, > >> > >> See the answer from Thomasso, Microsoft will never create these > >> breaking changes if they know this, and this is an obvious one. > >> > >> However, but let us take the not imaginable situation as well, that > >> Microsoft would stop checking in the dataadapter if the connection is > >> already open. Who is than sittin in Maui sipping Rum n Coke. > >> Definitely not you. > >> > >> :-) > >> > >> Cor > >> > >> "GhostInAK" <ghosti***@gmail.com> schreef in bericht > >> news:c71747b4291e68c87997223d6ae4@news.microsoft.com... > >> > >>> Hello Cor Ligthert [MVP], > >>> > >>> Well, when MS changes the way the .Fill method works so it doesn't > >>> automatically open the connection.. you al will be cryin and I'll be > >>> sittin in Maui sippin Rum n Coke. > >>> > >>> -Boo > >>> > >>>> GhostInAk > >>>> > >>>>> 3. You should always call connection.Open and .Close even if the > >>>>> DataAdapter does this work for you. It mokes the code more > >>>>> readable and you are less likely to forget when you use other > >>>>> methods for reading the data which do not perform this > >>>>> functionality for you. > >>>>> > >>>> I assume that this is a very personal opinion from you. I find this > >>>> a beginners approach, that should be avoided as soon as possible. > >>>> > >>>> Cor > >>>> > > > > > > > Martin,
In my idea should your code so be written that you don't have to check if a connection is open. Very thight opening and closing the connection will help for that (as long as it is not Jet). You can even do Using myconnection do whatever you need with that End using (Be aware that this dispose the connection what is in some framework 2.0 situation not always wanted.) Or you can do Try Conn.open Try do what you want Catch execptions 'what is an handling error end Try Catch execptions 'what is an connection error Finally Conn.close End try I hope this helps, Cor Show quoteHide quote "martin1" <mart***@discussions.microsoft.com> schreef in bericht news:93680815-206E-4FE1-923B-E798975226CF@microsoft.com... > Cor and Boo ... , > > Thank you, this is very good discussion. > > I tried to check connection property, if it is not 1 ( not sure) which > means > connection failed, then close application. I use try-catch and put > connection.open() in the try block and if conenction failed and close app > using me.close in catch block, but the app window did Not colse and still > point to someline in debug mode. any more advice? > > > "Cor Ligthert [MVP]" wrote: > >> Boo, >> >> In my opinion can there be in a good written program be not more guessing >> than that >> >> dim C as integer = 1 + 1' C will be 2. >> >> It is not something as this >> >> dim c as string = 1 + 1 'while some users thinks that this will give 11 >> while it will give used in this way still forever be 2. >> >> The dataadapter has a very fine mechanisme that opens and closes if that >> is >> not done implicitly. >> >> But just my opinion. >> >> Cor >> >> >> "GhostInAK" <ghosti***@gmail.com> schreef in bericht >> news:be1391bffa8e8c87a1b7eb3f9a6@news.microsoft.com... >> > Hello Cor Ligthert [MVP], >> > >> > You are most likely correct in that MS will not change the behaviour of >> > the DataAdapter.Fill method. However.. Seeing code that does something >> > with a connection whose state you are not in direct control of at >> > design >> > time leaves one to guess at state. Whereas if instead you implicitly >> > set >> > the state (both open and close) before doing something with the >> > connection, the guessing (at least as far as connection state is >> > concerned) is eliminated. It's just good technique to make sure you are >> > in >> > complete control of your environment. >> > >> > -Boo >> > >> >> GhostInAk, >> >> >> >> See the answer from Thomasso, Microsoft will never create these >> >> breaking changes if they know this, and this is an obvious one. >> >> >> >> However, but let us take the not imaginable situation as well, that >> >> Microsoft would stop checking in the dataadapter if the connection is >> >> already open. Who is than sittin in Maui sipping Rum n Coke. >> >> Definitely not you. >> >> >> >> :-) >> >> >> >> Cor >> >> >> >> "GhostInAK" <ghosti***@gmail.com> schreef in bericht >> >> news:c71747b4291e68c87997223d6ae4@news.microsoft.com... >> >> >> >>> Hello Cor Ligthert [MVP], >> >>> >> >>> Well, when MS changes the way the .Fill method works so it doesn't >> >>> automatically open the connection.. you al will be cryin and I'll be >> >>> sittin in Maui sippin Rum n Coke. >> >>> >> >>> -Boo >> >>> >> >>>> GhostInAk >> >>>> >> >>>>> 3. You should always call connection.Open and .Close even if the >> >>>>> DataAdapter does this work for you. It mokes the code more >> >>>>> readable and you are less likely to forget when you use other >> >>>>> methods for reading the data which do not perform this >> >>>>> functionality for you. >> >>>>> >> >>>> I assume that this is a very personal opinion from you. I find this >> >>>> a beginners approach, that should be avoided as soon as possible. >> >>>> >> >>>> Cor >> >>>> >> > >> > >> >> >> doh,
Sometimes my fingers are typing something else than I mean. Read for Framework 2.0 -> generated datasources in winform VS 2005 Cor Show quoteHide quote "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> schreef in bericht news:eaXQbmNrGHA.3908@TK2MSFTNGP05.phx.gbl... > Martin, > > In my idea should your code so be written that you don't have to check if > a connection is open. > > Very thight opening and closing the connection will help for that (as long > as it is not Jet). > > You can even do > Using myconnection > do whatever you need with that > End using > (Be aware that this dispose the connection what is in some framework 2.0 > situation not always wanted.) > > Or you can do > Try > Conn.open > Try > do what you want > Catch execptions > 'what is an handling error > end Try > Catch execptions > 'what is an connection error > Finally > Conn.close > End try > > I hope this helps, > > Cor > > > "martin1" <mart***@discussions.microsoft.com> schreef in bericht > news:93680815-206E-4FE1-923B-E798975226CF@microsoft.com... >> Cor and Boo ... , >> >> Thank you, this is very good discussion. >> >> I tried to check connection property, if it is not 1 ( not sure) which >> means >> connection failed, then close application. I use try-catch and put >> connection.open() in the try block and if conenction failed and close app >> using me.close in catch block, but the app window did Not colse and still >> point to someline in debug mode. any more advice? >> >> >> "Cor Ligthert [MVP]" wrote: >> >>> Boo, >>> >>> In my opinion can there be in a good written program be not more >>> guessing >>> than that >>> >>> dim C as integer = 1 + 1' C will be 2. >>> >>> It is not something as this >>> >>> dim c as string = 1 + 1 'while some users thinks that this will give 11 >>> while it will give used in this way still forever be 2. >>> >>> The dataadapter has a very fine mechanisme that opens and closes if that >>> is >>> not done implicitly. >>> >>> But just my opinion. >>> >>> Cor >>> >>> >>> "GhostInAK" <ghosti***@gmail.com> schreef in bericht >>> news:be1391bffa8e8c87a1b7eb3f9a6@news.microsoft.com... >>> > Hello Cor Ligthert [MVP], >>> > >>> > You are most likely correct in that MS will not change the behaviour >>> > of >>> > the DataAdapter.Fill method. However.. Seeing code that does >>> > something >>> > with a connection whose state you are not in direct control of at >>> > design >>> > time leaves one to guess at state. Whereas if instead you implicitly >>> > set >>> > the state (both open and close) before doing something with the >>> > connection, the guessing (at least as far as connection state is >>> > concerned) is eliminated. It's just good technique to make sure you >>> > are in >>> > complete control of your environment. >>> > >>> > -Boo >>> > >>> >> GhostInAk, >>> >> >>> >> See the answer from Thomasso, Microsoft will never create these >>> >> breaking changes if they know this, and this is an obvious one. >>> >> >>> >> However, but let us take the not imaginable situation as well, that >>> >> Microsoft would stop checking in the dataadapter if the connection is >>> >> already open. Who is than sittin in Maui sipping Rum n Coke. >>> >> Definitely not you. >>> >> >>> >> :-) >>> >> >>> >> Cor >>> >> >>> >> "GhostInAK" <ghosti***@gmail.com> schreef in bericht >>> >> news:c71747b4291e68c87997223d6ae4@news.microsoft.com... >>> >> >>> >>> Hello Cor Ligthert [MVP], >>> >>> >>> >>> Well, when MS changes the way the .Fill method works so it doesn't >>> >>> automatically open the connection.. you al will be cryin and I'll be >>> >>> sittin in Maui sippin Rum n Coke. >>> >>> >>> >>> -Boo >>> >>> >>> >>>> GhostInAk >>> >>>> >>> >>>>> 3. You should always call connection.Open and .Close even if the >>> >>>>> DataAdapter does this work for you. It mokes the code more >>> >>>>> readable and you are less likely to forget when you use other >>> >>>>> methods for reading the data which do not perform this >>> >>>>> functionality for you. >>> >>>>> >>> >>>> I assume that this is a very personal opinion from you. I find this >>> >>>> a beginners approach, that should be avoided as soon as possible. >>> >>>> >>> >>>> Cor >>> >>>> >>> > >>> > >>> >>> >>> > > 
Other interesting topics
Default date
Combining datatables HELP! Why is SqlException being caught in this code if it can't connect to SQL Server? concurrency question Access Databse "Select * from Bilag Where Mdates Between #1/1/2006# And #31/1/2006#" Display Unicode characters on Winforms Refreshing datagridview after a rowtemplate height change ADO.NET in VB6 How to download a file from Server to client local pc without user intervention? How to Automate Calls to a Web Site with Different Parameters on the URL |
|||||||||||||||||||||||
