VB.NET code to search if a record already exists or not

Q:

VB.NET 2005, Search string, Visual Basic .NET, VB.NET, Visual Basic 2005, SQL Database, Database programming

hi! i m fresher
i want a vb.net code to serch record from sql database table by using 2 fields of table as a condition i.e client name and client code before saving any new record and if record already exist in table it give me message for that
i m sending u code plz rectify it and reply

Imports System.Data.SqlClient
Imports System.Data

Public Class FrmClientMaster

#Region "Declaration"

Dim con As New SqlConnection("server=kd088;user id=sa;pwd=sa;database=koresbpo")
Dim da As New SqlDataAdapter("select * from client_master", con)
Dim ds As New DataSet
Dim dt As DataTable
Dim dr As DataRow
Dim dr1 As DataRow
Dim x As New SqlCommandBuilder(da)
Dim cur As Integer
Dim flag As Boolean

#End Region

#Region "Client Load Event"

Private Sub FrmClientMaster_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
adapter()
Catch ex As Exception
MsgBox(Err.Description)
End Try
End Sub

#End Region

#Region "Procedure Sub Adapter"

Sub adapter()
Try
ds.Reset()
da = New SqlDataAdapter("select * from client_master", con)
da.Fill(ds, "client_master")
dt = ds.Tables("client_master")
Dim i As Integer
LstClient.Items.Clear()
For i = 0 To dt.Rows.Count - 1
dr = dt.Rows(i)
LstClient.Items.Add(dr("cli_name"))
Next
LstClient.Sorted = True
Catch ex As Exception
MsgBox(Err.Description)
End Try
End Sub

#End Region

#Region "Procedure Sub Settable"

Sub settable()
Try
ds.Reset()
da = New SqlDataAdapter("select * from client_master", con)
da.Fill(ds, "client_master")
dt = ds.Tables("client_master")
Catch ex As Exception
MsgBox(Err.Description)
End Try
End Sub

#End Region

#Region "Procedure Sub Blank"

Sub blank()
Try
txtCliCode.Text = ""
txtCliNm.Text = ""
txtAddr.Text = ""
txtAddr1.Text = ""
txtPIN.Text = ""
txtFax.Text = ""
txtContPerson1.Text = ""
txtMbNo1.Text = ""
txtTelNo1.Text = ""
txtEmail1.Text = ""
txtContPerson2.Text = ""
txtMbNo2.Text = ""
txtTelNo2.Text = ""
txtEmail2.Text = ""
txtCliNm.Focus()
Catch ex As Exception
MsgBox(Err.Description)
End Try
End Sub

#End Region

#Region "Procedure Sub Showrecord"

Sub showrecord()
Try
Dim dr As DataRow
dr = dt.Rows(cur)
txtCliCode.Text = IIf(IsDBNull(dr("cli_code")) = True, "", dr("cli_code"))
txtCliNm.Text = IIf(IsDBNull(dr("cli_name")) = True, "", dr("cli_name"))
txtAddr.Text = IIf(IsDBNull(dr("add1")) = True, "", dr("add1"))
txtAddr1.Text = IIf(IsDBNull(dr("add2")) = True, "", dr("add2"))
txtPIN.Text = IIf(IsDBNull(dr("pin_no")) = True, "", dr("pin_no"))
txtFax.Text = IIf(IsDBNull(dr("fax")) = True, "", dr("fax"))
txtContPerson1.Text = IIf(IsDBNull(dr("cont_person1")) = True, "", dr("cont_person1"))
txtMbNo1.Text = IIf(IsDBNull(dr("mbl_no1")) = True, "", dr("mbl_no1"))
txtTelNo1.Text = IIf(IsDBNull(dr("tel_no1")) = True, "", dr("tel_no1"))
txtEmail1.Text = IIf(IsDBNull(dr("email_id1")) = True, "", dr("email_id1"))
txtContPerson2.Text = IIf(IsDBNull(dr("cont_person2")) = True, "", dr("cont_person2"))
txtMbNo2.Text = IIf(IsDBNull(dr("mbl_no2")) = True, "", dr("mbl_no2"))
txtTelNo2.Text = IIf(IsDBNull(dr("tel_no2")) = True, "", dr("tel_no2"))
txtEmail2.Text = IIf(IsDBNull(dr("email_id2")) = True, "", dr("email_id2"))
Catch ex As Exception
MsgBox(Err.Description)
End Try
End Sub

