SQL-connecton problem

[fredrik]

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!

[Shawn N]

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

[fredrik]

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

[Shawn N]

Try this:

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?

[fredrik]

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

Try this:

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?

[Shawn N]

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.

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

[SimonRigby]

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

[weijian]

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.

[fredrik]

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!