Database Connection Designer Variables

[jimmy]

Hi,

I am working on a database application and have used the database
connection wizard to connect to an SQL database. I now need to
reference the connection variable (cnn) from another form in the
program and i cannot do this. How can i make the variable global or
somehow pass the variable to the form?

Thanks in advance

James

 

[RobinS]

Assuming VS2005:

Store the connection string in your project Settings:

Double-click on "My Project", go to the Settings tab.

Put in a name (anything you like, but hopefully something
that makes sense).

Set the type to "(ConnectionString)".

Set the scope to Application.

Put the connection string in the value field.

Save it.

Now you can use it anywhere in your project as
My.Settings.theConnectionStringName.

Robin S.

 

[jimmy]

Thanks alot for that, that should come in really useful in my
application. I never knew about these settings! Just out of interest is
there any way i can store the actual connection itself in there?

I have made a new variable and set its type to
System.Data.SqlClient.SqlConnection however i dont know what to set its
value to? Can i make a connection this way or do i need to create a new
connection each time using the connection string?

Thanks

James

 

[RobinS]

I don't think so. You should close your connection
when you're done with it. It's not exactly difficult
to open one.

I'm not sure what you mean by this:

I have made a new variable and set its type to
System.Data.SqlClient.SqlConnection however i dont know
what to set its value to?

You don't really set its value to anything, you just open it.
Here's an example of how to use a connection and command object to fill a
datatable.
This disposes of the connection when it hits the
"End Using".

-------------------
Dim dt as DataTable
Using cnn As New SqlConnection(My.Settings.ProductConnectionString)
cnn.Open()

'define the command
Dim cmd As New SqlCommand
cmd.Connection = cnn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "ProductRetrieveByID_sp"
cmd.Parameters.AddWithValue("@productid", 1)

'define the data adapter and fill the data table
Dim da As New SqlDataAdapter(cmd)
dt = New DataTable
da.Fill(dt)
End Using
return dt

 

[jimmy]

By that i meant that i had created a new setting in My.Settings with a
type of System.Data.SqlClient.SqlConnection however i didnt know what
to set its value to, however i now realise that i dont need to do this
and i can simply create a new connection each time i need one and then
dispose of it after, like you mentioned in your post.

Thanks for all your help

 

[RobinS]

You're welcome. Good luck.
Robin S.