Record exists?

TomPhillips

Active member
Joined
Feb 24, 2005
Messages
33
Programming Experience
10+
What is the simplest and quickest code to determine if a record exists? Say I have a table of holidays and dates. I have a date I want to see is a holiday. Yes, No, thats all. So I try something like:

Dim myDate as DateTime = DateTime.now()
Dim isHoliday as Boolean = False
Dim strSQL as string = "Select count(*) from HolTable where hDate = '" & myDate & "''

How to get isHoliday set to true for '12/25/2005' and false for now()? I could use a data reader but that seems like overkill. What am I missing?
 

Paszt

Staff member
Joined
Jun 3, 2004
Messages
1,500
Location
Raleigh, NC - USA
Programming Experience
Beginner
The ExecuteNonQuery Function of a command object (either System.Data.OleDb.OleDbCommand or System.Data.SqlClient.SqlCommand depending on the type of database) executes a statement against a connection and returns the number of rows affected. A value greater than or equal to 1 would indicate a holiday.
The SQL statement would look similar to: "SELECT * FROM HolTable WHERE hDate = #" & myDate & "#"
I enclose the date in pound signs, but that too is dependant on the type of database used.
Now if you have the data in memory (in a dataTable or dataSet) then you can create a dataView and set the RowFilter property to the WHERE clause and then check the number of rows.
 
Last edited:

edd1em

Active member
Joined
Jul 21, 2004
Messages
28
Programming Experience
3-5
Hi,
Paszt's method would work excellent.

Another method would be to create a stored procedure.
The stored procedure (something like this):

ALTER PROCEDURE dbo.spDS_Date_Exist_Check
(
@Date Date
)
AS
SET NOCOUNT OFF;
Select count(*) From tblDates where Date = @Date

//**************
This will return a count of records that have the date passed.

-Edward
 

DavidT_macktool

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
here is another way

After your select statement and dataset load is performed...

Dim c As Integer = Me.BindingContext(DsDates1, "Dates").Count
If c > 0 Then
'do stuff here
else
MessageBox.Show("No Holidays")
end if
 
Last edited:
Top Bottom