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.