#End Region

#Region "Save"

Private Sub cmdSave_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
Try
If txtCliNm.Text = "" Then
MsgBox("Blank Records can't Save, Please Insert Records to Save!!!")
txtCliNm.Focus()
Else
Dim qry As String
qry = "insert into client_master(cli_name,add1,add2,pin_no,fax,cont_person1,mbl_no1,tel_no1,email_id1,cont_person2,mbl_no2,tel_no2,email_id2)values('" & Trim(txtCliNm.Text) & "','" & Trim(txtAddr.Text) & "','" & Trim(txtAddr1.Text) & "'," & Trim(Val(txtPIN.Text)) & "," & Trim(Val(txtFax.Text)) & ",'" & Trim(txtContPerson1.Text) & "'," & Trim(Val(txtMbNo1.Text)) & "," & Trim(Val(txtTelNo1.Text)) & ",'" & Trim(txtEmail1.Text) & "','" & Trim(txtContPerson2.Text) & "'," & Trim(Val(txtMbNo2.Text)) & "," & Trim(Val(txtTelNo2.Text)) & ",'" & Trim(txtEmail2.Text) & "')"
Dim cmd As New SqlCommand(qry, con)
con.Open()
cmd.ExecuteNonQuery()
da.Update(dt)
con.Close()
MsgBox("Record Saved Successfully!!!")
adapter()
settable()
flag = True
blank()
cur = dt.Rows.Count
End If
con.Close()
Catch ex As Exception
MsgBox(Err.Description)
con.Close()
End Try
End Sub

#End Region

#Region "Modify"

Private Sub cmdModify_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdModify.Click
Try
If txtCliNm.Text = "" Then
MsgBox("Please Select Record which you want to Modify!!!")
txtCliNm.Focus()
Else
Dim Str As String
Str = "update client_master set cli_name = '" & Trim(txtCliNm.Text) & "',add1 = '" & Trim(txtAddr.Text) & "',add2 = '" & Trim(txtAddr1.Text) & "',pin_no = " & Trim(Val(txtPIN.Text)) & ",fax = " & Trim(Val(txtFax.Text)) & ",cont_person1 = '" & Trim(txtContPerson1.Text) & "',mbl_no1 = " & Trim(Val(txtMbNo1.Text)) & ",tel_no1 = " & Trim(Val(txtTelNo1.Text)) & ",email_id1 = '" & Trim(txtEmail1.Text) & "',cont_person2 = '" & Trim(txtContPerson2.Text) & "',mbl_no2 = " & Trim(Val(txtMbNo2.Text)) & ",tel_no2 = " & Trim(Val(txtTelNo2.Text)) & ",email_id2 = '" & Trim(txtEmail2.Text) & "' where cli_code = " & Trim(Val(txtCliCode.Text)) & ""
Dim cmd1 As New SqlCommand(Str, con)
con.Open()
cmd1.ExecuteNonQuery()
con.Close()
da.Update(dt)
MsgBox("Record Modified Successfully!!!")
adapter()
flag = True
settable()
blank()
cur = dt.Rows.Count
End If
con.Close()
Catch ex As Exception
MsgBox(Err.Description)
con.Close()
End Try
End Sub

#End Region

#Region "Clear"

Private Sub cmdClear_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClear.Click
Try
blank()
Catch ex As Exception
MsgBox(Err.Description)
End Try
End Sub

#End Region

#Region "Exit"

Private Sub cmdExit_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExit.Click
Try
Me.Close()
Catch ex As Exception
MsgBox(Err.Description)
End Try
End Sub

#End Region

#Region "ListBox"

