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.
