[RESOLVED] How can I use the same connection string on the same page?

[gjon]

I am currently unable to get a second new connection in the same page without errors unless I use the same connection code. I connect once to validate the user and then I connect once again via a method to get related data. I had to do this because I needed to cycle through a couple columns until there's no more related data. This is just how I had to do it to get it to work(newbie code). But anyhow, my question is, how can I use the same connection to the database to requery it without recreating it from scratch again? Is that possible?


Here is the first connection:

public void loadFamHist()
{
//Define database connection
SqlConnection conn = new SqlConnection(
"Server=localhost\\SqlExpress;Database=MyDB;" + "Integrated Security=True");
//Create command
SqlCommand comm = new SqlCommand("SELECT * FROM UsrTbl, RelativeTable, IP_Table WHERE RelativeTable.UserID IN (SELECT UserID FROM UsrTbl WHERE UserName = @usrnmeLbl)", conn);
comm.Parameters.AddWithValue("@usrnmeLbl", usrNmeLbl.Text);
//comm.Parameters.AddWithValue("@usrnmeLbl", crrntusr);
conn.Open();
SqlDataReader reader = comm.ExecuteReader();
//Check to see if the reader has data(Did the username exist in the database? We must check this
if (reader.Read() != false)
{
while (reader.Read())
{
string usr = reader["UserName"].ToString();



then here is the second connection:

private void getRelatives()
{
//Define database connection
SqlConnection conn = new SqlConnection(
"Server=localhost\\SqlExpress;Database=MyDB;" + "Integrated Security=True");
//Create command
SqlCommand comm = new SqlCommand("SELECT Relation, RelativeFN FROM RelativeTable WHERE RelativeTable.UserID IN (SELECT UserID FROM UsrTbl WHERE UserName = @usrnmeLbl)", conn);
comm.Parameters.AddWithValue("@usrnmeLbl", usrNmeLbl.Text);
conn.Open();
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString();
Label4.Text += reader["RelativeLN"].ToString();


Thanks in advance!

[Ggalla1779]

did you close the connection??

If you didnt then you just reuse connection ie use your code from conn.open downwards

Its not always a good idea to keep connections open

[gjon]

Thank you for this.

[mendhak]

Always close and destroy your connection once you're done with it. To comment on your code though, I would use a different connection object for each logical query I perform.

Also, store your connection string in the web.config file, this way using the same connection object becomes an idiotic matter for you.