Check if value already exists in column

[primagic]

I am using the following code to insert a record into a table. I need to be able to check to see if the value in txtFilename already exists, if it does display a message "Filename already used" otherwise go ahead and insert the record

Thanks

Private Sub AddFile1()
        Dim ConnString As [String] = ConfigurationManager.ConnectionStrings("FileawaySQLConnectionString").ConnectionString
        Dim con As New SqlConnection(ConnString)
        Dim cmd As New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "AddFile"
        cmd.Parameters.Add("@BoxID", SqlDbType.Int).Value = HiddenBoXID.Value.Trim()
        cmd.Parameters.Add("@Filenumber", SqlDbType.VarChar).Value = txtFileName.Text.Trim()

        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteScalar()


        Catch ex As Exception
            Throw ex
        Finally
            con.Close()
            con.Dispose()
        End Try
        Session("Filename") = txtFileName.Text
        lblBoxNumber.Text = "File name " + Session("Filename") + " has been added."

        Me.pnlFilesAdded.Visible = True
        Me.grdFilesAdded.Rebind()
    End Sub

This is my SP

USE [Fileaway]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddFile]
(
@BoxID int,
@FileNumber varchar(50)



)
AS
BEGIN

SET NOCOUNT ON;

INSERT INTO dbo.Files (BoxID, FileNumber)
VALUES (@BoxID, @FileNumber)
END

 

[MarkSweetland]

You'll have to modify your sp to return either an output parameter indicating insert (or not) or use raiseerror in your sp so your code can catch a SQLException.

An other way would be to verify the filename does not exists in a seperate method before the add file routine is called...


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[primagic]

I have modified my SP to look like:

USE [Fileaway]
GO
/****** Object:  StoredProcedure [dbo].[AddFile]    Script Date: 07/18/2011 16:18:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddFile]
(
@BoxID varchar(4),
@FileNumber varchar(50)

)
AS

DECLARE @ErrorResponse varchar(100)

SET @ErrorResponse = 'Success'

IF NOT EXISTS (SELECT 1 FROM dbo.Files WHERE FileNumber = @FileNumber)
BEGIN

SET NOCOUNT ON;

INSERT INTO dbo.Files (BoxID, FileNumber)
VALUES (@BoxID, @FileNumber)


END
ELSE
BEGIN
    SET @ErrorResponse = 'Filename already exists'
    GOTO ReturnErrorResponse

    
END
  
 ReturnErrorResponse:

 

SELECT

@ErrorResponse

And it seems to not work by not insert the record into the databse if it exists, but how would I notify the user of this through a message?

 

[jbenson001]

As stated previously, modify your SP in one of 3 ways:
1. Return parameter
2. OUTPUT parameter
3. Raiserror