locked
Validation against SqlDataSource? RRS feed

  • Question

  • User732704340 posted

    Hi,

    I need to validate user input from a textbox against a database. Is it possible to make SqlDataSource (choose only the column that I need to validate) and then in on button click event validate the input? What method should I use?

    Wednesday, May 4, 2011 2:50 PM

Answers

  • User3866881 posted

    OK, here we go:

    1) Drag and drop the Customized Valiation control and bind the validation control to the TextBox

    2) Try this:

    protected void CustomValidator1_ServerValidate(object source, ServerValidateEventArgs args)
    {

      SqlConnection con = new SqlConnection("……");

      SqlCommand cmd = new SqlCommand("select top 1 FieldName from TableName where ……",con);

      con.Open();

      string valueDB = cmd.ExecuteScaler().ToString();


    string ID = args.Value; //Get the binded value from the textbox

    if(Id……)       //Compare with valueDB
    {

    args.IsValid = false;

    }

    else

    {

    args.IsValid = true;

    }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 6, 2011 3:20 AM
  • User732704340 posted

    I typed in your code but I'm getting errors. I commented them in the code below:

     

    using System;
    using System.Collections;
    using System.Configuration;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Xml.Linq;
    
    public partial class TESTPAGE : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
    
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            if (!IsValid)
            {
                Response.Redirect("Default.aspx");
            }
    
            else
            {
                Response.Redirect("Login.aspx");
            }
        }
    
        protected void CustomValidator1_ServerValidate(object source, ServerValidateEventArgs args)
        {
            string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\YourDatabase.mdf;Integrated Security=True;User Instance=True";
            SqlConnection con = new SqlConnection(connectionString); //The type or namespace name "SqlConnection" could not be found (are you missing a using directive or an assembly reference?
    
            SqlCommand cmd = new SqlCommand("SELECT [Column] FROM [Table]",con); //The type or namespace name "SqlCommand" could not be found (are you missing a using directive or an assembly reference?
            con.Open();
    
            string valueDB = cmd.ExecuteScaler().ToString();
            string ID = args.Value;
    
            if (args.Value == valueDB)    //Compare with valueDB
            {
            args.IsValid = false;
            }
    
            else
            {
                args.IsValid = true;
            }
        }

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 6, 2011 3:51 AM
  • User3866881 posted

    You should add this:

    using System.Data;

    using System.Data.SqlClient;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 6, 2011 4:11 AM
  • User3866881 posted

     string valueDB = cmd.ExecuteScalar().ToString();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 6, 2011 4:21 AM
  • User732704340 posted

    Decker Dong thanks a lot. It works perfectly. Smile I've been trying to do this for hours. Here I paste working code in case that someone needs it:

    using System;
    using System.Collections;
    using System.Configuration;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Xml.Linq;
    using System.Data.SqlClient;
    
    public partial class TESTPAGE : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
    
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            if (!IsValid)
            {
                Response.Redirect("Default.aspx");
            }
    
            else
            {
                Response.Redirect("Login.aspx");
            }
        }
    
        protected void CustomValidator1_ServerValidate(object source, ServerValidateEventArgs args)
        {
            string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\YourDatabase.mdf;Integrated Security=True;User Instance=True";
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("SELECT [Column] FROM [Table]",con);
            con.Open();
    
            string valueDB = cmd.ExecuteScalar().ToString();
            string ID = args.Value;
    
            if (args.Value == valueDB)
            {
            args.IsValid = false;
            }
    
            else
            {
                args.IsValid = true;
            }
        }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 6, 2011 4:35 AM

