vb.net and Ms SQL Connection ?

[Deasun]

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

 

[sloan]

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

 

[Patrice]

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

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]

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.

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).

 

[sloan]

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]

Ya thks for your comment!
But the messageboxs are in their as a debug attempt at the moment.
Not in there normally.

 

[Cor Ligthert[MVP]]

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

 

[sloan]

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.

 

[eBob.com]

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

 

[Michael Coles]

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

Separation of duties. Ideally your presentation code (messageboxes, etc.)
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

 

[Michael Coles]

What does your connection string look like?

--

========
Michael Coles
"Pro T-SQL 2008 Programmer's Guide"
http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X

 

[Patrice]

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.

 

[Deasun]

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.

 

[kmote]

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.
...................

Tell that to the MS tutorial authors. (e.g. http://support.microsoft.com/kb/316016/)