Member Avatar for Papa_Don

Hi group,

I'm attempting to write code to check to see if an existing account number exists. The user is to input a 10 digit account number. The code then is to query the database to see if that number exists. If it does, the message box displays the message that the number exists. However the code I've written isn't working as I wish. Can you offer some suggestions as to how to do this correctly?

Here's what I've attempted:

Private Sub tbxAccountNo_TextChanged(sender As Object, e As EventArgs) Handles tbxAccountNo.TextChanged
    If GlobalVariables.custpnl1 = 2 And tbxAccountNo.Text.Length = 10 And IsNumeric(tbxAccountNo.Text) = True Then
        Dim dt As New DataTable()
        Dim rowIndex As Integer = 0
        Dim searchID As Int64
        Dim strQ As String = String.Empty
        Dim conStr As String

        Dim msgAcctNo As String

        strQ = "SELECT CUST_ACCT_NO
                FROM CUSTREC 
                WHERE CUST_ACCT_NO = " & searchID
        conStr = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Don\Documents\Visual Studio 2019\SalesForm\DWHRPT.mdf;Integrated Security=True"

        Dim dataAdapter As New SqlDataAdapter(strQ, conStr)
        dataAdapter.Fill(dt)
        dataAdapter.Dispose()

        For i As Integer = 0 To (dt.Rows.Count - 1)
            rowIndex = i
            If IsDBNull(dt.Rows(rowIndex)("CUST_ACCT_NO")) Then
                acctNoExists = False
            Else
                msgAcctNo = CStr(dt.Rows(rowIndex)("CUST_ACCT_NO"))
                acctNoExists = True
                MessageBox.Show("This account number exists.  Please enter a unique 10 digit account number.", "", MessageBoxButtons.OK)
                Exit Sub
            End If
        Next
    End If
End Sub

The "Text_Changed" event is used to fire this off - when there are 10 numeric characters in the textbox. That part seems to be working correctly as I've toggled it to stop at "For i As Integer = 0 To (dt.Rows.Count - 1)". But at this point, that's the only thing that seems to be working.

If you can teach me what I'm doing wrong, please feel free to do so.

As always, thanks for your help.

Don

Edited by Dani because: Moved to correct forum
  • 2 Contributors
  • 2 Replies
  • 3K Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by xrjf

Dani AI

Generated

Nice progress moving the check to LostFocus, . Two changes will make this both safer and faster: use a parameterized, single‑value query and treat the account number as text. Building SQL with string concatenation risks injection, and filling a DataTable is overkill for an existence check. A lightweight SELECT COUNT(1) with parameters avoids injection and is optimized for this use case via ExecuteScalar ExecuteScalar, Configuring parameters.

Imports System.Data.SqlClient
Imports System.Text.RegularExpressions

Private Sub tbxAccountNo_LostFocus(sender As Object, e As EventArgs) Handles tbxAccountNo.LostFocus
    Dim acct = tbxAccountNo.Text.Trim()
    If Not Regex.IsMatch(acct, "^\d{10}$") Then Exit Sub   ' exactly 10 digits

    Using con As New SqlConnection(conStr),
          cmd As New SqlCommand("SELECT COUNT(1) FROM dbo.CUSTREC WHERE CUST_ACCT_NO = @acct;", con)
        cmd.Parameters.Add("@acct", SqlDbType.VarChar, 10).Value = acct  ' store as CHAR/VARCHAR(10)
        con.Open()
        Dim exists As Boolean = Convert.ToInt32(cmd.ExecuteScalar()) > 0
        If exists Then
            MessageBox.Show($"Account #{acct} exists. Please enter a unique 10 digit account number.")
            tbxAccountNo.Clear()
            tbxAccountNo.Select()
        End If
    End Using
End Sub

A couple of notes inspired by ’s comment: keep CUST_ACCT_NO as CHAR(10)/VARCHAR(10) so leading zeros are preserved; using Int64 can silently drop them. Also, enforce uniqueness in the database so you are protected even if UI checks are bypassed:

ALTER TABLE dbo.CUSTREC
ADD CONSTRAINT UQ_CUSTREC_CUST_ACCT_NO UNIQUE (CUST_ACCT_NO);

SQL Server automatically backs this with a unique index, which is the most reliable way to prevent duplicates Create unique constraints.

Recommended Answers

All 2 Replies

Member Avatar for Papa_Don

I was able to figure this out. Here's what I did:

 Private Sub tbxAccountNo_LostFocus(sender As Object, e As EventArgs) Handles tbxAccountNo.LostFocus
    If GlobalVariables.custpnl1 = 2 And tbxAccountNo.Text.Length = 10 And IsNumeric(tbxAccountNo.Text) = True Then
        Dim dt As New DataTable()
        Dim rowIndex As Integer = 0
        Dim searchID As Int64 = Convert.ToInt64(tbxAccountNo.Text)
        Dim strQ As String = String.Empty
        Dim conStr As String

        strQ = "SELECT CUST_ACCT_NO
                FROM CUSTREC 
                WHERE CUST_ACCT_NO = " & searchID
        conStr = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Don\Documents\Visual Studio 2019\SalesForm\DWHRPT.mdf;Integrated Security=True"

        Dim dataAdapter As New SqlDataAdapter(strQ, conStr)
        dataAdapter.Fill(dt)
        dataAdapter.Dispose()
        acctNoExists = False
        For i As Integer = 0 To (dt.Rows.Count - 1)
            rowIndex = i
            If searchID = Convert.ToInt64(CStr(dt.Rows(rowIndex)("CUST_ACCT_NO"))) Then
                acctNoExists = True
                MessageBox.Show("Account #" & searchID & " exists.  Please enter a unique 10 digit account number.", "", MessageBoxButtons.OK)
                tbxAccountNo.Clear()
                tbxAccountNo.Select()
                Exit Sub
            End If
        Next
    End If
End Sub

I hope someone else can use this method.

Don

Member Avatar for xrjf

I imagine you are aware of SQL injection. If not you may read here about it.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.