Re-using the SQL Command
Due to a freaky table schema, I had cause today to loop through a 'recordset' and then insert an amalgamated string into a database (saving me doing a ridiculous loop later on).
I didn't see any reason to open a new SQL command once the datareader had done its job so I re-used the SQLCommand - which turned out a lot easier than I thought. Certainly a ton easier than the old recordset. After using the imports for data and data.sqlClient I simply:
Function get_description(ByVal hcode As String)
Dim myQry As String = "SELECT field1 from table where hcode='" & hcode & "'"
Dim mytemp_Conn As SqlConnection = New SqlConnection(myConn)
mytemp_Conn.Open()
Dim myCmd As New SqlCommand(myQry, mytemp_Conn)
Dim myDr2 As SqlDataReader
Dim Description As String = "" ' Description of the hotel
Dim tempString As String = ""
myDr2 = myCmd.ExecuteReader()
While myDr2.Read()
tempString = myDr2.Item(0) & " "
tempString = tempString.Replace("'", "''")
Description &= tempString
' Concatenate each of the freaky fields.
End While
myDr2.Close()
myQry = "Insert into tcHolidays.dbo.hotel_Descriptions (hcode, description) values ('" & hcode & "','" & Description & "')"
myCmd.CommandText = myQry
myCmd.ExecuteNonQuery()
mytemp_Conn.Close()
Console.WriteLine("Description: {0}", Description)
End Function
That was basically it! In classic I'd have had to have opened a new recordset - but now its simply wash-n-go :)