ASP.net check if entry exists

Bakedgoods

Bakedgoods

Bakedgoods

Capodecina
Permabanned
Joined
31 Dec 2003
Posts
5,172
Location
Barrow-In-Furness
I'm using a detailsview to insert some information, i'll give a scenario to explain the problem...

A user inserts some information for a paticular date, they complete three fields (Date, TimeCode, Hours). If they then insert more information for the same date and same time code, there will be duplicate entries in the database for that date and timecode, meaning when I fetch the data it will be messed up.

I'm trying to check if any data exists for the selected date and timecode when the user inserts the information, the code i've come up with doesn't work though.

I've tried using...

Code: 
 Protected Sub InsertHoursDetailsView_ItemInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles InsertHoursDetailsView.ItemInserting
        Dim hoursAdapter As New ManHoursTableAdapters.tblHoursTableAdapter()
        Dim hours As ManHours.tblHoursDataTable = hoursAdapter.GetHours()

        For Each dr As System.Data.DataRow In hours.Rows()
           [B][COLOR="Red"] If e.Values("Date") And e.Values("TimeCode") = dr("Date") And dr("TimeCode") Then[/COLOR][/B]                e.Cancel = True
                ConfirmationLabel.ForeColor = Drawing.Color.Red
                ConfirmationLabel.Text = "Entry for this time code and date already exists, please check your logged hours if you wish to modify it"

            End If
        Next
    End Sub

On the line i've highlighted I get the error below when the user attempts to insert:

"Conversion from string "34" to type 'Date' is not valid."

Any help is appreciated :)
 
Change

Code: 
If e.Values("Date") And e.Values("TimeCode") = dr("Date") And dr("TimeCode") Then

to

Code: 
If e.Values("Date") = dr("Date")  And e.Values("TimeCode") = ("TimeCode") Then

Another way of doing what your doing is to set those fields so that they can't be duplicates and then trap the error returned from SQL in your code.
 
Wardie said:
That didn't give me an error but it allowed be to make a duplicate entry for the same date :(
Click to expand...

I think you have to actually cancel the inserting of the row, something like e.Cancel. Doing this from memory but can't remember the exact syntax/method.

All your currently doing is returning a message in red text.
 
You could use a sql select statement

select *
from table
where date=@date or timecode==@timecode

If that returns some rows then you can dissalow the add.

Edit: Not the actual SQL statement :D
 
Last edited:
Or as I mentioned above, set the fields in the database so that they can't be duplicates and then trap the error that SQL throws if you try and save a duplicate.
 
Heyy.

Not got this working yet. Is there anyway I can use the EXISTS command without it printing the result?

I need to cancel the Detailsview inserting using e.Cancel if the query finds a result that already exists. As you can tell from the first post this is not dependant on a primary key field so it has to be done with code
 
After debugging and trying to use the code I shown above i've found the problem.

Even though i'm using .ToString(), it's not working properly...

I checked the two dates it should be comparing and they are in a different format.

Yeah, even though i'm using .ToString it's comparing the date in two different formats

dr(Date) = #3/12/2008# (Date)

Date = "29/03/2008" {String}

Code: 
   For Each dr As System.Data.DataRow In hours.Rows()
            If (e.Values("Date").ToString() = dr("Date").ToString()) AndAlso (e.Values("TimeCode").ToString() = dr("TimeCode").ToString()) Then
                e.Cancel = True
                ConfirmationLabel.ForeColor = Drawing.Color.Red
                ConfirmationLabel.Text = "Entry for this time code and date already exists, please check your logged hours if you wish to modify it"

            End If
 
Why are you attempting to convert to a string if the values are dates?
I would just do a CType to DateTime and do the comparison of the dates.

It would probably be worthwhile having a look to see why you're getting dates in different formats as well though.
 
I've tried setting the format but it's not working :/

If e.Values("Date") = Format(dr("Date"), "dd/MM/yyyy") AndAlso e.Values("TimeCode") = dr("TimeCode") Then

Grrr

Can't get the format of the datarow date value correct, it's stored in the database as dd/mm/yyyy so it must be something to do with the dataset, I need to change how it's formatted otherwise it's not going to be able to match the dates.
 
Cracked this a different way, I had my primary keys setup wrong.

Changed two fields to a primary key rather than just the ID field then added code to catch the exception and display a message.
 
You must log in or register to reply here.
Back
Top Bottom