gianitoo
asked on
ado.net question to find specific row
I have this datareader connection below. How can I get first row or second or third?
Try
Dim conn As New Data.SqlClient.SqlConnecti on(Configu rationSett ings.AppSe ttings("co nnectionUS SL"))
conn.Open()
Dim cmd As New Data.SqlClient.SqlCommand( "dbo.wmIte mOptionGro upsbyLevel Sp", conn)
cmd.CommandType = CommandType.StoredProcedur e
'cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Request.QueryString("id"). ToString()
Dim dr As Data.SqlClient.SqlDataRead er = Nothing
dr = cmd.ExecuteReader()
While dr.Read()
Session("description") = dr("description").ToString ()
End While
conn.Close()
conn.Dispose()
Catch
End Try
Try
Dim conn As New Data.SqlClient.SqlConnecti
conn.Open()
Dim cmd As New Data.SqlClient.SqlCommand(
cmd.CommandType = CommandType.StoredProcedur
'cmd.Parameters.Add("@ID",
Dim dr As Data.SqlClient.SqlDataRead
dr = cmd.ExecuteReader()
While dr.Read()
Session("description") = dr("description").ToString
End While
conn.Close()
conn.Dispose()
Catch
End Try
Here is an example from one of my programs to help with concept:
Dim columns(2) As DataColumn, dtKeys(1) As DataColumn
Dim dt As New DataTable()
Dim dr As DataRow
columns(0) = New DataColumn("ID", Type.GetType("System.String"))
columns(1) = New DataColumn("Value", Type.GetType("System.Double"))
dtKeys(0) = columns(0)
dt.Columns.AddRange(columns)
dt.PrimaryKey = dtKeys
dr = dt.Rows.Find("MyID")
While dr.Read() is a loop, if you still want to keep SqlDataReader, then you can do something like this:
Dim i As Integer
If (dr.HasRows) Then 'Always to check before retrieving
While dr.Read()
If (i = 1) Then ' i=1 for the second row, i=2 third row, etc
Session("description") = dr("description").ToString()
End If
i += 1
End While
End If
Sorry, forgot to assign value to "i"
Dim i As Integer = 0
Dim i As Integer = 0
Dim i As Integer = 0
If (dr.HasRows) Then 'Always to check before retrieving
While dr.Read()
If (i = 1) Then ' i=1 for the second row, i=2 third row, etc
Session("description") = dr("description").ToString()
End If
i += 1
End While
End If
ASKER
when i use this
While dr.Read()
Session("description") = dr("description").ToString ()
End While
I get a result
when i use that one below i get zero results ????
Dim i As Integer = 0
If (dr.HasRows) Then 'Always to check before retrieving
While dr.Read()
If (i = 1) Then ' i=1 for the second row, i=2 third row, etc
Session("description") = dr("description").ToString ()
End If
i += 1
End While
End If
While dr.Read()
Session("description") = dr("description").ToString
End While
I get a result
when i use that one below i get zero results ????
Dim i As Integer = 0
If (dr.HasRows) Then 'Always to check before retrieving
While dr.Read()
If (i = 1) Then ' i=1 for the second row, i=2 third row, etc
Session("description") = dr("description").ToString
End If
i += 1
End While
End If
"if (i=1) Then" is looking for the second record in dr, but if you have only one record, then you will get nothing.
ASKER
i have 4 records
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Debugging would be good as you have a TRY/CATCH in your code above, so if you are getting a SQL exception of other error you will not see it since CATCH block is empty.
Removing that or adding a trace write there will help troubleshoot as well.
Removing that or adding a trace write there will help troubleshoot as well.
ASKER
This is my connection below. ur code is not running and i can tell because i added else statement and still get no data ????
Dim conn As SqlConnection = ConfigurationManager.Conne ctionStrin gs("test") .Connectio nString
conn.Open()
Dim cmd As New Data.SqlClient.SqlCommand( "wmItemOpt ionGroupsb yLevelSp", conn)
cmd.CommandType = CommandType.StoredProcedur e
'cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Request.QueryString("id"). ToString()
Dim dr As Data.SqlClient.SqlDataRead er = Nothing
dr = cmd.ExecuteReader()
'While dr.Read()
' Label1.Text = dr("description").ToString ()
'End While
Dim i As Integer = 0
If (dr.HasRows) Then 'Always to check before retrieving
While dr.Read()
If (i = 4) Then ' i=1 for the second row, i=2 third row, etc
Session("description") = dr("description").ToString ()
Exit While
End If
i += 1
End While
Else
Label1.Text = "no data"
End If
conn.Close()
conn.Dispose()
Dim conn As SqlConnection = ConfigurationManager.Conne
conn.Open()
Dim cmd As New Data.SqlClient.SqlCommand(
cmd.CommandType = CommandType.StoredProcedur
'cmd.Parameters.Add("@ID",
Dim dr As Data.SqlClient.SqlDataRead
dr = cmd.ExecuteReader()
'While dr.Read()
' Label1.Text = dr("description").ToString
'End While
Dim i As Integer = 0
If (dr.HasRows) Then 'Always to check before retrieving
While dr.Read()
If (i = 4) Then ' i=1 for the second row, i=2 third row, etc
Session("description") = dr("description").ToString
Exit While
End If
i += 1
End While
Else
Label1.Text = "no data"
End If
conn.Close()
conn.Dispose()
You only have 4 records, but i=4 is looking for the fifth record, that it why.
Option 1: Change i=4 to i=3
Option 2: Dim i As Integer = 1
Option 1: Change i=4 to i=3
Option 2: Dim i As Integer = 1
ASKER
this is what i have and nothing yet
Dim i As Integer = 1
If (dr.HasRows) Then 'Always to check before retrieving
While dr.Read()
If (i = 3) Then ' i=1 for the second row, i=2 third row, etc
Session("description") = dr("description").ToString ()
Exit While
End If
i += 1
End While
Else
Label1.Text = "no data"
End If
Dim i As Integer = 1
If (dr.HasRows) Then 'Always to check before retrieving
While dr.Read()
If (i = 3) Then ' i=1 for the second row, i=2 third row, etc
Session("description") = dr("description").ToString
Exit While
End If
i += 1
End While
Else
Label1.Text = "no data"
End If
Did you use the break point as I mentioned before to step into your code?
If you define what the primary key column(s) on DataTable you can use its functionality to find specific row based on id value.