Validate Against Values in a Database

Having a spot of bother with Validation. I have a Details view All setup to a DataSource with Insert. Works wonderfully. I've got required field validators where they need to be and they work. My slight issue is, I want to be able to validate if an integer entered into a textbox EXISTS in the Database, and if it does, ALLOW the transaction to take place. Otherwise if the integer DOES NOT exist in the DataBase, a validator must pop up telling them the number does not exist. to make matters slightly more complicated... Smile I need another condition to be true as well...

So I wrote an SQL to sum up the validation.

"Select JobNumber from Job Where Flag = false"

This will return a range of numbers (int) from my table. I want to validate my textbox value against them and make sure that whatever is typed into my textbox EXISTS in the database.

I found this link http://msdn2.microsoft.com/en-us/library/s5z00s5e(vs.80).aspx Which explaines the use of a custom validator, but I'm unable to modify it for my needs.

Can anybody help me?


A learning experience is one of those things that say, 'You know that thing you just did? Don't do that.'
1
Hornwood509
10/29/2007 11:21:01 AM
asp.net.web-forms 93655 articles. 3 followers. Follow

4 Replies
1187 Views

Similar Articles

[PageSpeed] 12

The greatness of the custom validator is that it can do serverside or clientside validation. In your case, you probably need to do serverside since you want to look at database values.

Here's something quick and dirty that I used to validate a textbox value against the ID's in a database table containing US STATES. The ID's in the states table range from 1 to 56.

In my tests, if the value returned in the SQL was equal to the value searched on, then TRUE was returned, else FALSE was returned. That boolean value was then passed to the serverside args.IsValid property which is exposed in the eventhandler for the customvalidator control

Here is the textbox, required field validator, custom validator and button which causes validation: note I have placed them all in their own validation group as to not cause validation on any other controls.

<asp:TextBox ID="txtIDExists" runat="server" ValidationGroup="Custom"></asp:TextBox>

<asp:RequiredFieldValidator ID="rfvIDExists" runat="server"

ControlToValidate="txtIDExists"

Display="Dynamic" ErrorMessage="Required!" SetFocusOnError="True"

ValidationGroup="Custom">Required!</asp:RequiredFieldValidator>

<asp:CustomValidator ID="custvIDExists" runat="server"

ControlToValidate="txtIDExists" Display="Dynamic" ErrorMessage="custvIDExists"

SetFocusOnError="True" ValidationGroup="Custom"

OnServerValidate="custvIDExists_ServerValidate">

ID Doesn't Exist!</asp:CustomValidator>

<asp:Button ID="btnIDExists" runat="server" OnClick="Button2_Click"

Text="Check If State_ID Exists!"

ValidationGroup="Custom" UseSubmitBehavior="False" />

 

 

 

// this is my actual validation code

protected bool ValidateIDExists()

{

MyToolbox.
SQLHelper mysqlhpr = new MyToolbox.SQLHelper(ConfigurationManager.ConnectionStrings["TestDataConnectionString"].ToString());

string text2validate = txtIDExists.Text;

string returnvalue =

mysqlhpr.getRowValue(

"SELECT State_ID FROM tblStates WHERE (State_ID = '" +

txtIDExists.Text + "')");

if (txtIDExists.Text == returnvalue)

return true;

else

return false;

}

// this is the event handler for the customvalidation control

protected void custvIDExists_ServerValidate(object source, ServerValidateEventArgs args)

{

// return whatever bool value was returned by the validation code

args.IsValid = ValidateIDExists();

}

 

 Here is the code for the SQLHelper method getRowValue()

/// <summary>

/// Executes a MS SQL Query, returns a string of the row value

/// </summary>

/// <returns>String return value</returns>

public string getRowValue(string sqlSELECT)

{

string value = "";

try

{

objMSSQLConn =
new SqlConnection(StrSQLConn);using (objMSSQLCmd = new SqlCommand(sqlSELECT, objMSSQLConn))

{

if (objMSSQLCmd.Connection.State == ConnectionState.Closed)

{

objMSSQLCmd.Connection.Open();

}

value = objMSSQLCmd.ExecuteScalar() + ""; // concatenating an empty string to eliminate null

objMSSQLCmd.Connection.Close();

// dont need dispose because using statement does it for us

// objConn.Dispose();

}

return value;

}

catch (Exception ex)

{

try // ditch the connection object

{

if (objMSSQLCmd.Connection.State == ConnectionState.Open)

{

objMSSQLCmd.Connection.Close();

objMSSQLCmd.Dispose();

string msg = ex.Message;

}

}

catch (Exception e)

{

// do nothing if still error persists

string msg = e.Message;

}

//Response.Write(ex.Message);

throw new Exception(ex.Message + ". SQL Statement: " + sqlSELECT);

}

}

