שתף באמצעות

Row count issue using VB.NET dataAdapter

Question

Thursday, October 23, 2014 2:36 PM

I have created codes for connection to SQL Server and I am able to count rows of the datatable as following.
            Dim con As New SqlConnection
            Dim cmd1 As New SqlCommand
            Dim dt As New DataTable
            Dim da As New SqlDataAdapter

            da.SelectCommand = cmd1
            con.ConnectionString = "Data Source=DS;Integrated Security=SSPI;Initial Catalog=test"
            con.Open()
            cmd1.Connection = con
            cmd1.CommandText = "select * from tblTEST"
            cmd1.ExecuteNonQuery()
            da.Fill(dt)
            Dim reader As SqlDataReader = cmd1.ExecuteReader()
            Dim C1Count, C2Count, C3Count As Integer
            For p As Integer = 0 To dt.Rows.Count - 1
                If dt.Rows(p).Item(0).ToString.Substring(0, 2) = "C1" And GetChar(dt.Rows(p).Item(0).ToString, 3) = "A" Then
                    C1Count += 1    'count class 1 
                ElseIf dt.Rows(p).Item(0).ToString.Substring(0, 2) = "C2" And GetChar(dt.Rows(p).Item(0).ToString, 3) = "B" Then
                    C2Count += 1    'count class 2 
                ElseIf dt.Rows(p).Item(0).ToString.Substring(0, 2) = "C3" And GetChar(dt.Rows(p).Item(0).ToString, 3) = "C" Then
                    C3Count += 1     'count class 3 
                End If
            Next

Everything works fine until I break my codes down and move the connection codes to a separate class, DataAccess, where other classes can call to this class.  In the new form1, the for loop above is changed to:
     For p As Integer = 0 To (DataAccess.GetClassCount(Class) - 1)

Where GetClassCount(Class) is a function I create in the DataAccess class that returns an integer for the count.  I just copy and paste my connection codes with the dataAdapter into this function. But when I run it, it stops at da.Fill(dt) and jump out the program. Please help me to identify what the problem is and how to fix it. Thank you in advance.

  

All replies (16)

Thursday, October 23, 2014 3:46 PM

I have tried to rewrite the codes in the function GetClassCount(Class) using DataReader.  I have made some researches to show how to get the count as following.

Function GetClassCount(ByVal s As Integer) As Integer
        Dim rowCount As Integer = 0
        Dim db As DatabaseConnection = DatabaseConnection.Instance
        Dim command As New SqlCommand("Select * from tblTest", db.Connection) 'assume this is OK
        Using records = command.ExecuteReader
           If records.HasRows Then
                While records.Read
                    If records.GetString(0).Substring(0, 2) = "C1" And records.GetString(0).Substring(4, 1) = "A" Then     'these codes are just for Class 1.
                        rowCount += 1
                    End If

                End While
           End If
        End Using

        Return rowCount   'this count return wrong number; I have 18 rows; it only return 8 rows :(

 

Friday, October 24, 2014 7:23 AM | 1 vote

Hi ttim,

According to your description, when you use the DataReader, you got the wrong rowCount.

I suggest you checking if the data like "C1xxAxxx" in the first column of tblTest has 18 rows.

From your code, before the rowCount+=1, you have a IF, if the condition is not met, the rowCount will be added 1.

you also could debug the code step by step, check if the records has 18 rows.

If you only like to get the count, you could remove the IF in your code.

If you have any other concern regarding this issue, please feel free to let me know.

Best regards,
Youjun Tang

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Friday, October 24, 2014 9:41 AM

Hi Youjun, you are smart to realize the C1xxAxxx pattern. Yes, this first column do have 18 rows because they are our questions for the survey.

How about the issue I posted at the top.  When breaking my codes down and move the connection codes into a separate class (DataAccess class), so other classes can call to this class for the connection without repeating conn codes.  I don't know why when it reaches to da.Fill(dt), it jumps out the program and show a blank output (apparently, the Fill() doesn't work). It seems that I am only able to use the DataReader when I call a function in in this program instead of DataAdapter. 

Please help me to identify what the problem is and how to fix it. Thank you in advance

Friday, October 24, 2014 12:48 PM | 1 vote

Hi ttim,

