[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

open connection

Posted on 2006-05-16
6
Medium Priority
?
180 Views
Last Modified: 2012-05-05
I want to keep the connection open as short as I can. Is below code good enought?


Dim StrConnection As String = "myconnection"

Dim Connection As New SqlConnection(StrConnection)

Dim myCommand As New SqlCommand("SELECT * FROM customers where customerid = @customerid ", Connection)

If Me.txtSearch.Text <> Nothing Then



Try



myCommand.Parameters.Add("@customerid", SqlDbType.NVarChar, 50)

myCommand.Parameters("@customerid").Value = Me.txtSearch.Text

Connection.Open()

Dim myreader As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.SingleRow)

If myreader.HasRows Then

myreader.Read()

Me.txtAddress.Text = myreader.Item("address").ToString

Me.txtCity.Text = myreader.Item("city").ToString

Me.txtState.Text = myreader.Item("country").ToString

Me.txtZip.Text = myreader.Item("postalcode").ToString

Me.RichTextBox1.Text = myreader.Item("phone").ToString

Else



MessageBox.Show("No result is found", "result", MessageBoxButtons.OK, MessageBoxIcon.Information)


End If

Connection.Close()



Catch ex As SyntaxErrorException

MsgBox(ex.ToString)



End Try

Else

MessageBox.Show("Please Enter Trannum", "Empty field", _

MessageBoxButtons.OK, MessageBoxIcon.Information)



End If




0
Comment
Question by:VBdotnet2005
  • 4
  • 2
6 Comments
 
LVL 5

Expert Comment

by:mydasx
ID: 16695511
ok couple of things.

you should put your close statement in the finally block, which means creating the connection object outside of the try, you dont have to instatiate it, but as it is now, if you throw an excpeption, the connection will stay open.  :(  Past that, if you are using vb.net 2.0 you can use a using statement which will automatically call the dispose method of the connection object.

Like so...

Using conn As New SqlConnection(dsn)
  Using cmd As New SqlCommand("SELECT * FROM Employees", conn)
    conn.Open()
      Using rdr As SqlDataReader = cmd.ExecuteReader()
        While rdr.Read()
          Console.WriteLine(rdr(0))
        End While
      End Using
  End Using
End Using
0
 

Author Comment

by:VBdotnet2005
ID: 16699676
should I do this  then?

...





Catch ex As SyntaxErrorException

MsgBox(ex.ToString)



End Try



Connection.Close()





or



...



Connection.Close()



Catch ex As SyntaxErrorException

MsgBox(ex.ToString)



Connection.Close()



End Try
0
 
LVL 5

Expert Comment

by:mydasx
ID: 16700894
Dim StrConnection As String = "myconnection"

Dim Connection As New SqlConnection(StrConnection)


Try
    myCommand.Parameters.Add("@customerid", SqlDbType.NVarChar, 50)
    myCommand.Parameters("@customerid").Value = Me.txtSearch.Text
    Connection.Open()
    Dim myreader As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.SingleRow)

    If myreader.HasRows Then
        myreader.Read()
        Me.txtAddress.Text = myreader.Item("address").ToString
        Me.txtCity.Text = myreader.Item("city").ToString
        Me.txtState.Text = myreader.Item("country").ToString
        Me.txtZip.Text = myreader.Item("postalcode").ToString
        Me.RichTextBox1.Text = myreader.Item("phone").ToString
    Else
        MessageBox.Show("No result is found", "result", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End If

Catch ex As SyntaxErrorException
    MsgBox(ex.ToString)
Finally
    Connection.Close()
End Try
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 5

Accepted Solution

by:
mydasx earned 2000 total points
ID: 16700916
The finally block gets called no matter what happens...  NO MATTER WHAT.  When writing code that uses external IO, it is essential that you mind your connections, to these resources.  By putting your close, or dispose in the finally block you can rest assured that your connections will always be closed error or no error.

mydasx
0
 
LVL 5

Expert Comment

by:mydasx
ID: 16719639
Points have not been allocated if this is indeed the correct answer.
0
 

Author Comment

by:VBdotnet2005
ID: 16723132
Sorry about that. I just have time to view your comment. I am fully satisfied with the answer. Thank you
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question