How to count number of rows in a sql table with vb.net using sqlclient class

Question

SELECT count(*) FROM table name

this above code work fine in standalone sql table, but who can I do this simple task with in vb.net wpf project ?

yes too much thing like grid.rows.count with loop loop with data reader loop with data table — Sonia Rehman
– Sonia Rehman, Commented May 31, 2015 at 13:08
first you need to create a sql connection..then create sql command..then execute it..try and post your code..then we can help — Sachu
– Sachu, Commented May 31, 2015 at 13:09
' Dim ds As DataSet = New DataSet Dim ds As DataTable = New DataTable 'adp.Fill(ds, "tencmpC1") adp.Fill(ds) grid.ItemsSource = ds.DefaultView Dim dss As New DataSet 'Dim dtt As New DataTable 'adpp.Fill(dss) ds = dss.Tables(0) Dim count As Integer = ds.Rows.Count txt1.Text = count — Sonia Rehman
– Sonia Rehman, Commented May 31, 2015 at 13:10
ok sachu I try one code here also that is not working stackoverflow.com/questions/30546153/… — Sonia Rehman
– Sonia Rehman, Commented May 31, 2015 at 13:12

Sachu · Accepted Answer · 2015-05-31 15:38:44Z

1

This is only a sample ..just check and try your own way.

Sample:

Dim connetionString As String
Dim connection As SqlConnection
Dim command As SqlCommand
Dim sql As String
connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;
                   User ID=UserName;Password=Password"
 sql = "Select count(*) from table"
 connection = New SqlConnection(connetionString)
 Try
   connection.Open()
   command = New SqlCommand(sql, connection)
   Dim sqlReader As SqlDataReader = command.ExecuteReader()
   While sqlReader.Read()
   MsgBox("Count =" & sqlReader.Item(0))
   End While
   sqlReader.Close()
   command.Dispose()
   connection.Close()
  Catch ex As Exception
  MsgBox("Can not open connection ! ")
 End Try

If your query return more than one values you can use SqlDataReader(), but if you are sure your query will return only a single value you can use ExecuteScalar() and if your query wont return any result, eg:- insert.it will insert value not return any data so we can use ExecuteNonQuery().The ExecuteNonQuery() will return a result which indicate is it successful or failure. If you want you can assign the same else no need.

edited May 31, 2015 at 15:38

answered May 31, 2015 at 13:36

Sachu

7,7549 gold badges59 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sachu Over a year ago

you can use anything depends on your requirement..since its return count and its a single value you can use executescalar as I specified..i just gave a guideline how to do..thats it

Sachu Over a year ago

@SoniaRehman if u think mine is wrong you can downvote no issue..i just asked the reason

har07 · Accepted Answer · 2015-05-31 13:56:36Z

1

Use SqlCommand.ExecuteScalar() method to execute query that return singular/scalar value (example based on that link) :

Dim count As Integer
Dim connString = "connection string to your database here"
Using conn As New SqlConnection(connString)
    Dim cmd As New SqlCommand("SELECT COUNT(*) FROM MyTable", conn)
    Try
        conn.Open()
        count = Convert.ToInt32(cmd.ExecuteScalar())
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try 
End Using

answered May 31, 2015 at 13:56

har07

89.5k12 gold badges87 silver badges143 bronze badges

Collectives™ on Stack Overflow

How to count number of rows in a sql table with vb.net using sqlclient class

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related