Checking for Duplicate Record of SQL Database before Inserting & Updating

[spider2vb]

Please help me to Checking for Duplicate Record of SQL Database before Inserting & Updating

this my code please may anyone check my code

If class1.sqlcon.state = connectionstate.open then
class1.sqlcon.close()
End If
class1.sqlcon.open()

dim cmd as new sqlcommand
dim Qresault as integer

cmd = new sqlcommand (" SELECT COUNT (*) FROM suppcust WHERE ID='" & TxtID.Text & "' , class1.sqlcon)
Qresault = cmd.executescalar

If Qresault = 0 then
Dim SaveOk As Boolean = winclass.SQLExicute (" Insert Into suppcust (supcust_Code, supcust_Name) VALUES ('" & Txtsupcust_code.text & "' , '" & Txtsupcust_Name.text & "')")
If SaveOk = True Then
Msgbox ("Save OK")
TxtID.Text = ""
Txtsupcust_code.Text = ""
Txtsupcust_Name.Text = ""
FillDataGrid()
End If

MsgBox("Record already exists")
Else
If IsUpdate = True Then

Dim cmd1 As New sqlcommand (" SELECT supcust_Code FROM suppcust WHERE 1 < (SELECT COUNT(*) FROM suppcust WHERE supcust_Code = '" & Txtsupcust_code.text & "' " , class1.sqlcon)
Dim dr As SqlDataReader = cmd1.ExcuteReader()
If dr.HasRows Then
MsgBox ("The Code already exists")
End If
Txtsupcust_code.Focus()

Dim cmd2 As New sqlcommand (" SELECT supcust_Name FROM suppcust WHERE 1 < (SELECT COUNT(*) FROM suppcust WHERE supcust_Name = '" & Txtsupcust_Name.text & "' " , class1.sqlcon)
Dim dr1 As SqlDataReader = cmd2.ExcuteReader()
If dr1.HasRows Then
MsgBox ("The Name already exists")
End If
Txtsupcust_Name.Focus()

Dim UpdateOk As Boolean = winclass.SQLExicute (" UPDATE suppcust SET supcust_Code = '" & Txtsupcust_code.text & "' , supcust_Name = '" & Txtsupcust_Name.text & "' WHERE ID=" & TxtID.Text & "")
If UpdateOk = True Then
MsgBox ("Updated OK")
TxtID.Text = ""
Txtsupcust_code.Text = ""
Txtsupcust_Name.Text = ""
FillDataGrid()
End If
End If

Please i need help with my code if something wrong in code

[ReyJEnriquez]

use a stored procedure

[spider2vb]

yes am using stored procedure like save and update

[ReyJEnriquez]

Try Checking duplicates of data inside a stored procedure...just avoid checking of duplicate in the front-end side...

[spider2vb]

Else
If IsUpdate = True Then
'.. To check doublicate if the user update field Txtsupcut_code.Text
Dim cmd1 As New sqlcommand (" SELECT supcust_Code FROM suppcust WHERE 1 < (SELECT COUNT(*) FROM suppcust WHERE supcust_Code = '" & Txtsupcust_code.text & "' " , class1.sqlcon)
Dim dr As SqlDataReader = cmd1.ExcuteReader()
If dr.HasRows Then
MsgBox ("The Code already exists")
End If
Txtsupcust_code.Focus()
'.. and thid To check doublicate if the user update field Txtsupcut_Name.text 
Dim cmd2 As New sqlcommand (" SELECT supcust_Name FROM suppcust WHERE 1 < (SELECT COUNT(*) FROM suppcust WHERE supcust_Name = '" & Txtsupcust_Name.text & "' " , class1.sqlcon)
Dim dr1 As SqlDataReader = cmd2.ExcuteReader()
If dr1.HasRows Then
MsgBox ("The Name already exists")
End If
Txtsupcust_Name.Focus()

Dim UpdateOk As Boolean = winclass.SQLExicute (" UPDATE suppcust SET supcust_Code = '" & Txtsupcust_code.text & "' , supcust_Name = '" & Txtsupcust_Name.text & "' WHERE ID=" & TxtID.Text & "")
If UpdateOk = True Then
MsgBox ("Updated OK")
TxtID.Text = ""
Txtsupcust_code.Text = ""
Txtsupcust_Name.Text = ""
FillDataGrid()
End If
End If

[spider2vb]

ReyJEnriquez how i do that please learn me

[ReyJEnriquez]

You are trying to do all things in the front-end side...can i see your stored procedure??

[spider2vb]

Function to save , delete , edite

public function sqlexcute(Byval sqlstatment as string) as boolean
try
dim cmd as new sqlcommand
cmd.commandtext = sqlstatment
sqlcon.open
cmd.connection = sqlcon
cmd.executenonquery()
return true
catch ex as exception
return false
msgbox (ex.message)
finally
sqlcon.close()
end try
end function

[ReyJEnriquez]

is that a stored procedure??..it's like it is coded in vb.net not in sql

[ReyJEnriquez]

-- Author:Rey Joseph        <Author,,Name>

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SaveBiography]

   @firstname varchar(50),
   @middlename varchar(50),
   @lastname varchar(50), 
   @sexID int,
   @statusID int,
   @bioID int output

AS

BEGIN

    SET NOCOUNT ON;

    SET @firstname = LTRIM(RTRIM(@firstname))
    SET @middlename = LTRIM(RTRIM(@middlename))
    SET @lastname = LTRIM(RTRIM(@lastname))



    IF EXISTS(SELECT * FROM Biography.dbo.Bio WHERE firstname = @firstname AND middlename = @middlename AND lastname = @lastname AND bioID <> @bioID)
        BEGIN
            RAISERROR ('[Error]Duplicate name', 16, 1)
            RETURN          
        END
    IF @firstname = ''
        BEGIN
            RAISERROR ('[Error]No first name', 16, 1)
            RETURN          
        END


    UPDATE Bio SET firstname = @firstname, middlename = @middlename, lastname = @lastname, sexID = @sexID, statusID = @statusID WHERE bioID = @bioID

    IF @@ROWCOUNT = 0  /* @@ROWCOUNT holds the number of rows affected by the LAST statement */
     BEGIN
          INSERT INTO Bio(firstname,middlename,lastname, sexID, statusID ) VALUES (@firstname, @middlename, @lastname,  @sexID, @statusID )
          SELECT @bioID = SCOPE_IDENTITY()
     END  

END

[spider2vb]

yes vb.net

[spider2vb]

please anyone help me for my code

[ReyJEnriquez]

Spider2vb....just create first a stored procedure...

[Ancient Dragon]

If you make the key field(s) of the table non-duplicate then your program won't need to check for duplicates because the database will return an error.

[ReyJEnriquez]

yeah...right

[spider2vb]

how can i do that in sql server 2005

[spider2vb]

GO
ALTER TABLE urtable
ADD CONSTRAINT AK_urtable UNIQUE (column);
GO

something like this code sql

[ReyJEnriquez]

whats dat for??..