HOpe this helps. It's not pretty, but it should show the process in a step-by-step situation.


"A common mistake that people make when trying to design something completely foolproof was to underestimate the ingenuity of complete fools." ~ Douglas Adams

http://pixelsyndicate.com/ps/
0
pixelsyndicate
10/29/2007 3:03:39 PM

Hey Pixel. Thanx eversomuch for the reply.I have worked through the example you gave me and managed to bend it to my whims. I had a slight bit of bother using the SQLHelper class you had stated in your code, but decieded against it in the end. if anybody who reads this thread is interested in the SQLHelper, but does not know about it, http://aspnet.4guysfromrolla.com/articles/070203-1.aspx this link should help them out. My custom Validator (inside of a databound DetailsView defaulted to insert) code is below. I'm not sure i would have made it this far without your help. Thanx again :)

 // this is the event handler for the customvalidation control

protected void cusValInsertJobNumber_ServerValidate(object source, ServerValidateEventArgs args)

{

args.IsValid =
false;

// return whatever bool value was returned by the validation code

args.IsValid = ValidateIDExists();

}

//Validation Logic for Custom Validator

protected bool ValidateIDExists()

{

//Find and reference the Textbox in DetailsView1

TextBox txtInJobNumber = (TextBox)DetailsView1.FindControl("txtInsertJobNumber");

//Run getRowValue Method

string returnValue = getRowValue();

if (txtInJobNumber.Text == returnValue)

return true;

else

return false;

}

public string getRowValue()

{

//Declare Variables

string value = "";

TextBox txtInJobNumber = (TextBox)DetailsView1.FindControl("txtInsertJobNumber");

string sqlLookup = "SELECT JobNumber FROM job WHERE JobNumber = " + txtInJobNumber.Text + " and JobStatusID <> 6 and Flag = 0";string connStr = ConfigurationManager.ConnectionStrings["connStringFromWeb.Config"].ConnectionString;

 

//Declare Connection, Open it, Execute SQL Scalar, Close Connection and Return the Value

SqlConnection conn = new SqlConnection(connStr);using (SqlCommand comm = new SqlCommand(sqlLookup, conn))

{

if (comm.Connection.State == ConnectionState.Closed)

{

comm.Connection.Open();

}

value = comm.ExecuteScalar() + ""; // concatenating an empty string to eliminate null

comm.Connection.Close();

 

}

return value;

}


A learning experience is one of those things that say, 'You know that thing you just did? Don't do that.'
0
Hornwood509
10/31/2007 8:00:08 AM

Hi Hornwood,

I am trying to accomplish a similar thing...i have a text box where the user needs to enter a serial number...I have this text in a custom create new wizard template....what i want to do is i have an sql database with the serial numbers...when the user enters a username, password, serial number, etc and hits the button to create the user account, i want to be able to check whether the serial number is valid against the database and if so create the user, otherwise throw an error.  I follow the code until here:

// this is the event handler for the customvalidation controlprotected void cusValInsertJobNumber_ServerValidate(object source, ServerValidateEventArgs args)

{

args.IsValid =
false;

// return whatever bool value was returned by the validation code

args.IsValid = ValidateIDExists();

}

//Validation Logic for Custom Validatorprotected bool ValidateIDExists()

{

 

i dont have a gridview of the database or anything, i just want the custom validator to check the sql database for the serial number.

 

thanks,

 

0
nkair19
12/15/2008 4:29:29 PM
Reply:

Similar Artilces:

C#.NET WEB APPLICATION
hei guys, i need help on my application, i'm using C#.NET as front-ed and SQL as back-end, aside from a datagrid, i want to display my data using textboxes and not a datagrid, i can do it using Visual Basic 6 but since i'm a newbee at C#.NET i don't know how to do it, specially the codes, can anyone help me with it? thank you in advance! If you are doing windows development, i would suggest using 'DataForm wizard' (Use Solution Explorer-->Rightclick project-->Add new Item-->New DataForm). It will guide you through steps. If you are doing web apps. Use quickstart to learn them. h...

Passing Values from a Web Form to Another Web Form
Hello All! I am trying to pass values from one web form to another web form. I know that there are 3 ways of doing this: queryString, Server.Transfer and Session. I am using the Server.Transfer method. But I am not able to create, in my 2nd web form, an instance of my 1st web form. My 1st Web form is named CallTrack.aspx and my 2nd is named Export.aspx. What I mean is something like this: Public value as CallTrack. I can't create it in my 2nd web form because when the intellisense is displayed, there's no CallTrack in the options. Thanks!   How about using cross-...

Implementing .Net Form into an APS.net web form, can this be done
I am new here, but have been searching for a while, and may not have the correct lingo to find what I am looking for.  I am tasked with implementing a .exe application that was writen vb6 then converted to .net into a new website my team is developing. The idea is to put each of the 3 different forms in this .exe application of 3 different .aspx pages. I have attempted multiple things to get this into the page and even started to just rewrite it as an ASP.net web form, but even then I can't reuse any of the code since the System.Web.UI.Page doesn't inherit the sa...

passing values to .net web form application
Hi Can any explain me abt how to pass values to my .NET web form application (designed using PB 11.1) from other web application? My intension is to redirect from a web application (ASP.NET to .net web form application) to my web application with user information. Thanks & Regards, Anbazhagan From the "Deploying Applications and Components to .NET" manual: >>> Starting an application with command line parameters If your application requires command line parameters, you can assign values to the PBCommandParm global property before you deploy the...

Value in Web Form to Delete Database Row
Hi everyone,I have a problem and was hoping someone might be able to help me with it.Basically I have a label control that displays an ID number. I also have an MS SQL database with a table called Images with a primary key called ID. What I need to do is dependent upon the label control value delete the corresponding row in the database table.e.g. Label Value in web form = 4. So from this I need to delete the row in the Database which has the Primary key ID = 4.I am coding in VB as I don't know any C#. Any help greatly appreciated.ThanksDaniel  Dan_no7: Hi everyone, I have a p...

Hiding a web form field based on database value.
  Hi, i want a textbox to be hidden based on the user access ID, and if the admin has clicked on a checkbox which makes it deactivated. I have the following; Code behind which displays all the fileds;private void DisplaySelectedRecord(DataView dv) { //populate the controls on the page to show the data on the page controls  this.txtMA_Code.Text = dv[0]["MA_Code"].ToString().Trim();   this.txtRate.Text = dv[0]["Rate"].ToString() ;if (dv[0]["Rate_Active"] != null && dv[0]["Rate_Active"] != System.DBNull.Value)this.CheckBox1...

Send e-mail with web form values (vb.net)
I'm sure this is very simple but;I have a form with numerous (<50) textbox controls and related Label controls; ie: <asp:Label ID="Label1" runat="server" Text="Last Name: "></asp:Label> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>When the form is submitted I want to loop through each label control and textbox control and get their Text values for the e-mail without specifying each control. Anyone have suggestions on the best way to do this?Any assistance appreciated greatly!   Here is a solution for you in VB.net:http://www.extremeexperts.com/...

Clearing Web Controls Values from a Web Form
I have a web form and after a user submit it I want to clear the form so that they may submit another entry. I have seen examples in VB of how to loop thru a web form and clear the controls, but I need a C# solution. I have tried the following code, while it does not give me an error, it does not clear the controls on the form. Any suggestions? Thanks! foreach (Control parent in Page.Controls)         {             foreach (Control child in parent.Controls)           ...

getting values from a web form to windows form
Hot Requirement ..... Is there any possible of getting values from a web form to windows form ????? Yes One way i to use the WebRequest and WebResponse classes of the System.Net namespace. For example:  WebRequest request = WebRequest.Create("URL of the web page); WebResponse response = request.GetResponse(); StreamReader reader = new StreamReader(response.GetResponseStream()); string content = reader.ReadLine(); ....    Reza Nassabehwww.professionalcsharp.comDon't forget to click "Mark as Answer" on the post that helped you. That way fut...

windows forms in vb .net into web forms
we are creating a database driven website as part of our project. currently we have forms connecting to the database created in visual basic. these windows forms we need to convert into web forms which are asp .net compatible. can u give me an answer or show me a way. Hi, because of the difference in nature between web and win applications you'll need to reimplement at least the UI part. If you have a multitiered application in which the busines logic and data access are in different parts/assemblies you can reuse them. Grz, Kris.Read my blog. Handy Firefox plugins for web developers.Wor...

Trying to edit web form, can't get database updated with newly edited values.
 This is my code:  when I try to update any of the fields, the update statement retains the old values retrieved from the database.  Could you tell me where my code is bad? -Thanks, Mark   <%@ Page Language="VB" %><%@Import Namespace="System.Data"%><%@Import Namespace="System.Data.SqlClient"%><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"> Protected Sub Page_Load(ByVa...

Submitting Web forms fields to sql database by clicking on SUBMIT FORM and sending the form fields to email box
I have a created or designed a form to be stored in a SQL database table and at the same time when a user clicks on SUBMIT button the form fields will automatically goto the visitor email address immediately. Also i have created a database with tables for each of this forms Pls help i have over 10 forms designed but the problem of submitting them to the table database and the user email address is the challenge now.... You ned to familiarise yourself with the basics of database access in ASP.NET.  There are free tutuorials here: http://quickstarts.asp.net/QuickStartv20/aspnet/doc/da...

Need to validate the Textbox value with database columm value
Working with ASP.NET 2.0 i have a textbox say txt1 for entering the date and a column in database for date.I need to validate the txt1 vaalue with the database value ,if txt1 date doesn't exist in the database i want to warn the user (Date doesnt Exist) either on textbox change event or on button click.How can i do this..... I would have a textbox, a label and a button. Then on the onClick event handler for the button I would have the code where the search in the DB is being made and if I receive any rows, modify the Text property of the label to say something like "Date exists&q...

.Net Web Form & Web Service Deployment
Hi all I have written some test web services and some web form applications and deployed them to my IIS. That's fine but I now want to deploy them to my real IIS server. I have created an msi for each but the problem is I seem to only be able to deploy to the default web site. Our default web site is turned off and instead we use several other sites. Does anybody happen to if Powerbuilder 11 supports non default web sites? If not do I need to hack the msi and if so can anybody suggest a suitable tool. I have Orca but it's not very friendly. Unless of course I knew exactly w...

validate Textbox in Web User Control from web form?
ASP.NET 2.0 I have a web user control that contains a textbox.  I need to add some validation (like requiredfieldvalidator, comparevalidator, etc). I would normally add the validator to my web user control by placing the validator next to my textbox.  However, I need the validation message somewhere else on my web form, not near the user control. So how would I associate a validation control on my web form with the text box inside my web user control?  Can this be done; any alternatives out there? Thanks!   The error message should occur wherever you place the vali...

Web resources about - Validate Against Values in a Database - asp.net.web-forms

Credit Card Validate on the App Store on iTunes
Get Credit Card Validate on the App Store. See screenshots and ratings, and read customer reviews.

Who Is Going to Shoot Obama? Paul Broun Chuckles, Validates Question - YouTube
Republican Congressman from George Paul Broun chuckles and does not even come close to denouncing a question asked by a constituent at a town ...

PM says MH370 debris find 'validates search'
Experts say recovered MH370 debris is consistent the area being searched in the southern Indian Ocean.

Spain's domination of England validates the Socceroos' search for identity
It is incredible to think that with all the billions of dollars of resources in the English game, their solitary success remains almost 50 years ...

Fracking report validates environmental concerns in N.W.T.
... Kenyon of the Pembina Institute. A director of the Pembina Institute in Edmonton says a new report on the risks of shale gas development validates ...

【medical-news】Study First to Validate That Singing Can Help People Learn a Foreign Language - 医药生命科学动态跟踪 ...
只是新闻而已Fernanda Ferreira’s family emigrated from Portugal to Canada when she was very young. There, like most Canadian children, she learned French ...

Supreme court validates private loans for rightful purposes
Supreme court validates private loans for rightful purposes

IXIA : Selected by China Network World to Validate High-Performance Cisco ...
IXIA : Selected by China Network World to Validate High-Performance Cisco ... 4-traders (press release) BEIJING - June 25, 2013 - Ixia (Nasdaq: ...

Apple Watch uses constant skin contact to validate Apple Pay purchases
... will the device make sure payments via Apple Pay are secure? Both the iPhone 6 and iPhone 6 Plus have the convenient Touch ID sensor to validate ...

Like You Really Need To Validate Your Social Media Presence….
How many Fortune 500 companies are blogging and using social media, and what can law firms learn from them?

resources last updated: 11/20/2015 7:57:41 PM