CODEWITHSUNDEEP
sql.netsql-servervb.netsql-server-2008
Hrishikesh
Hrishikesh

Reputation: 632

Checking if a row already exists in SQL Server database using stored procedure

I have inserted two rows with the same content of a table still the code for searching existing data is not returning the desired result.

This is my VB.Net code:

Private Sub BtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSave.Click

    Try

If DateTimePicker1.Value.Date >= DateTime.Parse(TxtDateFrom.Text) And DateTimePicker1.Value.Date <= DateTime.Parse(TxtDateTo.Text) Then


    Dim cmd1 As New SqlCommand
            cmd1 = New SqlCommand("check_sched", connection)
            cmd1.CommandType = CommandType.StoredProcedure
            Dim rd As SqlDataReader
            cmd1.Parameters.AddWithValue("@empid", Label17.Text)
            cmd1.Parameters.AddWithValue("@datesched", DateTimePicker1.Value.Date)
            cmd1.Parameters.AddWithValue("@timefrom", TimePicker1.Text)
            cmd1.Parameters.AddWithValue("@timeto", TimePicker1.Text)
            rd = cmd1.ExecuteReader()


            If rd.HasRows Then

                MsgBox("Employee already assigned a job for the particular time period", MsgBoxStyle.Information, "Change Time Period")

            Else

                Dim cmd As New SqlCommand
                cmd = New SqlCommand("insert_schedule1", connection)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@actid", SqlDbType.Int).Value = Label2.Text
                cmd.Parameters.Add("@activity", SqlDbType.NVarChar).Value = CmbActivity.Text
                cmd.Parameters.Add("@serviceid", SqlDbType.Int).Value = Label14.Text
                cmd.Parameters.Add("@servicename", SqlDbType.NVarChar).Value = TxtServiceType.Text
                cmd.Parameters.Add("@pdatefrom", SqlDbType.NVarChar).Value = TxtDateFrom.Text
                cmd.Parameters.Add("@pdateto", SqlDbType.NVarChar).Value = TxtDateTo.Text
                cmd.Parameters.Add("@deptname", SqlDbType.NVarChar).Value = Label16.Text
                cmd.Parameters.Add("@emp", SqlDbType.NVarChar).Value = CmbEmp.Text
                cmd.Parameters.Add("@empid", SqlDbType.Int).Value = Label17.Text
                cmd.Parameters.Add("@datesched", SqlDbType.DateTime).Value = myDate1
                cmd.Parameters.Add("@timefrom", SqlDbType.Time).Value = TimePicker1.Value.TimeOfDay
                cmd.Parameters.Add("@timeto", SqlDbType.Time).Value = TimePicker2.Value.TimeOfDay
                cmd.Parameters.Add("@status", SqlDbType.Int).Value = "1"
                cmd.ExecuteNonQuery()
                MsgBox("Scheduled Successfully", MsgBoxStyle.Information, "Task Schedule")
                CmbActivity.Text = ""
                TxtServiceType.Text = ""
                TxtDateFrom.Text = ""
                TxtDateTo.Text = ""
                'CmbSactivity.Text = ""
                CmbEmp.Text = ""
                DateTimePicker1.Text = Now
                TimePicker1.Text = TimeOfDay
                TimePicker2.Text = TimeOfDay
            End If
        Else
            MsgBox("Please select a date within the duration", MsgBoxStyle.Information, "Invalid Date")
        End If

    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

Can anyone help me find the error?

This is my stored procedure

create procedure check_sched
    @empid int,
    @datesched datetime,
    @timefrom datetime,
    @timeto datetime
as 
begin
    SELECT * 
    FROM tbl_schedule 
    WHERE empid = @empid 
      AND datesched = @datesched 
      AND timefrom >= @timefrom 
      AND timeto <= @timeto
end

Table schema:

CREATE TABLE [dbo].[tbl_schedule]
(   
     [actid] [int] NULL,    
     [activity] [nvarchar](350) NULL,   
     [serviceid] [int] NULL,    
     [servicename] [nvarchar](50) NULL,     
     [pdatefrom] [nvarchar](50) NULL,   
     [pdateto] [nvarchar](50) NULL,     
     [sactid] [int] NULL,   
     [sactivity] [nvarchar](500) NULL,  
     [deptname] [nvarchar](150) NULL,   
     [emp] [nvarchar](150) NULL,    
     [empid] [int] NULL,    
     [datesched] [datetime] NULL,   
     [timefrom] [datetime] NULL,    
     [timeto] [datetime] NULL,
     [actimefrom] [datetime] NULL,  
     [actimeto] [datetime] NULL,
     [completed] [int] NULL,    
     [status] [int] NULL 
) ON [PRIMARY]

Upvotes: 1

Views: 523

Answers (1)

druidicwyrm
druidicwyrm

Reputation: 500

Your check schedule uses the TimePicker1 value for both @timeFrom and @timeTo fields, while your insert uses TimePicker1 and TimePicker2 respectively. This is causing your check to look at the wrong dates.

To avoid this issue in the future and to make it easier to find these bugs, try refactoring the names to match what they are for (such as fromTimePicker and toTimePicker).

Upvotes: 4

Related Questions