how to check MS database table is empty or not

Question

0.00/5 (No votes)

See more:

hi..
I am developing invoice management system,i want to check the invoice table is empty or not, if the emp_id is empty in invoice table then textbox1 value=1 or the emp_id values are already fill then the textbox1 value is emp_id+1,i try to many way but result is null, so pls help me, my code and the result is below..

VB

conq = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\Programs\sathish\invoicedb.mdb")
       conq.Open()
       cmdq = New OleDbCommand("select  max(invoiceid) from invoice", conq)
       adpq = New OleDbDataAdapter(cmdq)
       dsq.Clear()
       adpq.Fill(dsq, "invoiceid")
       DataGridView2.DataSource = dsq.Tables(0)

VB

Dim i As Integer
       i = DataGridView2.CurrentRow.Index
       If DataGridView2.Item(0, i).Value = 0 Then
           TextBox12.Text = 1
       ElseIf DataGridView2.Item(0, i).Value >= 0 Then
           TextBox11.Text = DataGridView2.Item(0, i).Value.ToString
           TextBox12.Text = Val(TextBox11.Text + 1)
       End If

this code is properly run in the id is already fill case but the table is empty then it's show the error,the error is
"Operator '=' is not defined for type 'DBNull' and type 'Integer'."

pls send your ideas..

Posted 13-Aug-13 6:08am

sv sathish

Add a Solution

3 solutions

Solution 1

You have 2 optional ways:
1) check if grid's cell contains: DBNull.Value[^]
2) change query to return not nullable value ;)

VB

If Not DataGridView2.Item(0, i).Value Is DbNull.Value Then 'proceed

SQL

select  MAX(COALESCE(invoiceid,0)) from invoice

Posted 13-Aug-13 6:47am

Maciej Los

Updated 13-Aug-13 9:40am

v2

Comments

sv sathish 13-Aug-13 13:12pm

Sir it's not working it's showing error.. the code and error is below

Dim i As Integer
i = DataGridView2.CurrentRow.Index
If DataGridView2.Item(0, i).Value <> DBNull.Value Then
TextBox12.Text = 1
Else

TextBox11.Text = DataGridView2.Item(0, i).Value.ToString
TextBox12.Text = Val(TextBox11.Text + 1)
End If

the error is:
Operator '<>' is not defined for type 'Integer' and type 'DBNull'.

Maciej Los 13-Aug-13 15:41pm

Of course, my mistake, sorry.
Have a look now ;)

Solution 2

dataset.tables("nameofthetable").rows.count

Posted 13-Aug-13 7:54am

jerahmeel salayog

Comments

sv sathish 14-Aug-13 1:58am

i try this code but the problem is not clear..

Public Sub Display()
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\Programs\sathish\invoicedb.mdb")
con.Open()
cmd = New OleDbCommand("select MAX(invoiceid) from invoice ", con)
adp = New OleDbDataAdapter(cmd)
ds = New DataSet
adp.Fill(ds, "invoice")
TextBox1.Text = (ds.Tables(0).Rows(0).Item(0)) + 1
con.Close()
End Sub

the same problem is here, the database is already fill then in this code is good but the data field is empty then it's not working..

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sv sathish · Accepted Answer · 2013-08-13T22:49:00

i solve this problem my code is below..

VB

Public Sub Display()
       Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\Programs\sathish\invoicedb.mdb")
       con.Open()
       cmd = New OleDbCommand("select invoiceid from invoice ", con)
       adp = New OleDbDataAdapter(cmd)
       Dim ds12 As DataSet
       ds12 = New DataSet
       adp.Fill(ds12, "invoice")

       If ds12.Tables("invoice").Rows.Count = 0 Then
           TextBox11.Text = "1"
       Else
           cmd = New OleDbCommand("select MAX(invoiceid) from invoice ", con)
           adp = New OleDbDataAdapter(cmd)
           ds12 = New DataSet
           adp.Fill(ds12, "invoice")
           TextBox11.Text = (ds12.Tables(0).Rows(0).Item(0)) + 1
           con.Close()
       End If

   End Sub