473,327 Members | 2,094 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

how to make this variable available to other subs

djc
I have several subroutines (all inline code) that wind up using the same
database connection object variable. I have been declaring a new variable in
every sub. I just now came to a point where I want to call one sub that will
utilize a database connection from within another sub that is already
utilizing a database connection.

1) Can I just declare this database connection once outside of all
subroutines to make it global (the way I understand you make a var of global
scope) and use it for all my different command objects within the different
subs? And just call the Open and Close methods on it from within the
different subs?

I know that is how to make a global variable and have done it with other
things like simple strings and numbers but I am not sure if a database
connection object variable would have different requirements?

any input is appreciated. Thanks.
Nov 19 '05 #1
7 1853
Yes, you assume correctly that you can just declare outside the subs
and the variables will work within all subs.

An example:
Dim Conn As SqlConnection
Dim cmdSelect As SqlCommand
Dim SelectStr As String

Sub Page_Load(Sender As Object, E As EventArgs)
Conn = New
SqlConnection("server=SQLServer;trusted_connection =true;database=MyDatabase")
If Not IsPostBack Then
BindDataGrid
End IF
End Sub

Sub BindDataGrid
cmdSelect = New SqlCommand( "Select * from Servers ORDER BY ServerName
Asc;", Conn)
Conn.Open()
dgrdServers.DataSource = cmdSelect.ExecuteReader()
dgrdServers.DataBind()
Conn.Close()
End Sub

Nov 19 '05 #2
Alright... the code got messed up, so this time I'm going to hit the
preview button to make sure it's posting correctly.

Dim Conn As SqlConnection
Dim cmdSelect As SqlCommand
Dim SelectStr As String

Sub Page_Load(Sender As Object, E As EventArgs)
Conn = New
SqlConnection("server=SQLServer;trusted_connection =true;database=MyDatabase")
If Not IsPostBack Then
BindDataGrid
End IF
End Sub

Sub BindDataGrid
cmdSelect = New SqlCommand( "Select * from Servers ORDER BY ServerName
Asc;", Conn)
Conn.Open()
dgrdServers.DataSource = cmdSelect.ExecuteReader()
dgrdServers.DataBind()
Conn.Close()
End Sub

Nov 19 '05 #3
djc
Thanks for the reply. In my case the connection string will be the same in
all cases so I would declare the connection object and set the connection
string property too, all outside any subs.

1) do I need to be concerned about the resource usage doing this? for
example, I currently declare the connection, open it, use it, close it, and
then set it equal to Nothing to release it's resources. If I declare outside
all subs then I can't set it to Nothing right? Or am a missing a fundemental
of a page's lifecycle here? Does every post back create a new page with all
new instances of these variables?

2) I just realized another detail: I already have the connection open in the
calling sub. The called sub also needs to open a connection to the database
though. Can the called sub just use the existing open connection from the
calling sub? Would it be better to just have the called sub create it's own
new connection that it opens and closes when it's done (resulting in 2 open
connections to the same database at one time)?

thanks again for the input. Any more is welcome.

"Chad Devine" <su*****@gmail.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
Yes, you assume correctly that you can just declare outside the subs
and the variables will work within all subs.

An example:
Dim Conn As SqlConnection
Dim cmdSelect As SqlCommand
Dim SelectStr As String

Sub Page_Load(Sender As Object, E As EventArgs)
Conn = New
SqlConnection("server=SQLServer;trusted_connection =true;database=MyDatabase"
) If Not IsPostBack Then
BindDataGrid
End IF
End Sub

