problem with fetch methode

problem with fetch methode

Old forum URL: forums.lhotka.net/forums/t/2134.aspx

Wilfred posted on Friday, January 12, 2007

I don' usually put code on forums, but i can't seem to get a recordset with this code, while the stored procedure should return  one, i have tried to debug but just am stuck, anyone with an idea. There are no records affected, after cm.executereader. So the activelookup remains empty, anyone.

Willem

Protected Overrides Sub DataPortal_Fetch(ByVal Criteria As Object)

Dim s As String

Dim crit As CustomCriteria = DirectCast(Criteria, CustomCriteria)

Using cn As New System.Data.SqlClient.SqlConnection(ActiveObjects.My.Settings.CDOLVSConnection)

cn.Open()

Dim cm As IDbCommand = Nothing

cm = cn.CreateCommand

cm.CommandType = CommandType.StoredProcedure

cm.CommandText = "usp_getKolomNamen"

s = "tbl" + crit.TableName

Dim param As New SqlClient.SqlParameter

param.ParameterName = "@TabelNaam"

param.Value = s

MsgBox(crit.TableName)

param.Direction = ParameterDirection.Input

cm.Parameters.Add(param)

Using cm

Using dr As New SafeDataReader(cm.ExecuteReader)

IsReadOnly = False

Dim i As Integer

i = dr.RecordsAffected

Dim k As Integer

k = 0

Try

While dr.Read()

MsgBox(dr.GetValue(4 + i).ToString)

k = i - 1

Me.Add(New NameValuePair(k.ToString, dr.GetValue(4 + i).ToString))

i = i + 1

End While

Finally

End Try

IsReadOnly = True

End Using

End Using

End Using

End Sub

ajj3085 replied on Friday, January 12, 2007

Does the database call include a SET NOCOUNT ON statement?  Even with NOCOUNT OFF, i'm not sure that RecordsAffected is ever set properly...

Michael Hildner replied on Friday, January 12, 2007

I guess your sproc is a SELECT... statement? You may want to check out MSDN - says IDataReader.RecordsAffected doesn't get set until you read everything and close the IDataReader.

Regardless, whenver I expect to get some rows back and I don't, I fire up the profiler and check out what's really going on. Copy what the profiler says when you execute your sproc, and paste into a new query window and run it from there. Usually I have a simple typo.

Copyright (c) Marimer LLC