sql count

bapu2889

hello i am new in sql and i have create one small application with one table which has six columns i want to check recores from sql database but it always shows nothing this is the code bellow in load event, all i want to do is if there is no data in sql database then it should shows "Empty Database" in Combo box but it's not working

Private Sub LoadCboItems()

    dcIMS = GetDBConnection()
    cboDisp.Items.Clear()
   
    If Me.BindingContext(dsIMS, "IMS").Count = 0 Then   :confused:
        cboDisp.Text = "Empty Database"
        Exit Sub
    Else
        Dim MyReader As SqlDataReader = Nothing
        Try
            
            Dim MyData As New SqlCommand("SELECT \* FROM IMS", dcIMS)
            MyReader = MyData.ExecuteReader
            Do While MyReader.Read
                If rdoID.Checked Then

                    cboDisp.Items.Add(MyReader.Item("InkNumber").ToString)
                    cboDisp.SelectedIndex = 0

                ElseIf rdoName.Checked Then

                    cboDisp.Items.Add(MyReader.Item("InkName").ToString)
                    cboDisp.SelectedIndex = 0

                End If
            Loop
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            MyReader.Close()
            dcIMS.Close()
            dcIMS.Dispose()
        End Try
    End If

End Sub

Mycroft Holmes

bapu2889 wrote:

dcIMS = GetDBConnection() cboDisp.Items.Clear()

I hate binding questions, what is dcIMS declared as? You are testing the count of the binding context, presumably tied to the form (me), is this what you want to test. I use datatables and have no problem with the counting of rows nor binding to a combo box. Did i mention I hate binding widgets!

Never underestimate the power of human stupidity RAH

bapu2889

dcIMS is sql data connection cboDisp is combobox and what i want to do is when i start application so first it checks if there is any data in data base is it's there then it's shows the list of inks but if it's empty then i want to display "Empty Database" in combo box thanks for your rep. have a nice day

Blue_Boy

And your code work properly or not? If no do you have any error message or where is problem with code? Try to bind cboDisp with values from database then check items on cboDisp,if ItemCount of combobox is 0 then add string "Empty Database" otherwise add list of links. Hope this will help you.

I Love T-SQL "Don't torture yourself,let the life to do it for you."

NeverHeardOfMe

Wel, given that by line 3 all you have done is establish a database connection, what can you epect your If... statement to return? Try something more along these lines

Dim MyData As New SqlCommand("SELECT * FROM IMS", dcIMS
MyReader = MyData.ExecuteReader
If MyReader.HasRows Then
Do While MyReader.Read
' ...whatever
Loop
Else
cboDisp.Text = "Empty Database"
End If
MyReader.Close()
dcIMS.Close()

As an aside, you should not, as a rule, leave Closing your database connection to the Finally clause of a Try block - what happens if an error occurs outside that? Instead, open your Db connections at the last moment possible, and close them immediately after, and test for an open connection in Catch part - eg:

Dim dbConn As New SqlConnection
...
Try
..some code
dbConn.Open
.. data amnipulation
dbConn.Close
..more code
Catch
If dbConn.State = ConnectionState.Open Then dbConn.Close()
...
End Try
...

bapu2889

Hi Phil first of all thank you very much for your mail and rep. yes it works fine and now it's shows "empty database" if database is empty else it shows inkID so great i didn't know about the has rows but now i learn thanks again :) have a nice day :rose: