missing records ...

[college_amy]

The problem I am having is when searching a database my repeater or something else is skipping the first record of every search.

For example, I have three records that are specific to the state of Kansas. When I search on Kansas only the second and third record shows up, not the first.

This happens with EVERY search that is done. It just doesn't show the first result for any search and goes to the next. It shows every result after the first.

 

Here is the code:

DBConnection = New OleDbConnection _

("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=d:\mypath.mdb")

DBConnection.Open()

SQLString = "SELECT * FROM tablename " & _

"WHERE state = '" & state.SelectedItem.Value & "'"

DBCommand = New OleDbCommand(SQLString, DBConnection)

DBReader = DBCommand.ExecuteReader()

If NOT DBReader.read() THEN

err.text="No Records found!"

working.Visible ="false"

Else

err.text="Search is Complete!"

working.Visible="true"

Pro_Display.DataSource = DBReader

Pro_Display.DataBind()

 

END IF

DBReader.Close()

DBConnection.Close()

 

End Sub

 

I am using a Repeater to display the results.

Any help is greatly appreciated!!!

[kahlua001]

when u check for DBReader.read(), it moves to first record.

[college_amy]

I know that it does that but how do I get that first record to be included in the results rather than omitting it from the display?

[college_amy]

Geesh.. never mind... I have the answer - in case anyone else stumbles on this little problem... here is the solution:

 

SQL = "SELECT Count(*) FROM table WHERE criteria"

DBCommand = New OleDbCommand(SQL, DBConnection)

If DBCommand.ExecuteScalar() = 0 Then

err.Text = "No matching records"

Else

SQL = "SELECT * FROM Table WHERE criteria"

DBCommand = New OleDbCommand(SQL, DBConnection)

DBReader = DBCommand.ExecuteReader()

While DBReader.Read()

...display records

End While

DBReader.Close()

End If

[kahlua001]

That is one way. But you are making two trips to the DB. You can fill a dataset with your sql statement, then check for the dataset.items.count for greater than 0. If there are items in your dataset,then loop thru it, bind it to something, etc.

 

Another way is to just bind whatever control you want populated with your sql. Then check for the items.count of that control, and hide if it returns 0.

[college_amy]

Do you mind giving me an example of how that would look? The second way sounds better, but I can't see it... I am still learning asp.net... and the book I have doesn't explain much at all!

[college_amy]

actually I think I like the idea of working with datasets better than keeping the connection to my database open.. so if you know a good way to check using a dataset.. that would be appreciated!!

[mr relaxo]

'create a dataset
dim ds as new dataset

dim con as new OleDbConnection("your connection string")
dim da as new OleDbAdapter("select * from table", con)
try
da.fill(ds,"datatablename")
return true
catch e as exception
return false
end try

if ds.tables("datatablename").rows.count = 0
then err.text = "no matching records"
end if

 

the best thing to do with this type of operation is make it reusable by sticking it in a function and passing the sql statement, dataset and possibly a data table name as parameters, that way you can use it to fill any dataset you want.

[college_amy]

Thanks for the code... I have implemented it and ran it and the error I am getting is this:

MYColumnName is neither a DataColumn nor a DataRelation for table MYTable.

 

I am using a repeater to display and that is where the error is...

<%# Container.DataItem("MYColumnName") %>

 

Here is the code that runs it all:

SQLString = "SELECT Count(*) FROM MYTable " & _

"WHERE state = '" & state.SelectedItem.Value & "'AND Display = 'Yes'"

DBAdapter = New OleDbDataAdapter(SQLString, DBConnection)

DBDataSet = New DataSet()

DBAdapter.Fill(DBDataSet, "MYTable")

DBDataView = New DataView(DBDataSet.Tables("MYTable"))

If DBDataSet.Tables("MYTable").Rows.Count = 0 Then

err.Text = "No matching records"

working.Visible="false"

Else

err.Text="Search is Complete!"

working.Visible="true"

Pro_Display.DataSource = DBDataView

Pro_Display.DataBind()

 

End If

 

End Sub

 

 

What am I missing here that is causing it to puke?

[mr relaxo]

Probably because you're returning a count of records in your database table rather than the records themselves which is what you want. the line ds.tables("tablename").rows.count will tell you how many records were returned, so make the sql statement "select * from ..... where...."

[college_amy]

You know that old joke about not being able to see your own mistakes??? DUHHH>>> SOMEBODY SLAP ME UPSIDE THE HEAD AND SCREAM DUH! Thanks, Mr. Relaxo... I can't believe I didn't catch that myself.. I don't deserve to program!

Geesh!