I am using Visual Studio 2008, and Sql Server 2000, and I have the following code:

'Load connection module

                    cmd = con.CreateCommand
                    cmd.CommandText = "SELECT * FROM tblUser WHERE username = 'xxx' "
                    da.SelectCommand = cmd
                    da.Fill(ds, "tblUser")
                    dt = ds.Tables("tblUser")


I don't know why it return the number of all available rows in the table, even I don't have username with xxx.

what is the problem?

dt = ds.Tables("tblUser") -> dt is data table

MsgBox(dt.Rows.Count) -> count all row in table user
yes, cause it will count all rows in table user.

I like to count all those rows where username = 'xxx'

not others.

How can I do that?

I have no problem using the count(*), but still I have the same problem.

I want to just count the row which satisfy with WHERE clause.

i mean use count() function.

Dim myReader As SqlDataReader
        conn = GetConnect()
        Dim temp As String
            Dim sql As String = "SELECT count(*) AS 'KODE' FROM tblUser where UserName = 'xxx' "
            Dim comm As SqlCommand = New SqlCommand(sql, conn)
            myReader = comm.ExecuteReader
            If myReader.HasRows Then
                While myReader.Read()
                    temp = myReader.Item("KODE")
                End While
            End If
        Catch ex As Exception

        End Try

        MsgBox(temp) ' Showing with messagebox

Thanks for the reply,

Do u think it is needed to use the loop, I want to use the rows.count()

i was tried to use row.count() and it running good.

Dim cmdCustomer As New SqlCommand
        Dim daCustomer As New SqlDataAdapter
        Dim dsCustomer As New DataSet
        Dim dtCustomer As New DataTable

        conn = GetConnect()
            cmdCustomer = conn.CreateCommand
            cmdCustomer.CommandText = "SELECT * FROM Customer where IDCustomer = '1' "
            daCustomer.SelectCommand = cmdCustomer
            daCustomer.Fill(dsCustomer, "Customer")
            dtCustomer = dsCustomer.Tables("Customer")
        Catch ex As Exception
            MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OKOnly, "Connection Error !!")
        End Try

hi tanha

Pls clear your dt before fill


and then

da.SelectCommand = cmd
da.Fill(ds, "tblUser")
dt = ds.Tables("tblUser")


This article has been dead for over six months. Start a new discussion instead.