Record exists?

[TomPhillips]

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]

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.

 

[TomPhillips]

Thanks

got it thanks.

 

[edd1em]

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]

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