Return Row Count

[tanha]

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

'Load connection module
                    connection()

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

                    MsgBox(dt.Rows.Count)

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?

[Jx_Man]

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.

[tanha]

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

not others.

How can I do that?

[Jx_Man]

why you didn't use sql query?
select count(*) where user name = 'xxx'

[tanha]

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.

[Jx_Man]

i mean use count() function.

Dim myReader As SqlDataReader
        conn = GetConnect()
        conn.Open()
        Dim temp As String
        Try
            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
            myReader.Close()
        Catch ex As Exception

        End Try
        conn.Close()

        MsgBox(temp) ' Showing with messagebox

[tanha]

Thanks for the reply,

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

[Jx_Man]

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()
        Try
            cmdCustomer = conn.CreateCommand
            cmdCustomer.CommandText = "SELECT * FROM Customer where IDCustomer = '1' "
            daCustomer.SelectCommand = cmdCustomer
            daCustomer.Fill(dsCustomer, "Customer")
            dtCustomer = dsCustomer.Tables("Customer")
            MsgBox(dtCustomer.Rows.Count)
        Catch ex As Exception
            MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OKOnly, "Connection Error !!")
        End Try

[tanha]

Thanks much...

[Jx_Man]

it mean solved?

[anoopkh]

hi tanha

Pls clear your dt before fill

dt.rows.clear

and then

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

MsgBox(dt.Rows.Count)