SQL-connecton problem

I've got a problem on my aspx-page when I'm using SQL-connections.

What I'm trying to do is this:

1. Open a Connection (Conn1)
2. Open a datareader (based on Conn1)
3. Loop through the reader
4. Run a SQLcommand inside the "reader-loop" and use the Conn1 as connection.

But it doesn't work!

I gets an error-msg where it says that conn1 is locked as long as the datareader uses it.

Does this mean that I must create a new connetion-object for each command/reader a want to use in the same time? That sounds hard if I'm using nestled loops with readers/commands.

Any ideas? Thanks in advance!

Shouldn't your data reader be based off a SqlCommand?

Yes, the reader is based on a command, but that command is based on a connection....

Try this:

VB Code:

Dim conSopon As SqlConnection
Dim sqdCmd As SqlCommand
Dim rdrSQL As SqlDataReader
 
Dim dsn As String = ConfigurationSettings.AppSettings("dsn")
Dim cmbArray As New ArrayList()
 
conSopon = New SqlConnection(dsn)
sqdCmd = New SqlCommand("SELECT pIndex FROM Referer", conSopon)
sqdCmd.Connection.Open()
rdrSQL = sqdCmd.ExecuteReader
 
Do While rdrSQL.Read
     cmbArray.Add(Trim(rdrSQL("pIndex")))
Loop
 
rdrSQL.Close()
sqdCmd.Connection.Close()

Are you invoking the "Close" method off the reader?

Yes, that works, but if you're using another command inside the loop, it doensn't work.

Try this:

Code:

---

Dim strCN as string = "CONNECTION_STRING"
Dim strQuery as string = "SELECT * FROM table"
Dim cnSQL as new sqlConnection(strCN)
Dim cmdSQL as sqlCommand(strQuery, cnSQL)
Dim rdSQL as sqlDataReader

dim cmdExecSQL as new sqlCommand(cnSQL)

cnSQL.open
rdSQL = cmdSQL.ExecuteReader

do until rdSQL.read = false

'Execute the second command-object
cmdExecSQL.commandText = "INSERT INTO table2 (COL) VALUES ('" & rdSQL.items(0) &"')"
cmdExecSQL.executeNonQuery

loop

cmdExecSQL.close

rdSQL.close
cnSQL.close

---

This doesn't work, because it complains about that the connection can't execute the second Command, as it it in use by the reader...Any ideas?

Oh, I get you now. From the looks of it you're going to have to create a second command that isn't associated with the reader in the loop. That's the only thing I can think of.

VB Code:

Dim strCN as string = "CONNECTION_STRING"
Dim strQuery as string = "SELECT * FROM table"
Dim cnSQL as new sqlConnection(strCN)
Dim cmdSQL as sqlCommand(strQuery, cnSQL)
Dim rdSQL as sqlDataReader
 
dim cmdExecSQL as new sqlCommand(cnSQL)
dim cmdExecSQL2 as new sqlCommand(cnSQL)
 
cnSQL.open
rdSQL = cmdSQL.ExecuteReader
 
do until rdSQL.read = false
 
'Execute the second command-object
cmdExecSQL2.commandText = "INSERT INTO table2 (COL) VALUES ('" & rdSQL.items(0) &"')"
cmdExecSQL2.executeNonQuery
 
loop
 
cmdExecSQL.close
 
rdSQL.close
cnSQL.close

The datareader hogs the connection. When a datareader has a connection (ie before datareader.close) you cannot use that connection for anything else.

Cheers
Simon

I think there's a more effecient way to execute the command... using stored procedures, and combination of stored procedures. It works perfectly in Access databases, I know SQL Server is more powerful, but I have not a slightest clue how to do it in SQL. That didn't quite help hehe. But exploring stored procedures is the way to go.

Yes, I'm using Stored Procedures (just didn't use it in the example), but I still need a connection to execute them....

Thanks anyway!