[B]check if table is empty or not[/B]

[Vivek_1986]

Hi to all, Programmers. :icon_smile:

i require a general syntax for checking if the SQL table is empty or Contains records.

if the table is empty i'm suppose to add initial counter (like 0001) and if table already contain records then i'm suppose to increment the counter by ONE. The data type of the field: counter , is nvarchar(50).

thanking in advance.

[kvprajapati]

Here is sql statement:

Dim sql as String = "select count(*) from tablename"
  ...

It retun zero (0) result when a table is empty.

[Teme64]

i require a general syntax for checking if the SQL table is empty or Contains records.

Although I used SQL Server's data types, this is very general syntax

Dim strSQL As String
Dim oCmd As Data.SqlClient.SqlCommand
Dim RecCount As Integer

' SQL Statement
strSQL = "SELECT COUNT(*) AS RecCount FROM <tablename>"

' Execute SQL statement
oCmd.CommandText = strSQL
RecCount = CInt(oCmd.ExecuteScalar)
If RecCount = 0 Then
  ' Table is empty
End If

Dim Counter As String
Dim CounterValue As Integer

' SQL Statement
strSQL = "SELECT <counter> AS Counter FROM <tablename>"

' Get (textual) counter
oCmd.CommandText = strSQL
' Execute SQL statement
Counter = CStr(oCmd.ExecuteScalar)
' Convert to integer
CounterValue = CInt(Counter)
' Increase
CounterValue += 1
' Convert to string
Counter = CounterValue.ToString.PadLeft(4, CChar("0"))
' Update DB
' SQL Statement
strSQL = "UPDATE <tablename> SET <counter>='" & Counter & "'"

oCmd.CommandText = strSQL
' Execute SQL statement
oCmd.ExecuteNonQuery()

for checking record count and incrementing a textual counter. There are in the code above every method you need to make it actual VB.NET code.

HTH

[sknake]

Here is sql statement:

Dim sql as String = "select count(*) from tablename"
  ...

It retun zero (0) result when a table is empty.

Beware -- doing a Select Count(*) from a table locks the entire table and the solution will not scale with multiple users. You will start seeing query timeouts and transaction deadlocking.

You may try an approach like this to test for any empty table:

IF EXISTS(Select * From sysobjects (NOLOCK))
BEGIN
  Select Cast(1 as bit) As Result
END ELSE
BEGIN
  Select Cast(0 as bit) As Result
END

To count the table you should set the transaction isolation level to read uncommitted or use the NOLOCK table hint:

Select Count(*) From Table (NOLOCK)