Private Sub LstClient_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LstClient.SelectedIndexChanged
Try
cur = 0
Dim qry As String
qry = "select * from client_master where cli_name = '" & Trim(LstClient.SelectedItem) & "'"
Dim c As New SqlCommand(qry, con)
Dim s As SqlDataReader
con.Open()
s = c.ExecuteReader()
While s.Read()
txtCliCode.Text = Trim(s.GetValue(0))
txtCliNm.Text = Trim(s.GetValue(1))
txtAddr.Text = Trim(s.GetValue(2))
txtAddr1.Text = Trim(s.GetValue(3))
txtPIN.Text = Trim(s.GetValue(4))
txtFax.Text = Trim(s.GetValue(5))
txtContPerson1.Text = Trim(s.GetValue(6))
txtMbNo1.Text = Trim(s.GetValue(7))
txtTelNo1.Text = Trim(s.GetValue(8))
txtEmail1.Text = Trim(s.GetValue(9))
txtContPerson2.Text = Trim(s.GetValue(10))
txtMbNo2.Text = Trim(s.GetValue(11))
txtTelNo2.Text = Trim(s.GetValue(12))
txtEmail2.Text = Trim(s.GetValue(13))
End While
con.Close()
Catch ex As Exception
MsgBox(Err.Description)
con.Close()
End Try
End Sub

#End Region

#Region "Validations"

Private Sub txtCliCode_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtCliCode.KeyPress
ans = Check(1, AscW(e.KeyChar()))
If ans = 0 Then
e.KeyChar = ChrW(0)
End If
End Sub

Private Sub txtCliCode_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtCliCode.Validated
txtCliCode.Text = txtCliCode.Text.ToUpper
End Sub

Private Sub txtCliNm_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtCliNm.Validated
txtCliNm.Text = txtCliNm.Text.ToUpper
End Sub

Private Sub txtAddr_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtAddr.Validated
txtAddr.Text = txtAddr.Text.ToUpper
End Sub

Private Sub txtAddr1_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtAddr1.Validated
txtAddr1.Text = txtAddr1.Text.ToUpper
End Sub

Private Sub txtPIN_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtPIN.KeyPress
ans = Check(1, AscW(e.KeyChar()))
If ans = 0 Then
e.KeyChar = ChrW(0)
End If
End Sub

Private Sub txtPIN_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtPIN.Validated
txtPIN.Text = txtPIN.Text.ToUpper
End Sub

Private Sub txtFax_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtFax.KeyPress
ans = Check(1, AscW(e.KeyChar()))
If ans = 0 Then
e.KeyChar = ChrW(0)
End If
End Sub

Private Sub txtFax_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtFax.Validated
txtFax.Text = txtFax.Text.ToUpper
End Sub

Private Sub txtContPerson1_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtContPerson1.Validated
txtContPerson1.Text = txtContPerson1.Text.ToUpper
End Sub

Private Sub txtMbNo1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtMbNo1.KeyPress
ans = Check(1, AscW(e.KeyChar()))
If ans = 0 Then
e.KeyChar = ChrW(0)
End If
End Sub

Private Sub txtMbNo1_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtMbNo1.Validated
txtMbNo1.Text = txtMbNo1.Text.ToUpper
End Sub

Private Sub txtTelNo1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtTelNo1.KeyPress
ans = Check(1, AscW(e.KeyChar()))
If ans = 0 Then
e.KeyChar = ChrW(0)
End If
End Sub

Private Sub txtTelNo1_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtTelNo1.Validated
txtTelNo1.Text = txtTelNo1.Text.ToUpper
End Sub

Private Sub txtEmail1_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtEmail1.Validated
txtEmail1.Text = txtEmail1.Text.ToUpper
End Sub

Private Sub txtContPerson2_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtContPerson2.Validated
txtContPerson2.Text = txtContPerson2.Text.ToUpper
End Sub

Private Sub txtMbNo2_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtMbNo2.KeyPress
ans = Check(1, AscW(e.KeyChar()))
If ans = 0 Then
e.KeyChar = ChrW(0)
End If
End Sub

Private Sub txtMbNo2_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtMbNo2.Validated
txtMbNo2.Text = txtMbNo2.Text.ToUpper
End Sub

Private Sub txtTelNo2_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtTelNo2.KeyPress
ans = Check(1, AscW(e.KeyChar()))
If ans = 0 Then
e.KeyChar = ChrW(0)
End If
End Sub

Private Sub txtTelNo2_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtTelNo2.Validated
txtTelNo2.Text = txtTelNo2.Text.ToUpper
End Sub

Private Sub txtEmail2_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtEmail2.Validated
txtEmail2.Text = txtEmail2.Text.ToUpper
End Sub

