Validate data during entry in table fields against database in ASP classic

[chandhseke]

I have a form with various fields, one of which I need to check against data in a database table. i.e if the value entered into the text input field exists in the database, the form should be submitted normally; otherwise an error message should be issued without submitting the form. How can I achieve this kind of validation?

Thanks

[Frinavale]

You should always do server side validation.

You can also use JavaScript to do some client side validation to prevent unnecessary, invalid, page posts to the server; however, it's easy to get around client side validation so make sure you do server validation as well.

When you receive the data on the server......tes t it to make sure that the data meets the validation conditions (your requirements) before trying to use it (especially before accessing a database).

On the server, if any validation fails, print error messages prompting the user to make the necessary corrections onto the page and send the page back to the user.

In the browser, call a JavaScript method that validates the data being submitted before it's submitted...if the data is not valid then cancel the page submit and display messages prompting the user to correct any invalid data...otherwis e let it submit to the server.

[chandhseke]

Thanks for the above reply... But in this case i need to validate a Employee ID number entered into a text box against a database table to check whether the ID number exists in the database, if the ID number doesn't exist in database i should not allow him to submit the form, need to print error message..I am using Active Server pages for form design, JavaScript for server side validation and VBScript for Database connectivity.

How can i validate a text box value against database table using ASP and VB Script..Please advise

Thanks again,
Chandru

[Markus]

What kind of database is it?

[chandhseke]

It is an sql server database.

[Frinavale]

Well you could use SQL to query the number records with the Employee number provided and check if there are any matching records.....

You could use the SQL Count() function to do this.

For example:

SELECT COUNT(EmployeeIDs) AS NumEmpIDs FROM Employees
  WHERE ID=theValueInTheTextBox

If the value returned from that select statement is 0 then there is no matching employee ID's and you need to display the message.

[chandhseke]

Thanks for your response..

As SQL Count() function is used to get the number of empID's in the table, how will it check whether the employee ID entered by user(Front end) exists in the database??

Might be a silly question, but want to have a better understanding? Can you please advise?

Thanks again :)

[Frinavale]

I didn't realise what you were asking.

Umm...yeah...I don't know how to do this with classic ASP.

I'm moving the question to the ASP forum so you can get more help :) :)

[Frinavale]

Well actually one thing that I can think of is to make an ASP page simply used to check the employee id.

In that ASP page you'd have a method that validates the ID provided against the database. If it finds something wrong then it returns an error message, otherwise it returns an empty string.

You'd call this method using Ajax, passing it the value that was entered in the text box.

You still have to send this information to the server so that it can validate against the database, but you don't have to post the whole page back to the server to do so. All you'd be doing is making a very small request, moving a small bit of data from client to the server and back.

The following is an example of what would be in the validate.asp page. Please note that this is my second time every using ASP classic so all that it does is take the employee ID provided and sends it back to the user. You'll have to modify this so that it checks if the ID provided exists in the database (using the SQL Count() method we talked about earlier) and base the return off of that result. I don't know how to connect to and us a database in asp classic at this time (I feel like a fish out of water here) but I'm sure it's pretty simple. Here's the validate.asp:

<%  Response.Buffer = true 
    Response.Expires=-1
    Response.CacheControl="no-cache"
    dim _id
    _id=Request.QueryString("empID")

	Function Validate() 
		'Here you need to do your validation against the database.
		Response.Write("The EmployeeID Provided Was:" + _id)
	End Function 


       Call Validate() 
%>

The following JavaScript code has to go in your normal ASP page. You need to call the validateEmpID() JavaScript method when the user's done entering the employee id....maybe on the JavaScript onblur event for the textbox. Please note that right now the validateEmpID() method assumes that you have a textbox on the page with the id "TextBoxWithEmp ID". The validateEmpID() method makes an Ajax call to the validate.asp page passing it the value provided in the TextBox. When the Ajax call returns, this JavaScript displays an alert with what the validate.asp page returned:

var xmlHttp;

function validateEmpID()
{	
  /*  Instanciating the HttpRequest object that will be used to make the Ajax calls.  See http://www.w3.org/TR/XMLHttpRequest/ for more information on this object.*/
   
  xmlHttp=GetXmlHttp();

  /*  validate.asp is an ASP page has been created for the sole purpose of validating the employee ID.*/
  var urlRequest = "http://localhost/validate.asp?empID=" + document.getElementByID('TextBoxWithEmpID').value;
  xmlHttp.open('GET', urlRequest , true); 

  //making the request
   xmlHttp.send(null); 
};

xmlHttp.onreadystatechange=function()
{	/* Make sure that the transaction has finished. 
            The XMLHttpRequest object 
	      has a property called readyState with several states:
				0: Uninitialized
				1: Loading
				2: Loaded
				3: Interactive
				4: Finished */
    if(xmlHttp.readyState == 1)
    {	
    }
    if(xmlHttp.readyState == 4)
    { //Finished loading the response
	
        /* We have got the response from the server-side script,
            let's see just what it was. using the responseText property of 
            the XMLHttpRequest object. */		
        try
        {
            if(xmlHttp.status == 200){	
                alert(xmlHttp.responseText;);
            }else{
                alert('There was a problem validating');
            }
        }catch(e){	
            alert("An error occrred:"+e.description);
        }
};



function GetXmlHttp()
{    /*This function is responsible for creating an HttpRequest object 
    based on the browser that the user is currently using. */
    var xmlHttp = null;            
    try
    {   //Mozilla, Opera, Safari etc.
        xmlHttp=XMLHttpRequest();
     }catch(e)
    {   //Internet Explorer uses ActiveX objects to make Ajax calls.
        //the following are valid versions, here we will loop through 
        //the versions and attempt to create the ActiveX that matches the browser.

        var versionIds = ["Msxml2.XMLHTTP.6.0","Msxml2.XMLHTTP.5.0",
                    "Msxml2.XMLHTTP.4.0","Msxml2.XMLHTTP.3.0", 
                    "Msxml2.XMLHTTP.2.6","Microsoft.XMLHTTP.1.0", 
                         "Microsoft.XMLHTTP.1","Microsoft.XMLHTTP"];
        for(var i=0; i<versionIds.length && xmlHttp == null; i++) 
        {
            xmlHttp = CreateXmlHttp(versionIds[i]);
        }
    }
    return xmlHttp;
}

function CreateXmlHttp(id) 
{   /*Creates an ActiveX object used by Internet Explorer that will make Ajax calls*/
    var xmlHttp = null;
    try 
    {
        xmlHttp = new ActiveXObject(id);
    }catch(e) {}
    return xmlHttp;
}

I hope this answers your question because it was a lot of fun finding the answer....I don't think I'm prepared to continue into asp classic at this time...so maybe the experts in this forum (the asp classic forum) can help you further.