Sub BindDataGrid
cmdSelect = New SqlCommand( "Select * from Servers ORDER BY ServerName
Asc;", Conn)
Conn.Open()
dgrdServers.DataSource = cmdSelect.ExecuteReader()
dgrdServers.DataBind()
Conn.Close()
End Sub

Nov 19 '05 #4
You won't be able to set the connection property outside of a sub, but
once you set it in say... the page_load sub it will be set publically
througout your <script runat="server"> </script> part of the page.

So you can't do this outside of a sub:
Dim cmdSelect As SqlCommand
cmdSelect = New SqlCommand( "Select * from Servers ORDER BY ServerName
Asc;", Conn)

1) You can still set it to nothing within a sub, that will define the
variable publically thus releasing it's resources. I believe those
resources are negligible as once the page loads it's out of the
server's memory... but I guess you can never be too certain because I'm
not sure if that is a fact. I do know that you can set it to nothing,
and it will be nothing publically within the script tags. I'm pretty
sure every postback creates a new page with all the new instances, but
again I'm not 100%.

2)You can open the connection from a previous sub, or use a previous
connection's instance, for example:

--------------------------------------------------
Dim Conn As SqlConnection
Dim cmdSelect As SqlCommand

Sub Page_Load(Sender As Object, E As EventArgs)
Conn = New
SqlConnection("server=server;trusted_connection=tr ue;database=database")
If Not IsPostBack Then
cmdSelect = New SqlCommand( "Select * from Servers ORDER BY ServerName
Asc;", Conn)

'We open the connection before binding the data to the datagrid.
Conn.Open()
BindDataGrid
End If
End Sub

Sub BindDataGrid
dgrdServers.DataSource = cmdSelect.ExecuteReader()
dgrdServers.DataBind()

'Here the connection closes from the previous sub.
Conn.Close()
End Sub
--------------------------------

As for the rest of your question, it's best to have only one connection
open to the database at one time. So, just use the existing connection
that's open and don't close it until you're done. You can use it in
other subs as long as you make the "Dim Conn As SqlConnection" outside
of the subs.

Hope I got all your questions. :D
Chad

Nov 19 '05 #5
Yes, you can declare the connection globally (I assume you mean somewhere other than
global.aspx) like you would a string or integer and use the open and close methods as
you need the connection. Make sure that you are only attempting to access this
connection in a one-at-the-time fashion. If you attempt to use this connection as
'shared' you will get all kinds of concurrency problems and your prog will go nuts.


"djc" <no***@nowhere.com> wrote in message news:OO**************@TK2MSFTNGP10.phx.gbl...
| I have several subroutines (all inline code) that wind up using the same
| database connection object variable. I have been declaring a new variable in
| every sub. I just now came to a point where I want to call one sub that will
| utilize a database connection from within another sub that is already
| utilizing a database connection.
|
| 1) Can I just declare this database connection once outside of all
| subroutines to make it global (the way I understand you make a var of global
| scope) and use it for all my different command objects within the different
| subs? And just call the Open and Close methods on it from within the
| different subs?
|
| I know that is how to make a global variable and have done it with other
| things like simple strings and numbers but I am not sure if a database
| connection object variable would have different requirements?
|
| any input is appreciated. Thanks.
|
|

Nov 19 '05 #6
djc
Thank you very much Chad. You have been very helpful!

"Chad Devine" <su*****@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
You won't be able to set the connection property outside of a sub, but
once you set it in say... the page_load sub it will be set publically
througout your <script runat="server"> </script> part of the page.

So you can't do this outside of a sub:
Dim cmdSelect As SqlCommand
cmdSelect = New SqlCommand( "Select * from Servers ORDER BY ServerName
Asc;", Conn)

1) You can still set it to nothing within a sub, that will define the
variable publically thus releasing it's resources. I believe those
resources are negligible as once the page loads it's out of the
server's memory... but I guess you can never be too certain because I'm
not sure if that is a fact. I do know that you can set it to nothing,
and it will be nothing publically within the script tags. I'm pretty
sure every postback creates a new page with all the new instances, but
again I'm not 100%.

2)You can open the connection from a previous sub, or use a previous
connection's instance, for example:

--------------------------------------------------
Dim Conn As SqlConnection
Dim cmdSelect As SqlCommand

