Checking to see if a record exists before inserting

I can't seem to get this work. I'm using SQL2005

I want to check if a record exists before entering it. I just can't figure out how to check it before hand.

Thanks in advance.

Protected Sub BTNCreateProdIDandName_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles BTNCreateProdIDandName.Click' Define data objectsDim connAs SqlConnectionDim commAs SqlCommand' Reads the connection string from Web.configDim connectionStringAs String = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString' Initialize connection conn =New SqlConnection(connectionString)' Check to see if the record existsIf comm =New SqlCommand("EXISTS (SELECT (BuilderID, OptionNO FROM optionlist WHERE (BuilderID = @.BuilderID) AND (OptionNO = @.OptionNO)", conn)Then'if the record is exists display this message. LBerror.Text ="This item already exists in your Option List."Else'If the record does not exist, add it. FYI - This part works fine by itself. comm =New SqlCommand("INSERT INTO [OptionList] ([BuilderID], [OptionNO], [OptionName]) VALUES (@.BuilderID, @.OptionNO, @.OptionName)", conn) comm.Parameters.Add("@.BuilderID", System.Data.SqlDbType.Int) comm.Parameters("@.BuilderID").Value = LBBuilderID.Text comm.Parameters.Add("@.OptionNO", System.Data.SqlDbType.NVarChar) comm.Parameters("@.OptionNO").Value = DDLProdID.SelectedItem.Value comm.Parameters.Add("@.OptionName", System.Data.SqlDbType.NVarChar) comm.Parameters("@.OptionName").Value = DDLProdname.SelectedItem.Value LBerror.Text = DDLProdname.SelectedItem.Value &" was added to your Option List."Try'open connection conn.Open()'execute comm.ExecuteNonQuery()Catch'Display error message LBerror.Text ="There was an error adding this Option. Please try again."Finally'close connection conn.Close()End Try End If End Sub

You need to execute the command to see if the record exists

comm =New SqlCommand("EXISTS (SELECT (BuilderID, OptionNO FROM optionlist WHERE (BuilderID = @.BuilderID) AND (OptionNO = @.OptionNO)", conn)

If cbool(comm.executescalar) then

|||

Hello my friend,

Try this in your SQL: -

IF EXISTS (SELECT 1 FROM optionlist WHERE BuilderID = @.BuilderID AND OptionNO = @.OptionNO)
BEGIN
RETURN 'ALREADY_EXISTS'
END
ELSE BEGIN
INSERT INTO [OptionList] ([BuilderID], [OptionNO], [OptionName])
VALUES (@.BuilderID, @.OptionNO, @.OptionName)

RETURN 'INSERT_OKAY'
END

Then execute this with Dim strResult as string = comm.ExecuteScalar(), not ExecuteNonQuery(), and check the strResult string to determine whether or not to display the LBerror text.

If you have any questions on this, please let me know.

Kind regards

Scotty

|||

Scotty,

Thanks for your help. I inserted the sql and I'm getting an error.

Incorrect syntax near ')'.
A RETURN statement with a return value cannot be used in this context.
A RETURN statement with a return value cannot be used in this context.

Line 81: Dim strResult As String = comm.ExecuteScalar()

Here is what I'm using.

I don't think I did this right..."Then execute this with Dim strResult as string = comm.ExecuteScalar(), not ExecuteNonQuery(), and check the strResult string to determine whether or not to display the LBerror text. "

Protected

Sub BTN1_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles BTN1.Click' Define data objectsDim connAs SqlConnectionDim commAs SqlCommand' Reads the connection string from Web.configDim connectionStringAsString = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString' Initialize connection

conn =

New SqlConnection(connectionString)' Check to see if the record exists

conn.Open()

comm =

New SqlCommand("IF EXISTS (SELECT 1 FROM optionlist WHERE BuilderID = @.BuilderID AND OptionNO = @.OptionNO) BEGIN()Return 'ALREADY_EXISTS' End Else : BEGIN()INSERT INTO [OptionList] ([BuilderID], [OptionNO], [OptionName]) VALUES (@.BuilderID, @.OptionNO, @.OptionName)Return 'INSERT_OKAY'End)", conn)

comm.Parameters.Add(

"@.BuilderID", System.Data.SqlDbType.Int)

comm.Parameters(

"@.BuilderID").Value = LBBuilderID.Text

comm.Parameters.Add(

"@.OptionNO", System.Data.SqlDbType.NVarChar)

comm.Parameters(

"@.OptionNO").Value = DDLProdID.SelectedItem.Value

comm.Parameters.Add(

"@.OptionName", System.Data.SqlDbType.NVarChar)

comm.Parameters(

"@.OptionName").Value = DDLProdname.SelectedItem.ValueDim strResultAsString = comm.ExecuteScalar()

' I'm not sure what to do here. to get my error message to show.

LBerror.Text = strResult.ToString

conn.Close()

EndSub

|||

Option 1

=====

You can modify the query as

SELECT COUNT(*) as CountOfRecords

FROM

FROM optionlist WHERE BuilderID = @.BuilderID AND OptionNO = @.OptionNO

If the Count is greater than 0 then you know the record exists

Option2

=======

Wrap Scotty's SQL in a Stored procedure and call the SP. I would do this way. SPs are fast and adds alayer of abstraction.

|||

rednelo:

Scotty,

Thanks for your help. I inserted the sql and I'm getting an error.

Incorrect syntax near ')'.
A RETURN statement with a return value cannot be used in this context.
A RETURN statement with a return value cannot be used in this context.

Line 81: Dim strResult As String = comm.ExecuteScalar()

That because you added a set of parentheses following the BEGIN statement that Scott did not have in his supplied code. Remove those and you should have better luck.

|||

I removed the parentheses. Don't know how they got there...

I also tired putting it in a sproc, but I keep getting the same message.

Msg 178, Level 15, State 1, Line 3

A RETURN statement with a return value cannot be used in this context.

Msg 178, Level 15, State 1, Line 9

A RETURN statement with a return value cannot be used in this context.

|||

rednelo:

I removed the parentheses. Don't know how they got there...

I also tired putting it in a sproc, but I keep getting the same message.

Msg 178, Level 15, State 1, Line 3

A RETURN statement with a return value cannot be used in this context.

Msg 178, Level 15, State 1, Line 9

A RETURN statement with a return value cannot be used in this context.

A RETURN statement can only return an integer value. Scotty made a typo in his original code. Use SELECT instead:

SELECT 'ALREADY_EXISTS'

and

SELECT 'INSERT_OKAY'

|||

Those little typos can really cause one to pull their hair out! 4 hours later... We finally got it.

Thank-you for your help this works well.