All replies

  • User1483970546 posted

    Hi,

    You can use CustomValidator with TextBox and compare the TextBox value with database value in CustomValidtor_OnServerValidation event. Please see how to use server side validation with custom validator: http://www.4guysfromrolla.com/articles/073102-1.aspx

    Thanks

    -Akhtar

    Wednesday, May 4, 2011 3:26 PM
  • User732704340 posted
    Thanks,It seems to be well explained but its in vb. I need something in c#. I'll try customvalidator_onservervalidation event tomorrow and let to know if i managed to get it work.
    Wednesday, May 4, 2011 6:21 PM
  • User732704340 posted

    I still can't get it working. Even though I know what to do I still don't know how to do it. I'm really confused when it's about databases and connecting to them. As far I know that I need to set as below:

    protected void CustomValidator1_ServerValidate(object source, ServerValidateEventArgs args)
        {
            args.IsValid = false;
            string ID = TextBox1.Text;
            SqlDataSource1 = new SqlDataSource;
        }
    What else should I add to make it right?

     

    Thursday, May 5, 2011 10:25 AM
  • User1483970546 posted

    Hi,

    In CustomValidator1_ServerValidate event you need to do following things,

    1. Get the record (that you want to compare with textbox) from your database. Please see this article.

    2. Compre the database record with TextBox.Text.

    3. If TextBox.Text is same as database record then set args.IsValid=True, otherwise False.

    Hope it helps!

    Thanks

    -Akhtar

    Thursday, May 5, 2011 2:55 PM
  • User3866881 posted

    Hi:)
    Do you want to validate the input values in the EditTemplate's TextBoxes of the GridView? If yes——

    If yes, you can drag and drop the Customized Validation Control into EditTemplate and bind to the TextBox there, and do something like this:

    protected void CustomValidator1_ServerValidate(object source, ServerValidateEventArgs args)
       
    { 
            string ID = args.Value;  //Get the binded value from the textbox

             if(Id……)
               {

                 args.IsValid = false;

               }

            else

               {

                  args.IsValid = true;

               }
       
    }

    And in the Page_Load event, say this:

    Page.Validate();

    Thursday, May 5, 2011 11:27 PM
  • User732704340 posted

    Not exactly. I want to validate a user ID, which is saved in SQL database but I thought that maybe I could do that by using SqlDataSource, in which I just choose the column that I need. I'm not using GridView. I don't want to display anything but to validate users input and if it's correct redirect them to another page. So basically I've got on my page a TextBox a button and an SqlDataSource. Is is possible to do that with this three components? Maybe I should also mention that I use Visual Studio 2008.

    Friday, May 6, 2011 2:45 AM
  • User3866881 posted

    Hi again:)
    As far as I know, I think if you use SqlDataSource to query for a specific value, you'll have to change the SqlCommandText, this may effect the bound GridView. So I suggest you using another new instance like SqlCommand+SqlConnection or SqlDataAdapter.

    Thx

    Friday, May 6, 2011 2:57 AM
  • User-1011137159 posted

    One more thing I would like to add in button clcik event you have add one condtion Page.IsValid == true then only preform save operation else return.

    Friday, May 6, 2011 3:01 AM
  • User732704340 posted

    Decker Dong could you give me an example like the one in the previous post cos I'm really "green".

    Friday, May 6, 2011 3:07 AM
  • User3866881 posted

    OK, here we go:

    1) Drag and drop the Customized Valiation control and bind the validation control to the TextBox

    2) Try this:

    protected void CustomValidator1_ServerValidate(object source, ServerValidateEventArgs args)
    {

      SqlConnection con = new SqlConnection("……");

      SqlCommand cmd = new SqlCommand("select top 1 FieldName from TableName where ……",con);

      con.Open();

      string valueDB = cmd.ExecuteScaler().ToString();


    string ID = args.Value; //Get the binded value from the textbox

    if(Id……)       //Compare with valueDB
    {

    args.IsValid = false;

    }

    else

    {

    args.IsValid = true;

    }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 6, 2011 3:20 AM
  • User732704340 posted

    I typed in your code but I'm getting errors. I commented them in the code below:

     

    using System;
    using System.Collections;
    using System.Configuration;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Xml.Linq;
    
    public partial class TESTPAGE : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
    
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            if (!IsValid)
            {
                Response.Redirect("Default.aspx");
            }
    
            else
            {
                Response.Redirect("Login.aspx");
            }
        }
    
        protected void CustomValidator1_ServerValidate(object source, ServerValidateEventArgs args)
        {
            string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\YourDatabase.mdf;Integrated Security=True;User Instance=True";
            SqlConnection con = new SqlConnection(connectionString); //The type or namespace name "SqlConnection" could not be found (are you missing a using directive or an assembly reference?
    
            SqlCommand cmd = new SqlCommand("SELECT [Column] FROM [Table]",con); //The type or namespace name "SqlCommand" could not be found (are you missing a using directive or an assembly reference?
            con.Open();
    
            string valueDB = cmd.ExecuteScaler().ToString();
            string ID = args.Value;
    
            if (args.Value == valueDB)    //Compare with valueDB
            {
            args.IsValid = false;
            }
    
            else
            {
                args.IsValid = true;
            }
        }

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 6, 2011 3:51 AM
  • User3866881 posted

    You should add this:

    using System.Data;

    using System.Data.SqlClient;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 6, 2011 4:11 AM
  • User732704340 posted

    Ok, they seem to be ok but now I'm getting error for ExecuteScaler:

    'System.Data.SqlClient.SqlCommand' does not contain a definition for 'ExecuteScaler' and no extension method 'ExecuteScaler' accepting a first argument of type 'System.Data.SqlClient.SqlCommand' could be found (are you missing a using directive or an assembly reference?)

    Friday, May 6, 2011 4:17 AM
  • User3866881 posted

     string valueDB = cmd.ExecuteScalar().ToString();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 6, 2011 4:21 AM
  • User732704340 posted

    Decker Dong thanks a lot. It works perfectly. Smile I've been trying to do this for hours. Here I paste working code in case that someone needs it:

    using System;
    using System.Collections;
    using System.Configuration;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Xml.Linq;
    using System.Data.SqlClient;
    
    public partial class TESTPAGE : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
    
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            if (!IsValid)
            {
                Response.Redirect("Default.aspx");
            }
    
            else
            {
                Response.Redirect("Login.aspx");
            }
        }
    
        protected void CustomValidator1_ServerValidate(object source, ServerValidateEventArgs args)
        {
            string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\YourDatabase.mdf;Integrated Security=True;User Instance=True";
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("SELECT [Column] FROM [Table]",con);
            con.Open();
    
            string valueDB = cmd.ExecuteScalar().ToString();
            string ID = args.Value;
    
            if (args.Value == valueDB)
            {
            args.IsValid = false;
            }
    
            else
            {
                args.IsValid = true;
            }
        }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 6, 2011 4:35 AM