2 Replies - 937 Views - Last Post: 28 April 2012 - 07:50 AM Rate Topic: -----

#1 AN1554  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 130
  • Joined: 03-April 11

Check if a record with a specific ID exists in a table

Posted 28 April 2012 - 06:53 AM

I want to check if a record with ID 1 exists.

rs.Open "SELECT * FROM tblTransaction WHERE TransactionID = 1", DBConn


There are no records with ID 1. rs.Fields.Count returns 9. How can it?

This post has been edited by AN1554: 28 April 2012 - 06:54 AM

Is This A Good Question/Topic? 0
  • +

Replies To: Check if a record with a specific ID exists in a table

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3730
  • View blog
  • Posts: 6,017
  • Joined: 08-June 10

Re: Check if a record with a specific ID exists in a table

Posted 28 April 2012 - 07:39 AM

I would guess that even though the result set has no data, the meta-data for the result set is being sent, including the number of fields should be in each row.


But I would question your method there. Why, if you are only interested in seeing if the row exists, are you attempting to fetch all the data in the row? Unless you actually need the data, you should not be fetching it. It's a waste of resources. - Instead, use the COUNT() function to see how many rows there are.

I'm no VB coder, but I'd imagine it being something like this:
rs.Open "SELECT COUNT(*) FROM tblTransaction WHERE TransactionID = 1", DBConn
Set rowCount = CInt(rs(0))


Was This Post Helpful? 1
  • +
  • -

#3 AN1554  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 130
  • Joined: 03-April 11

Re: Check if a record with a specific ID exists in a table

Posted 28 April 2012 - 07:50 AM

I did not think about COUNT. rs.Fields.Count returns the number of fields, not the number of records fetched by the recordset, which rs(0) returns.

This post has been edited by AN1554: 28 April 2012 - 07:57 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1