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

10 pts.
Tags:
Database programming
Search string
SQL Database
VB .NET
VB.NET 2005
Visual Basic .NET
Visual Basic 2005
Hi!
I need a fresher..I want a VB.net code to search record from SQL database table by using two 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 you the code. Please help 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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • 5256
    please write coding of search,edit,reset,and delete buttons???????/
    10 pointsBadges:
    report
  • carlosdl
    5256: No, we won't do your homework for you.
    68,820 pointsBadges:
    report
  • Koohiisan
    Setting up the SQL administrator account with 'sa' for the password? Using the omnipotent SQL administrator account to run queries? Coping/pasting your complete db connection string for the entire universe to see totally unredacted (by all appearances)? What 'best practices' do they teach in your school...? *facepalm*
    5,020 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following