Returning row count of 0 from a dataset

[Greenmet29]

Hi, I am wondering why my program won't return a row count of zero from a dataset.

This is my code:

Dim qa As New DataSetTableAdapters.TrucksTableAdapter
            Dim i As Integer = qa.countTrucksQA(txtTruckID.Text)
            If i >= 1 Then
                MsgBox("1")
            Else
                MsgBox("none")
            End If

The countTrucksQA is:

SELECT COUNT(*) FROM Trucks WHERE truckID=?

It works fine when it finds a truck, returning a value of 1 but when it doesn't return a truck I get an exception of:

"Object variable or with block variable not set"

Any ideas what I might be doing wrong?

 

[s1ckOh]

Dim ds As DataSet = Me.TrucksDataSet

Dim da As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM Trucks WHERE TruckID=" & [whatever], [oledbconnection])

'fill the dataset with info
da.Fill(ds, "Trucks")

'Set up data viewer
Dim dv As DataView = New DataView(ds.Tables("Trucks"))

 If ds.Tables("Trucks").Rows.Count > 0 Then
     [your code here]
End If

Hope this works for you.

 

[Greenmet29]

        Try

            Dim ole As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\db.accdb")
            Dim ds As DataSet = Me.dbDataSet

            Dim da As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM Trucks WHERE truckID=" & txtTruckID.Text, ole)

            'fill the dataset with info
            da.Fill(ds, "Trucks")

            'Set up data viewer
            Dim dv As DataView = New DataView(ds.Tables("Trucks"))
            MsgBox("hi")
            MsgBox(ds.Tables("Trucks").Rows.Count)
            If ds.Tables("Trucks").Rows.Count = 0 Then
                MsgBox("nothing")
            End If
        Catch ex As Exception
            MsgBox(ex.InnerException.Message)
        End Try

this is what i have, and when I run it now I get nothing, not even the "hi" message box

 

[s1ckOh]

This is what I use for my connection, works for me but you may have a connection error before the message box and its catching the exception. did you get an error? also when catching i like to use... catch : msgbox(errortostring) : end try.

Dim cn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
                                                            System.IO.Directory.GetCurrentDirectory & "\[yourdb].mdb;")

also the dataview line isnt needed. i just used that to bind to a combobox... forgot to remove it

 

[Greenmet29]

OK.. i got it to work by putting single quotes around the input. but not it comes up with 2 records every time, regardless of if I put something that is in the database or not. (there are only 2 records in the database)

 

[s1ckOh]

when you create the dataset from the database those records are going to stay in the dataset until it is cleared so before your connection declaration say...

ds.Tables("Trucks").Rows.Clear()

this will clear out the dataset and then you can refill it with the oledbadapter with the selected truckID

Dim ds as DataSet = Me.dbDataSet
ds.Tables("Trucks").Rows.Clear()
[FONT=monospace]Dim ole As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\db.accdb")[/FONT]

etc...

 

[Greenmet29]

awesome! Thanks so much for your help!