CODEWITHSUNDEEP
sqlvb.netms-access
Taffs
Taffs

Reputation: 153

Detecting if data exists for OleDB COUNT query

I'm trying to pull data from ACCESS database.

As it is, the code works, and gives no errors... However, I can't seem to be able to display a messagebox if the record doesn't exist. It simply returns an empty string.

        Using dbCon = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source = '" & Application.StartupPath & "\Res\T500G.accdb'")

        dbCon.Open()

        Dim Query1 As String = "SELECT SUM(Total) FROM [T500] WHERE Pro=@Pro"

        Dim cmd1 As OleDb.OleDbCommand = New OleDbCommand(Query1, dbCon)
        cmd1.Parameters.AddWithValue("@Pro", ComboBoxBP.SelectedItem.ToString)

        Dim reader As OleDb.OleDbDataReader
        reader = cmd1.ExecuteReader


        While reader.Read()

            TextBox1.Text = reader.GetValue(0).ToString

        End While

        reader.Close()
        dbCon.Close()

    End Using

I've tried using If reader.hasrows then display result in textbox, else show messagebox etc, but it doesn't work.

        If reader.HasRows Then
            While reader.Read()

                TextBox1.Text = reader.GetValue(0).ToString

            End While
        Else

            MessageBox.Show("asd")

        End If

If I remove the .ToString from reader.GetValue(0) I get an error if the selected item from the combobox doesn't exist in the database. Cannot convert DBNull to integer or something.

So my question is, how to display a messagebox if the record "@Pro" doesn't exist?

Thanks~

Fixed(Workaround) with this

Dim ValueReturned As String
        While reader.Read()

            ValueReturned = reader.GetValue(0).ToString

            If ValueReturned = "" Then

                MessageBox.Show("Not Found", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

            Else

                MetroTextBox1.Text = ValueReturned

            End If

        End While

Upvotes: 1

Views: 847

Answers (2)

Ňɏssa Pøngjǣrdenlarp
Ňɏssa Pøngjǣrdenlarp

Reputation: 38875

Using OleDbDataReader is suboptimal for your query because it is never going to return a set of records to traverse:

Dim sql = "SELECT SUM(...=@p1"

' rather than sprinkling you connection string all over
' the app, you can make a function returning one
Using conn As OleDbConnection = GetConnection(),
        cmd As New OleDbCommand(sql, GetConnection())
    conn.Open())

    ' ToDo: Check that ComboBoxBP.SelectedItems.Count >0 before this
    cmd.Parameters.AddWithValue("@p1", ComboBoxBP.SelectedItem.ToString)

    ' execute the query, get a result
    Dim total = cmd.ExecuteScalar

    ' if there is no matches, OleDb returns DBNull
    If total Is System.DBNull.Value Then
        ' no matches
        MessageBox.Show("No matching records!"...)
    Else
        MessageBox.Show("The Total is: " & total.ToString()...)
    End If

End Using   ' disposes of the Connection and Command objects

Alteratively, you could use If IsDBNull(total) Then.... If you want, you can also convert it:

Dim total = cmd.ExecuteScalar.ToString()
' DBNull will convert to an empty string
If String.IsNullOrEmpty(total) Then
    MessageBox.Show("No Soup For You!")
Else
    ...
End If

Upvotes: 1

S. Adam Nissley
S. Adam Nissley

Reputation: 776

You could change your query to (this is for SQL-Server):

IF EXISTS (SELECT * FROM [T500] WHERE Pro = @Pro) SELECT SUM(Total) FROM [T500] WHERE Pro = @Pro ELSE SELECT 'Not Found'

And change your code to:

    Dim ValueReturned As String
    While reader.Read()

        ValueReturned = reader.GetValue(0).ToString

    End While
    If ValueReturned Is Nothing OrElse ValueReturned = "Not Found" Then
        MessageBox.Show("Not Found", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    Else
        TextBox1.Text = ValueReturned
    End If

Upvotes: 1

Related Questions