Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 612
  • Last Modified:

VS 2005/VB/Validate user entry against SQL database at textbox change event

In my web form I have a textbox(txtEmplID1) where I need to validate what the user enters against a table in my database.  I've added a custom validator and I've written the behind code but it is not validating at all.  I need it to validate when on the textbox change event.


Web form code:
<asp:TextBox ID="txtEmplID1" runat="server" AutoPostBack="True" TabIndex="3" CssClass="section1_text" Width="104px"></asp:TextBox></td>
                <td style="width: 233px; height: 27px;">
                    &nbsp;<asp:RequiredFieldValidator ID="reqvemplid" runat="server" ControlToValidate="txtEmplID1"
                        Display="Dynamic" ErrorMessage="Please Enter an Employee ID" SetFocusOnError="True"></asp:RequiredFieldValidator>
                    <asp:CustomValidator ID="cvEmployee" runat="server" OnServerValidate="cvEmployee_ServerValidate" ControlToValidate="txtEmplID1"  Display="Dynamic" ErrorMessage="Invalid ID: Please try again or contact Accounting" ValidateEmptyText="True"></asp:CustomValidator>
 
Behind Code:
Protected Sub cvEmployee_ServerValidate(ByVal source As Object, ByVal args As System.Web.UI.WebControls.ServerValidateEventArgs) Handles cvEmployee.ServerValidate
        Dim oConn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("CMSConnectionString").ConnectionString)
        Dim sql As New System.Data.SqlClient.SqlCommand()
        sql.Connection = oConn
 
        oConn.Open()
 
        ' Add the parameters for the query.
        sql.Parameters.AddWithValue("MEENO", txtEmplID1.Text)
        sql.CommandText = "Select MNM25 from Employee_Dept where @MEENO = MEENO"
        sql.Connection = oConn
        sql.ExecuteScalar()
 
 
        Dim dr As Data.SqlClient.SqlDataReader
        dr = sql.ExecuteReader()
 
        Try
            If dr.HasRows Then
 
                ' Employee ID entry is valid
                cvEmployee.IsValid = True
            
            End If
            cvEmployee.IsValid = False
        Catch ex As Exception
            ' Handle exceptions appropriately here
        Finally
            dr.Close()
            oConn.Close()
            oConn.Dispose()
            sql.Dispose()
        End Try
        
 
    End Sub

Open in new window

0
imstac73
Asked:
imstac73
  • 6
  • 5
1 Solution
 
jmwheelerCommented:
The nature of validator is that they only fire when a form is submitted.  If you want to force the issue you can.  What you have is almost there but you need to add an event for OnTextChanged to your TextBox to force the validation.

Also, in your validation routine instead of setting "cvEmployee.IsValid = True" you should use "args.IsValid = True".
<asp:TextBox ID="txtEmplID1" runat="server" AutoPostBack="True" TabIndex="3" CssClass="section1_text" Width="104px" OnTextChanged="txtEmplID1_TextChanged"></asp:TextBox>
 
//Code behind file
Protected Sub txtEmplID1_TextChanged(ByVal source As Object, ByVal args As System.EventArgs) Handles txtEmplID1.TextChanged
   Validate() 'Forces validation of the page, if you need to perform some actions you can check the result with Page.IsValid
End Sub

Open in new window

0
 
imstac73Author Commented:
I added the validate() and change the code to args.isvalid. When I key in an invalid entry I get a SqlException "Arithmetic overflow error converting numeric to data type numeric" on my sql.ExecuteScalar().  Do I need to do some conversion or something?  The field "MNM25" in my db is a decimal type.  
0
 
jmwheelerCommented:
You shouldn't need to use ExecuteScalar() and ExecuteReader() because ExecuteScalar() returns one value.  This should be all you need to determine if a match was found.

Try this:
Dim oConn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("CMSConnectionString").ConnectionString)
        Dim sql As New System.Data.SqlClient.SqlCommand()
        sql.Connection = oConn
 
        oConn.Open()
 
        ' Add the parameters for the query.
        sql.Parameters.AddWithValue("MEENO", txtEmplID1.Text)
        sql.CommandText = "Select MNM25 from Employee_Dept where @MEENO = MEENO"
        sql.Connection = oConn
        Dim mnm25 As String = sql.ExecuteScalar().ToString()
 
        Try
            If nmn25.Length > 0 Then
 
                ' Employee ID entry is valid
                args.IsValid = True
            
            End If
            args.IsValid = False
        Catch ex As Exception
            ' Handle exceptions appropriately here
        Finally
            dr.Close()
            oConn.Close()
            oConn.Dispose()
            sql.Dispose()
        End Try

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
imstac73Author Commented:
I addded a convert to my code
Convert.ToDecimal(txtEmplID1.Text) but if the user enters anything but numbers by accident I get a server error that the "Input string was not in the correct format".  I tried adding a compare validator and put the type as a double and also tried adding a range validator but neither stops the other validation error.
0
 
jmwheelerCommented:
I generally use javascript to prevent non-authorized characters.  You could use something like this.
<script type="text/javascript">
  function isNumberKey(evt)
  {
    if (window.event)
        kc = evt.keyCode;
    else
        kc = evt.which;
    return (kc >= '0'.charCodeAt() && kc <= '9'.charCodeAt()) || (kc == 0) || (kc == 13) || (kc == 8);
  }
</script>
 
 
<asp:TextBox ID="txtEmplID1" runat="server" AutoPostBack="True" TabIndex="3" CssClass="section1_text" Width="104px" OnTextChanged="txtEmplID1_TextChanged" onkeypress="return isNumberKey(event);"></asp:TextBox>

Open in new window

0
 
imstac73Author Commented:
I get it error that 'Onkeypress' is not valid for a textbox.
0
 
jmwheelerCommented:
You can ignore it, your page should still build and run correctly.
0
 
imstac73Author Commented:
Okay, the onkeypress is working however when I key in an entry in the textbox that is not a valid I get the following error "Object reference not set to an instance of an object". on the Dim mnm25 as String = sqlExecuteScalar().ToString()
 
0
 
jmwheelerCommented:
Try this instead then
Dim mnm25 as Object = sqlExecuteScalar()
 
Try
    If Not mnm25 Is Nothing Then
       ' Employee ID entry is valid
       args.IsValid = True  
    Else
       args.IsValid = False
    End If

Open in new window

0
 
jmwheelerCommented:
I meant sql.ExecuteScalar() not sqlExecuteScalar().  I have been having some keyboard problems lately.
0
 
imstac73Author Commented:
Perfect...thanks so much!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now