Data Reader is not getting all the records from the DB. What am I doing wrong?

Question

User1216627406 posted

Greetings experts,

As you can see from the code below, there are six input parameters and any one of those can be passed to display several rows of data from the database.

There is especially one called call_list_id.

When a value of call_list_id is passed, there are at least 15 records associated with it.

I can confirm this when I run it from SSMS.

However, I will pass same value through my app, only one record is displayed.

What am I doing wrong?

Many thanks in advance

            Dim constr As String = ConfigurationManager.ConnectionStrings("stringst").ConnectionString
            Dim conn As SqlConnection = New SqlConnection(constr)
            Dim whereclause = ""
            Dim Sql As String
            Dim s As String = ""
            conn.Open()
            Dim cmd As New SqlCommand(s)
            Sql = "SELECT phone_number, callerid, call_list_id, startttime,"
            Sql += "connecttime, endtime, duration, fullduration," 
            Sql += "camapign_id, queue_id, call_type_id, roll_id, cause_code, uuid, box_id, trunk_name, uuid, customer_id "
            Sql += "FROM cti.qpcdr "
            'Response.Write(Sql)
            'Response.End()
            whereclause = " uuid = @uuid Or call_list_id=@callerlist Or phone_number=@phone Or startttime=@start Or endtime=@end Or call_type_id=@calltype "
            whereclause = whereclause & " ORDER BY endtime DESC"
            'Response.Write(whereclause)
            'Response.End()

            'cmd.Connection = conn
            cmd = New SqlCommand(Sql & "WHERE " & whereclause, conn)
            cmd.Parameters.AddWithValue("@uuid", uuid.Text)
            cmd.Parameters.AddWithValue("@callerlist", call_list_id.Text)
            cmd.Parameters.AddWithValue("@phone", phone_number.Text)
            cmd.Parameters.AddWithValue("@start", date_start.Text)
            cmd.Parameters.AddWithValue("@end", date_end.Text)
            cmd.Parameters.AddWithValue("@calltype", call_type.SelectedValue)
            'conn.Open()
            Dim dr As SqlDataReader = cmd.ExecuteReader()
            If dr.HasRows() Then
                'Employee exists'
                While dr.Read()

                    lblphonenumber.Text = dr("phone_number").ToString()
                    lblcallerid.Text = dr("callerid").ToString()
                    lblcallerlist.Text = dr("call_list_id").ToString()
                    lblstarttime.Text = dr("startttime").ToString()
                    lblconnectime.Text = dr("connecttime").ToString()
                    lblendtime.Text = dr("endtime").ToString()
                    lblduration.Text = dr("duration").ToString()
                    lblfullduration.Text = dr("fullduration").ToString()
                    lblampaignname.Text = dr("camapign_id").ToString()
                    lblqueuename.Text = dr("queue_id").ToString()
                    lblcalltype.Text = dr("call_type_id").ToString()
                    lblemployee.Text = dr("roll_id").ToString()
                    lblisdn.Text = dr("cause_code").ToString()
                    lbluuid.Text = dr("uuid").ToString()
                    lbltelephony.Text = dr("box_id").ToString()
                    lbltrunkname.Text = dr("trunk_name").ToString()
                    lbluuid.Text = dr("uuid").ToString()
                    lblrecording.Text = dr("customer_id").ToString()
                    dr.NextResult()
                End While
            End If
            dr.Close()
            conn.Close()

Answer 1

User475983607 posted

The code looks kinda' ok.  I'm guessing you're getting unexpected input parameters. 

I do not recommend accessing user input directly without first verifying the input values.  This code can be problematic. 

cmd.Parameters.AddWithValue("@uuid", uuid.Text)

If the uuid.Text is an empty string and @uuid is an INT then the parameter value becomes zero due to SQL casting.

For example, this code returns 0.

DECLARE @uuid INT
SET @uuid = ''
SELECT @uuid

