For VB.Net users

Question

For VB.Net users

Hall of Fame

Points: 3715

April 17, 2007 at 11:04 am

#195118

This is an example how to retrieve multiple results from DB:

Private Sub RetrieveMultipleResults(ByVal connection As SqlConnection)
    Using connection
        Dim command As SqlCommand = New SqlCommand( _
          "SELECT CategoryID, CategoryName FROM Categories;" & _
          "SELECT EmployeeID, LastName FROM Employees", connection)
        connection.Open()

        Dim reader As SqlDataReader = command.ExecuteReader()

        Do While reader.HasRows
            Console.WriteLine(vbTab & reader.GetName(0) _
              & vbTab & reader.GetName(1))

            Do While reader.Read()
                Console.WriteLine(vbTab & reader.GetInt32(0) _
                  & vbTab & reader.GetString(1))
            Loop

            reader.NextResult()
        Loop
    End Using
End Sub

But if i need to retrieve results from one table and use it 
for doing something(insert/delete) with some other tables and this should be in the loop, how do I do it?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

l0n3i200n Ten Centuries Points: 1091 More actions · Answer 1

I'm not sure what you need. Do you need code to do a CLR stored proc? I think the CLR is a bit of overkill for the kind of command ur doing.

You could do something like this in TSQL

INSERT INTO ClientX([ClientID],[SOMETHING])

SELECT [EmployeeID], [ClientSurname] FROM Client

Gift Peddie SSC Guru Points: 73570 More actions · Answer 2

I sorry I have not answered this earlier the easy way to do it is to call two stored procs one doing insert and the other doing delete and the deletes are best in a transaction code block so other operations will not affect the delete. You can do it with the DataList or GridView. The reason the DataReader cannot be used is because it is a one way cursor going through one table. In Asp.net most data stuff is best to use existing code sample to fit your needs, the Asp.net founder have many Asp.net data links in one place so take a look and post again. The other way two ways to do it is with the Object datasource control which also let you do update and to use a Dataset and put two tables in it and create relationship, the problem with the later is the data types must be the same. Hope this helps.

http://weblogs.asp.net/scottgu/archive/2007/04/14/working-with-data-in-asp-net-2-0.aspx

Kind regards,
Gift Peddie

P Jones SSChampion Points: 12361 More actions · Answer 3

There's lots of appropriate examples of vb.net with sql server at http://aspnet.4guysfromrolla.com/

I learnt much of my data handling here! The datagrid series is especially recommended.

And P.S. you'd get slated in my office for putting SQL statements in VB code - ALWAYS use a stored procedure!!

Vikka Hall of Fame Points: 3715 More actions · Answer 4

Thank you all guys! I will go look at the examples. I can only learn from some practical examples.

I don't know which way to go though, stored procedures in the database, data adapters, SQL queries.. Different people say different things. My co-worker was using stored procedures but then it was not his database so they changed tables structure, it stopped working, they changed permissions, it's stopped working. He was cursing all the time. Then he was using data adapters like copy data from tables into the static table and work with this "snap shot". Again it works fine till somebody changes something in the database. Then not possible to fix it, he just had to redo everything.

Since I am .NET beginner, I was just trying to mimic what I was doing in Perl where I could prepare all the statements outside the loop(s) then run one query, get the output and use it as input for another query in the loop and do something else. Not possible here. So I run one query, put the result into the array, then loop thrugh the array and run another query. I guess, I just used array as my own primitive "data adapter". This is all because of the lack of knowledge. I am like a monkey using a calculater to crack a nut .

I still try to figure out what is the best approach when I work with the database: queries, data adapters, stored procedures??

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

Assuming you opened a connection with the MARS=True attribute:

Using

cnn As New SqlConnection("Persist Security Info=False;Integrated Security=SSPI;server=xyz;database=abc;MultipleActiveResultSets=True")

You can open a SqlDataReader object and process the rows, and while the datareader is still open you can use the same connection to execute other commands (including updates, inserts, deletes) on the same connection. You probably can't find examples of this because there's nothing else that you have to do to make it work. And it doesn't matter whether you are calling a stored procedure or using ad hoc SQL, or whether your VB.NET code is in SQLCLR or not, those issues don't apply to your original question.

There are some implications for transactions. All actions using the same connection will participate in the same transaction, so if you want to wrap sets of commands in separate transactions you have to use separate connections. I have even used this to update the same rows the data reader is reading, on the same connection, with no deadlocks. By the time you get a row from the data reader, the firehose cursor that drives it is probably hundreds of rows further into the table and is no longer locking the row. The rows you haven't processed yet are being cached in the client.

Vikka Hall of Fame Points: 3715 More actions · Answer 6

"and while the datareader is still open you can use the same connection to execute other commands (including updates, inserts, deletes) on the same connection"

I tried to read data then insert using same connection before . Didn't work. Error was something like "connection is already opened". So I had to open one connection for reading, another connection to insert, then I was able to read and insert in the loop. But there is limited amount of connections to the database so I didn't like that idea. I don't know if practically it is possible to use same connection to read, process data somehow based on some criteria and insert.

I wish I could find some working example of the same connection used in both cases somewhere.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 7

If you are running .NET 2.0 and add the MultipleActiveResultSets=True attribute to the connection string, it should work. Your error message sounds like you were trying to re-open the open connection. You should just be re-using it.