Code behind : Check database to see if the record already exists

[YMCMb]

Hi everyone,

Here i am once again,

I have a business logic layer, which will be called upon by aspx page,

The BLL has a SQL statement which add's records on to the database, which works fine, however i would like to make sure the record that is being added does not already exist! If it does i will display a message saying record already exists try again!

Any help would be really appriciated! thanks

Kushal

[prashantchalise]

Hi Kushal,

I guess you are talking about creating the proper SQL statement.

May be this can help you

Dim sqlStr As String
			sqlStr = "IF EXISTS(SELECT 'True' FROM MyTable WHERE strCol = 'SearchParam') "
			sqlStr &= "BEGIN "
			sqlStr &= "SELECT 'record exists !' "
			sqlStr &= "END ELSE BEGIN "
			sqlStr &= "SELECT 'added New Reocrd.' "
			sqlStr &= "INSERT INTO MyTable(param1,param2...) VALUES(@param1,@param2)"
			sqlStr &= "END"

Now, you need to execute the statement and parse the return string using your connection.

If (returnStr = "record exists !") Then
				'Record Exist.
			Else
				'Record Inserted
			End If

Hope this help.
Thanks,

[YMCMb]

Not quite my example that i would like to give is as follows:

The code for the Business logic layer should be something like the following.
ASP.NET Syntax (Toggle Plain Text)
<System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Insert, True)> _
    Public Function InsertModules( _
        ByVal StudentName As String, ByVal StudentId As Nullable(Of Double), ByVal TotalMark As Nullable(Of Int32)) _
        As Boolean
 
 
 
        Dim Modules As New Students.ModulesDataTable()
        Dim Module As Students.ModulesRow = Bookings.NewModuleRow()
 
        Module.StudentName = StudentName.ToString
        Module.StudentId = StudentId.Value
        Module.TotalMark = TotalMark.Value
 
        Modules.AddModuleRow(Module)
        Dim rowsAffected As Integer = AdapterModules.Update(Modules)
 
        Return rowsAffected = 1
 
    End Function

Then the Default.aspx.vb

does the following:

Dim diditwork As Boolean = bll.InsertModules(StudentName, StudentId, TotalMark)
 
        If diditwork = True Then
            Dim ModulesMarkAdd = "Module Added"
            ModulesAdded.Text = ModulesMarkAdd
        End If

So now the above code works however the business logic layer must check to see if the record doesnt already exist, if it does it should display a error message saying! wait this record already exists, Try again!

Else record added.

I Hope this makes it a little bit more clearer to what i am trying to achieve!

[Akash Saikia]

Hi,

1 )In your BL,put some code to check whether the StudentId already exists in your given table.
If it exists,then return 2,if it doesnot exist,then only execute the insert statement or call the Data Layer insert statement/Sp,method.
2 ) Instead of returning boolean,return three differnt values
0 for successful insertion
1 for certain error for
2 if record already exists

The code for the Business logic layer should be something like the following.
ASP.NET Syntax (Toggle Plain Text)
<System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Insert, True)> _
    Public Function InsertModules( _
        ByVal StudentName As String, ByVal StudentId As Nullable(Of Double), ByVal TotalMark As Nullable(Of Int32)) _
        As Boolean
 
 
 
        Dim Modules As New Students.ModulesDataTable()
        Dim Module As Students.ModulesRow = Bookings.NewModuleRow()
 
        Module.StudentName = StudentName.ToString
        Module.StudentId = StudentId.Value
        Module.TotalMark = TotalMark.Value
 ' Put your check code here that wheter a record with the same student Id exist in 
 ' the table or not
 'ex select count(*) from table where studentid=@studentid
 'put a code like written above in ur sql and make another BL and DL method for that
 ' and then call here
 'If it returns more than 0,it means a record already exist,so simply return 3
  'else execute the below mentioned code by you
        Modules.AddModuleRow(Module)
        Dim rowsAffected As Integer = AdapterModules.Update(Modules)
 
        Return rowsAffected = 1
 
    End Function

Hope it Helps... :)

[YMCMb]

See thats the thing i have a function which checks if the Student id exists which is as follows:

------BLL------

<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetMarksByStudentId(ByVal Student_id As Int32) _
As SalesCalc.scMarksDataTable
Return AdapterMyMarks.GetMarksByStudentId(Student_id)
End Function
_________________________________________________________________________________

The code for the Business logic layer should be something like the following.
ASP.NET Syntax (Toggle Plain Text)
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Insert, True)> _
Public Function InsertModules( _
ByVal StudentName As String, ByVal StudentId As Nullable(Of Double), ByVal TotalMark As Nullable(Of Int32)) _
As Boolean

Dim Modules As New Students.ModulesDataTable()
Dim Module As Students.ModulesRow = Bookings.NewModuleRow()

Module.StudentName = StudentName.ToString
Module.StudentId = StudentId.Value
Module.TotalMark = TotalMark.Value

Modules.AddModuleRow(Module)
Dim rowsAffected As Integer = AdapterModules.Update(Modules)

Return rowsAffected = 1

End Function

___________ --------__________------_________--------____________________

Default.aspx

Dim diditwork As Boolean = bll.InsertModules(StudentName, StudentId, TotalMark)

If diditwork = True Then
Dim ModulesMarkAdd = "Module Added"
ModulesAdded.Text = ModulesMarkAdd
End If

_______________________________________________________________________

So in other words the function in BLL is already there but because im new to vb im not sure how to call upon it!
if that makes any sense? can you help?

[YMCMb]

Inserted a unique key to see if it already exists
which solved it thanks :)