Sorry for the typing error, actually, I would like to say "If the condition is not met, the rowCount will be Not added 1"

I suggest returning the dt instead of returning the count. because the dt in the GetClassCount() method is different from the dt outside.

Function GetClassCount(ByVal s As Integer) As DataTable
        Dim con As New SqlConnection
        Dim cmd1 As New SqlCommand
        Dim dt As New DataTable
        Dim da As New SqlDataAdapter

        da.SelectCommand = cmd1
        con.ConnectionString = "Data Source=DS;Integrated Security=SSPI;Initial Catalog=test"
        con.Open()
        cmd1.Connection = con
        cmd1.CommandText = "select * from tblTEST"
        cmd1.ExecuteNonQuery()
        da.Fill(dt)
        Return dt
    End Function

If you have any other concern regarding this issue, please feel free to let me know.

Regards,
Youjun Tang

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Friday, October 24, 2014 1:26 PM

Thanks, Youjun. Yes, I have done the same as you showed me.  It worked nicely until I broke down the codes and moved the connection part into the Connection class, create the function and call the connection from the function. I am not able to figure out why the Fill() doesn't work yet. If I use the DataReader then it's OK, but I must use DataAdapter to manipulate Datatable with indexes (DataReader doesn't support indexes).

In the VS2010 Solution, I have the Data project / DataAccess class and Connection class. In the DataAccess class I create the function mentioned above. It seems to me that this way only allow me to use DataReader; if I use DataAdapter, the Fill method won't work. I don't know why. I would be appreciate if you can point out why. I have spent all day for this. Please ignore the count because I got it working.

Monday, October 27, 2014 2:23 AM

Hi ttim,

Accoring to your description, If you move the connection part into the Connection class, you will get the error.

In my previous reply, I returned the DataTable not the count. you could get the index from the DataTable.

If you could share the code of the DataAccess class and Connection class, I could help you better.

Best regards,
Youjun 

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Monday, October 27, 2014 4:29 AM

Youjun, the Connection class, DataAccess class and the form, frm1 that calls to these classes as following
'''The connection Class. It's already there in the solution. I didn't create it
Imports System.Data.SqlClient
Imports myDBImports Resources = myDB.My.Resources
Friend Class DatabaseConnection

    Implements IDisposable

    Private Shared _inst As DatabaseConnection = Nothing

    Private database As SqlConnection

    Public ReadOnly Property Connection As SqlConnection

        Get

            Return database

        End Get

    End Property

   

    Public Shared ReadOnly Property Instance As DatabaseConnection

        Get

            If IsNothing(_inst) Then

                _inst = New DatabaseConnection

            End If

            Return _inst

        End Get

    End Property

        Public Sub Dispose() Implements IDisposable.Dispose

        database.Close()

        _inst = Nothing

    End Sub

    Private Sub New()

        Dim s As New SqlConnectionStringBuilder

        s.DataSource = Resources.DatabaseServerTest
        s.InitialCatalog = Resources.DatabaseName

        s.IntegratedSecurity = True

        database = New SqlConnection(s.ConnectionString)

        database.Open()

    End Sub

End Class

================================== '''The frm1 that calls to the DataAccess class' s function GetSectionCount() For test As Integer = 0 to 2                   For m As Integer = 0 to (DataAccess.GetSectionCount(test) – 1)                                 If DataAccess.GetQuestionType(m)  = “B” Then                                                 ‘Do something                                 End If                 Next Next

'''the DataAccess class. In this class, I add a function to call to the connection class above
Function GetSectionCount(ByVal s As Integer) As Integer

            Dim con As New SqlConnection
            Dim cmd1 As New SqlCommand
            Dim dt As New DataTable
            Dim da As New SqlDataAdapter

            da.SelectCommand = cmd1
            con.ConnectionString = "Data Source=DS;Integrated Security=SSPI;Initial Catalog=test"
            con.Open()
            cmd1.Connection = con
            cmd1.CommandText = "select * from tblTEST"
            cmd1.ExecuteNonQuery()
            da.Fill(dt)    <====== *****when running, the execution stops here and exit the solution
             Dim reader As SqlDataReader = cmd1.ExecuteReader()
            Dim Count1, Count2, Count3 As Integer
            For p As Integer = 0 To dt.Rows.Count - 1
                   'some codes to counts  here
            Next  

Return numCount
db.Dispose()

End Function

Can you see something wrong with these codes? When I run it, it stop by the Fill() method and jump out the solution. It even didn't  go further for the count yet.  Thank you!

Monday, October 27, 2014 8:05 AM

Yea there are more difficult ways to handle easy problems.

There are three ways to solve your problem

  1. Remove the ElseIf, if it is C3 that is most probably never reached
  2. Use AndAlso to improve it a little bit
  3. Use the defaultview and a rowfilter and then the cound (the way I would do it).

For the rest of your code I've already replied in another thread, but you wrote you are a newbie and want to do it your way, not the resulting way.

Success
Cor

Monday, October 27, 2014 1:25 PM

Thanks Cor. I will check ways you listed and I will let you know. It's actually not my way, but I have to do it. Hope you understand what I mean. I have never used defaultview and rowfilter. I have to make some researches about them. I don't think AndIf is a condition clause in VB.Net.  Thanks for replying my other post, but that is a different question.

Monday, October 27, 2014 2:42 PM

AndAlso slip of my mind, changed it in the original reply.

Success
Cor

Monday, October 27, 2014 8:07 PM

Youjun,
I have used the function just like yours to return a Datatable. Since I already have a Connection class, I will use that class for connection instead of hard coded as shown below.  It jumps out at the Fill() method and shows an empty form. It also skips other codes, so I suspect I have set up the connection incorrect.  I don't know why. I think if I can setup the connection properly, it should go through.

Function GetClassCount(ByVal s As Integer) As     DataTable
     Dim dt As New DataTable
     Dim da As New SqlDataAdapter

   Dim db As DatabaseConnection = DatabaseConnection.Instance  'DataConnection class

   Dim command As New SqlCommand("Select * from tblTEST", db.Connection)

     da.SelectCommand = command     
     da.Fill(dt)    'it jumps out at this point and show a blank form
     Return dt

     'db.Dispose()

 End Function

 
As I mentioned above, there seems something is wrong with these codes. It never gets pass the Fill(). I get stuck right there, so I can't go further with my programming. I really need your help.  

Monday, October 27, 2014 8:14 PM

Cor, I debug the codes and it never gets past the connection. Therefore, I think there is something wrong with my connection codes (I use the DataConnection class created by somebody else posted above). So I get stuck right at the beginning and not able to go further to apply your ways to check. I posted the codes that calls to the connection class above in reponse to Youjun. I hope you guys take a look and please Help! I have spent all day for this one. Thanks

Monday, October 27, 2014 10:20 PM

Yea I see now that in my previous reply I simply copied that from your code. Be aware that DataBase Connectionstrings are not testable.

Maybe does this sample give you a better idea.

Public Class Form1
    Private DT As New DataTable
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim da As New System.Data.SqlClient.SqlDataAdapter("select * from tblTEST1", "Server=YourDatabaseServerName;DataBase=TheDataBaseName;Integrated Security=SSPI")
        da.Fill(DT)
    End Sub
End Class

Success
Cor

Tuesday, October 28, 2014 4:19 PM

As I mentioned earlier, in the Form1, I have to call to the functions that are created in DataAccess class to manipulate data and return the result to Form1. I can't use DataTable directly.

Tuesday, October 28, 2014 6:58 PM

As I mentioned earlier, in the Form1, I have to call to the functions that are created in DataAccess class to manipulate data and return the result to Form1. I can't use DataTable directly.

That DatabaseConnection class is doing horrible things: it is returning the same instance of an open SqlConnection for everything. This is not how an SqlConnection is meant to be used. It should be used as: open connection, use connection, close (and dispose of) connection. That allows connection pooling to be used to efficiently use the SQL Server's resources.

If you absolutely have to use it, and I advise against doing so, you should use it like... no, sorry, I cannot see a way to use it without rewriting parts of it.

--
Andrew

Monday, November 3, 2014 4:30 AM

Andrew, sorry I am not able to come back to you until now. Anyway, can you please explain, "...it is returning the same instance of an open SqlConnection for everything". Can you please re-use the codes I posted above in your explanation? That way will help me understand, hopefully.  I can't see anything, but this issue you are alerting me it's very important. Thanks