CODEWITHSUNDEEP
sqlasp.netvb.net
Manoj Baswal
Manoj Baswal

Reputation: 11

Not able to get all records of column

I want to get employee names (from an SQL database) which fall in a particular date range and display it through response.write.

Why is this loop is not working?

 Dim constr6 As String = ConfigurationManager.ConnectionStrings("cmdpns").ConnectionString
            Using conn As New SqlConnection(constr6)
                conn.Open()
                Using cmd8 As New SqlCommand("Select * from insu_dtl WHERE ins_valid Between @c_date And @c_date2 ")
                    cmd8.Parameters.AddWithValue("@c_date", c_date2)
                    cmd8.Parameters.AddWithValue("@c_date2", Session("c_date3"))
                    cmd8.CommandType = CommandType.Text
                    cmd8.Connection = conn



                    objreader = cmd8.ExecuteReader()
                    Do Until objreader.Read <> False

                        Session("emp_name") = objreader("emp_name").ToString()
                        Response.Write(Session("emp_name"))
                        Response.Write("working")

                    Loop

Upvotes: 0

Views: 50

Answers (2)

Andrew Morton
Andrew Morton

Reputation: 25013

There could be more than one thing going wrong:

  • using BETWEEN can be tricky
  • AddWithValue may be confused as to the data type
  • using Response.Write might be inserting the text somewhere you can't see it

An easy way to get round the BETWEEN is to use x >= A AND x < B where A is the start date and B is the (end date + 1 day). Remove any time component from them so that you know exactly what is being supplied to the query.

Instead of AddWithValue, explicitly add a parameter with the correct type.

Rather than using Response.Write, place an asp:Literal control on the page and set its .Text.

Option Strict On
' ...

' remove any time component from the dates...
Dim startDate As DateTime = c_date2.Date
Dim endDate As DateTime = DirectCast(Session("c_date3"), DateTime).Date.AddDays(1)

Dim sb As New StringBuilder
Dim constr6 As String = ConfigurationManager.ConnectionStrings("cmdpns").ConnectionString

Using conn As New SqlConnection(constr6)
    Using cmd8 As New SqlCommand("SELECT [emp_name] FROM [insu_dtl] WHERE [ins_valid] >= @startDate AND [ins_valid] < @endDate", conn)
        cmd8.Parameters.Add(New SqlParameter With {.ParameterName = "@startDate", .SqlDbType = SqlDbType.DateTime, .Value = startDate})
        cmd8.Parameters.Add(New SqlParameter With {.ParameterName = "@endDate", .SqlDbType = SqlDbType.DateTime, .Value = endDate})
        conn.Open()

        Dim rdr As SqlDataReader = cmd8.ExecuteReader()
        If rdr.HasRows Then
            While rdr.Read
                sb.Append(rdr.GetString(0))

            End While
        End If    

    End Using
End Using

' use an <asp:Literal> control instead of using Response.Write: '
Literal1.Text = sb.ToString()

Also, you can add the connection when you instantiate an SqlCommand, and the default .CommandType is CommandType.Text, so you don't need to set that.

Upvotes: 0

MartW
MartW

Reputation: 12538

This bit is (one reason at least) why your loop doesn't work:

Do Until objreader.Read <> False

objReader.Read returns True when there's a row, so your loop immediately ends. I suspect what you meant was just:

Do Until objreader.Read = False

Upvotes: 1

Related Questions