Sub Page_Load(Sender As Object, E As EventArgs)
Conn = New
SqlConnection("server=server;trusted_connection=tr ue;database=database")
If Not IsPostBack Then
cmdSelect = New SqlCommand( "Select * from Servers ORDER BY ServerName
Asc;", Conn)

'We open the connection before binding the data to the datagrid.
Conn.Open()
BindDataGrid
End If
End Sub

Sub BindDataGrid
dgrdServers.DataSource = cmdSelect.ExecuteReader()
dgrdServers.DataBind()

'Here the connection closes from the previous sub.
Conn.Close()
End Sub
--------------------------------

As for the rest of your question, it's best to have only one connection
open to the database at one time. So, just use the existing connection
that's open and don't close it until you're done. You can use it in
other subs as long as you make the "Dim Conn As SqlConnection" outside
of the subs.

Hope I got all your questions. :D
Chad

Nov 19 '05 #7
djc
Thank you!

"Clamps" <Cl****@RobotMafia.Futurama.com> wrote in message
news:OG**************@TK2MSFTNGP14.phx.gbl...
Yes, you can declare the connection globally (I assume you mean somewhere other than global.aspx) like you would a string or integer and use the open and close methods as you need the connection. Make sure that you are only attempting to access this connection in a one-at-the-time fashion. If you attempt to use this connection as 'shared' you will get all kinds of concurrency problems and your prog will go nuts.



"djc" <no***@nowhere.com> wrote in message news:OO**************@TK2MSFTNGP10.phx.gbl... | I have several subroutines (all inline code) that wind up using the same
| database connection object variable. I have been declaring a new variable in | every sub. I just now came to a point where I want to call one sub that will | utilize a database connection from within another sub that is already
| utilizing a database connection.
|
| 1) Can I just declare this database connection once outside of all
| subroutines to make it global (the way I understand you make a var of global | scope) and use it for all my different command objects within the different | subs? And just call the Open and Close methods on it from within the
| different subs?
|
| I know that is how to make a global variable and have done it with other
| things like simple strings and numbers but I am not sure if a database
| connection object variable would have different requirements?
|
| any input is appreciated. Thanks.
|
|

Nov 19 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: BKM | last post by:
I've been using the following 2 ways to make sure my WebBrowser is finished loading a page before continuing with the next code. do:doevents:loop while WebBrowser.Busy do:doevents:loop until...
1
by: ajay | last post by:
I have following code for a slide menu but i twiked it to work for a single level menu. Open it in a Browser to get a clear picture. I have 2 Qs 1) How to make first entry as non-link. i.e i...
3
by: Lerp | last post by:
hi all, If I have a variable declared outside the page_load sub and all other subs on my page, and I assign a value to that variable within a sub why can't I access this value in the second sub...
1
by: tfs | last post by:
I have a variable I am trying to keep the values in and I have it set as a global variable. <script runat="server"> Dim saveCommandText as String Sub SortDataGrid (sender as Object, e as...
7
by: Aaron | last post by:
Complete code follows. I am new to .NET programming (and programming in general) and I am having a difficult time understanding how to fill a variable in one sub, and then access it from...
3
by: sista via DotNetMonster.com | last post by:
hi.. just wondering what does this error means? here's my vb code: Imports System.Web.Security Imports System.Data Imports System.Data.OleDb Public Class dpitem Inherits System.Web.UI.Page
10
by: Phillip Vong | last post by:
Newbie learning in VB.NET 2. I'm creating a simple ASP.NET 2 page and I pulling a querystring from a link and I want to use this querystring over and over again through out the page. Example....
7
by: Max | last post by:
Please somebody can shed a light... How can I have a variable visible and modifiable, inside one and only webform? I mean , I d like to see that variable from all the Sub of that webform code,...
8
by: Jeff | last post by:
Still new to vb.net in VS2005 web developer... What is the proper/standard way of doing the following - setting the value of a variable in one sub and calling it from another? E.g., as below....
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.