How to connect to a DB multiple times through page

Hey all,

I am still pretty new to all of this and I am having problems accessing a the same DB twice in my page. I want to pull information once in one spot, but then pull different information in a different spot on the page. Anyway, in the first spot, I have the following code:

Dim conStringAsString = WebConfigurationManager.ConnectionStrings("DataConn").ConnectionString

Dim conAsNew SqlConnection(conString)

Dim cmdAsNew SqlCommand("Select Team1.TeamID as Team1ID, Team1.TeamName as HomeTeam, Team2.TeamID as Team2ID, Team2.TeamName as AwayTeam, Football_Schedule.ScheduleID as ScheduleID from (Select * from Football_Teams) as Team1, (Select * from Football_Teams) as Team2, Football_Schedule where Football_schedule.team1 = Team1.teamid AND Football_Schedule.team2 = Team2.teamid order by Football_Schedule.WeekNum, Team1.TeamName, Team2.Teamname", con)Using con

con.Open()

Dim RSAs SqlDataReader = cmd.ExecuteReader()

While RS.Read()

blah blah blah

End While

End Using

Then in my other spot on the page I have the following:

Dim cmdresultsAsNew SqlCommand("Select Users.Firstname, sum(PointsID) as TotalPoints from Football_Input, Football_Schedule, Users where Football_Input.TeamID = Football_Schedule.winID and users.userid = Football_Input.UserID Group by Users.firstname")

Using con

con.Open()

Dim RDAs SqlDataReader = cmdresults.ExecuteReader()

While RD.Read()%>

<tr>

<td>

<%=RD%>

</td>

</tr>

<%EndWhile

EndUsing

When I try to execute I get this error, "ExecuteReader: Connection property has not been initialized." on the following line, "

RDAs SqlDataReader = cmdresults.ExecuteReader()" Any ideas? If possible a little explanation on how multiple connections to the same database work would be nice just for future reference.

Thanks in advance!!,

Chris

I think you are not using separate file for the code behind.

fischecp:

Dim cmdAsNew SqlCommand("Select Team1.TeamID as Team1ID, Team1.TeamName as HomeTeam, Team2.TeamID as Team2ID, Team2.TeamName as AwayTeam, Football_Schedule.ScheduleID as ScheduleID from (Select * from Football_Teams) as Team1, (Select * from Football_Teams) as Team2, Football_Schedule where Football_schedule.team1 = Team1.teamid AND Football_Schedule.team2 = Team2.teamid order by Football_Schedule.WeekNum, Team1.TeamName, Team2.Teamname", con)

fischecp:

Dim cmdresultsAsNew SqlCommand("Select Users.Firstname, sum(PointsID) as TotalPoints from Football_Input, Football_Schedule, Users where Football_Input.TeamID = Football_Schedule.winID and users.userid = Football_Input.UserID Group by Users.firstname")

Did you observe that you haven't provided the connection for the second command ?

Even if you would have done so, unless you close the previous datareader you'll get another error when trying to bind the second datareader stating you already have a reader attached to this connection. One more thing, you're trying to open the same connection again in your second code. This will also result in an error stating the connection is already open.

Now, my advices to you are:

If possible, use code behind files. This way you will be able to differentiate your UI and Code.

In the code behind write small functions for the tasks you've mentioned above. Create a connection object in each of the function and dispose it before the code leaves the function.

You can use data binding techniques to bind the data returned in your datareader to any of the UI controls.

Hope this will help.

|||

fischecp:

Dim cmdresultsAsNew SqlCommand("Select Users.Firstname, sum(PointsID) as TotalPoints from Football_Input, Football_Schedule, Users where Football_Input.TeamID = Football_Schedule.winID and users.userid = Football_Input.UserID Group by Users.firstname")

i think you forgot to give "con" as SqlConnection in New SqlCommand arguments... as..

Dim cmdresultsAsNew SqlCommand("SelectUsers.Firstname, sum(PointsID) as TotalPoints from Football_Input,Football_Schedule, Users where Football_Input.TeamID =Football_Schedule.winID and users.userid = Football_Input.UserID Groupby Users.firstname",con)

also make sure you close the connection each time after operation completes.. and re-open for a new operation..

Good Luck./.

|||

Ok, I appreciate the help. At least my page runs without errors, however! Now I get the page to load but in place of the data from the database is, "System.Data.SqlClient.SqlDataReader" in every line where the information from the database should be displaying.

Here is my code that I fixed (At least I think I fixed haha)

con =New SqlConnection(conString)

Dim cmdresultsAsNew SqlCommand("Select Users.Firstname, sum(PointsID) as TotalPoints from Football_Input, Football_Schedule, Users where Football_Input.TeamID = Football_Schedule.winID and users.userid = Football_Input.UserID Group by Users.firstname", con)

Using con

con.Open()

Dim RDAs SqlDataReader = cmdresults.ExecuteReader()

While RD.Read()%>

<tr>

<td>

<%=RD%>

</td>

</tr>

<%EndWhile

RD.close()

con.close()

EndUsing

I also closed out my previous connection and datareader like you guys mentioned. I'll post the code for that just incase. And I will use functions to clean it all up. I just would like to see how it works first =)

Dim conStringAsString = WebConfigurationManager.ConnectionStrings("DataConn").ConnectionString

Dim conAsNew SqlConnection(conString)

Dim cmdAsNew SqlCommand("Select Team1.TeamID as Team1ID, Team1.TeamName as HomeTeam, Team2.TeamID as Team2ID, Team2.TeamName as AwayTeam, Football_Schedule.ScheduleID as ScheduleID from (Select * from Football_Teams) as Team1, (Select * from Football_Teams) as Team2, Football_Schedule where Football_schedule.team1 = Team1.teamid AND Football_Schedule.team2 = Team2.teamid order by Football_Schedule.WeekNum, Team1.TeamName, Team2.Teamname", con)Using con

con.Open()

Dim RSAs SqlDataReader = cmd.ExecuteReader()

While RS.Read()

EndWhile

con.Close()

RS.Close()

EndUsing

Any Ideas? Thanks again guys,

Chris

|||

<%=RD%>

You probably want to specify which field you are trying to display on the page. Like:

<%= RD.Item(0) %>

Or "columnname" instead of index.

|||

haha wow, I have had my head so deep into this I didn't even stop to think that. Sometimes you just need to take a step back =)

Thanks again guys! you solved everything!

sql