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.

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.ExecuteScal ar())>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

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

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.

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

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.

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