Checking for dulicates

[majhl]

Hello All,

I need to check data before an insert for obvious reasons!

I've set up a stored procedure in my db which is the following:

Create Procedure SPChkRefno

@refno nvarchar(5)
    
AS

SELECT count(refno) 
AS RefCount
FROM tblscreening 
WHERE refno = @refno

I have then written the following code in my .net page:

            Dim strConn As New SqlConnection
            Dim comm As SqlCommand
            Dim paramRefNo As SqlParameter
            
            strConn = New                             SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))

            comm = New SqlCommand("SPCheckDRefNo", strConn)
            comm.CommandType = CommandType.StoredProcedure

            paramRefNo = New SqlParameter("@refno", SqlDbType.NVarChar,5)
            paramRefNo.Value = txtRefNo.Text
            comm.Parameters.Add(paramRefNo)

            strConn.Open()
            comm.ExecuteScalar()

            If Not (txtRefNo.Text = String.Empty) Then

                If  Then

                  
                ???What goes here???

               End If
               
               End if

I'm puzzled as to how to complete the code, i.e. how to find out the result of the query and act accordingly. Can someone help out?

Thanks very much.

 

[dan-cat]

You need to encapsulate the processing and result of your check in a seperate function to the rest of your procedure. ie.

Public Function screenExists(strRefNo as String) as Boolean
Dim bool as Boolean = False
            
Using strConn as New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))

Using comm as New SqlCommand("SPCheckDRefNo", strConn)
comm.CommandType = CommandType.StoredProcedure
Dim paramRefNo as New SqlParameter("@refno", SqlDbType.NVarChar,5)
            paramRefNo.Value = strRefNo
            comm.Parameters.Add(paramRefNo)

            strConn.Open()

If Not IsNothing(comm.ExecuteScalar()) Then _
bool = Convert.ToBoolean(Convert.ToInt32(comm.ExecuteScalar())>0)

End Using
End Using

Return bool


End Function

Then in your insert routine...

Public Sub insertMyRecord()

If Not screenExists(Me.txtRefNo.Text) Then

' process your insertion here
Else
' the record exists so do whatever you gotta do here...

End If

End Sub

 

[majhl]

Thanks Dan. I'll implement as soon as I get the chance and see what happens.

 

[georgedwilkinson]

I need to check data before an insert for obvious reasons!

Why are the reasons obvious? Based on the title of your post, I would assume you don't want duplicate data in your table. If that's the case, why not just create a unique index on the column(s) that you don't want duplicated? Not near as fun but much more effective.

 

[majhl]

Because I'd rather give the user a nice friendly message before they get round to doing an update.

 

[dan-cat]

It's always best to use constraints at data level to preserve the integrity of your data, rather than solely relying on higher level tiers.

However this should only be used as a safety net, IMO. Should definitely be using higher level code to prevent the data tier from throwing unfriendly errors to your user with long, detailed descriptions of your 'confidential' table structures.

 

[majhl]

Thanks for that Dan. I have implemented the constraints both you and George suggested in your posts.