|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
vb.net and Ms SQL Connection ?I have a very strange probelm. In my code i set the SQLConnection objects connections str. Then I do Connection.open. The app never comes back or errors out when I know there is no connection. How long is the attempt suppose to take. Any ideas as to why it would just be hanging there. I have the enter code wrapped in a try catch and it never gets triggered either. And it never gets to next line in code. Thanks Deasun There is a connection timeout property.
You can set it. Default is 30 seconds? Not sure, look at documentation. Connection Timeout is the "Try to find this server for this long value". There is also a seperate CommandTimeout property...on the SqlCommand object. I point it out because sometimes people get the two confused. ............ If you want "clean" DataAccessLayer (Helper) code, then I'd suggest getting the EnterpriseLibrary (.Data) 3.1 or 4.0 (or 4.1 I guess now). It will simplify your DataAccess and implement many many best practices. http://msdn.microsoft.com/en-us/library/cc467894.aspx Show quoteHide quote "Deasun" <Dea***@discussions.microsoft.com> wrote in message news:63767C4E-B959-4CD7-BB95-DCDDA2E8C365@microsoft.com... > Morning, > > I have a very strange probelm. > In my code i set the SQLConnection objects connections str. > Then I do Connection.open. > > The app never comes back or errors out when I know there is no connection. > How long is the attempt suppose to take. > > Any ideas as to why it would just be hanging there. > I have the enter code wrapped in a try catch and it never gets triggered > either. > And it never gets to next line in code. > > Thanks > Deasun > -- > Deasun > Home Site: www.tirnaog.com > Check out: The Code Vault in my forums section. Also make sure you don't hide the exception rather than revealing it. An
agressive firewall perhaps ? Seeing some code could also help to understand if it is a coding issue (just the minimal amount of lines needed to repro the issue). -- Patrice "sloan" <sl***@ipass.net> a écrit dans le message de groupe de discussion : #mBXtFl#JHA.3***@TK2MSFTNGP04.phx.gbl...Show quoteHide quote > > There is a connection timeout property. > You can set it. Default is 30 seconds? Not sure, look at documentation. > > Connection Timeout is the "Try to find this server for this long value". > > There is also a seperate CommandTimeout property...on the SqlCommand > object. > I point it out because sometimes people get the two confused. > > ........... > > If you want "clean" DataAccessLayer (Helper) code, then I'd suggest > getting the EnterpriseLibrary (.Data) 3.1 or 4.0 (or 4.1 I guess now). > It will simplify your DataAccess and implement many many best practices. > > http://msdn.microsoft.com/en-us/library/cc467894.aspx > > > > > > "Deasun" <Dea***@discussions.microsoft.com> wrote in message > news:63767C4E-B959-4CD7-BB95-DCDDA2E8C365@microsoft.com... >> Morning, >> >> I have a very strange probelm. >> In my code i set the SQLConnection objects connections str. >> Then I do Connection.open. >> >> The app never comes back or errors out when I know there is no >> connection. >> How long is the attempt suppose to take. >> >> Any ideas as to why it would just be hanging there. >> I have the enter code wrapped in a try catch and it never gets triggered >> either. >> And it never gets to next line in code. >> >> Thanks >> Deasun >> -- >> Deasun >> Home Site: www.tirnaog.com >> Check out: The Code Vault in my forums section. > > Heres the current code base:
<code> Public Function DBQuery(ByVal strCmdType As String, ByVal strSQL As String, ByVal strWhoCalledMe As String, Optional ByVal strCstr_ToUse As String = "None Given") As String Dim strWhoAmI As String = My.Application.Info.Title & ".clsCommonTools.DBQuery" Dim strAnswer As String = "False" Dim strSQLCmdWas As String = strSQL Dim strResult As String = "" Dim Connection As New SqlConnection '#### Execute Query #### Try ' MessageBox.Show("Here I am!", "just entered DbQuery", MessageBoxButtons.OK, MessageBoxIcon.Error) If (strCstr_ToUse <> "None Given") Then Connection.ConnectionString = strCstr_ToUse Else Connection.ConnectionString = My.Forms.USAGoldCorp.msConnectionStrToUse End If ' MessageBox.Show("Here I am!", "Cstr Set", MessageBoxButtons.OK, MessageBoxIcon.Error) ' MessageBox.Show(Connection.ConnectionString.ToString, "Cstr Is", MessageBoxButtons.OK, MessageBoxIcon.Error) Connection.Open() ' MessageBox.Show("Here I am!", "Connection opened", MessageBoxButtons.OK, MessageBoxIcon.Error) Dim cmdRequest As New SqlCommand With cmdRequest .CommandText = strSQL .CommandType = Data.CommandType.Text .Connection = Connection .CommandTimeout = 60000 '3400000 End With ' MessageBox.Show("Here I am!", "set Request Obj", MessageBoxButtons.OK, MessageBoxIcon.Error) Select Case strCmdType Case "CMD" cmdRequest.ExecuteNonQuery() strResult = "True" Case "QUERY" strResult = "" strResult = cmdRequest.ExecuteScalar() If (strResult = Nothing) Then With Connection .Close() .Dispose() End With Else strResult = "True" End If Case "RESULT" strResult = "" ' MessageBox.Show("Here I am!", "About to execute SQL", MessageBoxButtons.OK, MessageBoxIcon.Error) strResult = cmdRequest.ExecuteScalar() ' MessageBox.Show("Here I am!", "done Execution SQL", MessageBoxButtons.OK, MessageBoxIcon.Error) If (strResult = Nothing) Then strResult = "False" Else End If End Select Non_error1: strAnswer = strResult Catch ex As Exception MessageBox.Show(ex.Message, "Db connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error) strResult = "False" RecordErrorMsg(ex.Message, My.Application.Info.Title, My.Application.Info.Version.ToString, strWhoAmI, strWhoCalledMe, ("CMD Type was: " & strCmdType & Environment.NewLine), strSQL, My.Forms.USAGoldCorp.msUserID) End Try With Connection If (Connection.State = Data.ConnectionState.Open) Then .Close() End If .Dispose() End With DBQuery = strAnswer ' MessageBox.Show("Here I am!", "Leaving DbQuery", MessageBoxButtons.OK, MessageBoxIcon.Error) End Function </code> Show quoteHide quote "Patrice" wrote: > Also make sure you don't hide the exception rather than revealing it. An > agressive firewall perhaps ? > > Seeing some code could also help to understand if it is a coding issue (just > the minimal amount of lines needed to repro the issue). > > -- > Patrice > > "sloan" <sl***@ipass.net> a écrit dans le message de groupe de discussion : > #mBXtFl#JHA.3***@TK2MSFTNGP04.phx.gbl... > > > > There is a connection timeout property. > > You can set it. Default is 30 seconds? Not sure, look at documentation. > > > > Connection Timeout is the "Try to find this server for this long value". > > > > There is also a seperate CommandTimeout property...on the SqlCommand > > object. > > I point it out because sometimes people get the two confused. > > > > ........... > > > > If you want "clean" DataAccessLayer (Helper) code, then I'd suggest > > getting the EnterpriseLibrary (.Data) 3.1 or 4.0 (or 4.1 I guess now). > > It will simplify your DataAccess and implement many many best practices. > > > > http://msdn.microsoft.com/en-us/library/cc467894.aspx > > > > > > > > > > > > "Deasun" <Dea***@discussions.microsoft.com> wrote in message > > news:63767C4E-B959-4CD7-BB95-DCDDA2E8C365@microsoft.com... > >> Morning, > >> > >> I have a very strange probelm. > >> In my code i set the SQLConnection objects connections str. > >> Then I do Connection.open. > >> > >> The app never comes back or errors out when I know there is no > >> connection. > >> How long is the attempt suppose to take. > >> > >> Any ideas as to why it would just be hanging there. > >> I have the enter code wrapped in a try catch and it never gets triggered > >> either. > >> And it never gets to next line in code. > >> > >> Thanks > >> Deasun > >> -- > >> Deasun > >> Home Site: www.tirnaog.com > >> Check out: The Code Vault in my forums section. > > > > > > That is some of the ugliest code I've seen in 10 years. Its spaghetti'ed
together (Presentation and DAL) code. A method that has SqlCommand in it shouldn't know what a MessageBox is. I am trying to say it gently, but at the same time, it needs to be said. Youch. You've got bigger troubles than not being able to locate a sqlserver. .................... Here is a decent sample. http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!139.entry It uses DAAB 2.0. Here is a slightly updated version: http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry It uses EnterpriseLibrary.Data 3.1. Try to find the "bird's eye view" article I mention at the first URL. Its a Microsoft article. Its a little dated, but very useful. Show quoteHide quote "Deasun" <Dea***@discussions.microsoft.com> wrote in message news:1468E8D3-33FE-4F5B-AAD4-FF43F814EDC6@microsoft.com... > Heres the current code base: > <code> > Public Function DBQuery(ByVal strCmdType As String, ByVal strSQL As > String, > ByVal strWhoCalledMe As String, Optional ByVal strCstr_ToUse As String = > "None Given") As String > Dim strWhoAmI As String = My.Application.Info.Title & > ".clsCommonTools.DBQuery" > Dim strAnswer As String = "False" > Dim strSQLCmdWas As String = strSQL > Dim strResult As String = "" > Dim Connection As New SqlConnection > '#### Execute Query #### > Try > ' MessageBox.Show("Here I am!", "just entered DbQuery", > MessageBoxButtons.OK, MessageBoxIcon.Error) > If (strCstr_ToUse <> "None Given") Then > Connection.ConnectionString = strCstr_ToUse > Else > Connection.ConnectionString = > My.Forms.USAGoldCorp.msConnectionStrToUse > End If > ' MessageBox.Show("Here I am!", "Cstr Set", > MessageBoxButtons.OK, MessageBoxIcon.Error) > ' MessageBox.Show(Connection.ConnectionString.ToString, > "Cstr > Is", MessageBoxButtons.OK, MessageBoxIcon.Error) > > Connection.Open() > ' MessageBox.Show("Here I am!", "Connection opened", > MessageBoxButtons.OK, MessageBoxIcon.Error) > Dim cmdRequest As New SqlCommand > With cmdRequest > .CommandText = strSQL > .CommandType = Data.CommandType.Text > .Connection = Connection > .CommandTimeout = 60000 '3400000 > End With > ' MessageBox.Show("Here I am!", "set Request Obj", > MessageBoxButtons.OK, MessageBoxIcon.Error) > Select Case strCmdType > Case "CMD" > cmdRequest.ExecuteNonQuery() > strResult = "True" > Case "QUERY" > strResult = "" > strResult = cmdRequest.ExecuteScalar() > If (strResult = Nothing) Then > With Connection > .Close() > .Dispose() > End With > Else > strResult = "True" > End If > Case "RESULT" > strResult = "" > ' MessageBox.Show("Here I am!", "About to > execute SQL", MessageBoxButtons.OK, MessageBoxIcon.Error) > strResult = cmdRequest.ExecuteScalar() > ' MessageBox.Show("Here I am!", "done Execution > SQL", MessageBoxButtons.OK, MessageBoxIcon.Error) > If (strResult = Nothing) Then > strResult = "False" > Else > End If > End Select > Non_error1: > strAnswer = strResult > > Catch ex As Exception > MessageBox.Show(ex.Message, "Db connection Error", > MessageBoxButtons.OK, MessageBoxIcon.Error) > > strResult = "False" > RecordErrorMsg(ex.Message, My.Application.Info.Title, > My.Application.Info.Version.ToString, strWhoAmI, strWhoCalledMe, ("CMD > Type > was: " & strCmdType & Environment.NewLine), strSQL, > My.Forms.USAGoldCorp.msUserID) > End Try > With Connection > If (Connection.State = Data.ConnectionState.Open) Then > .Close() > End If > .Dispose() > End With > DBQuery = strAnswer > ' MessageBox.Show("Here I am!", "Leaving DbQuery", > MessageBoxButtons.OK, MessageBoxIcon.Error) > End Function > </code> > -- > Deasun > Home Site: www.tirnaog.com > Check out: The Code Vault in my forums section. > > > "Patrice" wrote: > >> Also make sure you don't hide the exception rather than revealing it. An >> agressive firewall perhaps ? >> >> Seeing some code could also help to understand if it is a coding issue >> (just >> the minimal amount of lines needed to repro the issue). >> >> -- >> Patrice >> >> "sloan" <sl***@ipass.net> a écrit dans le message de groupe de discussion >> : >> #mBXtFl#JHA.3***@TK2MSFTNGP04.phx.gbl... >> > >> > There is a connection timeout property. >> > You can set it. Default is 30 seconds? Not sure, look at >> > documentation. >> > >> > Connection Timeout is the "Try to find this server for this long >> > value". >> > >> > There is also a seperate CommandTimeout property...on the SqlCommand >> > object. >> > I point it out because sometimes people get the two confused. >> > >> > ........... >> > >> > If you want "clean" DataAccessLayer (Helper) code, then I'd suggest >> > getting the EnterpriseLibrary (.Data) 3.1 or 4.0 (or 4.1 I guess now). >> > It will simplify your DataAccess and implement many many best >> > practices. >> > >> > http://msdn.microsoft.com/en-us/library/cc467894.aspx >> > >> > >> > >> > >> > >> > "Deasun" <Dea***@discussions.microsoft.com> wrote in message >> > news:63767C4E-B959-4CD7-BB95-DCDDA2E8C365@microsoft.com... >> >> Morning, >> >> >> >> I have a very strange probelm. >> >> In my code i set the SQLConnection objects connections str. >> >> Then I do Connection.open. >> >> >> >> The app never comes back or errors out when I know there is no >> >> connection. >> >> How long is the attempt suppose to take. >> >> >> >> Any ideas as to why it would just be hanging there. >> >> I have the enter code wrapped in a try catch and it never gets >> >> triggered >> >> either. >> >> And it never gets to next line in code. >> >> >> >> Thanks >> >> Deasun >> >> -- >> >> Deasun >> >> Home Site: www.tirnaog.com >> >> Check out: The Code Vault in my forums section. >> > >> > >> >> Ya thks for your comment!
But the messageboxs are in their as a debug attempt at the moment. Not in there normally. Show quoteHide quote "sloan" wrote: > > > That is some of the ugliest code I've seen in 10 years. Its spaghetti'ed > together (Presentation and DAL) code. A method that has SqlCommand in it > shouldn't know what a MessageBox is. > > I am trying to say it gently, but at the same time, it needs to be said. > > Youch. You've got bigger troubles than not being able to locate a > sqlserver. > > .................... > > Here is a decent sample. > http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!139.entry > It uses DAAB 2.0. > > Here is a slightly updated version: > http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry > It uses EnterpriseLibrary.Data 3.1. > > > Try to find the "bird's eye view" article I mention at the first URL. Its a > Microsoft article. Its a little dated, but very useful. > > > > "Deasun" <Dea***@discussions.microsoft.com> wrote in message > news:1468E8D3-33FE-4F5B-AAD4-FF43F814EDC6@microsoft.com... > > Heres the current code base: > > <code> > > Public Function DBQuery(ByVal strCmdType As String, ByVal strSQL As > > String, > > ByVal strWhoCalledMe As String, Optional ByVal strCstr_ToUse As String = > > "None Given") As String > > Dim strWhoAmI As String = My.Application.Info.Title & > > ".clsCommonTools.DBQuery" > > Dim strAnswer As String = "False" > > Dim strSQLCmdWas As String = strSQL > > Dim strResult As String = "" > > Dim Connection As New SqlConnection > > '#### Execute Query #### > > Try > > ' MessageBox.Show("Here I am!", "just entered DbQuery", > > MessageBoxButtons.OK, MessageBoxIcon.Error) > > If (strCstr_ToUse <> "None Given") Then > > Connection.ConnectionString = strCstr_ToUse > > Else > > Connection.ConnectionString = > > My.Forms.USAGoldCorp.msConnectionStrToUse > > End If > > ' MessageBox.Show("Here I am!", "Cstr Set", > > MessageBoxButtons.OK, MessageBoxIcon.Error) > > ' MessageBox.Show(Connection.ConnectionString.ToString, > > "Cstr > > Is", MessageBoxButtons.OK, MessageBoxIcon.Error) > > > > Connection.Open() > > ' MessageBox.Show("Here I am!", "Connection opened", > > MessageBoxButtons.OK, MessageBoxIcon.Error) > > Dim cmdRequest As New SqlCommand > > With cmdRequest > > .CommandText = strSQL > > .CommandType = Data.CommandType.Text > > .Connection = Connection > > .CommandTimeout = 60000 '3400000 > > End With > > ' MessageBox.Show("Here I am!", "set Request Obj", > > MessageBoxButtons.OK, MessageBoxIcon.Error) > > Select Case strCmdType > > Case "CMD" > > cmdRequest.ExecuteNonQuery() > > strResult = "True" > > Case "QUERY" > > strResult = "" > > strResult = cmdRequest.ExecuteScalar() > > If (strResult = Nothing) Then > > With Connection > > .Close() > > .Dispose() > > End With > > Else > > strResult = "True" > > End If > > Case "RESULT" > > strResult = "" > > ' MessageBox.Show("Here I am!", "About to > > execute SQL", MessageBoxButtons.OK, MessageBoxIcon.Error) > > strResult = cmdRequest.ExecuteScalar() > > ' MessageBox.Show("Here I am!", "done Execution > > SQL", MessageBoxButtons.OK, MessageBoxIcon.Error) > > If (strResult = Nothing) Then > > strResult = "False" > > Else > > End If > > End Select > > Non_error1: > > strAnswer = strResult > > > > Catch ex As Exception > > MessageBox.Show(ex.Message, "Db connection Error", > > MessageBoxButtons.OK, MessageBoxIcon.Error) > > > > strResult = "False" > > RecordErrorMsg(ex.Message, My.Application.Info.Title, > > My.Application.Info.Version.ToString, strWhoAmI, strWhoCalledMe, ("CMD > > Type > > was: " & strCmdType & Environment.NewLine), strSQL, > > My.Forms.USAGoldCorp.msUserID) > > End Try > > With Connection > > If (Connection.State = Data.ConnectionState.Open) Then > > .Close() > > End If > > .Dispose() > > End With > > DBQuery = strAnswer > > ' MessageBox.Show("Here I am!", "Leaving DbQuery", > > MessageBoxButtons.OK, MessageBoxIcon.Error) > > End Function > > </code> > > -- > > Deasun > > Home Site: www.tirnaog.com > > Check out: The Code Vault in my forums section. > > > > > > "Patrice" wrote: > > > >> Also make sure you don't hide the exception rather than revealing it. An > >> agressive firewall perhaps ? > >> > >> Seeing some code could also help to understand if it is a coding issue > >> (just > >> the minimal amount of lines needed to repro the issue). > >> > >> -- > >> Patrice > >> > >> "sloan" <sl***@ipass.net> a écrit dans le message de groupe de discussion > >> : > >> #mBXtFl#JHA.3***@TK2MSFTNGP04.phx.gbl... > >> > > >> > There is a connection timeout property. > >> > You can set it. Default is 30 seconds? Not sure, look at > >> > documentation. > >> > > >> > Connection Timeout is the "Try to find this server for this long > >> > value". > >> > > >> > There is also a seperate CommandTimeout property...on the SqlCommand > >> > object. > >> > I point it out because sometimes people get the two confused. > >> > > >> > ........... > >> > > >> > If you want "clean" DataAccessLayer (Helper) code, then I'd suggest > >> > getting the EnterpriseLibrary (.Data) 3.1 or 4.0 (or 4.1 I guess now). > >> > It will simplify your DataAccess and implement many many best > >> > practices. > >> > > >> > http://msdn.microsoft.com/en-us/library/cc467894.aspx > >> > > >> > > >> > > >> > > >> > > >> > "Deasun" <Dea***@discussions.microsoft.com> wrote in message > >> > news:63767C4E-B959-4CD7-BB95-DCDDA2E8C365@microsoft.com... > >> >> Morning, > >> >> > >> >> I have a very strange probelm. > >> >> In my code i set the SQLConnection objects connections str. > >> >> Then I do Connection.open. > >> >> > >> >> The app never comes back or errors out when I know there is no > >> >> connection. > >> >> How long is the attempt suppose to take. > >> >> > >> >> Any ideas as to why it would just be hanging there. > >> >> I have the enter code wrapped in a try catch and it never gets > >> >> triggered > >> >> either. > >> >> And it never gets to next line in code. > >> >> > >> >> Thanks > >> >> Deasun > >> >> -- > >> >> Deasun > >> >> Home Site: www.tirnaog.com > >> >> Check out: The Code Vault in my forums section. > >> > > >> > > >> > >> > > > Deason,
Then make a simple test project with only one piece of code where you connect to the database and do by instance an cmd.executescalar (about 8 rows of code) Then you know what goes wrong with your connection. Cor Show quoteHide quote "Deasun" <Dea***@discussions.microsoft.com> wrote in message news:7AD5EFF3-25AC-40EB-B74B-F73A1DB88BD1@microsoft.com... > Ya thks for your comment! > But the messageboxs are in their as a debug attempt at the moment. > Not in there normally. > > > -- > Deasun > Home Site: www.tirnaog.com > Check out: The Code Vault in my forums section. > > > "sloan" wrote: > >> >> >> That is some of the ugliest code I've seen in 10 years. Its spaghetti'ed >> together (Presentation and DAL) code. A method that has SqlCommand in it >> shouldn't know what a MessageBox is. >> >> I am trying to say it gently, but at the same time, it needs to be said. >> >> Youch. You've got bigger troubles than not being able to locate a >> sqlserver. >> >> .................... >> >> Here is a decent sample. >> http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!139.entry >> It uses DAAB 2.0. >> >> Here is a slightly updated version: >> http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry >> It uses EnterpriseLibrary.Data 3.1. >> >> >> Try to find the "bird's eye view" article I mention at the first URL. >> Its a >> Microsoft article. Its a little dated, but very useful. >> >> >> >> "Deasun" <Dea***@discussions.microsoft.com> wrote in message >> news:1468E8D3-33FE-4F5B-AAD4-FF43F814EDC6@microsoft.com... >> > Heres the current code base: >> > <code> >> > Public Function DBQuery(ByVal strCmdType As String, ByVal strSQL As >> > String, >> > ByVal strWhoCalledMe As String, Optional ByVal strCstr_ToUse As String >> > = >> > "None Given") As String >> > Dim strWhoAmI As String = My.Application.Info.Title & >> > ".clsCommonTools.DBQuery" >> > Dim strAnswer As String = "False" >> > Dim strSQLCmdWas As String = strSQL >> > Dim strResult As String = "" >> > Dim Connection As New SqlConnection >> > '#### Execute Query #### >> > Try >> > ' MessageBox.Show("Here I am!", "just entered DbQuery", >> > MessageBoxButtons.OK, MessageBoxIcon.Error) >> > If (strCstr_ToUse <> "None Given") Then >> > Connection.ConnectionString = strCstr_ToUse >> > Else >> > Connection.ConnectionString = >> > My.Forms.USAGoldCorp.msConnectionStrToUse >> > End If >> > ' MessageBox.Show("Here I am!", "Cstr Set", >> > MessageBoxButtons.OK, MessageBoxIcon.Error) >> > ' MessageBox.Show(Connection.ConnectionString.ToString, >> > "Cstr >> > Is", MessageBoxButtons.OK, MessageBoxIcon.Error) >> > >> > Connection.Open() >> > ' MessageBox.Show("Here I am!", "Connection opened", >> > MessageBoxButtons.OK, MessageBoxIcon.Error) >> > Dim cmdRequest As New SqlCommand >> > With cmdRequest >> > .CommandText = strSQL >> > .CommandType = Data.CommandType.Text >> > .Connection = Connection >> > .CommandTimeout = 60000 '3400000 >> > End With >> > ' MessageBox.Show("Here I am!", "set Request Obj", >> > MessageBoxButtons.OK, MessageBoxIcon.Error) >> > Select Case strCmdType >> > Case "CMD" >> > cmdRequest.ExecuteNonQuery() >> > strResult = "True" >> > Case "QUERY" >> > strResult = "" >> > strResult = cmdRequest.ExecuteScalar() >> > If (strResult = Nothing) Then >> > With Connection >> > .Close() >> > .Dispose() >> > End With >> > Else >> > strResult = "True" >> > End If >> > Case "RESULT" >> > strResult = "" >> > ' MessageBox.Show("Here I am!", "About to >> > execute SQL", MessageBoxButtons.OK, MessageBoxIcon.Error) >> > strResult = cmdRequest.ExecuteScalar() >> > ' MessageBox.Show("Here I am!", "done >> > Execution >> > SQL", MessageBoxButtons.OK, MessageBoxIcon.Error) >> > If (strResult = Nothing) Then >> > strResult = "False" >> > Else >> > End If >> > End Select >> > Non_error1: >> > strAnswer = strResult >> > >> > Catch ex As Exception >> > MessageBox.Show(ex.Message, "Db connection Error", >> > MessageBoxButtons.OK, MessageBoxIcon.Error) >> > >> > strResult = "False" >> > RecordErrorMsg(ex.Message, My.Application.Info.Title, >> > My.Application.Info.Version.ToString, strWhoAmI, strWhoCalledMe, ("CMD >> > Type >> > was: " & strCmdType & Environment.NewLine), strSQL, >> > My.Forms.USAGoldCorp.msUserID) >> > End Try >> > With Connection >> > If (Connection.State = Data.ConnectionState.Open) Then >> > .Close() >> > End If >> > .Dispose() >> > End With >> > DBQuery = strAnswer >> > ' MessageBox.Show("Here I am!", "Leaving DbQuery", >> > MessageBoxButtons.OK, MessageBoxIcon.Error) >> > End Function >> > </code> >> > -- >> > Deasun >> > Home Site: www.tirnaog.com >> > Check out: The Code Vault in my forums section. >> > >> > >> > "Patrice" wrote: >> > >> >> Also make sure you don't hide the exception rather than revealing it. >> >> An >> >> agressive firewall perhaps ? >> >> >> >> Seeing some code could also help to understand if it is a coding issue >> >> (just >> >> the minimal amount of lines needed to repro the issue). >> >> >> >> -- >> >> Patrice >> >> >> >> "sloan" <sl***@ipass.net> a écrit dans le message de groupe de >> >> discussion >> >> : >> >> #mBXtFl#JHA.3***@TK2MSFTNGP04.phx.gbl... >> >> > >> >> > There is a connection timeout property. >> >> > You can set it. Default is 30 seconds? Not sure, look at >> >> > documentation. >> >> > >> >> > Connection Timeout is the "Try to find this server for this long >> >> > value". >> >> > >> >> > There is also a seperate CommandTimeout property...on the SqlCommand >> >> > object. >> >> > I point it out because sometimes people get the two confused. >> >> > >> >> > ........... >> >> > >> >> > If you want "clean" DataAccessLayer (Helper) code, then I'd suggest >> >> > getting the EnterpriseLibrary (.Data) 3.1 or 4.0 (or 4.1 I guess >> >> > now). >> >> > It will simplify your DataAccess and implement many many best >> >> > practices. >> >> > >> >> > http://msdn.microsoft.com/en-us/library/cc467894.aspx >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > "Deasun" <Dea***@discussions.microsoft.com> wrote in message >> >> > news:63767C4E-B959-4CD7-BB95-DCDDA2E8C365@microsoft.com... >> >> >> Morning, >> >> >> >> >> >> I have a very strange probelm. >> >> >> In my code i set the SQLConnection objects connections str. >> >> >> Then I do Connection.open. >> >> >> >> >> >> The app never comes back or errors out when I know there is no >> >> >> connection. >> >> >> How long is the attempt suppose to take. >> >> >> >> >> >> Any ideas as to why it would just be hanging there. >> >> >> I have the enter code wrapped in a try catch and it never gets >> >> >> triggered >> >> >> either. >> >> >> And it never gets to next line in code. >> >> >> >> >> >> Thanks >> >> >> Deasun >> >> >> -- >> >> >> Deasun >> >> >> Home Site: www.tirnaog.com >> >> >> Check out: The Code Vault in my forums section. >> >> > >> >> > >> >> >> >> >> >> >> If you used the EnterpriseLibrary.Data framework, your DAL code would be
very clean. Like this: Public Function ExcecuteScalarExample1(ByVal managerId As Integer) As Int32 Dim returnValue As Integer = 0 Try Dim db As Database = DatabaseFactory.CreateDatabase(); Dim dbc As DbCommand = db.GetStoredProcCommand("dbo.uspMyStoredProcedure) db.AddInParameter(dbc, "@ManagerID", DbType.Int32, managerId) '' just an example of a parameter Dim o As Object = db.ExecuteScalar(dbc) returnValue = Convert.ToInt32(o) Return returnValue ''The next 3 lines can be commented out later after debugging takes place Catch ex as Exception '' ''comment out later dim temp as string = ex.Message ''comment out later Throw ''comment out later Finally ''Keep this in the final code. Google "Brad Abrams Try Finally" for more info End Try End Function That would be about 100X easier to debug. http://www.devx.com/dotnet/Article/30910/1954 Keep It Short As you have seen from this article, the EntLib Data Access Block obviates the need to write the repetitive data access plumbing code by providing a set of highly reusable classes for performing operations against the database. Through the use of these classes, you can eliminate errors, bugs, and typos in your application and focus on the core business logic of the application thereby gain productivity. Show quoteHide quote "Cor Ligthert[MVP]" <Notmyfirstn***@planet.nl> wrote in message news:ucFcDOo%23JHA.4168@TK2MSFTNGP05.phx.gbl... > Deason, > > Then make a simple test project with only one piece of code where you > connect to the database and do by instance an cmd.executescalar > (about 8 rows of code) > > Then you know what goes wrong with your connection. > > Cor > > "Deasun" <Dea***@discussions.microsoft.com> wrote in message > news:7AD5EFF3-25AC-40EB-B74B-F73A1DB88BD1@microsoft.com... >> Ya thks for your comment! >> But the messageboxs are in their as a debug attempt at the moment. >> Not in there normally. >> >> >> -- >> Deasun >> Home Site: www.tirnaog.com >> Check out: The Code Vault in my forums section. >> >> >> "sloan" wrote: >> >>> >>> >>> That is some of the ugliest code I've seen in 10 years. Its >>> spaghetti'ed >>> together (Presentation and DAL) code. A method that has SqlCommand in >>> it >>> shouldn't know what a MessageBox is. >>> >>> I am trying to say it gently, but at the same time, it needs to be said. >>> >>> Youch. You've got bigger troubles than not being able to locate a >>> sqlserver. >>> >>> .................... >>> >>> Here is a decent sample. >>> http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!139.entry >>> It uses DAAB 2.0. >>> >>> Here is a slightly updated version: >>> http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry >>> It uses EnterpriseLibrary.Data 3.1. >>> >>> >>> Try to find the "bird's eye view" article I mention at the first URL. >>> Its a >>> Microsoft article. Its a little dated, but very useful. >>> >>> >>> >>> "Deasun" <Dea***@discussions.microsoft.com> wrote in message >>> news:1468E8D3-33FE-4F5B-AAD4-FF43F814EDC6@microsoft.com... >>> > Heres the current code base: >>> > <code> >>> > Public Function DBQuery(ByVal strCmdType As String, ByVal strSQL As >>> > String, >>> > ByVal strWhoCalledMe As String, Optional ByVal strCstr_ToUse As String >>> > = >>> > "None Given") As String >>> > Dim strWhoAmI As String = My.Application.Info.Title & >>> > ".clsCommonTools.DBQuery" >>> > Dim strAnswer As String = "False" >>> > Dim strSQLCmdWas As String = strSQL >>> > Dim strResult As String = "" >>> > Dim Connection As New SqlConnection >>> > '#### Execute Query #### >>> > Try >>> > ' MessageBox.Show("Here I am!", "just entered >>> > DbQuery", >>> > MessageBoxButtons.OK, MessageBoxIcon.Error) >>> > If (strCstr_ToUse <> "None Given") Then >>> > Connection.ConnectionString = strCstr_ToUse >>> > Else >>> > Connection.ConnectionString = >>> > My.Forms.USAGoldCorp.msConnectionStrToUse >>> > End If >>> > ' MessageBox.Show("Here I am!", "Cstr Set", >>> > MessageBoxButtons.OK, MessageBoxIcon.Error) >>> > ' MessageBox.Show(Connection.ConnectionString.ToString, >>> > "Cstr >>> > Is", MessageBoxButtons.OK, MessageBoxIcon.Error) >>> > >>> > Connection.Open() >>> > ' MessageBox.Show("Here I am!", "Connection opened", >>> > MessageBoxButtons.OK, MessageBoxIcon.Error) >>> > Dim cmdRequest As New SqlCommand >>> > With cmdRequest >>> > .CommandText = strSQL >>> > .CommandType = Data.CommandType.Text >>> > .Connection = Connection >>> > .CommandTimeout = 60000 '3400000 >>> > End With >>> > ' MessageBox.Show("Here I am!", "set Request Obj", >>> > MessageBoxButtons.OK, MessageBoxIcon.Error) >>> > Select Case strCmdType >>> > Case "CMD" >>> > cmdRequest.ExecuteNonQuery() >>> > strResult = "True" >>> > Case "QUERY" >>> > strResult = "" >>> > strResult = cmdRequest.ExecuteScalar() >>> > If (strResult = Nothing) Then >>> > With Connection >>> > .Close() >>> > .Dispose() >>> > End With >>> > Else >>> > strResult = "True" >>> > End If >>> > Case "RESULT" >>> > strResult = "" >>> > ' MessageBox.Show("Here I am!", "About to >>> > execute SQL", MessageBoxButtons.OK, MessageBoxIcon.Error) >>> > strResult = cmdRequest.ExecuteScalar() >>> > ' MessageBox.Show("Here I am!", "done >>> > Execution >>> > SQL", MessageBoxButtons.OK, MessageBoxIcon.Error) >>> > If (strResult = Nothing) Then >>> > strResult = "False" >>> > Else >>> > End If >>> > End Select >>> > Non_error1: >>> > strAnswer = strResult >>> > >>> > Catch ex As Exception >>> > MessageBox.Show(ex.Message, "Db connection Error", >>> > MessageBoxButtons.OK, MessageBoxIcon.Error) >>> > >>> > strResult = "False" >>> > RecordErrorMsg(ex.Message, My.Application.Info.Title, >>> > My.Application.Info.Version.ToString, strWhoAmI, strWhoCalledMe, ("CMD >>> > Type >>> > was: " & strCmdType & Environment.NewLine), strSQL, >>> > My.Forms.USAGoldCorp.msUserID) >>> > End Try >>> > With Connection >>> > If (Connection.State = Data.ConnectionState.Open) Then >>> > .Close() >>> > End If >>> > .Dispose() >>> > End With >>> > DBQuery = strAnswer >>> > ' MessageBox.Show("Here I am!", "Leaving DbQuery", >>> > MessageBoxButtons.OK, MessageBoxIcon.Error) >>> > End Function >>> > </code> >>> > -- >>> > Deasun >>> > Home Site: www.tirnaog.com >>> > Check out: The Code Vault in my forums section. >>> > >>> > >>> > "Patrice" wrote: >>> > >>> >> Also make sure you don't hide the exception rather than revealing it. >>> >> An >>> >> agressive firewall perhaps ? >>> >> >>> >> Seeing some code could also help to understand if it is a coding >>> >> issue >>> >> (just >>> >> the minimal amount of lines needed to repro the issue). >>> >> >>> >> -- >>> >> Patrice >>> >> >>> >> "sloan" <sl***@ipass.net> a écrit dans le message de groupe de >>> >> discussion >>> >> : >>> >> #mBXtFl#JHA.3***@TK2MSFTNGP04.phx.gbl... >>> >> > >>> >> > There is a connection timeout property. >>> >> > You can set it. Default is 30 seconds? Not sure, look at >>> >> > documentation. >>> >> > >>> >> > Connection Timeout is the "Try to find this server for this long >>> >> > value". >>> >> > >>> >> > There is also a seperate CommandTimeout property...on the >>> >> > SqlCommand >>> >> > object. >>> >> > I point it out because sometimes people get the two confused. >>> >> > >>> >> > ........... >>> >> > >>> >> > If you want "clean" DataAccessLayer (Helper) code, then I'd suggest >>> >> > getting the EnterpriseLibrary (.Data) 3.1 or 4.0 (or 4.1 I guess >>> >> > now). >>> >> > It will simplify your DataAccess and implement many many best >>> >> > practices. >>> >> > >>> >> > http://msdn.microsoft.com/en-us/library/cc467894.aspx >>> >> > >>> >> > >>> >> > >>> >> > >>> >> > >>> >> > "Deasun" <Dea***@discussions.microsoft.com> wrote in message >>> >> > news:63767C4E-B959-4CD7-BB95-DCDDA2E8C365@microsoft.com... >>> >> >> Morning, >>> >> >> >>> >> >> I have a very strange probelm. >>> >> >> In my code i set the SQLConnection objects connections str. >>> >> >> Then I do Connection.open. >>> >> >> >>> >> >> The app never comes back or errors out when I know there is no >>> >> >> connection. >>> >> >> How long is the attempt suppose to take. >>> >> >> >>> >> >> Any ideas as to why it would just be hanging there. >>> >> >> I have the enter code wrapped in a try catch and it never gets >>> >> >> triggered >>> >> >> either. >>> >> >> And it never gets to next line in code. >>> >> >> >>> >> >> Thanks >>> >> >> Deasun >>> >> >> -- >>> >> >> Deasun >>> >> >> Home Site: www.tirnaog.com >>> >> >> Check out: The Code Vault in my forums section. >>> >> > >>> >> > >>> >> >>> >> >>> >>> >>> > I'm just beginning to learn MS SQL. Why do you say that a method that has
SqlCommand in it shoudn't know what a MessageBox is? Thanks, Bob Show quoteHide quote "sloan" <sl***@ipass.net> wrote in message news:uEL1yUn%23JHA.3320@TK2MSFTNGP04.phx.gbl... > > > That is some of the ugliest code I've seen in 10 years. Its spaghetti'ed > together (Presentation and DAL) code. A method that has SqlCommand in it > shouldn't know what a MessageBox is. > > I am trying to say it gently, but at the same time, it needs to be said. > > Youch. You've got bigger troubles than not being able to locate a > sqlserver. > > ................... > > Here is a decent sample. > http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!139.entry > It uses DAAB 2.0. > > Here is a slightly updated version: > http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry > It uses EnterpriseLibrary.Data 3.1. > > > Try to find the "bird's eye view" article I mention at the first URL. Its > a Microsoft article. Its a little dated, but very useful. > > > > "Deasun" <Dea***@discussions.microsoft.com> wrote in message > news:1468E8D3-33FE-4F5B-AAD4-FF43F814EDC6@microsoft.com... >> Heres the current code base: >> <code> >> Public Function DBQuery(ByVal strCmdType As String, ByVal strSQL As >> String, >> ByVal strWhoCalledMe As String, Optional ByVal strCstr_ToUse As String = >> "None Given") As String >> Dim strWhoAmI As String = My.Application.Info.Title & >> ".clsCommonTools.DBQuery" >> Dim strAnswer As String = "False" >> Dim strSQLCmdWas As String = strSQL >> Dim strResult As String = "" >> Dim Connection As New SqlConnection >> '#### Execute Query #### >> Try >> ' MessageBox.Show("Here I am!", "just entered DbQuery", >> MessageBoxButtons.OK, MessageBoxIcon.Error) >> If (strCstr_ToUse <> "None Given") Then >> Connection.ConnectionString = strCstr_ToUse >> Else >> Connection.ConnectionString = >> My.Forms.USAGoldCorp.msConnectionStrToUse >> End If >> ' MessageBox.Show("Here I am!", "Cstr Set", >> MessageBoxButtons.OK, MessageBoxIcon.Error) >> ' MessageBox.Show(Connection.ConnectionString.ToString, >> "Cstr >> Is", MessageBoxButtons.OK, MessageBoxIcon.Error) >> >> Connection.Open() >> ' MessageBox.Show("Here I am!", "Connection opened", >> MessageBoxButtons.OK, MessageBoxIcon.Error) >> Dim cmdRequest As New SqlCommand >> With cmdRequest >> .CommandText = strSQL >> .CommandType = Data.CommandType.Text >> .Connection = Connection >> .CommandTimeout = 60000 '3400000 >> End With >> ' MessageBox.Show("Here I am!", "set Request Obj", >> MessageBoxButtons.OK, MessageBoxIcon.Error) >> Select Case strCmdType >> Case "CMD" >> cmdRequest.ExecuteNonQuery() >> strResult = "True" >> Case "QUERY" >> strResult = "" >> strResult = cmdRequest.ExecuteScalar() >> If (strResult = Nothing) Then >> With Connection >> .Close() >> .Dispose() >> End With >> Else >> strResult = "True" >> End If >> Case "RESULT" >> strResult = "" >> ' MessageBox.Show("Here I am!", "About to >> execute SQL", MessageBoxButtons.OK, MessageBoxIcon.Error) >> strResult = cmdRequest.ExecuteScalar() >> ' MessageBox.Show("Here I am!", "done Execution >> SQL", MessageBoxButtons.OK, MessageBoxIcon.Error) >> If (strResult = Nothing) Then >> strResult = "False" >> Else >> End If >> End Select >> Non_error1: >> strAnswer = strResult >> >> Catch ex As Exception >> MessageBox.Show(ex.Message, "Db connection Error", >> MessageBoxButtons.OK, MessageBoxIcon.Error) >> >> strResult = "False" >> RecordErrorMsg(ex.Message, My.Application.Info.Title, >> My.Application.Info.Version.ToString, strWhoAmI, strWhoCalledMe, ("CMD >> Type >> was: " & strCmdType & Environment.NewLine), strSQL, >> My.Forms.USAGoldCorp.msUserID) >> End Try >> With Connection >> If (Connection.State = Data.ConnectionState.Open) Then >> .Close() >> End If >> .Dispose() >> End With >> DBQuery = strAnswer >> ' MessageBox.Show("Here I am!", "Leaving DbQuery", >> MessageBoxButtons.OK, MessageBoxIcon.Error) >> End Function >> </code> >> -- >> Deasun >> Home Site: www.tirnaog.com >> Check out: The Code Vault in my forums section. >> >> >> "Patrice" wrote: >> >>> Also make sure you don't hide the exception rather than revealing it. An >>> agressive firewall perhaps ? >>> >>> Seeing some code could also help to understand if it is a coding issue >>> (just >>> the minimal amount of lines needed to repro the issue). >>> >>> -- >>> Patrice >>> >>> "sloan" <sl***@ipass.net> a écrit dans le message de groupe de >>> discussion : >>> #mBXtFl#JHA.3***@TK2MSFTNGP04.phx.gbl... >>> > >>> > There is a connection timeout property. >>> > You can set it. Default is 30 seconds? Not sure, look at >>> > documentation. >>> > >>> > Connection Timeout is the "Try to find this server for this long >>> > value". >>> > >>> > There is also a seperate CommandTimeout property...on the SqlCommand >>> > object. >>> > I point it out because sometimes people get the two confused. >>> > >>> > ........... >>> > >>> > If you want "clean" DataAccessLayer (Helper) code, then I'd suggest >>> > getting the EnterpriseLibrary (.Data) 3.1 or 4.0 (or 4.1 I guess now). >>> > It will simplify your DataAccess and implement many many best >>> > practices. >>> > >>> > http://msdn.microsoft.com/en-us/library/cc467894.aspx >>> > >>> > >>> > >>> > >>> > >>> > "Deasun" <Dea***@discussions.microsoft.com> wrote in message >>> > news:63767C4E-B959-4CD7-BB95-DCDDA2E8C365@microsoft.com... >>> >> Morning, >>> >> >>> >> I have a very strange probelm. >>> >> In my code i set the SQLConnection objects connections str. >>> >> Then I do Connection.open. >>> >> >>> >> The app never comes back or errors out when I know there is no >>> >> connection. >>> >> How long is the attempt suppose to take. >>> >> >>> >> Any ideas as to why it would just be hanging there. >>> >> I have the enter code wrapped in a try catch and it never gets >>> >> triggered >>> >> either. >>> >> And it never gets to next line in code. >>> >> >>> >> Thanks >>> >> Deasun >>> >> -- >>> >> Deasun >>> >> Home Site: www.tirnaog.com >>> >> Check out: The Code Vault in my forums section. >>> > >>> > >>> >>> > > "eBob.com" <faken***@totallybogus.com> wrote in message Separation of duties. Ideally your presentation code (messageboxes, etc.) news:uOMlvlq%23JHA.2824@TK2MSFTNGP03.phx.gbl... > I'm just beginning to learn MS SQL. Why do you say that a method that has > SqlCommand in it shoudn't know what a MessageBox is? > > Thanks, Bob should be separate from your business logic and back-end code (connecting to the database, etc.) When you combine tiers you can introduce really strange and intricate dependencies in the code (even by accident), your code becomes harder to maintain, and of course it's not pretty :) -- ======== Michael Coles "Pro T-SQL 2008 Programmer's Guide" http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X What does your connection string look like?
-- Show quoteHide quote======== Michael Coles "Pro T-SQL 2008 Programmer's Guide" http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X "Deasun" <Dea***@discussions.microsoft.com> wrote in message news:1468E8D3-33FE-4F5B-AAD4-FF43F814EDC6@microsoft.com... > Heres the current code base: > <code> > Public Function DBQuery(ByVal strCmdType As String, ByVal strSQL As > String, > ByVal strWhoCalledMe As String, Optional ByVal strCstr_ToUse As String = > "None Given") As String > Dim strWhoAmI As String = My.Application.Info.Title & > ".clsCommonTools.DBQuery" > Dim strAnswer As String = "False" > Dim strSQLCmdWas As String = strSQL > Dim strResult As String = "" > Dim Connection As New SqlConnection > '#### Execute Query #### > Try > ' MessageBox.Show("Here I am!", "just entered DbQuery", > MessageBoxButtons.OK, MessageBoxIcon.Error) > If (strCstr_ToUse <> "None Given") Then > Connection.ConnectionString = strCstr_ToUse > Else > Connection.ConnectionString = > My.Forms.USAGoldCorp.msConnectionStrToUse > End If > ' MessageBox.Show("Here I am!", "Cstr Set", > MessageBoxButtons.OK, MessageBoxIcon.Error) > ' MessageBox.Show(Connection.ConnectionString.ToString, > "Cstr > Is", MessageBoxButtons.OK, MessageBoxIcon.Error) > > Connection.Open() > ' MessageBox.Show("Here I am!", "Connection opened", > MessageBoxButtons.OK, MessageBoxIcon.Error) > Dim cmdRequest As New SqlCommand > With cmdRequest > .CommandText = strSQL > .CommandType = Data.CommandType.Text > .Connection = Connection > .CommandTimeout = 60000 '3400000 > End With > ' MessageBox.Show("Here I am!", "set Request Obj", > MessageBoxButtons.OK, MessageBoxIcon.Error) > Select Case strCmdType > Case "CMD" > cmdRequest.ExecuteNonQuery() > strResult = "True" > Case "QUERY" > strResult = "" > strResult = cmdRequest.ExecuteScalar() > If (strResult = Nothing) Then > With Connection > .Close() > .Dispose() > End With > Else > strResult = "True" > End If > Case "RESULT" > strResult = "" > ' MessageBox.Show("Here I am!", "About to > execute SQL", MessageBoxButtons.OK, MessageBoxIcon.Error) > strResult = cmdRequest.ExecuteScalar() > ' MessageBox.Show("Here I am!", "done Execution > SQL", MessageBoxButtons.OK, MessageBoxIcon.Error) > If (strResult = Nothing) Then > strResult = "False" > Else > End If > End Select > Non_error1: > strAnswer = strResult > > Catch ex As Exception > MessageBox.Show(ex.Message, "Db connection Error", > MessageBoxButtons.OK, MessageBoxIcon.Error) > > strResult = "False" > RecordErrorMsg(ex.Message, My.Application.Info.Title, > My.Application.Info.Version.ToString, strWhoAmI, strWhoCalledMe, ("CMD > Type > was: " & strCmdType & Environment.NewLine), strSQL, > My.Forms.USAGoldCorp.msUserID) > End Try > With Connection > If (Connection.State = Data.ConnectionState.Open) Then > .Close() > End If > .Dispose() > End With > DBQuery = strAnswer > ' MessageBox.Show("Here I am!", "Leaving DbQuery", > MessageBoxButtons.OK, MessageBoxIcon.Error) > End Function > </code> > -- > Deasun > Home Site: www.tirnaog.com > Check out: The Code Vault in my forums section. > > > "Patrice" wrote: > >> Also make sure you don't hide the exception rather than revealing it. An >> agressive firewall perhaps ? >> >> Seeing some code could also help to understand if it is a coding issue >> (just >> the minimal amount of lines needed to repro the issue). >> >> -- >> Patrice >> >> "sloan" <sl***@ipass.net> a écrit dans le message de groupe de discussion >> : >> #mBXtFl#JHA.3***@TK2MSFTNGP04.phx.gbl... >> > >> > There is a connection timeout property. >> > You can set it. Default is 30 seconds? Not sure, look at >> > documentation. >> > >> > Connection Timeout is the "Try to find this server for this long >> > value". >> > >> > There is also a seperate CommandTimeout property...on the SqlCommand >> > object. >> > I point it out because sometimes people get the two confused. >> > >> > ........... >> > >> > If you want "clean" DataAccessLayer (Helper) code, then I'd suggest >> > getting the EnterpriseLibrary (.Data) 3.1 or 4.0 (or 4.1 I guess now). >> > It will simplify your DataAccess and implement many many best >> > practices. >> > >> > http://msdn.microsoft.com/en-us/library/cc467894.aspx >> > >> > >> > >> > >> > >> > "Deasun" <Dea***@discussions.microsoft.com> wrote in message >> > news:63767C4E-B959-4CD7-BB95-DCDDA2E8C365@microsoft.com... >> >> Morning, >> >> >> >> I have a very strange probelm. >> >> In my code i set the SQLConnection objects connections str. >> >> Then I do Connection.open. >> >> >> >> The app never comes back or errors out when I know there is no >> >> connection. >> >> How long is the attempt suppose to take. >> >> >> >> Any ideas as to why it would just be hanging there. >> >> I have the enter code wrapped in a try catch and it never gets >> >> triggered >> >> either. >> >> And it never gets to next line in code. >> >> >> >> Thanks >> >> Deasun >> >> -- >> >> Deasun >> >> Home Site: www.tirnaog.com >> >> Check out: The Code Vault in my forums section. >> > >> > >> >> If strCmdType is not one of the intended value nothing will happen (do do
you step into the code with debugger or do you you see what happens and think it never connected ?). You may want to add a else clause and thrwo an exception there as a best practice... This is not what I had in mind when talling about the minimal amount of code to repro the problem. I was talking about doing another application with all and only the code needed to show the problem such as : Connection.ConnectionString = "Something we can read" Connection.Open() Dim cmdRequest As New SqlCommand With cmdRequest .CommandText = strSQL .CommandType = "Something we can read" .Connection = Connection '.CommandTimeout = 60000 '3400000 Don"t care for no End With cmdRequest.ExecuteNonQuery() Stop ' Or we'll put a breakkpoint... Then : - if it doesn't wor you can post this and someone will hopefully what goes wrong - if it works, then you can help yourself and go further reintroducing some elements from your standard code but you know that technically it works and that your code is the cullprit. Ultimately you find yourself what goes wrong or you can post the minimal amount of code needed so that other can repro the problem. -- Patrice "Deasun" <Dea***@discussions.microsoft.com> a écrit dans le message de groupe de discussion : 1468E8D3-33FE-4F5B-AAD4-FF43F814E***@microsoft.com...Show quoteHide quote > Heres the current code base: > <code> > Public Function DBQuery(ByVal strCmdType As String, ByVal strSQL As > String, > ByVal strWhoCalledMe As String, Optional ByVal strCstr_ToUse As String = > "None Given") As String > Dim strWhoAmI As String = My.Application.Info.Title & > ".clsCommonTools.DBQuery" > Dim strAnswer As String = "False" > Dim strSQLCmdWas As String = strSQL > Dim strResult As String = "" > Dim Connection As New SqlConnection > '#### Execute Query #### > Try > ' MessageBox.Show("Here I am!", "just entered DbQuery", > MessageBoxButtons.OK, MessageBoxIcon.Error) > If (strCstr_ToUse <> "None Given") Then > Connection.ConnectionString = strCstr_ToUse > Else > Connection.ConnectionString = > My.Forms.USAGoldCorp.msConnectionStrToUse > End If > ' MessageBox.Show("Here I am!", "Cstr Set", > MessageBoxButtons.OK, MessageBoxIcon.Error) > ' MessageBox.Show(Connection.ConnectionString.ToString, > "Cstr > Is", MessageBoxButtons.OK, MessageBoxIcon.Error) > > Connection.Open() > ' MessageBox.Show("Here I am!", "Connection opened", > MessageBoxButtons.OK, MessageBoxIcon.Error) > Dim cmdRequest As New SqlCommand > With cmdRequest > .CommandText = strSQL > .CommandType = Data.CommandType.Text > .Connection = Connection > .CommandTimeout = 60000 '3400000 > End With > ' MessageBox.Show("Here I am!", "set Request Obj", > MessageBoxButtons.OK, MessageBoxIcon.Error) > Select Case strCmdType > Case "CMD" > cmdRequest.ExecuteNonQuery() > strResult = "True" > Case "QUERY" > strResult = "" > strResult = cmdRequest.ExecuteScalar() > If (strResult = Nothing) Then > With Connection > .Close() > .Dispose() > End With > Else > strResult = "True" > End If > Case "RESULT" > strResult = "" > ' MessageBox.Show("Here I am!", "About to > execute SQL", MessageBoxButtons.OK, MessageBoxIcon.Error) > strResult = cmdRequest.ExecuteScalar() > ' MessageBox.Show("Here I am!", "done Execution > SQL", MessageBoxButtons.OK, MessageBoxIcon.Error) > If (strResult = Nothing) Then > strResult = "False" > Else > End If > End Select > Non_error1: > strAnswer = strResult > > Catch ex As Exception > MessageBox.Show(ex.Message, "Db connection Error", > MessageBoxButtons.OK, MessageBoxIcon.Error) > > strResult = "False" > RecordErrorMsg(ex.Message, My.Application.Info.Title, > My.Application.Info.Version.ToString, strWhoAmI, strWhoCalledMe, ("CMD > Type > was: " & strCmdType & Environment.NewLine), strSQL, > My.Forms.USAGoldCorp.msUserID) > End Try > With Connection > If (Connection.State = Data.ConnectionState.Open) Then > .Close() > End If > .Dispose() > End With > DBQuery = strAnswer > ' MessageBox.Show("Here I am!", "Leaving DbQuery", > MessageBoxButtons.OK, MessageBoxIcon.Error) > End Function > </code> > -- > Deasun > Home Site: www.tirnaog.com > Check out: The Code Vault in my forums section. > > > "Patrice" wrote: > >> Also make sure you don't hide the exception rather than revealing it. An >> agressive firewall perhaps ? >> >> Seeing some code could also help to understand if it is a coding issue >> (just >> the minimal amount of lines needed to repro the issue). >> >> -- >> Patrice >> >> "sloan" <sl***@ipass.net> a écrit dans le message de groupe de discussion >> : >> #mBXtFl#JHA.3***@TK2MSFTNGP04.phx.gbl... >> > >> > There is a connection timeout property. >> > You can set it. Default is 30 seconds? Not sure, look at >> > documentation. >> > >> > Connection Timeout is the "Try to find this server for this long >> > value". >> > >> > There is also a seperate CommandTimeout property...on the SqlCommand >> > object. >> > I point it out because sometimes people get the two confused. >> > >> > ........... >> > >> > If you want "clean" DataAccessLayer (Helper) code, then I'd suggest >> > getting the EnterpriseLibrary (.Data) 3.1 or 4.0 (or 4.1 I guess now). >> > It will simplify your DataAccess and implement many many best >> > practices. >> > >> > http://msdn.microsoft.com/en-us/library/cc467894.aspx >> > >> > >> > >> > >> > >> > "Deasun" <Dea***@discussions.microsoft.com> wrote in message >> > news:63767C4E-B959-4CD7-BB95-DCDDA2E8C365@microsoft.com... >> >> Morning, >> >> >> >> I have a very strange probelm. >> >> In my code i set the SQLConnection objects connections str. >> >> Then I do Connection.open. >> >> >> >> The app never comes back or errors out when I know there is no >> >> connection. >> >> How long is the attempt suppose to take. >> >> >> >> Any ideas as to why it would just be hanging there. >> >> I have the enter code wrapped in a try catch and it never gets >> >> triggered >> >> either. >> >> And it never gets to next line in code. >> >> >> >> Thanks >> >> Deasun >> >> -- >> >> Deasun >> >> Home Site: www.tirnaog.com >> >> Check out: The Code Vault in my forums section. >> > >> > >> >> 1] the Messagbox thingy:
Its in here only for degugging on the netbook, as on dev PC the connection works fine. 2] the Hang happens on the Connection.open command. This I know because the messagebox after it never happens and the try-catch errror never comes off either. I will try a new function bared down to only the nesscessary code to see if that works. I should mention that this current function has worked fine for me. its just al of a sudden doing this hanging thing. Thanks for all the replies. Show quoteHide quote "Patrice" wrote: > If strCmdType is not one of the intended value nothing will happen (do do > you step into the code with debugger or do you you see what happens and > think it never connected ?). You may want to add a else clause and thrwo an > exception there as a best practice... > > This is not what I had in mind when talling about the minimal amount of code > to repro the problem. I was talking about doing another application with all > and only the code needed to show the problem such as : > > Connection.ConnectionString = "Something we can read" > Connection.Open() > Dim cmdRequest As New SqlCommand > With cmdRequest > .CommandText = strSQL > .CommandType = "Something we can read" > .Connection = Connection > '.CommandTimeout = 60000 '3400000 Don"t care for no > End With > cmdRequest.ExecuteNonQuery() > Stop ' Or we'll put a breakkpoint... > > Then : > - if it doesn't wor you can post this and someone will hopefully what goes > wrong > - if it works, then you can help yourself and go further reintroducing some > elements from your standard code but you know that technically it works and > that your code is the cullprit. Ultimately you find yourself what goes wrong > or you can post the minimal amount of code needed so that other can repro > the problem. > > -- > Patrice > > > "Deasun" <Dea***@discussions.microsoft.com> a écrit dans le message de > groupe de discussion : 1468E8D3-33FE-4F5B-AAD4-FF43F814E***@microsoft.com... > > Heres the current code base: > > <code> > > Public Function DBQuery(ByVal strCmdType As String, ByVal strSQL As > > String, > > ByVal strWhoCalledMe As String, Optional ByVal strCstr_ToUse As String = > > "None Given") As String > > Dim strWhoAmI As String = My.Application.Info.Title & > > ".clsCommonTools.DBQuery" > > Dim strAnswer As String = "False" > > Dim strSQLCmdWas As String = strSQL > > Dim strResult As String = "" > > Dim Connection As New SqlConnection > > '#### Execute Query #### > > Try > > ' MessageBox.Show("Here I am!", "just entered DbQuery", > > MessageBoxButtons.OK, MessageBoxIcon.Error) > > If (strCstr_ToUse <> "None Given") Then > > Connection.ConnectionString = strCstr_ToUse > > Else > > Connection.ConnectionString = > > My.Forms.USAGoldCorp.msConnectionStrToUse > > End If > > ' MessageBox.Show("Here I am!", "Cstr Set", > > MessageBoxButtons.OK, MessageBoxIcon.Error) > > ' MessageBox.Show(Connection.ConnectionString.ToString, > > "Cstr > > Is", MessageBoxButtons.OK, MessageBoxIcon.Error) > > > > Connection.Open() > > ' MessageBox.Show("Here I am!", "Connection opened", > > MessageBoxButtons.OK, MessageBoxIcon.Error) > > Dim cmdRequest As New SqlCommand > > With cmdRequest > > .CommandText = strSQL > > .CommandType = Data.CommandType.Text > > .Connection = Connection > > .CommandTimeout = 60000 '3400000 > > End With > > ' MessageBox.Show("Here I am!", "set Request Obj", > > MessageBoxButtons.OK, MessageBoxIcon.Error) > > Select Case strCmdType > > Case "CMD" > > cmdRequest.ExecuteNonQuery() > > strResult = "True" > > Case "QUERY" > > strResult = "" > > strResult = cmdRequest.ExecuteScalar() > > If (strResult = Nothing) Then > > With Connection > > .Close() > > .Dispose() > > End With > > Else > > strResult = "True" > > End If > > Case "RESULT" > > strResult = "" > > ' MessageBox.Show("Here I am!", "About to > > execute SQL", MessageBoxButtons.OK, MessageBoxIcon.Error) > > strResult = cmdRequest.ExecuteScalar() > > ' MessageBox.Show("Here I am!", "done Execution > > SQL", MessageBoxButtons.OK, MessageBoxIcon.Error) > > If (strResult = Nothing) Then > > strResult = "False" > > Else > > End If > > End Select > > Non_error1: > > strAnswer = strResult > > > > Catch ex As Exception > > MessageBox.Show(ex.Message, "Db connection Error", > > MessageBoxButtons.OK, MessageBoxIcon.Error) > > > > strResult = "False" > > RecordErrorMsg(ex.Message, My.Application.Info.Title, > > My.Application.Info.Version.ToString, strWhoAmI, strWhoCalledMe, ("CMD > > Type > > was: " & strCmdType & Environment.NewLine), strSQL, > > My.Forms.USAGoldCorp.msUserID) > > End Try > > With Connection > > If (Connection.State = Data.ConnectionState.Open) Then > > .Close() > > End If > > .Dispose() > > End With > > DBQuery = strAnswer > > ' MessageBox.Show("Here I am!", "Leaving DbQuery", > > MessageBoxButtons.OK, MessageBoxIcon.Error) > > End Function > > </code> > > -- > > Deasun > > Home Site: www.tirnaog.com > > Check out: The Code Vault in my forums section. > > > > > > "Patrice" wrote: > > > >> Also make sure you don't hide the exception rather than revealing it. An > >> agressive firewall perhaps ? > >> > >> Seeing some code could also help to understand if it is a coding issue > >> (just > >> the minimal amount of lines needed to repro the issue). > >> > >> -- > >> Patrice > >> > >> "sloan" <sl***@ipass.net> a écrit dans le message de groupe de discussion > >> : > >> #mBXtFl#JHA.3***@TK2MSFTNGP04.phx.gbl... > >> > > >> > There is a connection timeout property. > >> > You can set it. Default is 30 seconds? Not sure, look at > >> > documentation. > >> > > >> > Connection Timeout is the "Try to find this server for this long > >> > value". > >> > > >> > There is also a seperate CommandTimeout property...on the SqlCommand > >> > object. > >> > I point it out because sometimes people get the two confused. > >> > > >> > ........... > >> > > >> > If you want "clean" DataAccessLayer (Helper) code, then I'd suggest > >> > getting the EnterpriseLibrary (.Data) 3.1 or 4.0 (or 4.1 I guess now). > >> > It will simplify your DataAccess and implement many many best > >> > practices. > >> > > >> > http://msdn.microsoft.com/en-us/library/cc467894.aspx > >> > > >> > > >> > > >> > > >> > > >> > "Deasun" <Dea***@discussions.microsoft.com> wrote in message > >> > news:63767C4E-B959-4CD7-BB95-DCDDA2E8C365@microsoft.com... > >> >> Morning, > >> >> > >> >> I have a very strange probelm. > >> >> In my code i set the SQLConnection objects connections str. > >> >> Then I do Connection.open. > >> >> > >> >> The app never comes back or errors out when I know there is no > >> >> connection. > >> >> How long is the attempt suppose to take. > >> >> > >> >> Any ideas as to why it would just be hanging there. > >> >> I have the enter code wrapped in a try catch and it never gets > >> >> triggered > >> >> either. > >> >> And it never gets to next line in code. > >> >> > >> >> Thanks > >> >> Deasun > >> >> -- > >> >> Deasun > >> >> Home Site: www.tirnaog.com > >> >> Check out: The Code Vault in my forums section. > >> > > >> > > >> > >> > > 
Other interesting topics
Using "Me"
Using WPF with a "normal" windows app Unable to connect to the remote server How to reference class properties using MyClass("PropertyName") VB Net/Com Interoperability/Late Binding [XPost] - Visual Studio 2008 - Instruct IDE Not To Generate Adapter Client Server app using winsock setting a timeout for web service response cancelling a backgroundworker Session Variables in asp.net |
|||||||||||||||||||||||