The DATETIME type will have a similar issue and result in 1900-01-01.

DECLARE @start DATETIME
SET @start = ''
SELECT @start

Answer 2

User1216627406 posted

Ok, that is well and good, your explanation that is.

However, if you look at the sql string, you could search with any or all of the parameters.

As stated in my original post, I used call_list_id as search input param.

When I use it as the WHERE predicate in SSMS, I get at least 10 records.

Also, I used IF HasRows() to protect against the point you just raised.

There is got to be a good reason why it is only giving one record.

Thanks so much for your help

Answer 3

User288213138 posted

Hi  simflex,
 
Your question appears on Dr. NextResult(), The NextResult advances to the next result.

Remove the NextResult(), and it will work well.
 
Here is the NextResult Method link:

https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader.nextresult?view=netframework-4.8

Best Regards,

Sam

Answer 4

User1216627406 posted

Thank you samwu.

Same result, only one row of records.

But here is what is so weird.

That one record displays even before I filter the results with call_list_id.

The results section should be blank until I search with a param.

This is really freaking me out.

Answer 5

User475983607 posted

Oh, after looking closer at what the code does, the results will always be the last record in the result set.   I'm assuming that your initial problem statement is incorrect and the Data Reader is looping through records.  Have you run the code through the Visual Studio debugger and verified there is one iteration?  Or are you looking at the final results on the screen?

You'll need to create a set of labels for each record.  This is usually handled in a databound control.

                While dr.Read()

                    lblphonenumber.Text = dr("phone_number").ToString()
                    lblcallerid.Text = dr("callerid").ToString()
                    lblcallerlist.Text = dr("call_list_id").ToString()
                    lblstarttime.Text = dr("startttime").ToString()
                    lblconnectime.Text = dr("connecttime").ToString()
                    lblendtime.Text = dr("endtime").ToString()
                    lblduration.Text = dr("duration").ToString()
                    lblfullduration.Text = dr("fullduration").ToString()
                    lblampaignname.Text = dr("camapign_id").ToString()
                    lblqueuename.Text = dr("queue_id").ToString()
                    lblcalltype.Text = dr("call_type_id").ToString()
                    lblemployee.Text = dr("roll_id").ToString()
                    lblisdn.Text = dr("cause_code").ToString()
                    lbluuid.Text = dr("uuid").ToString()
                    lbltelephony.Text = dr("box_id").ToString()
                    lbltrunkname.Text = dr("trunk_name").ToString()
                    lbluuid.Text = dr("uuid").ToString()
                    lblrecording.Text = dr("customer_id").ToString()
                    dr.NextResult()
                End While

Answer 6

User288213138 posted

Hi  simflex,
 
If you want to return the result of the query to the TextBox control, you can only query one record.
You can display the results of your query by using list collection.

The Next Result() be used to process multiple results, which can be generated by executing batch Transact-SQL statements.

By default, the data reader is positioned on the first result.
 
The Code:

public class Model
        {
            public string phone_number { get; set; }
            public string callerid { get; set; }
            public string call_list_id { get; set; }
            public string startttime { get; set; }
            public string connecttime { get; set; }
        }
string constr = ConfigurationManager.ConnectionStrings["stringst"].ConnectionString;

                using(SqlConnection con=new SqlConnection(constr))
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("select * from TestDate", con);
                    SqlDataReader dr = cmd.ExecuteReader();
                    if (dr.HasRows)
                    {
                    List<Model> mod = new List<Model>();
                    while (dr.Read())
                        {
                        Model m = new Model();
                        m.phone_number = dr["phone_number"].ToString();
                        m.callerid = dr["callerid"].ToString();
                        m.call_list_id = dr["call_list_id"].ToString();
                        m.startttime = dr["startttime"].ToString();
                        m.connecttime = dr["connecttime"].ToString();
                        mod.Add(m);                        
                    }
                }
                }

The Result:

Best Regards,

Sam