simplest way to see if record exists

[cwfontan]

I have a sub that checks table to see if row exists that match userinput (its checking on leave event of 2 textboxes custname and custnumb
sql server 2005 table with data

I also have filled dataset with the data


I dont want to filter the dataset at this point..
is there a way to just see if record exists in dataset?


if not whats the simplest way to return a count or boolean that a row exists?


just for ref here is my sub i think its clunky.. and doesnt work with select.. the insertcmd.executenonquery() part

Public Sub CustomerCheckExists()
        If custFail = True Or custFail = Nothing Then 'check CustomerName
            Try
                Dim sql1007 As String = "SELECT * FROM M8_Customers WHERE CustomerName ='" & Dispatch.txt_CustName_Customers.Text & "'"
                sqlConn.Open()
                Dim insertcmd As New SqlCommand(sql1007, sqlConn)
                Dim count As Integer =
                sqlConn.Close()
                If count > 0 Then
                    Dispatch.lbl_custExist_Customers.ForeColor = Color.Red
                    Dispatch.lbl_custExist_Customers.Text = "Customer Name Exists =("
                    Dispatch.txt_CustName_Customers.Focus()
                    'MsgBox("Record already exists.", MsgBoxStyle.Critical, "Atention")
                    custFail = True
                    Exit Sub
                Else
                    Dispatch.lbl_custExist_Customers.ForeColor = Color.Green
                    Dispatch.lbl_custExist_Customers.Text = " =) "
                    Dispatch.txt_CustName_Customers.ReadOnly = True
                    Dispatch.txt_CustNumber_Customers.Focus()
                    custFail = False
                    Exit Sub
                End If
            Catch ex As Exception
                MsgBox(ex.Message & "In CustomersModule sql1007")
            End Try
        Else 'check CustomerNumber
            Try
                Dim sql1008 As String = "SELECT * FROM M8_Customers WHERE CustomerNumber ='" & Dispatch.txt_CustNumber_Customers.Text & "'"
                sqlConn.Open()
                Dim insertcmd As New SqlCommand(sql1008, sqlConn)
                Dim count As Integer = insertcmd.ExecuteNonQuery()
                sqlConn.Close()
                If count > 0 Then
                    Dispatch.lbl_custNumbExists_Customers.ForeColor = Color.Red
                    Dispatch.lbl_custNumbExists_Customers.Text = "Customer Name Exists =("
                    Dispatch.txt_CustNumber_Customers.Focus()
                    'MsgBox("Record already exists.", MsgBoxStyle.Critical, "Atention")
                    Exit Sub
                Else
                    Dispatch.lbl_custNumbExists_Customers.ForeColor = Color.Green
                    Dispatch.lbl_custNumbExists_Customers.Text = " =) "
                    Dispatch.txt_Contact_Customers.Focus()
                    Dispatch.txt_CustNumber_Customers.ReadOnly = True
                    custFail = False
                    Exit Sub

                End If
            Catch ex As Exception
                MsgBox(ex.Message & "In CustomersModule sql1008")
            End Try
        End If

    End Sub

 

[jmcilhinney]

There are various ways. If the data is bound then you can call the Find method of your BindingSource. Whether the data is bound or not, you can call the Select method of your DataTable.

 

[Hack]

whats the simplest way to return a count or boolean that a row exists?

This is a pretty simple way

Dim sql1007 As String = "SELECT * FROM M8_Customers WHERE CustomerName ='" & Dispatch.txt_CustName_Customers.Text & "'"
                sqlConn.Open()
                Dim insertcmd As New SqlCommand(sql1007, sqlConn)
                Dim reader As SqlDataReader = insertcmd.ExecuteReader()
                If reader.HasRows Then 'then it does exist
                   'do stuff
                Else
                   Messagebox.Show("No records returned.")
                End If

 

[cjard]

Hack, please don't advise newbies to build SQLs using string concatenation. It's dumb stuff like that that leaves systems open to SQL Injection attacks. Please learn what parameters are and start using them, or stop giving advice that involves you writing an SQL

 

[cjard]

I dont want to filter the dataset at this point..
is there a way to just see if record exists in dataset?

If you assert that the customer name is how you uniquely identify a customer, then make it the primary key, and just try to insert the data anyway, handling the error (pk violation) if it fails..