#End Region

End Class

ASKED: Feb 11 2009 9:49 AM GMT

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

RELATED QUESTIONS

A:

RATE THIS ANSWER

0
Click to Vote:

0
0

If you are getting errors with the code you posted, please provide the exact error messages or tell us what the problem with that code is.

If this code is working but you want to add the necessary logic to check if the record already exists before inserting it, you could put the insert into a try-catch block, and check for the specific error number for duplicated key (which I think is 2627). This would work if you have a unique index/constraint on those fields. Another way could be to issue a select statement to get a record with the same client name and code that the one you are going to insert, and only execute the insert if you get no rows.

or try using the HasRows property.

Dim cmd As SqlCommand = new SqlCommand("SELECT * FROM Table1 WHERE Id = 1", conn)
Dim reader As SqlDataReader = cmd.ExecuteReader()
IF Not reader.HasRows Then
'the data does not exist.
Else
'The record exists
End IF

The following site might help you
A programming guide

----------------- kccrosser
From a programming style standpoint, I prefer to use try/catch and avoid doing the fetch/count transactions just to see whether I can insert or update a record.
If you think about the overhead of the transactions, including network round-trips, etc., it is more efficient to simply try the insert and then handle the collisions when they occur. Unless a collision is highly likely (like 75% or more), handling the collisions after the fact is much more efficient.

try {
insert ...
}
catch {
-- handle collisions here
}

Also - whenever possible, avoid "select *" or "select count(*)". If you need to test for existence of a record, "where exists" is perfect, and you can select a literal "1" as the result.
For example, in Oracle:
select count(*) from mytable where <condition>
If there are N records matching <condition>, this query will result in N+1 fetches.
select * from mytable where <condition>
This is worse, as the first one might only hit index records - this will do at least N+1 data fetches from the table.
select 1 from dual where exists (select 1 from mytable where <condition>)
This will do ONE fetch against mytable (hopefully against the index) - it doesn't need to count all the records, just find out if there is at least one record meeting the criteria.

Last Answered: Nov 10 2009 2:28 PM GMT by Carlosdl

29855 pts.

Latest Contributors: Kccrosser

1850 pts., Mk4567

45 pts., Sanisto

35 pts.

View History

A:

If you are getting errors with the code you posted, please provide the exact error messages or tell us what the problem with that code is.

If this code is working but you want to add the necessary logic to check if the record already exists before inserting it, you could put the insert into a try-catch block, and check for the specific error number for duplicated key (which I think is 2627).  This would work if you have a unique index/constraint on those fields.  Another way could be to issue a select statement to get a record with the same client name and code that the one you are going to insert, and only execute the insert if you get no rows.

or try using the HasRows property.

Dim  cmd  As SqlCommand = new SqlCommand("SELECT * FROM Table1 WHERE Id = 1", conn)
Dim reader As SqlDataReader  = cmd.ExecuteReader()
IF Not reader.HasRows Then
'the data does not exist.
Else
'The record exists
End IF

The following site might help you
[A href="http://reydacoco.blogspot.com"]A programming guide[/A]

-----------------  kccrosser
From a programming style standpoint, I prefer to use try/catch and avoid doing the fetch/count transactions just to see whether I can insert or update a record.
If you think about the overhead of the transactions, including network round-trips, etc., it is more efficient to simply try the insert and then handle the collisions when they occur.  Unless a collision is highly likely (like 75% or more), handling the collisions after the fact is much more efficient.

try {
insert ...
}
catch {
-- handle collisions here
}

Also - whenever possible, avoid "select *" or "select count(*)".  If you need to test for existence of a record, "where exists" is perfect, and you can select a literal "1" as the result.
For example, in Oracle:
select count(*) from mytable where <condition>
If there are N records matching <condition>, this query will result in N+1 fetches.
select * from mytable where <condition>
This is worse, as the first one might only hit index records - this will do at least N+1 data fetches from the table.
select 1 from dual where exists (select 1 from mytable where <condition>)
This will do ONE fetch against mytable (hopefully against the index) - it doesn't need to count all the records, just find out if there is at least one record meeting the criteria.

RSS - Discussions Help

Discuss This Answer:

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Ask a Question

Browse IT Answers by Topic